Re: 8.4 optimization regression?

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