Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BEEC69FA3B1 for ; Mon, 4 Dec 2006 01:35:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 68923-02 for ; Mon, 4 Dec 2006 01:35:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 745A29F9F7B for ; Mon, 4 Dec 2006 01:16:25 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 4C1E030E2B; Mon, 4 Dec 2006 06:16:24 +0100 (MET) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Is Vacuum/analyze destroying my performance? Date: Mon, 4 Dec 2006 00:16:22 -0500 Organization: Hub.Org Networking Services Lines: 21 Message-ID: X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2869 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50 X-Spam-Level: X-Archive-Number: 200612/38 X-Sequence-Number: 22094 I have always been frustrated by the wildly erratic performance of our postgresql 8 server. We run aprogram that does heavy data importing via a heuristics-based import program. Sometime records being imported would just fly by, sometimes they would crawl. The import program imports records from a flat table and uses heuristics to normalise and dedupe. This is done via a sequence of updates and inserts bracketed by a start-end transaction. At a certain checkpoint representing about 1,000,000 rows read and imported, I ran a vacuum/analyze on all of the tables in the target schema. To my horror, performance reduced to less than TEN percent of what it was befor the vacuum/analyze. I thought that turning autovacuum off and doing my own vacuuming would improve performance, but it seems to be killing it. I have since turned autovacuum on and am tearing my hair out wathcing the imported records crawl by. I have tried vacuuming the entire DB as well as rebuilding indexes. Nothing. Any ideas what could have happened? What is the right thing to do? Carlo