On Wed, May 06, 2009 at 09:38:59AM +0200, Dimitri wrote: > Hi, > > any idea if there is a more optimal execution plan possible for this query: > > select S.REF as stref, S.NAME as stnm, H.HORDER as hord, H.BEGIN_DATE as hbeg, > H.END_DATE as hend, H.NOTE as hnote > from HISTORY H, STAT S > where S.REF = H.REF_STAT > and H.REF_OBJECT = '0000000001' > order by H.HORDER ; > > EXPLAIN ANALYZE output on 8.4: > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------ > Sort (cost=4549.75..4555.76 rows=2404 width=176) (actual > time=1.341..1.343 rows=20 loops=1) > Sort Key: h.horder > Sort Method: quicksort Memory: 30kB > -> Hash Join (cost=33.50..4414.75 rows=2404 width=176) (actual > time=1.200..1.232 rows=20 loops=1) > Hash Cond: (h.ref_stat = s.ref) > -> Index Scan using history_ref_idx on history h > (cost=0.00..4348.20 rows=2404 width=135) (actual time=0.042..0.052 > rows=20 loops=1) > Index Cond: (ref_object = '0000000001'::bpchar) > -> Hash (cost=21.00..21.00 rows=1000 width=45) (actual > time=1.147..1.147 rows=1000 loops=1) > -> Seq Scan on stat s (cost=0.00..21.00 rows=1000 > width=45) (actual time=0.005..0.325 rows=1000 loops=1) > Total runtime: 1.442 ms > (10 rows) > > Table HISTORY contains 200M rows, only 20 needed > Table STAT contains 1000 rows, only 20 needed to be joined to HISTORY values. > > Table definitions: > """"""""""""""""""""""""""""""""""""""""""""""""""" > create table STAT > ( > REF CHAR(3) not null, > NAME CHAR(40) not null, > NUMB INT not null > ); > > create table HISTORY > ( > REF_OBJECT CHAR(10) not null, > HORDER INT not null, > REF_STAT CHAR(3) not null, > BEGIN_DATE CHAR(12) not null, > END_DATE CHAR(12) , > NOTE CHAR(100) > ); > > create unique index stat_ref_idx on STAT( ref ); > create index history_ref_idx on HISTORY( ref_object, horder ); > """"""""""""""""""""""""""""""""""""""""""""""""""" > > NOTE: The same query runs 2 times faster on MySQL. > > Any idea?.. > > Rgds, > -Dimitri > Dimitri, Is there any chance of profiling the postgres backend to see where the time is used? Just an idea, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
