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

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