On 3/4/08, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> 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
