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

Re: DELETE using an outer join

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: DELETE using an outer join
Date: 2012-07-19 14:52:34
Message-ID: 21289.1342709554@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
> Lately I had some queries of the form:

>     select t.*
>     from some_table t
>     where t.id not in (select some_id from some_other_table);

> I could improve the performance of them drastically by changing the NOT NULL into an outer join:

>     select t.*
>     from some_table t
>        left join some_other_table ot on ot.id = t.id
>     where ot.id is null;

If you're using a reasonably recent version of PG, replacing the NOT IN
by a NOT EXISTS test should also help.

> Now I was wondering if a DELETE statement could be rewritten with the same "strategy":

Not at the moment.  There have been discussions of allowing the same
table name to be respecified in USING, but there are complications.

			regards, tom lane

In response to

Responses

pgsql-sql by date

Next:From: Thomas KellererDate: 2012-07-20 08:17:27
Subject: Re: DELETE using an outer join
Previous:From: Sergey KonoplevDate: 2012-07-19 14:33:45
Subject: Re: DELETE using an outer join

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