X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 34E94D1BA35 for ; Tue, 24 Feb 2004 18:36:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 39394-10 for ; Tue, 24 Feb 2004 14:36:23 -0400 (AST) Received: from mail.codenet.net (mail.codenet.net [209.248.81.8]) by svr1.postgresql.org (Postfix) with ESMTP id 73643D1B4AA for ; Tue, 24 Feb 2004 14:36:21 -0400 (AST) Received: from elvis [68.232.114.7] by mail.codenet.net with ESMTP (SMTPD32-7.07) id A9A26C01BC; Tue, 24 Feb 2004 11:36:18 -0700 Content-Type: text/plain; charset="utf-8" From: "Ed L." To: Tom Lane Subject: Re: [PERFORMANCE] slow small delete on large table Date: Tue, 24 Feb 2004 11:36:08 -0700 User-Agent: KMail/1.4.3 Cc: pgsql-performance@postgresql.org References: <200402231910.57078.pgsql@bluepolka.net> <18315.1077600238@sss.pgh.pa.us> In-Reply-To: <18315.1077600238@sss.pgh.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200402241136.08556.pgsql@bluepolka.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/285 X-Sequence-Number: 5809 On Monday February 23 2004 10:23, Tom Lane wrote: > "Ed L." writes: > > If I could say it the way I think for a simple example, it'd be > > like this: > > > > delete from mytable > > where posteddatetime < now() - '90 days' > > limit 100; > > > > Of course, that's not legal 7.3.4 syntax. > > Assuming you have a primary key on the table, consider this: > > CREATE TEMP TABLE doomed AS > SELECT key FROM mytable WHERE posteddatetime < now() - '90 days' > LIMIT 100; > > DELETE FROM mytable WHERE key =3D doomed.key; > > DROP TABLE doomed; > > Depending on the size of mytable, you might need an "ANALYZE doomed" > in there, but I'm suspecting not. A quick experiment suggests that > you'll get a plan with an inner indexscan on mytable.key, which is > exactly what you need. I didn't mention I'd written a trigger to do delete N rows on each new=20 insert (with a delay governor preventing deletion avalanches). The=20 approach looks a little heavy to be done from within a trigger with the=20 response time I need, but I'll try it. Cantchajust toss in that "limit N"= =20 functionality to delete clauses? How hard could that be? ;) > See also Chris Browne's excellent suggestions nearby, if you are willing > to make larger readjustments in your thinking... I did a search for articles by Chris Browne, didn't see one that appeared= =20 relevant. What is the thread subject to which you refer?