Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 40B669F993C for ; Mon, 24 Sep 2007 03:46:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 80719-09 for ; Mon, 24 Sep 2007 03:46:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from wdscexfe01.sc.wdc.com (wdscexfe01.sc.wdc.com [129.253.170.53]) by postgresql.org (Postfix) with ESMTP id F2D579F9862 for ; Mon, 24 Sep 2007 03:46:24 -0300 (ADT) Received: from wdmyexbe03.my.asia.wdc.com ([129.253.105.55]) by wdscexfe01.sc.wdc.com with Microsoft SMTPSVC(6.0.3790.1830); Sun, 23 Sep 2007 23:46:11 -0700 Received: from 129.253.107.184 ([129.253.107.184]) by wdmyexbe03.my.asia.wdc.com ([129.253.105.55]) with Microsoft Exchange Server HTTP-DAV ; Mon, 24 Sep 2007 06:46:04 +0000 Received: from neuromancer by 129.253.105.55; 24 Sep 2007 14:46:16 +0800 Subject: Re: REPOST: Nested loops row estimates always too high From: Ow Mun Heng To: Carlo Stonebanks Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Mon, 24 Sep 2007 14:46:16 +0800 Message-Id: <1190616376.17050.51.camel@neuromancer.home.net> Mime-Version: 1.0 X-Mailer: Evolution 2.10.2 X-OriginalArrivalTime: 24 Sep 2007 06:46:11.0310 (UTC) FILETIME=[980048E0:01C7FE76] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.384 tagged_above=0 required=5 tests=AWL=-0.684, BAYES_50=0.001, RCVD_NUMERIC_HELO=2.067 X-Spam-Level: * X-Archive-Number: 200709/411 X-Sequence-Number: 26956 On Thu, 2007-09-20 at 11:02 -0400, Carlo Stonebanks wrote: > (SORRY FOR THE REPOST, I DON'T SEE MY ORIGINAL QUESTION OR ANY ANSWERS HERE) > > I am noticing that my queries are spending a lot of time in nested loops. > The table/index row estimates are not bad, but the nested loops can be off > by a factor of 50. In any case, they are always too high. > > Are the over-estimations below significant, and if so, is this an indication > of a general configuration problem? Sounds much like the issue I was seeing as well. > > Unique (cost=67605.91..67653.18 rows=4727 width=16) (actual > time=8634.618..8637.918 rows=907 loops=1) You can to rewrite the queries to individual queries to see it if helps. In my case, I was doing select a.a,b.b,c.c from (select a from x where) a <--- Put as a SRF left join ( select b from y where ) b <--- Put as a SRF on a.a = b.a