On Thu, May 7, 2009 at 4:20 AM, Dimitri <dimitrik.fr@gmail.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