Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 43D672E0223 for ; Tue, 18 Mar 2008 12:58:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 69675-06 for ; Tue, 18 Mar 2008 12:58:22 -0300 (ADT) X-Greylist: delayed 00:23:12.98797 by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.186]) by postgresql.org (Postfix) with ESMTP id 3E6952E0221 for ; Tue, 18 Mar 2008 12:58:22 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id g13so3875491rvb.43 for ; Tue, 18 Mar 2008 08:58:21 -0700 (PDT) Received: by 10.140.203.9 with SMTP id a9mr892900rvg.203.1205855901503; Tue, 18 Mar 2008 08:58:21 -0700 (PDT) Received: by 10.140.163.11 with HTTP; Tue, 18 Mar 2008 08:58:21 -0700 (PDT) Message-ID: <3642025c0803180858k704d8c6ah39e30b8ab75f4df0@mail.gmail.com> Date: Tue, 18 Mar 2008 11:58:21 -0400 From: "Chris Kratz" To: "Joshua D. Drake" Subject: Re: Planner mis-estimation using nested loops followup Cc: pgsql-performance@postgresql.org In-Reply-To: <20080318085059.7c5da59a@commandprompt.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4922_7145837.1205855901489" References: <3642025c0803180835n20062314g94870b137d8c3ff7@mail.gmail.com> <20080318085059.7c5da59a@commandprompt.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/374 X-Sequence-Number: 29328 ------=_Part_4922_7145837.1205855901489 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Y, turning nested loops off in specific cases has increased performance greatly. It didn't fix the planner mis-estimation, just the plan it chose. It's certainly not a panacea, but it's something we now try early on when trying to speed up a query that matches these characteristics. -Chris On 3/18/08, Joshua D. Drake wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > On Tue, 18 Mar 2008 11:35:08 -0400 > "Chris Kratz" wrote: > > > Nondefault settings of interest from postgresql.conf > > > > > > shared_buffers = 1024MB # min 128kB or > > max_connections*16kB work_mem = 256MB > > # min 64kB maintenance_work_mem = 256MB # min 1MB > > random_page_cost = 1.75 # same scale as above > > effective_cache_size = 4096MB > > default_statistics_target = 100 # range 1-1000 > > > > > > If nothing else, perhaps this will help somebody else who has run > > into the same problem. If explain analyze of a query shows a large > > mis-estimation of rows returned on a join (estimate=1, actual=2k) > > causing the planner to choose nested loops instead of another join > > type, you might try running the query with nested loops set to off > > and see if that helps w/ performance. > > > Did you try that? Did it work? > > Joshua D. Drake > > > - -- > The PostgreSQL Company since 1997: http://www.commandprompt.com/ > PostgreSQL Community Conference: http://www.postgresqlconference.org/ > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL political pundit | Mocker of Dolphins > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFH3+TlATb/zqfZUUQRAmXUAKCjwidfW0KXjzUM26I4yTx94/wSiQCfaqWU > eI9i5yucBH718okW3w2UewQ= > =BO3E > -----END PGP SIGNATURE----- > ------=_Part_4922_7145837.1205855901489 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Y, turning nested loops off in specific cases has increased performance greatly.  It didn't fix the planner mis-estimation, just the plan it chose.  It's certainly not a panacea, but it's something we now try early on when trying to speed up a query that matches these characteristics.

-Chris

On 3/18/08, Joshua D. Drake <jd@commandprompt.com> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


On Tue, 18 Mar 2008 11:35:08 -0400
"Chris Kratz" <chris.kratz@vistashare.com> wrote:

> Nondefault settings of interest from postgresql.conf
>
>
> shared_buffers = 1024MB                 # min 128kB or
> max_connections*16kB work_mem = 256MB
> # min 64kB maintenance_work_mem = 256MB            # min 1MB
> random_page_cost = 1.75                 # same scale as above
> effective_cache_size = 4096MB
> default_statistics_target = 100         # range 1-1000
>
>
> If nothing else, perhaps this will help somebody else who has run
> into the same problem.  If explain analyze of a query shows a large
> mis-estimation of rows returned on a join (estimate=1, actual=2k)
> causing the planner to choose nested loops instead of another join
> type, you might try running the query with nested loops set to off
> and see if that helps w/ performance.


Did you try that? Did it work?

Joshua D. Drake


- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
      PostgreSQL political pundit | Mocker of Dolphins

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH3+TlATb/zqfZUUQRAmXUAKCjwidfW0KXjzUM26I4yTx94/wSiQCfaqWU
eI9i5yucBH718okW3w2UewQ=
=BO3E
-----END PGP SIGNATURE-----

 
------=_Part_4922_7145837.1205855901489--