Re: Ramifications of turning off Nested Loops for slow queries

"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