where not exists

From: "Llew" <postgres(at)lg(dot)ndirect(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: where not exists
Date: 2002-03-06 19:27:45
Message-ID: a65qji$2i6k$1@jupiter.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Dear everyone,
What is the best way of removing rows which are not in another table?

I have these two tables each with millions of rows/tuples.
They are joined on two fields:
CREATE TABLE a
(
join1 OID,
join2 OID,
--a fair number of other fields
.....
)
CREATE TABLE b
(
join1 OID,
join2 OID,
--a fair number of other fields
.....
)
There are indices on both of them (on "join1, join2").
At the moment, I am doing

1) get items which are orphaned in a.
CREATE TEMP TABLE orphans as
SELECT join1, join2
FROM a
WHERE NOT EXISTS
(
SELECT *
FROM b
WHERE
a.join1 = b.join1 AND
a.join2 = b.join2
)
2) DELETE FROM a where orphans.join1 = a.join1 and orphans.join2=a.join2
3) DROP TABLE orphans

This is very slow. Is there a better way?
Should I first copy all join1. join2 from a and b into temporary tables
first?
Do I need to index the temporary tables?
Surely this is a general enough a problem that optimal sets of solutions
exists in people's experience.
Thanks a lot.

Llew

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message W. McCloud 2002-03-06 19:31:31 createlang pltclu <databasename>
Previous Message Stephan Szabo 2002-03-06 19:23:34 Re: SELECT currval within a transaction