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 2A761D80D6 for ; Thu, 13 Oct 2005 11:50:40 -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 19079-04 for ; Thu, 13 Oct 2005 14:50:33 +0000 (GMT) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.204]) by svr1.postgresql.org (Postfix) with ESMTP id 25833D80A8 for ; Thu, 13 Oct 2005 11:50:35 -0300 (ADT) Received: by xproxy.gmail.com with SMTP id i30so239313wxd for ; Thu, 13 Oct 2005 07:50:40 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=aEnvjfOwYz/NiGPRmh0Aqzh9uA9J9rirfxdHYUa9oXVmD2NipChg431OJ75DrSBh+bjBNE/oXvovGpkadk35lkTeutg8tVURdQVjNazAV8Z6wGYEYDqZMdgs/orxl4xEMAdcqPHWT2eiJzql6r0tIoMhzgrf8JGoWTEw2CMpSEM= Received: by 10.70.76.12 with SMTP id y12mr719697wxa; Thu, 13 Oct 2005 07:50:40 -0700 (PDT) Received: by 10.70.128.19 with HTTP; Thu, 13 Oct 2005 07:50:40 -0700 (PDT) Message-ID: Date: Thu, 13 Oct 2005 09:50:40 -0500 From: Matthew Nuzum Reply-To: newz@bearfruit.org To: Csaba Nagy Subject: Re: Help tuning postgres Cc: postgres performance list In-Reply-To: <1129209283.2995.226.camel@coppola.muc.ecircle.de> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1129128315.2995.174.camel@coppola.muc.ecircle.de> <1129136130.2995.188.camel@coppola.muc.ecircle.de> <20051012220036.GE13571@phlogiston.dyndns.org> <1129191303.2995.192.camel@coppola.muc.ecircle.de> <20051013124007.GA15592@phlogiston.dyndns.org> <1129209283.2995.226.camel@coppola.muc.ecircle.de> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.319 required=5 tests=[AWL=0.295, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/225 X-Sequence-Number: 14993 On 10/13/05, Csaba Nagy wrote: > On Thu, 2005-10-13 at 14:40, Andrew Sullivan wrote: > > On Thu, Oct 13, 2005 at 10:15:03AM +0200, Csaba Nagy wrote: > > > And how would the analyze help in finding this out ? I thought it wou= ld > > > only show me additionally the actual timings, not more detail in what > > > was done... > > > > Yes, it shows the actual timings, and the actual number of rows. But > > if the estimates that the planner makes are wildly different than the > > actual results, then you know your statistics are wrong, and that the > > planner is going about things the wrong way. ANALYSE is a big help. > > There's also a verbose option to it, but it's usually less useful in > > production situations. This is the point I was trying to make. I've seen special instances where people have posted an explain annalyze for a select/update to the list and suggestions have arisen allowing major performance improvements. If this task is where your database is performing its worst then it is the best place to start with optimizing, short of the obvious stuff, which it sounds like you've covered. Sometimes, and I think this has often been true for databases that are either very large or very small, statistics can be tweaked to get better performance. One good example is when a sequential scan is being chosen when an index scan may be better; something like this would definately peg your disk i/o. Throwing more hardware at your problem will definately help, but I'm a performance freak and I like to optimize everything to the max. *Sometimes* you can get drastic improvements without adding any hardware. I have seen some truly miraculus turn-arounds by tweaking some non-obvious settings based on suggestions made on this list. -- Matthew Nuzum www.bearfruit.org