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

I think Tom is talking about something like this:

explain select * from foo();
QUERY PLAN
----------------------------------------------------------------------
Function Scan on foo (cost=0.00..12.50 rows=1000 width=50)

The planner is estimating the function will return 1000 rows.

explain select * from foo() where id > 0;
QUERY PLAN
---------------------------------------------------------------------
Function Scan on foo (cost=0.00..15.00 rows=333 width=50)
Filter: (id > 0)

In the second case I am asking for all ids greater than zero, but my ids are
all positive integers. The planner doesn't know that, so it assumes the
where clause will decrease the number of results.

I would still say this is a kludge, and since you already found a kludge
that works, this may not help you at all.

Dave

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Carlo
Stonebanks
Sent: Tuesday, September 18, 2007 1:29 AM
To: 'Tom Lane'; 'Merlin Moncure'
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Query works when kludged, but would prefer "best
practice" solution

Hi Tom,

Thanks for the suggestion - this concept is pretty new to me. Can you expand
a bit on the idea of how to place such a "dummy" constraint on a function,
and the conditions on which it affects the planner? Would this require that
constraint_exclusion be set on?

(When I go to sleep, I have a dream -- and in this dream Tom writes a
brilliant three line code sample that makes it all clear to me, and I wake
up a PostgreSQL guru)

;-)

Carlo

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: September 17, 2007 11:30 PM
To: Merlin Moncure
Cc: Carlo Stonebanks; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Query works when kludged, but would prefer "best
practice" solution

"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-PostgreSQ
L-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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings