Postgres Pro
Downloads
Home   >   mailing lists

Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2 - Mailing list pgsql-performance

From Robert Haas
Subject Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2
Date
Msg-id AANLkTi=QzQXtLYkdatf1ginDXyJ=c-bOYhPKT766KB+u@mail.gmail.com
Whole thread Raw
In response to Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2  (Mladen Gogala <mladen.gogala@vmsinfo.com>)
Responses Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2
List pgsql-performance
Tree view 
On Fri, Jan 21, 2011 at 12:42 PM, Mladen Gogala
<mladen.gogala@vmsinfo.com> wrote:
> On 1/21/2011 12:09 PM, Robert Haas wrote:
>>
>> Looks like the bad selectivity estimate there is what's killing it.
>> Not sure I completely understand why 9.0.2 is coming up with such a
>> bad estimate, though.
>>
>
> I would recommend setting default_statistics_target to 1024 and effective
> cache size to 20480MB and see what happens.

I am starting to suspect that there is a bug in the join selectivity
logic in 9.0.  We've had a few complaints where the join was projected
to return more rows than the product of the inner side and outer side
of the join, which is clearly nonsense.  I read the function and I
don't see anything weird... and it clearly can't be too bad or we
would have had more complaints... but...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

pgsql-performance by date:

Previous
From: Robert Haas
Date:
Subject: Re: the XID question
Next
From: Mladen Gogala
Date:
Subject: Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2