pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
where not exists
7+ messages / 6 participants
[nested] [flat]

* where not exists
@ 2002-03-06 19:27  Llew <postgres@lg.ndirect.co.uk>
  0 siblings, 1 reply; 7+ messages in thread

From: Llew @ 2002-03-06 19:27 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: where not exists
@ 2002-03-06 20:25  Josh Berkus <josh@agliodbs.com>
  parent: Llew <postgres@lg.ndirect.co.uk>
  0 siblings, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2002-03-06 20:25 UTC (permalink / raw)
  To: Llew <postgres@lg.ndirect.co.uk>; pgsql-sql

Llew,

Do it all in one query:

> DELETE FROM a
>         WHERE NOT EXISTS
>         (
>             SELECT b.join1
>                 FROM b
>                 WHERE
>                 a.join1 = b.join1 AND
>                 a.join2 = b.join2
>         )

And make sure that join1 and join2 are indexed in both tables.

-Josh Berkus



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: where not exists
@ 2002-03-06 20:29  Jie Liang <jie@stbernard.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Jie Liang @ 2002-03-06 20:29 UTC (permalink / raw)
  To: Llew <postgres@lg.ndirect.co.uk>; pgsql-sql

set operation especially NOT EXIST is very slow(for big table),
I recommand you use a few queries for your propose:
1. select * into c from a where join1=b.join1 and join2=b.join2;
2. truncate table a;
3. insert into a select * from c;
4. drop table c;

You don't need index c since you use full table scan anyway.

Jie Liang

-----Original Message-----
From: Llew [mailto:postgres@lg.ndirect.co.uk]
Sent: Wednesday, March 06, 2002 11:28 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] where not exists


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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: where not exists
@ 2002-03-06 20:54  Llew <leo.goodstadt@anat.ox.ac.uk>
  parent: Jie Liang <jie@stbernard.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Llew @ 2002-03-06 20:54 UTC (permalink / raw)
  To: pgsql-sql

Dear Jie Liang,
The common case is that there will only be a few or no orphans. Won't
recreateing and reindicing this massive table (table a) take ages and known
my db out of commission for everyone else? One of the reasons why I do
things is two steps is that this allows me archive the deleted items. This
is, alas, a common (read daily) operation / query.
Leo

"Jie Liang" <jie@stbernard.com> wrote in message
news:7C760DAA511DC74B99E7D22189F786F1906D98@MAIL01.stbernard.com...
> set operation especially NOT EXIST is very slow(for big table),
> I recommand you use a few queries for your propose:
> 1. select * into c from a where join1=b.join1 and join2=b.join2;
> 2. truncate table a;
> 3. insert into a select * from c;
> 4. drop table c;
>
> You don't need index c since you use full table scan anyway.
>
> 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?






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: where not exists
@ 2002-03-06 21:14  Llew <leo.goodstadt@anat.ox.ac.uk>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Llew @ 2002-03-06 21:14 UTC (permalink / raw)
  To: pgsql-sql

Dear Josh,
The reason why I do things is two steps is that this allows me archive the
deleted items.
Llew

""Josh Berkus"" <josh@agliodbs.com> wrote in message
news:web-819049@davinci.ethosmedia.com...
> Llew,
>
> Do it all in one query:
>
> > DELETE FROM a
> >         WHERE NOT EXISTS
> >         (
> >             SELECT b.join1
> >                 FROM b
> >                 WHERE
> >                 a.join1 = b.join1 AND
> >                 a.join2 = b.join2
> >         )
>
> And make sure that join1 and join2 are indexed in both tables.
>
> -Josh Berkus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: where not exists
@ 2002-03-11 10:54  Dag Arne Matre <dag-arne@matreweb.com.antispam>
  0 siblings, 1 reply; 7+ messages in thread

From: Dag Arne Matre @ 2002-03-11 10:54 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: where not exists
@ 2002-03-15 12:46  Llew Sion Goodstadt <leo.goodstadt@human-anatomy.oxford.ac.uk>
  parent: Dag Arne Matre <dag-arne@matreweb.com.antispam>
  0 siblings, 0 replies; 7+ messages in thread

From: Llew Sion Goodstadt @ 2002-03-15 12:46 UTC (permalink / raw)
  To: pgsql-sql

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?




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2002-03-15 12:46 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-06 19:27 where not exists Llew <postgres@lg.ndirect.co.uk>
2002-03-06 20:25 ` Josh Berkus <josh@agliodbs.com>
2002-03-06 21:14   ` Llew <leo.goodstadt@anat.ox.ac.uk>
2002-03-06 20:29 Re: where not exists Jie Liang <jie@stbernard.com>
2002-03-06 20:54 ` Llew <leo.goodstadt@anat.ox.ac.uk>
2002-03-11 10:54 Re: where not exists Dag Arne Matre <dag-arne@matreweb.com.antispam>
2002-03-15 12:46 ` Llew Sion Goodstadt <leo.goodstadt@human-anatomy.oxford.ac.uk>

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