Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B30299F9D87 for ; Tue, 18 Sep 2007 00:29:56 -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 20946-02 for ; Tue, 18 Sep 2007 00:29:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 1374F9F9D47 for ; Tue, 18 Sep 2007 00:29:53 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l8I3TpIp024951; Mon, 17 Sep 2007 23:29:51 -0400 (EDT) To: "Merlin Moncure" cc: "Carlo Stonebanks" , pgsql-performance@postgresql.org Subject: Re: Query works when kludged, but would prefer "best practice" solution In-reply-to: References: Comments: In-reply-to "Merlin Moncure" message dated "Mon, 17 Sep 2007 20:03:19 -0400" Date: Mon, 17 Sep 2007 23:29:51 -0400 Message-ID: <24950.1190086191@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/302 X-Sequence-Number: 26847 "Merlin Moncure" writes: > On 9/17/07, Carlo Stonebanks wrote: >> Please see the section marked as "PROBLEM" in "ORIGINAL QUERY" plan below. > This looks like it might be the problem tom caught and rigged a solution to: > http://people.planetpostgresql.org/dfetter/index.php?/archives/134-PostgreSQL-Weekly-News-September-03-2007.html > (look fro band-aid). No, fraid not, that was about misestimation of outer joins, and I see no outer join here. What I do see is misestimation of a set-returning-function's output: -> Function Scan on zips_in_mile_range (cost=0.00..12.50 rows=1000 width=40) (actual time=149.850..149.920 rows=66 loops=1) There's not any very nice way to improve that in existing releases :-(. In 8.3 it will be possible to add a ROWS option to function definitions to replace the default "1000 rows" estimate with some other number, but that still helps little if the number of result rows is widely variable. As far as kluges go: rather than kluging conditions affecting unrelated tables, maybe you could put in a dummy constraint on the function's output --- ie, a condition you know is always true, but the planner won't know that, and will scale down its result-rows estimate accordingly. regards, tom lane