To: Robert Haas <robertmhaas@gmail.com>
cc: Kaloyan Iliev Iliev <kaloyan@digsys.bg>, pgsql-performance@postgresql.org
Subject: Re: Change query join order 
In-reply-to: <603c8f071001081101t5c7b26fci407edd8523a7f4a@mail.gmail.com> 
References: <4B47722A.4010502@digsys.bg> <9969.1262975223@sss.pgh.pa.us>
	<603c8f071001081101t5c7b26fci407edd8523a7f4a@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Fri, 08 Jan 2010 14:01:18 -0500"
Date: Fri, 08 Jan 2010 14:23:50 -0500
Message-ID: <10916.1262978630@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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