Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 202212E00C9 for ; Tue, 4 Mar 2008 14:42:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 43534-10 for ; Tue, 4 Mar 2008 14:42:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.229]) by postgresql.org (Postfix) with ESMTP id F0F972E00A4 for ; Tue, 4 Mar 2008 14:42:12 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id c49so954758wra.19 for ; Tue, 04 Mar 2008 10:42:12 -0800 (PST) Received: by 10.141.156.19 with SMTP id i19mr857698rvo.25.1204654575441; Tue, 04 Mar 2008 10:16:15 -0800 (PST) Received: by 10.140.163.11 with HTTP; Tue, 4 Mar 2008 10:16:15 -0800 (PST) Message-ID: <3642025c0803041016o1f4eaaeanfe166a333849167@mail.gmail.com> Date: Tue, 4 Mar 2008 13:16:15 -0500 From: "Chris Kratz" To: "Tom Lane" Subject: Re: Ramifications of turning off Nested Loops for slow queries Cc: "Kevin Grittner" , pgsql-performance@postgresql.org In-Reply-To: <16225.1204651157@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13191_26228105.1204654575433" References: <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com> <47CD2203.EE98.0025.0@wicourts.gov> <16225.1204651157@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/57 X-Sequence-Number: 29011 ------=_Part_13191_26228105.1204654575433 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 3/4/08, Tom Lane wrote: > > "Kevin Grittner" writes: > > On Tue, Mar 4, 2008 at 8:42 AM, in message > > <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com>, Chris Kratz > > 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 ------=_Part_13191_26228105.1204654575433 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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
------=_Part_13191_26228105.1204654575433--