Skip site navigation (1) Skip section navigation (2)

Re: 8.4 optimization regression?

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PGSQL Performance <pgsql-performance(at)postgresql(dot)org>, Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
Subject: Re: 8.4 optimization regression?
Date: 2011-08-29 22:38:55
Message-ID: 4E5C14FF.8060909@catalyst.net.nz (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Szymon KosokDate: 2011-08-30 05:36:20
Subject: Query optimization help
Previous:From: Tomas VondraDate: 2011-08-29 20:38:06
Subject: Re: Performance with many updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group