Re: Any better plan for this query?..

On Thu, May 7, 2009 at 4:20 AM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
> Hi Simon,
>
> may you explain why REINDEX may help here?.. - database was just
> created, data loaded, and then indexes were created + analyzed.. What
> may change here after REINDEX?..
>
> With hashjoin disabled was a good try!
> Running this query "as it" from 1.50ms we move to 0.84ms now,
> and the plan is here:
>
>                                                                      QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------
>  Sort  (cost=4562.83..4568.66 rows=2329 width=176) (actual
> time=0.225..0.229 rows=20 loops=1)
>   Sort Key: h.horder
>   Sort Method:  quicksort  Memory: 30kB
>   ->  Merge Join  (cost=4345.89..4432.58 rows=2329 width=176) (actual
> time=0.056..0.205 rows=20 loops=1)
>         Merge Cond: (s.ref = h.ref_stat)
>         ->  Index Scan using stat_ref_idx on stat s
> (cost=0.00..49.25 rows=1000 width=45) (actual time=0.012..0.079
> rows=193 loops=1)
>         ->  Sort  (cost=4345.89..4351.72 rows=2329 width=135) (actual
> time=0.041..0.043 rows=20 loops=1)
>               Sort Key: h.ref_stat
>               Sort Method:  quicksort  Memory: 30kB
>               ->  Index Scan using history_ref_idx on history h
> (cost=0.00..4215.64 rows=2329 width=135) (actual time=0.013..0.024
> rows=20 loops=1)
>                     Index Cond: (ref_object = '0000000001'::bpchar)
>  Total runtime: 0.261 ms
> (12 rows)
>
> Curiously planner expect to run it in 0.26ms
>
> Any idea why planner is not choosing this plan from the beginning?..
> Any way to keep this plan without having a global or per sessions
> hashjoin disabled?..

can you work prepared statements into your app? turn off hash join,
prepare the query, then turn it back on.

merlin