X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 93BB59DC810 for ; Tue, 21 Feb 2006 11:01:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87006-04 for ; Tue, 21 Feb 2006 11:01:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 4415C9DC80D for ; Tue, 21 Feb 2006 11:01:24 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1LF1RZR015418; Tue, 21 Feb 2006 10:01:28 -0500 (EST) To: adey cc: Pgsql-Admin Subject: Re: Delete performance In-reply-to: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com> References: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com> Comments: In-reply-to adey message dated "Tue, 21 Feb 2006 20:02:53 +1300" Date: Tue, 21 Feb 2006 10:01:27 -0500 Message-ID: <15417.1140534087@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.248 required=5 tests=[AWL=-0.028, MAILTO_TO_SPAM_ADDR=0.276] X-Spam-Score: 0.248 X-Spam-Level: X-Archive-Number: 200602/243 X-Sequence-Number: 20664 adey writes: > We are attempting many deletes in our production database for the first > time, and we're getting nowhere fast. > The SQL runs for more than 12 hours to delete 2 million rows, and hasn't > finished each time we've tried it as we've had to cancel it. The usual cause of slow deletes is that (a) the table is the target of some foreign key references from other large tables, and (b) the referencing columns in those tables aren't indexed, or (in older PG versions such as 7.4) aren't exactly the same datatype as the master column. This forces the FK actions to use inefficient sequential-scan plans. Fix the index situation and then start a fresh session to ensure you have fresh FK-action plans. Please also think *hard* about running something more modern than 7.4.2. That release series is at 7.4.12 --- you are missing nearly two years' worth of critical bug fixes. regards, tom lane