Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0EITBV51941 for ; Mon, 14 Jan 2002 13:29:15 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g0EIRr805681 for pgsql-sql@postgresql.org; Mon, 14 Jan 2002 13:27:53 -0500 (EST) (envelope-from news) From: jboes@nexcerpt.com (Jeff Boes) X-Newsgroups: comp.databases.postgresql.sql Subject: Long-running DELETE...WHERE... Date: 14 Jan 2002 10:27:50 -0800 Organization: http://groups.google.com/ Lines: 25 Message-ID: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200201/143 X-Sequence-Number: 6231 Why would a delete involving a subselect run so much longer than the individual delete commands? 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. But if I do \o tmpfile \t SELECT 'DELETE FROM a WHERE id = ' || id || ';' from tmp limit 800; \o \i tmpfile this completes in about 15 seconds, or 1/50 of the time for the single-statement delete above. In trying to optimize this process, I disabled all the relational integrity triggers (foreign keys) involving the table, and then I dropped all the indexes EXCEPT that of the primary key. All the experiments were done within a single transaction using BEGIN. The database version is 7.1.3, and the table was vacuumed very recently.