Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0F1L9V04090 for ; Mon, 14 Jan 2002 20:21:09 -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 g0F1LAg21144; Mon, 14 Jan 2002 20:21:11 -0500 (EST) To: jboes@nexcerpt.com (Jeff Boes) cc: pgsql-sql@postgresql.org Subject: Re: Long-running DELETE...WHERE... In-reply-to: References: Comments: In-reply-to jboes@nexcerpt.com (Jeff Boes) message dated "14 Jan 2002 10:27:50 -0800" Date: Mon, 14 Jan 2002 20:21:10 -0500 Message-ID: <21141.1011057670@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200201/156 X-Sequence-Number: 6242 jboes@nexcerpt.com (Jeff Boes) writes: > My situation: table A has 200,000 rows. I've made up a temporary table > which holds the single-valued primary key for 80,000 rows which I want > to delete. > DELETE FROM a WHERE id IN (select ID from tmp LIMIT 800); > runs for several minutes. WHERE ... IN ... is notoriously inefficient. I'd try DELETE FROM aa WHERE id = tmp.id; which is not standard SQL but should be able to produce a decent plan. You might find that a VACUUM ANALYZE on both tables beforehand would be a good idea, too; never-vacuumed temp tables have some default statistics assumed that are a lot less than 80k rows. regards, tom lane