From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
Cc: pgsql-performance@postgresql.org
Subject: Re: Change query join order
Date: Fri, 08 Jan 2010 14:23:50 -0500
Message-ID: <10916.1262978630@sss.pgh.pa.us> (raw)
In-Reply-To: <603c8f071001081101t5c7b26fci407edd8523a7f4a@mail.gmail.com>
References: <4B47722A.4010502@digsys.bg>
<9969.1262975223@sss.pgh.pa.us>
<603c8f071001081101t5c7b26fci407edd8523a7f4a@mail.gmail.com>
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Jan 8, 2010 at 1:27 PM, Tom Lane <tgl@sss.pgh.pa.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
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: tgl@sss.pgh.pa.us, robertmhaas@gmail.com, kaloyan@digsys.bg
Subject: Re: Change query join order
In-Reply-To: <10916.1262978630@sss.pgh.pa.us>
* 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