Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C8ED69FA192 for ; Mon, 4 Dec 2006 13:08:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 29797-01 for ; Mon, 4 Dec 2006 13:08:37 -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 1D3D69F9F7B for ; Mon, 4 Dec 2006 13:08:36 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id EE82C30AF0; Mon, 4 Dec 2006 18:08:34 +0100 (MET) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Is Vacuum/analyze destroying my performance? Date: Mon, 4 Dec 2006 12:08:42 -0500 Organization: Hub.Org Networking Services Lines: 24 Message-ID: References: <45743240.7050302@zeut.net> 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; Response 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.022 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200612/48 X-Sequence-Number: 22104 ""Matthew O'Connor"" wrote in message news:45743240.7050302@zeut.net... > Just a wild guess, but the performance problem sounds like maybe as your > data changes, eventually the planner moves some query from an index scan > to a sequential scan, do you have any details on what queries are taking > so long when things are running slow? You can turn on the GUC var > "log_min_duration_statement" and see what queries are slow and then > manually check them with an explain analyze, that might help. > > Matt This is pretty well what I think is happening - I expect all queries to eventually move from seq scans to index scans. I actually have a SQL logging opion built into the import app. I just can't figure out how the planner can be so wrong. We are running a 4 CPU server (two dual core 3.2 GHz Xeons) with 4GB RAM and Windows Server 2003 x64 and a PERC RAID subsystem (I don't know the RAID type). I know that the metrics for the planner can be changed - is the default config for postgesql not suitable for our setup? For this server, we would like to be optimised for high speed over a few connections, rather than the classic balanced speed over many connections.