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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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