Skip site navigation (1) Skip section navigation (2)

Re: not in(subselect) in 8.4

From: Scott Carey <scott(at)richrelevance(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, =?iso-8859-2?Q?Grzegorz_Ja=B6kiewicz?=<gryzman(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: not in(subselect) in 8.4
Date: 2009-02-21 19:29:10
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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 from foo f left join bar b on where is null;

Postgres does not do that, because they don't mean the same thing ---
the behavior for NULLs in 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:

In response to


pgsql-performance by date

Next:From: Jonah H. HarrisDate: 2009-02-22 02:04:49
Subject: Re: Benchmark comparing PostgreSQL, MySQL and Oracle
Previous:From: Tom LaneDate: 2009-02-21 17:42:50
Subject: Re: not in(subselect) in 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group