X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id AC9A2D1B441 for ; Tue, 25 Nov 2003 23:56:03 +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 59279-03 for ; Tue, 25 Nov 2003 19:55:33 -0400 (AST) Received: from bob.samurai.com (bob.samurai.com [205.207.28.75]) by svr1.postgresql.org (Postfix) with ESMTP id E0BDDD1B446 for ; Tue, 25 Nov 2003 19:55:31 -0400 (AST) Received: from tokyo.samurai.com (d226-89-59.home.cgocable.net [24.226.89.59]) by bob.samurai.com (Postfix) with ESMTP id B12AE2092; Tue, 25 Nov 2003 18:55:33 -0500 (EST) To: schampailler@easynet.be Cc: pgsql-performance@postgresql.org Subject: Re: Impossibly slow DELETEs From: Neil Conway In-Reply-To: <200311252256.56433.schampailler@easynet.be> (Stefan Champailler's message of "Tue, 25 Nov 2003 22:56:56 +0100") References: <200311252256.56433.schampailler@easynet.be> Date: Tue, 25 Nov 2003 18:55:33 -0500 Message-ID: <87znekuhoq.fsf@mailbox.samurai.com> User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/262 X-Sequence-Number: 4822 Stefan Champailler writes: > So here's my trouble : some DELETE statement take up to 1 minute to > complete (but not always, sometimes it's fast, sometimes it's that > slow). Here's a typical one : DELETE FROM response_bool WHERE > response_id = '125' The response_bool table has no foreing key and > no index on response_id column. No foreign key reference the > response_bool table. I'm skeptical that PostgreSQL is causing the performance problem here -- 1 minute for a DELETE on a single-page table is absurdly slow. If you enable the log_min_duration_statement configuration variable, you should be able to get an idea of how long it actually takes PostgreSQL to execute each query -- do you see some 60 second queries in the log? What is the system load like when the query takes a long time? For example, `vmstat 1` output around this point in time would be helpful. Does PostgreSQL consume a lot of CPU time or do a lot of disk I/O? Can you confirm this problem using psql? > There are 6 rows in the table (given that size, I assumed that an > index was not necessary). That's a reasonable assumption. -Neil