Kevin Traster <kevin@mffais.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!