Skip site navigation (1) Skip section navigation (2)

Re: Query works when kludged, but would prefer "best practice" solution

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
Cc: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Query works when kludged, but would prefer "best practice" solution
Date: 2007-09-18 03:29:51
Message-ID: 24950.1190086191@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On 9/17/07, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca> 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

In response to

Responses

pgsql-performance by date

Next:From: soni deDate: 2007-09-18 04:31:46
Subject: Re: Regarding COPY command from Postgres 8.2.0
Previous:From: Carlo StonebanksDate: 2007-09-18 00:38:58
Subject: Re: Query works when kludged, but would prefer "best practice" solution

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group