Re: [Tuning questions..] - Mailing list pgsql-admin

Not sure about tuning, but it seems to me that this query would be much more
effective if it's rewritten like this (especially if style_id columns on both
tables are indexed):

SELECT count(DISTINCT song_id) AS X
FROM ssa_candidate SC JOIN station_subgenre SS ON SC.style_id = SS.style_id
WHERE SS.station_id = 48

Please correct me if I'm wrong. Also, Michael, if you give this one a try,
could you send me the query plan, I'm just curious.



"Michael T. Halligan" <michael@echo.com> wrote:
> Hi.. I seem to be running into a bottle neck on a query, and I'm not
> sure what the bottleneck is .
> The machine is a dual-processor p3 750 with 2 gigs of (pc100) memory,
> and 3 72 gig disks setup
> in raid 5. Right now i'm just testing our db for speed (we're porting
> from oracle) .. later on
> We're looking @ a quad xeon 700 with 16 gigs of ram & 10 drives in
> hardware raid 5.
>
> We've tuned the queries a bit, added some indices, and we got this query
> down from about 15 minutes
> to 7.6 seconds.. but it just seems like we should be able to get this
> query down to under a second on
> this box..  It's running the latest suse, with 2.4.16 kernel, reiserfs,
> postgres 7.2b3. I've tried many different combinations
> of buffers, stat collection space, sort space, etc. none of them really
> effect performance..
>
> When I run this particular query, the only resource that seems to change
> is one of the processors gets up to
> about 99% usage.. I've tried setting postgres to use up to 1.6 gigs of
> memory, but the postmaster never seems
> to get above about 700megs.. it's not swapping at all, though the
> contact switching seems to get a bit high (peaking
> at 150) ..
>
> The query sorts through about 80k rows.. here's the query
> --------------------------------------------------
> SELECT count(*) FROM (
>                   SELECT DISTINCT song_id FROM ssa_candidate WHERE
> style_id IN (
>                              SELECT style_id FROM station_subgenre WHERE
> station_id = 48
>                                             )
>                             ) AS X;
> --------------------------------------------------
> and the query plan :
> --------------------------------------------------
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=12236300.87..12236300.87 rows=1 width=13)
>   ->  Subquery Scan x  (cost=12236163.64..12236288.40 rows=4990 width=13)
>         ->  Unique  (cost=12236163.64..12236288.40 rows=4990 width=13)
>               ->  Sort  (cost=12236163.64..12236163.64 rows=49902 width=13)
>                     ->  Seq Scan on ssa_candidate
> (cost=0.00..12232269.54 rows=49902 width=13)
>                           SubPlan
>                             ->  Materialize  (cost=122.53..122.53
> rows=31 width=11)
>                                   ->  Index Scan using
> station_subgenre_pk on station_subgenre  (cost=0.00..122.53 rows=31
> width=11)
>
> EXPLAIN
> --------------------------------------------------
>
>
> If anybody has any ideas, I'd be really appreciative..
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


____________________________________________________________________
Get free e-mail and a permanent address at http://www.amexmail.com/?A=1