On 3/4/08, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes:
> On Tue, Mar 4, 2008 at 8:42 AM, in message
> <483ACAF5-A485-40D9-9D7E-7008EF12F909@xxxxxxxxxxxxxx>, Chris Kratz
> <chris.kratz@xxxxxxxxxxxxxx> 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
So, if we can't find another way to solve the problem, probably our best bet is to turn off nested loops on particularly bad queries by prepending them w/ set enable_nested_loop=off? But, leave them on for the remainder of the system?
Do you think it's worth testing on 8.3 to see if the estimator is able to make a better estimate?
-Chris
