Re: Any better plan for this query?..

On Wed, May 6, 2009 at 3:38 AM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> 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.

couple of things to try:
*) as others have noted, get rid of char() columns. use varchar, or
int if you can. this is a bigger deal in postgres than mysql.
*) curious if disabling sequential scan helps (set enable_seqscan =
false) or changes the plan. .3 msec is spent on seq scan and an index
lookup is likely much faster.
*) prepare the query:

prepare history_stat(char(10) as
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 = $1
order by H.HORDER ;

execute history_stat('0000000001');

(prepared queries have some annoyances you need to be prepared to
deal with. however, they are quite useful when squeezing every last
msec out of fast queries).

merlin