Re: [Tuning questions..]

Thanks, that sped things up a bit, from 7.6 sec. to about 5.5 sec.  However
the plan still includes a sequential scan on ssa_candidate:

Aggregate  (cost=12161.11..12161.11 rows=1 width=35)
   -> Merge Join  (cost=11611.57..12111.12 rows=19996 width=35)
         -> Sort  (cost=11488.27..11488.27 rows=99805 width=24)
               -> Seq Scan on ssa_candidate sc  (cost=0.00..3201.05
rows=99805 width=24)
         -> Sort  (cost=123.30..123.30 rows=31 width=11)
               -> Index Scan using station_subgenre_pk on station_subgenre
ss  (cost=0.00..122.53 rows=31 width=11)


If we run the same query on Oracle (modified slightly for syntax):

SELECT count(DISTINCT song_id) AS X
FROM ssa_candidate SC,
  station_subgenre SS
WHERE SC.style_id = SS.style_id
   AND SS.station_id =

with the same data, it runs almost instaneously.  Oracle's plan uses a hash
join:

SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=1 Bytes=15)
   SORT (GROUP BY)
     HASH JOIN (Cost=63 Card=8943 Bytes=134145)
       INDEX (RANGE SCAN) OF STATION_SUBGENRE_PK (UNIQUE) (Cost=3 Card=12
Bytes=84)
       TABLE ACCESS (FULL) OF SSA_CANDIDATE (Cost=59 Card=60364
Bytes=482912)

Is there some way to convince PostgreSQL to use a hash join?

Bojan Belovic wrote:

 >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.
 >
 >SELECT count(DISTINCT song_id) FROM ssa_candidate, station_subgenre
 >WHERE
 >
 >SELECT count(DISTINCT song_id) FROM ssa_candidate, station_subgenre
 >WHERE "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)SELECT count(DISTINCT song_id) FROM ssa_candidate, station_subgenre
 >>WHERE
 >>              ->  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
 >
 >---------------------------(end of broadcast)---------------------------
 >TIP 3: if posting/reading through Usenet, please send an appropriate
 >subscribe-nomail command to majordomo@postgresql.org so that your
 >message can get through to the mailing list cleanly
 >