Re: Change query join order

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kaloyan Iliev Iliev <kaloyan(at)digsys(dot)bg>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Change query join order
Date: 2010-01-08 19:01:18
Message-ID: 603c8f071001081101t5c7b26fci407edd8523a7f4a@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-01-08 19:23:50 Re: Change query join order
Previous Message Tom Lane 2010-01-08 18:27:03 Re: Change query join order