pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Llew <postgres@lg.ndirect.co.uk>
To: pgsql-sql@postgresql.org
Subject: where not exists
Date: Wed, 6 Mar 2002 19:27:45 -0000
Message-ID: <a65qji$2i6k$1@jupiter.hub.org> (raw)

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





view thread (7+ messages)  latest in thread

Message-ID: <a65qji$2i6k$1@jupiter.hub.org>
Permalink:  ../a65qji$2i6k$1@jupiter.hub.org/
Also on:    postgresql.org/message-id/a65qji$2i6k$1@jupiter.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: postgres@lg.ndirect.co.uk
  Subject: Re: where not exists
  In-Reply-To: <a65qji$2i6k$1@jupiter.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox