Re: Postgres NOT IN vs NOT EXISTS optimization

"Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)com> writes:
> Is Postgres able to drive the query the same way with the NOT IN as the
> NOT EXISTS is doing or is that only available if the query has a NOT
> EXISTS?

NOT IN is not optimized very well in PG, because of the strange
semantics that the SQL spec demands when the sub-query produces any
null values. There's been some interest in detecting cases where
we can prove that the subquery produces no nulls and then optimizing
it into NOT EXISTS, but it seems like a lot of work for not-great
return, so nothing's happened (yet). Perhaps Oracle does something
like that already, or perhaps they're just ignoring the semantics
problem; they do not have a reputation for hewing closely to the
spec on behavior regarding nulls.

regards, tom lane