Thread: Ramifications of turning off Nested Loops for slow queries

Hello Everyone,

I had posted an issue previously that we've been unable to resolve.
An early mis-estimation in one or more subqueries causes the remainder
of the query to choose nested loops instead of a more efficient method
and runs very slowly (CPU Bound).  I don't think there is any way to
"suggest" to the planner it not do what it's doing, so we are starting
to think about turning off nested loops entirely.

Here is the history so far:

http://archives.postgresql.org/pgsql-performance/2008-02/msg00205.php

At the suggestion of the list, we upgraded to 8.2.6 and are still
experiencing the same problem.  I'm now installing 8.3 on my
workstation  to see if it chooses a better plan, but it will take some
time to get it compiled, a db loaded, etc.

We have a number of very long running reports that will run in seconds
if nested loops are turned off.  The other alternative we are
exploring is programmatically turning off nested loops just for the
problematic reports.  But with the speedups we are seeing, others are
getting gun shy about having them on at all.

So, I've now been asked to ping the list as to whether turning off
nested loops system wide is a bad idea, and why or why not.

Any other thoughts or suggestions?

Thanks,

-Chris

>>> On Tue, Mar 4, 2008 at  8:42 AM, in message
<483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com>, Chris Kratz
<chris.kratz@vistashare.com> wrote:

> So, I've now been asked to ping the list as to whether turning off
> nested loops system wide is a bad idea, and why or why not.

In our environment, the fastest plan for a lot of queries involve
nested loops.  Of course, it's possible that these never provide the
fasted plan in your environment, but it seems very unlikely --
you're just not noticing the queries where it's doing fine.

> 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

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> On Tue, Mar 4, 2008 at  8:42 AM, in message
> <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com>, Chris Kratz
> <chris.kratz@vistashare.com> wrote:
>> So, I've now been asked to ping the list as to whether turning off
>> nested loops system wide is a bad idea, and why or why not.

> In our environment, the fastest plan for a lot of queries involve
> nested loops.  Of course, it's possible that these never provide the
> fasted plan in your environment, but it seems very unlikely --
> you're just not noticing the queries where it's doing fine.

Yeah, I seem to recall similar queries from other people who were
considering the opposite, ie disabling the other join types :-(

The rule of thumb is that nestloop with an inner indexscan will beat
anything else for pulling a few rows out of a large table.  But on
the other hand it loses big for selecting lots of rows.  I don't think
that a global disable in either direction would be a smart move, unless
you run only a very small number of query types and have checked them
all.

            regards, tom lane