Re: Change query join order

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jan 8, 2010 at 1:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

Right, sorry for the overly brief response. It might switch to a merge
join next, in which case try enable_mergejoin = off as well.

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

Tweaking the cost parameters to suit your local situation is the
recommended cure for planner misjudgments; but I'd recommend against
changing them on the basis of only one example. You could easily
find yourself making other cases worse. Get a collection of common
queries for your app and look at the overall effects.

regards, tom lane