Re: not in(subselect) in 8.4

Are there any optimizations planned for the case where columns are defined as NOT NULL? Or other special path filtering for cases where the planner can know that the set of values in the subselect won't contain NULLs (such as in (select a from b where (a > 0 and a < 10000).

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. In the common case, the subselect does not contain nulls. I would like to see Postgres optimize for the common case.

________________________________________
From: pgsql-performance-owner(at)postgresql(dot)org [pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, February 20, 2009 7:33 AM
To: Grzegorz Jaśkiewicz
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] not in(subselect) in 8.4

=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= <gryzman(at)gmail(dot)com> writes:
> I mean query like:
> select id from foo where id not in ( select id from bar);
> into:
> select f.id from foo f left join bar b on f.id=b.id where b.id is null;

Postgres does not do that, because they don't mean the same thing ---
the behavior for NULLs in bar.id is different.

8.4 does understand that NOT EXISTS is an antijoin, though.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance