To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: pgsql-performance@postgresql.org
Subject: Re: Planner reluctant to start from subquery 
In-reply-to: <43E0BEDD.EE98.0025.0@wicourts.gov> 
References: 
 <43E0AA7E.EE98.0025.0@wicourts.gov><43E0AA7E.EE98.0025.0@wicourts.gov>
	<3759.1138822464@sss.pgh.pa.us> <43E0BEDD.EE98.0025.0@wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Wed, 01 Feb 2006 13:59:57 -0600"
Date: Wed, 01 Feb 2006 15:14:45 -0500
Message-ID: <4218.1138824885@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote: 
>> ... expected an equivalent IN clause to work better.  In fact, I'm not
>> clear why the planner isn't finding the cheapest plan (which it does
>> estimate as cheapest) from the IN version you posted.

> All I know is that trying various permutations, I saw it pick a good
> plan for the IN format when I eliminated the last outer join in the FROM
> clause.  I know it isn't conclusive, but it was a correlation which
> suggested a possible causality to me.

But there is still an outer join in your third example (the one with the
best plan), so that doesn't seem to hold water.  In any case, the way
that IN planning works these days it really should have considered the
plan equivalent to your JOIN-against-GROUP-BY variant.

I'm interested to poke at this ... are you in a position to provide a
test case?

			regards, tom lane