Re: not in(subselect) in 8.4

Scott Carey <scott(at)richrelevance(dot)com> writes:
> Are there any optimizations planned for the case where columns are
> defined as NOT NULL?

We might get around to recognizing that case as an antijoin sometime.
It's nontrivial though, because you have to check for an intermediate
outer join causing the column to be possibly nullable after all.

> It turns out to be a rare use case for someone to write a subselect
> for a NOT IN or IN clause that will have NULL values.

Judging from the steady flow of "why doesn't my NOT IN query work"
newbie questions, I don't think it's so rare as all that.

There's surely some population of people who know enough or could be
trained to be careful about using NOT NULL columns, but they could also
be trained to use NOT EXISTS, and dodge the whole bullet from the start.

regards, tom lane