From: Jie Liang <jie@stbernard.com>
To: Llew <postgres@lg.ndirect.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: where not exists
Date: Wed, 6 Mar 2002 12:29:33 -0800
Message-ID: <7C760DAA511DC74B99E7D22189F786F1906D98@MAIL01.stbernard.com> (raw)
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
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: jie@stbernard.com, postgres@lg.ndirect.co.uk
Subject: Re: where not exists
In-Reply-To: <7C760DAA511DC74B99E7D22189F786F1906D98@MAIL01.stbernard.com>
* 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