| From: | "postgresql" <postgres(at)lg(dot)ndirect(dot)co(dot)uk> | 
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org | 
| Subject: | efficient deletes on subqueries | 
| Date: | 2001-10-30 15:33:05 | 
| Message-ID: | 9rmh7p$1e89$1@news.tht.net | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-sql | 
I have a very large table with > 1 million entries and I wish to delete rows
which match any entries in a second table.
What is the most efficient way of doing this?
CREATE TABLE AA (keyA integer NOT NULL, info TEXT);
CREATE TABLE obsolete_AA_entries (keyA integer NOT NULL);
I want to remove all entries from AA where keyA matches that from
obsolete_AA_entries,
i.e. SELECT * FROM AA NATURAL JOIN obsolete_AA_entries;
Both of the tables are UNIQUE indiced on keyA.
DELETE FROM AA WHERE EXISTS(
          SELECT * from obsolete_AA_entries o where AA.keyA = o.keyA);
seems to be faster than
DELETE FROM AA where (keyA) in (SELECT * from obsolete_AA_entries);
However, both are sequentially going through AA which is huge rather than
looking up values one by one from obsolete_AA_entries which is small.
How do I persuade the database to change its query strategy?
Thanks a lot
Llew
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2001-10-30 16:48:33 | Re: efficient deletes on subqueries | 
| Previous Message | sharmad | 2001-10-30 10:00:20 | error in postgresql |