Received: from web12702.mail.yahoo.com (web12702.mail.yahoo.com [216.136.173.239]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0F24CV15329 for ; Mon, 14 Jan 2002 21:04:12 -0500 (EST) (envelope-from chestercyoung@yahoo.com) Message-ID: <20020115020415.45801.qmail@web12702.mail.yahoo.com> Received: from [207.188.162.30] by web12702.mail.yahoo.com via HTTP; Mon, 14 Jan 2002 18:04:15 PST Date: Mon, 14 Jan 2002 18:04:15 -0800 (PST) From: Chester Carlton Young Subject: Re: Long-running DELETE...WHERE... To: Tom Lane Cc: pgsql-sql@postgresql.org In-Reply-To: <21141.1011057670@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200201/157 X-Sequence-Number: 6243 Nice syntax. Could not find in doc. Do you have any ideas where I could find it? --- Tom Lane wrote: > 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 > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly __________________________________________________ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/