Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id C51D49F9987 for ; Thu, 27 Sep 2007 03:59:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 89585-04 for ; Thu, 27 Sep 2007 03:59:19 -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 7F3C69F994A for ; Thu, 27 Sep 2007 03:59:21 -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); Wed, 26 Sep 2007 23:59:06 -0700 Received: from 129.253.107.181 ([129.253.107.181]) by wdmyexbe03.my.asia.wdc.com ([129.253.105.55]) with Microsoft Exchange Server HTTP-DAV ; Thu, 27 Sep 2007 06:59:03 +0000 Received: from neuromancer by 129.253.105.55; 27 Sep 2007 14:59:13 +0800 Subject: Re: REPOST: Nested loops row estimates always too high From: Ow Mun Heng To: Tom Lane Cc: "Steinar H. Gunderson" , Carlo Stonebanks , pgsql-performance@postgresql.org In-Reply-To: <12946.1190779378@sss.pgh.pa.us> References: <1190616376.17050.51.camel@neuromancer.home.net> <1190681440.22945.8.camel@neuromancer.home.net> <53877A06E07146D2898619A7025C6107@serenity> <20070925093133.GA27052@uio.no> <1190778646.5402.16.camel@neuromancer.home.net> <12946.1190779378@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Thu, 27 Sep 2007 14:59:13 +0800 Message-Id: <1190876353.15634.65.camel@neuromancer.home.net> Mime-Version: 1.0 X-Mailer: Evolution 2.10.2 X-OriginalArrivalTime: 27 Sep 2007 06:59:06.0571 (UTC) FILETIME=[E554F9B0:01C800D3] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.086 tagged_above=0 required=5 tests=AWL=0.618, BAYES_00=-2.599, RCVD_NUMERIC_HELO=2.067 X-Spam-Level: X-Archive-Number: 200709/453 X-Sequence-Number: 26998 On Wed, 2007-09-26 at 00:02 -0400, Tom Lane wrote: > Ow Mun Heng writes: > > Where can I erad more about this new "feature"? > > http://developer.postgresql.org/pgdocs/postgres/sql-createfunction.html > > http://developer.postgresql.org/pgdocs/postgres/ always has a current > snapshot of CVS-HEAD documentation... I read these two items ... execution_cost A positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. If the cost is not specified, 1 unit is assumed for C-language and internal functions, and 100 units for functions in all other languages. Larger values cause the planner to try to avoid evaluating the function more often than necessary. result_rows A positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set. The default assumption is 1000 rows. ... [/snip]