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 F2A31D1BAC2 for ; Tue, 24 Feb 2004 05:24:01 +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 82908-10 for ; Tue, 24 Feb 2004 01:24:01 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 1E8CCD1BABF for ; Tue, 24 Feb 2004 01:24:00 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1O5Nwar018316; Tue, 24 Feb 2004 00:23:58 -0500 (EST) To: "Ed L." Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORMANCE] slow small delete on large table In-reply-to: <200402231910.57078.pgsql@bluepolka.net> References: <200402231910.57078.pgsql@bluepolka.net> Comments: In-reply-to "Ed L." message dated "Mon, 23 Feb 2004 19:10:57 -0700" Date: Tue, 24 Feb 2004 00:23:58 -0500 Message-ID: <18315.1077600238@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/275 X-Sequence-Number: 5799 "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 = 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. See also Chris Browne's excellent suggestions nearby, if you are willing to make larger readjustments in your thinking... regards, tom lane