X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F3878D8817 for ; Tue, 6 Sep 2005 12:55:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00357-09 for ; Tue, 6 Sep 2005 15:55:42 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id B5838D883C for ; Tue, 6 Sep 2005 12:55:39 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 1DAEE4166A3; Tue, 6 Sep 2005 16:55:25 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 356A915F1D; Tue, 6 Sep 2005 16:51:12 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04875-10; Tue, 6 Sep 2005 16:51:09 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id A723B15F1C; Tue, 6 Sep 2005 16:51:09 +0100 (BST) Message-ID: <431DBAE9.3060205@archonet.com> Date: Tue, 06 Sep 2005 16:51:05 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Brian Choate Cc: pgsql-performance@postgresql.org Subject: Re: Poor performance of delete by primary key References: <2F9239F5E316C54688FDA9814FF045D6EADACC@EXCHANGE.sf.nimblefish.com> In-Reply-To: <2F9239F5E316C54688FDA9814FF045D6EADACC@EXCHANGE.sf.nimblefish.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 required=5 tests=[AWL=0.007, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200509/96 X-Sequence-Number: 14404 Brian Choate wrote: > Hello, > > We are seeing a very strange behavior from postgres. For one of our > very common tasks we have to delete records from a table of around > 500,000 rows. The delete is by id which is the primary key. It seems > to be consistently taking around 10 minutes to preform. This is > totally out of line with the rest of the performance of the database. > Any ideas on what might be going on? Well, it sounds like *something* isn't using an index. You say that all your FK's are indexed, but that's something worth checking. Also keep an eye out for type conflicts. If the system is otherwise idle, it might be worthwhile to compare before and after values of pg_stat* (user-tables and user-indexes). -- Richard Huxton Archonet Ltd