Re: Any better plan for this query?..

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?..

Rgds,
-Dimitri

On 5/6/09, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Wed, 2009-05-06 at 10:31 +0200, Dimitri wrote:
>
>> I've already tried a target 1000 and the only thing it changes
>> comparing to the current 100 (default) is instead of 2404 rows it says
>> 240 rows, but the plan remaining the same..
>
> Try both of these things
> * REINDEX on the index being used in the query, then re-EXPLAIN
> * enable_hashjoin = off, then re-EXPLAIN
>
> You should first attempt to get the same plan, then confirm it really is
> faster before we worry why the optimizer hadn't picked that plan.
>
> We already know that MySQL favors nested loop joins, so turning up a
> plan that on this occasion is actually better that way is in no way
> representative of general performance. Does MySQL support hash joins?
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>