Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9UFaEP55035 for ; Tue, 30 Oct 2001 10:36:14 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f9UFXEU47374 for pgsql-sql@postgresql.org; Tue, 30 Oct 2001 10:33:14 -0500 (EST) (envelope-from news) From: "postgresql" X-Newsgroups: comp.databases.postgresql.sql Subject: efficient deletes on subqueries Date: Tue, 30 Oct 2001 15:33:05 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 25 Message-ID: <9rmh7p$1e89$1@news.tht.net> Reply-To: "postgresql" X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 To: pgsql-sql@postgresql.org X-Archive-Number: 200110/415 X-Sequence-Number: 5366 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