Re: Ramifications of turning off Nested Loops for slow queries

On 3/4/08, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)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