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

Re: NOT IN >2hrs vs EXCEPT < 2 sec.

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: Kevin Traster <kevin(at)mffais(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: NOT IN >2hrs vs EXCEPT < 2 sec.
Date: 2009-01-29 13:00:16
Message-ID: 87d4e6qnbz.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
Kevin Traster <kevin(at)mffais(dot)com> writes:

> Regarding the previous posts about the same issues of PERFORMENCE between
> NOT IN versus EXCEPT. There has not been any answer to explain it - just
> talk about the differenences between the two results.
>
> Yes, I can still get the results using EXCEPT but it would be nice to no why
> I can't get NOT IN to complete the simple query.


There are two answers here. One you've already been given, that NOT IN has to
handle NULLs specially and that makes these plans not equivalent. The NOT IN
is decidedly harder to solve.

The other answer is that EXCEPT is a set operation which in Postgres uses a
completely different set of logic. Even if you used NOT EXISTS which really is
equivalent to EXCEPT the resulting plans would be different. Which one would
be better would depend on the circumstances. In an ideal world every
equivalent query would generate identical plans. We don't live in an ideal
world and Postgres isn't perfect.


-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2009-01-29 14:56:41
Subject: Re: NOT IN >2hrs vs EXCEPT < 2 sec.
Previous:From: Kevin TrasterDate: 2009-01-29 07:56:15
Subject: Re: NOT IN >2hrs vs EXCEPT < 2 sec.

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