From: Chris Kratz <chris.kratz@vistashare.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Ramifications of turning off Nested Loops for slow queries
Date: Tue, 4 Mar 2008 13:16:15 -0500
Message-ID: <3642025c0803041016o1f4eaaeanfe166a333849167@mail.gmail.com> (raw)
In-Reply-To: <16225.1204651157@sss.pgh.pa.us>
References: <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com>
<47CD2203.EE98.0025.0@wicourts.gov>
<16225.1204651157@sss.pgh.pa.us>
On 3/4/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "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
>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chris.kratz@vistashare.com, tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov
Subject: Re: Ramifications of turning off Nested Loops for slow queries
In-Reply-To: <3642025c0803041016o1f4eaaeanfe166a333849167@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox