Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A1D842E02B3 for ; Tue, 4 Mar 2008 14:20:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 33217-09 for ; Tue, 4 Mar 2008 14:20:47 -0400 (AST) X-Greylist: delayed 00:06:46.586133 by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.237]) by postgresql.org (Postfix) with ESMTP id 388D02E0340 for ; Tue, 4 Mar 2008 14:20:47 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id c49so935178wra.19 for ; Tue, 04 Mar 2008 10:20:46 -0800 (PST) Received: by 10.141.171.6 with SMTP id y6mr832564rvo.174.1204654438508; Tue, 04 Mar 2008 10:13:58 -0800 (PST) Received: by 10.140.163.11 with HTTP; Tue, 4 Mar 2008 10:13:58 -0800 (PST) Message-ID: <3642025c0803041013j4118f7c1ka51ac409e4901b23@mail.gmail.com> Date: Tue, 4 Mar 2008 13:13:58 -0500 From: "Chris Kratz" To: "Kevin Grittner" Subject: Re: Ramifications of turning off Nested Loops for slow queries Cc: pgsql-performance@postgresql.org In-Reply-To: <47CD2203.EE98.0025.0@wicourts.gov> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13178_105828.1204654438488" References: <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com> <47CD2203.EE98.0025.0@wicourts.gov> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/56 X-Sequence-Number: 29010 ------=_Part_13178_105828.1204654438488 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 3/4/08, Kevin Grittner wrote: > > >>> On Tue, Mar 4, 2008 at 8:42 AM, in message > > Any other thoughts or suggestions? > > > Make sure your effective_cache_size is properly configured. > > Increase random_page_cost and/or decrease seq_page_cost. > You can play with the cost settings on a connection, using EXPLAIN > on the query, to see what plan you get with each configuration > before putting it into the postgresql.conf file. > > > -Kevin That was a good idea. I hadn't tried playing with those settings in a session. This is a 8G box, and we've dedicated half of that (4G) to the file system cache. So, 4G is what effective_cache_size is set to. Our seq_page_cost is set to 1 and our random_page_cost is set to 1.75 in the postgresql.conf. In testing this one particular slow query in a session, I changed these settings alternating in increments of 0.25. The random_page_cost up to 4 and the seq_page_cost down to 0.25. This made perhaps a second difference, but at the end, we were back to to the 37s. Doing a set enable_nestloop=off in the session reduced the runtime to 1.2s with the other settings back to our normal day to day settings. So, for now I think we are going to have to modify the code to prepend the problematic queries with this setting and hope the estimator is able to better estimate this particular query in 8.3. Thanks for the suggestions, -Chris ------=_Part_13178_105828.1204654438488 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On 3/4/08, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>>> On Tue, Mar 4, 2008 at  8:42 AM, in message
> Any other thoughts or suggestions?


Make sure your effective_cache_size is properly configured.

Increase random_page_cost and/or decrease seq_page_cost.
You can play with the cost settings on a connection, using EXPLAIN
on the query, to see what plan you get with each configuration
before putting it into the postgresql.conf file.


-Kevin

That was a good idea.  I hadn't tried playing with those settings in a session.  This is a 8G box, and we've dedicated half of that (4G) to the file system cache.  So, 4G is what effective_cache_size is set to.  Our seq_page_cost is set to 1 and our random_page_cost is set to 1.75 in the postgresql.conf.

In testing this one particular slow query in a session, I changed these settings alternating in increments of 0.25.  The random_page_cost up to 4 and the seq_page_cost down to 0.25.  This made perhaps a second difference, but at the end, we were back to to the 37s.  Doing a set enable_nestloop=off in the session reduced the runtime to 1.2s with the other settings back to our normal day to day settings.

So, for now I think we are going to have to modify the code to prepend the problematic queries with this setting and hope the estimator is able to better estimate this particular query in 8.3.

Thanks for the suggestions,

-Chris

 

 
------=_Part_13178_105828.1204654438488--