Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E61169FA4CF for ; Tue, 5 Dec 2006 01:18:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 21039-02 for ; Tue, 5 Dec 2006 01:17:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by postgresql.org (Postfix) with ESMTP id 20A069FA48A for ; Tue, 5 Dec 2006 01:12:07 -0400 (AST) Received: from pool-72-88-194-98.nwrknj.fios.verizon.net ([72.88.194.98] helo=[192.168.4.67]) by outbound.mailhop.org with esmtpa (Exim 4.63) (envelope-from ) id 1GrSaw-0005kN-G2; Tue, 05 Dec 2006 00:12:06 -0500 X-Mail-Handler: MailHop Outbound by DynDNS X-Originating-IP: 72.88.194.98 X-Report-Abuse-To: abuse@dyndns.com (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: tocr Message-ID: <4574FF9E.3000600@zeut.net> Date: Tue, 05 Dec 2006 00:11:58 -0500 From: "Matthew T. O'Connor" User-Agent: Thunderbird 2.0a1 (Windows/20060724) MIME-Version: 1.0 To: Carlo Stonebanks CC: pgsql-performance@postgresql.org Subject: Re: Is Vacuum/analyze destroying my performance? References: <45743240.7050302@zeut.net> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/67 X-Sequence-Number: 22123 Carlo Stonebanks wrote: >> 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. >> > 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. If it is the planner choosing a very bad plan, then I don't think your hardware has anything to do with it. And, we can't diagnose why the planner is doing what it's doing without a lot more detail. I suggest you do something to figure out what queries are taking so long, then send us an explain analyze, that might shine some light on the subject.