Re: SQL Function Performance

On Tue, Feb 14, 2006 at 11:33:57AM +0200, Adnan DURSUN wrote:
> -> Nested Loop (cost=5.90..267.19 rows=3 width=101) (actual time=76.240..30974.777 rows=63193 loops=1)
> -> Nested Loop (cost=5.90..123.48 rows=26 width=73) (actual time=32.082..4357.786 rows=14296 loops=1)

A prepared query is planned before the parameters' values are known,
so the planner can't take full advantage of column statistics to
estimate row counts. The planner must therefore decide on a plan
that should be reasonable in most cases; apparently this isn't one
of those cases, as the disparity between estimated and actual rows
shows. Maybe Tom (one of the core developers) can comment on whether
anything can be done to improve the plan in this case.

Absent a better solution, you could write a PL/pgSQL function and
build the query as a text string, then EXECUTE it. That would give
you a new plan each time, one that can take better advantage of
statistics, at the cost of having to plan the query each time you
call the function (but you probably don't care about that cost
as long as the overall results are better). Here's an example:

CREATE FUNCTION fooquery(qval text) RETURNS SETOF foo AS $$
DECLARE
row foo%ROWTYPE;
query text;
BEGIN
query := 'SELECT * FROM foo WHERE val = ' || quote_literal(qval);

FOR row IN EXECUTE query LOOP
RETURN NEXT row;
END LOOP;

RETURN;
END;
$$ LANGUAGE plpgsql STABLE STRICT;

--
Michael Fuhr