Re: Optimizing NOT IN plans / verify rewrite - Mailing list pgsql-performance

Maciek Sakrejda <msakrejda@truviso.com> wrote:

> DELETE FROM foo WHERE type = 'o' AND b NOT IN (SELECT cqc.b FROM
> bar cqc) AND b NOT IN (SELECT car.b FROM foo car WHERE car.type !=
> 'o');

Can "b" be null in any of these tables?  If not, then you can
rewrite your query to us NOT EXISTS and have the same semantics.
That will often be much faster.  Something like:

DELETE FROM foo
  WHERE type = 'o'
    AND NOT EXISTS (SELECT * FROM bar cqc where cqc.b = foo.b)
    AND NOT EXISTS (SELECT * FROM foo car WHERE car.b = foo.b
                    AND car.type <> 'o');

-Kevin