Re: Change query join order

On Fri, Jan 8, 2010 at 1:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kaloyan Iliev Iliev <kaloyan(at)digsys(dot)bg> writes:
>> My question is why the planner didn't do the index scan first on ms_data
>> to reduce the rows to ~ 11000 and the use the PK index on
>> ms_commands_history.
>
> 11000 index probes aren't exactly free.  If they take more than about
> 1msec apiece, the planner picked the right plan.

The OP could try setting enable_hashjoin to false (just for testing,
never for production) and do EXPLAIN ANALYZE again. That might
generate the desired plan, and we could see which one is actually
faster.

If the other plan does turn out to be faster (and I agree with Tom
that there is no guarantee of that), then one thing to check is
whether seq_page_cost and random_page_cost are set too high. If the
data is all cached, the default values of 4 and 1 are three orders of
magnitude too large, and they should also be set to equal rather than
unequal values.

...Robert