From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
Cc: pgsql-performance@postgresql.org
Subject: Re: Change query join order
Date: Fri, 8 Jan 2010 14:01:18 -0500
Message-ID: <603c8f071001081101t5c7b26fci407edd8523a7f4a@mail.gmail.com> (raw)
In-Reply-To: <9969.1262975223@sss.pgh.pa.us>
References: <4B47722A.4010502@digsys.bg>
<9969.1262975223@sss.pgh.pa.us>
On Fri, Jan 8, 2010 at 1:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kaloyan Iliev Iliev <kaloyan@digsys.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, tgl@sss.pgh.pa.us, kaloyan@digsys.bg
Subject: Re: Change query join order
In-Reply-To: <603c8f071001081101t5c7b26fci407edd8523a7f4a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox