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: BDFBB77C9E07BE4A984DAAE981D19F961AE363D982@EXVMBX018-1.exch018.msoutlookonline.net (view raw or flat)
Thread:
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 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

In response to

Responses

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-2014 The PostgreSQL Global Development Group