Message-ID: <4E5C14FF.8060909@catalyst.net.nz>
Date: Tue, 30 Aug 2011 10:38:55 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: PGSQL Performance <pgsql-performance@postgresql.org>, =?UTF-8?B?R3J6ZQ==?=
	=?UTF-8?B?Z29yeiBKYcWba2lld2ljeg==?= <gryzman@gmail.com>
Subject: Re: 8.4 optimization regression?
References: <4E5331FF.3000900@catalyst.net.nz>
 <15708.1314155740@sss.pgh.pa.us>
	<4E548A7D.6090003@catalyst.net.nz>
In-Reply-To: <4E548A7D.6090003@catalyst.net.nz>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 24/08/11 17:22, Mark Kirkwood wrote:
> On 24/08/11 15:15, Tom Lane wrote:
>>
>> Hmmm ... this is structurally a pretty simple query, so I'm surprised
>> that 8.3 and 8.4 see it very much differently.  The relation-level
>> estimates and plan choices are very nearly the same; the only thing
>> that's changed much is the estimates of the join sizes, and there were
>> not that many changes in the join selectivity estimation for simple
>> inner joins.  I wonder whether you are seeing a bad side-effect of this
>> patch:
>>
>> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=7f3eba30 
>>
>>
>
> Here is what the plan looks like with that patch reversed (it is back 
> to 8.3 speed too).
>
> QUERY PLAN  8.4 - 7f3eba30  (better plan snipped)
>
>

I note from the commit message that the fix test case was from Grzegorz 
Jaskiewicz (antijoin against a small subset of a relation).  I was not 
able to find this in the archives - Grzegorz do you recall the actual 
test case? I thought it might be useful for me to spend some time 
studying both cases and seeing if I can come up with any tweaks that 
would let both your and my queries work well!

Cheers

Mark