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: [email protected] [[email protected]] On Behalf Of Tom Lane [[email protected]] Sent: Friday, February 20, 2009 7:33 AM To: Grzegorz Jaśkiewicz Cc: [email protected] Subject: Re: [PERFORM] not in(subselect) in 8.4 =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
