From: Віталій Тимчишин <tivv00@gmail.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL NOT IN performance
Date: Wed, 19 Nov 2008 16:55:18 +0200
Message-ID: <331e40660811190655xccd1752rcb1ac7769e5cc8a4@mail.gmail.com> (raw)
In-Reply-To: <20081119062806.Y58658@megazone.bigpanda.com>
References: <331e40660811190351n6bde6caic5fd8cc3a0f777d2@mail.gmail.com>
<20081119062806.Y58658@megazone.bigpanda.com>
2008/11/19 Stephan Szabo <sszabo@megazone.bigpanda.com>
>
> On Wed, 19 Nov 2008, [ISO-8859-5] Віталій Тимчишин wrote:
>
> > Query 1:
> > select * from t1 where id not in (select id from t2);
> >
> > Query 2 (gives same result as Q1):
> > select * from t1 except all (select id from t2);
>
> It gives the same result as long as no nulls are in either table. If
> either table can have a null, the conversion changes the results.
>
> In addition, a conversion like the above only happens to work because t1
> only has an id column. If t1 had two columns you'd get an error because
> the two sides of except all must have the same number of columns.
>
Actually It can be done even for multi-column mode if the selection is done
on unique key. It would look like:
select * from t1 inner join (
select id from t1 except select id from t2) talias on t1.id = talias.id
And it would produce better results then "not in" for large counts in t1 and
t2.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, sszabo@megazone.bigpanda.com
Subject: Re: PostgreSQL NOT IN performance
In-Reply-To: <331e40660811190655xccd1752rcb1ac7769e5cc8a4@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox