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..
