Received: from tomts8-srv.bellnexxia.net (tomts8.bellnexxia.net [209.226.175.52]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0FJO2V19465 for ; Tue, 15 Jan 2002 14:24:02 -0500 (EST) (envelope-from fbax@sympatico.ca) Received: from ACER ([65.93.0.91]) by tomts8-srv.bellnexxia.net (InterMail vM.4.01.03.16 201-229-121-116-20010115) with SMTP id <20020115192357.MFOI16860.tomts8-srv.bellnexxia.net@ACER>; Tue, 15 Jan 2002 14:23:57 -0500 Message-Id: <3.0.6.32.20020115142250.009b3970@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.6 (32) Date: Tue, 15 Jan 2002 14:22:50 -0500 To: Tom Lane From: Frank Bax Subject: Re: Long-running DELETE...WHERE... Cc: jboes@nexcerpt.com (Jeff Boes), pgsql-sql@postgresql.org In-Reply-To: <21141.1011057670@sss.pgh.pa.us> References: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Archive-Number: 200201/171 X-Sequence-Number: 6257 At 08:21 PM 1/14/02 -0500, 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. Nice alternative. Is there an alternate format for this one: DELETE FROM teamwork WHERE emp NOT IN ( SELECT DISTINCT emp FROM timesheet WHERE lo_shift > (now()-'90days'::interval) ) Frank