pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dag Arne Matre <dag-arne@matreweb.com.antispam>
To: pgsql-sql@postgresql.org
Subject: Re: where not exists
Date: Mon, 11 Mar 2002 11:54:44 +0100
Message-ID: <a6i2cb$129h$1@jupiter.hub.org> (raw)
References: <a65qm1$2k6g$1@jupiter.hub.org>

Sorry for popping in a bit late here, but you could try this:

1) get items which are orphaned in a.
CREATE TEMP TABLE orphans as
    SELECT a.join1, a.join2
        FROM a LEFT OUTER JOIN b ON a.join1 = b.join1 AND a.join2 = b.join2
        WHERE b.join1 IS NULL AND b.join2 IS NULL

D A


"Llew" <leo.goodstadt@anat.ox.ac.uk> wrote in message
news:a65qm1$2k6g$1@jupiter.hub.org...
> 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: <a6i2cb$129h$1@jupiter.hub.org>
Permalink:  ../a6i2cb$129h$1@jupiter.hub.org/
Also on:    postgresql.org/message-id/a6i2cb$129h$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: dag-arne@matreweb.com.antispam
  Subject: Re: where not exists
  In-Reply-To: <a6i2cb$129h$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