On 3/4/08, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > On Tue, Mar 4, 2008 at 8:42 AM, in message > > <[EMAIL PROTECTED]>, Chris Kratz > > <[EMAIL PROTECTED]> 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