pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Kratz <chris.kratz@vistashare.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Ramifications of turning off Nested Loops for slow queries
Date: Tue, 4 Mar 2008 13:13:58 -0500
Message-ID: <3642025c0803041013j4118f7c1ka51ac409e4901b23@mail.gmail.com> (raw)
In-Reply-To: <47CD2203.EE98.0025.0@wicourts.gov>
References: <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com>
	<47CD2203.EE98.0025.0@wicourts.gov>

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

view thread (5+ messages)  latest in thread

Message-ID: <3642025c0803041013j4118f7c1ka51ac409e4901b23@mail.gmail.com>
Permalink:  ../3642025c0803041013j4118f7c1ka51ac409e4901b23@mail.gmail.com/
Also on:    postgresql.org/message-id/3642025c0803041013j4118f7c1ka51ac409e4901b23@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: chris.kratz@vistashare.com, Kevin.Grittner@wicourts.gov
  Subject: Re: Ramifications of turning off Nested Loops for slow queries
  In-Reply-To: <3642025c0803041013j4118f7c1ka51ac409e4901b23@mail.gmail.com>
  References: <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com> <47CD2203.EE98.0025.0@wicourts.gov> <3642025c0803041013j4118f7c1ka51ac409e4901b23@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox