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

Re: BUG #7509: x NOT IN (select x from z) extremely slow in compare to select x from y except select x from z;

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: stefan(at)konink(dot)de
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #7509: x NOT IN (select x from z) extremely slow in compare to select x from y except select x from z;
Date: 2012-08-29 15:20:44
Message-ID: 10470.1346253644@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
stefan(at)konink(dot)de writes:
> The following is relatively fast:
> bag-2012-aug=# explain select count(*) from (select kvk from kvk_normal
> except select kvk from bag_kvk) as x;

> The 'normal' case basically doesn't finish:
> bag-2012-aug=# explain select count(*) from (select kvk_normal.kvk from
> kvk_normal where kvk_normal.kvk not in (select bag_kvk.kvk from bag_kvk)) as
> x;

NOT IN is difficult to optimize, as well as hard to use, because of its
rather bizarre behavior for nulls.  You might consider using NOT EXISTS
instead.

			regards, tom lane


In response to

pgsql-bugs by date

Next:From: Stuart BishopDate: 2012-08-29 15:32:31
Subject: Re: BUG #7500: hot-standby replica crash after an initial rsync
Previous:From: Tom LaneDate: 2012-08-29 15:11:52
Subject: Re: hot standby lagging vs warm that is up-to-date

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