Re: [Tuning questions..]

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(at)echo(dot)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(at)postgresql(dot)org)

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