Skip site navigation (1) Skip section navigation (2)

Any better plan for this query?..

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 (view raw, whole thread or download thread mbox)
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

Responses

pgsql-performance by date

Next:From: Craig RingerDate: 2009-05-06 08:01:03
Subject: Re: Any better plan for this query?..
Previous:From: Greg SmithDate: 2009-05-06 05:10:53
Subject: Re: Limit I/O bandwidth of a certain backend

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group