Re: eliminating records not in (select id ... so SLOW? - Mailing list pgsql-general

From Daniel Verite
Subject Re: eliminating records not in (select id ... so SLOW?
Date
Msg-id df6405c9-c07e-4a72-ba4c-caa66b1756d1@mm
Whole thread Raw
In response to Re: eliminating records not in (select id ... so SLOW?  (Ivan Sergio Borgonovo <mail@webthatworks.it>)
List pgsql-general
    Ivan Sergio Borgonovo wrote:

> But what if I *really* had to execute that query?
> Any other magic I could play to speed it up?

A trick that is sometimes spectacularly efficient is to rewrite the
query to use an outer join instead of NOT IN.

Try:

DELETE FROM table1 WHERE id IN
 (SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL)


 Best regards,
--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

pgsql-general by date:

Previous
From: Ivan Sergio Borgonovo
Date:
Subject: function definition and "entity"
Next
From: Marc Cuypers
Date:
Subject: Re: Postgresql not using an index