Received: from sss.pgh.pa.us (sss.pgh.pa.us [216.151.103.158] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9UGmkP72289 for ; Tue, 30 Oct 2001 11:48:46 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f9UGmXc20898; Tue, 30 Oct 2001 11:48:33 -0500 (EST) To: "postgresql" cc: pgsql-sql@postgresql.org Subject: Re: efficient deletes on subqueries In-reply-to: <9rmh7p$1e89$1@news.tht.net> References: <9rmh7p$1e89$1@news.tht.net> Comments: In-reply-to "postgresql" message dated "Tue, 30 Oct 2001 15:33:05 +0000" Date: Tue, 30 Oct 2001 11:48:33 -0500 Message-ID: <20895.1004460513@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/416 X-Sequence-Number: 5367 "postgresql" writes: > 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; > 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? Try DELETE FROM AA WHERE AA.keyA = obsolete_AA_entries.keyA; This is not valid SQL according to the SQL standard, but Postgres takes it anyway. Turning sub-SELECT queries into plannable joins is on the todo list, but it's not done yet :-( regards, tom lane