Re: Ramifications of turning off Nested Loops for slow queries

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