From: | Dimitri <dimitrik(dot)fr(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Any better plan for this query?.. |
Date: | 2009-05-06 07:38:59 |
Message-ID: | 5482c80a0905060038o34d0b705g834efd532c80d5fa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2009-05-06 08:01:03 | Re: Any better plan for this query?.. |
Previous Message | Greg Smith | 2009-05-06 05:10:53 | Re: Limit I/O bandwidth of a certain backend |