pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Llew Sion Goodstadt <leo.goodstadt@human-anatomy.oxford.ac.uk>
To: pgsql-sql@postgresql.org
Subject: Re: where not exists
Date: Fri, 15 Mar 2002 12:46:17 -0000
Message-ID: <004801c1cc1f$663c39c0$1c1d01a3@FGU028> (raw)
In-Reply-To: <a6i2cb$129h$1@jupiter.hub.org>

I ended up by using an external programme.
NOT EXISTS is just a set difference.
Doing set compares is really quick if both sets are sorted.
I use CRC64s for the data and just compare the resulting sorted sets of
(large CRC 64-bit) numbers.
Because everything hashes to a number, the memory requirements are not
that bad either (8 bytes per item ~256000 tuples per Mb).
The programme is in C++ but is as fast in something like Perl.
I.e. comparing millions of rows of data takes 10s of seconds rather than
10s of minutes.


Leo

> 
> 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?




view thread (7+ messages)

Message-ID: <004801c1cc1f$663c39c0$1c1d01a3@FGU028>
Permalink:  ../004801c1cc1f$663c39c0$1c1d01a3@FGU028/
Also on:    postgresql.org/message-id/004801c1cc1f$663c39c0$1c1d01a3@FGU028

 · 

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: leo.goodstadt@human-anatomy.oxford.ac.uk
  Subject: Re: where not exists
  In-Reply-To: <004801c1cc1f$663c39c0$1c1d01a3@FGU028>

* 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