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 17188D1B91C for ; Tue, 24 Feb 2004 02:11:05 +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 40858-03 for ; Mon, 23 Feb 2004 22:11:05 -0400 (AST) Received: from mail.codenet.net (mail.codenet.net [209.248.81.8]) by svr1.postgresql.org (Postfix) with ESMTP id 18F82D1B4AA for ; Mon, 23 Feb 2004 22:11:01 -0400 (AST) Received: from elvis [68.232.114.7] by mail.codenet.net with ESMTP (SMTPD32-7.07) id A2B71B5A02A4; Mon, 23 Feb 2004 19:11:03 -0700 Content-Type: text/plain; charset="us-ascii" From: "Ed L." To: pgsql-performance@postgresql.org Subject: [PERFORMANCE] slow small delete on large table Date: Mon, 23 Feb 2004 19:10:57 -0700 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200402231910.57078.pgsql@bluepolka.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/270 X-Sequence-Number: 5794 A 7.3.4 question... I want to "expire" some data after 90 days, but not delete too much at once so as not to overwhelm a system with precariously balanced disk I/O and on a table with millions of rows. If I=20 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. These are both too slow due to sequential scan of table: delete from mytable where key in ( select key from mytable where posteddatetime < now() - '90 days' limit 100); or=20 delete from mytable where exists ( select m.key from mytable m where m.key =3D mytable.key and m.posteddatetime < now() - '90 days' limit 100); Tried to use a cursor, but couldn't figure out the syntax for select-for-delete yet, or find appropriate example on google. Any clues? TIA.