Re: "not in" clause too slow?

On Fri, Sep 21, 2007 at 12:09:50PM +0200, Ottavio Campana wrote:
> 2) how can I speed it up? by using indexes? or by changing the query?

Note that NOT IN cannot be optimised in the same way as NOT EXISTS due
to the different ways they handle NULL. In particular if the subquery
of the NOT IN produces a NULL *anywhere* it will always return FALSE,
hence it often needs to scan the entire subquery even when an index
might be better. You might know this cannot happen, but postgres can't
always tell. NOT EXISTS doesn't have this problem.

Blame the SQL standard if you like.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.