From: Michael Fuhr <mike@fuhr.org>
To: Adnan DURSUN <a_dursun@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SQL Function Performance
Date: Tue, 14 Feb 2006 14:04:05 -0700
Message-ID: <20060214210404.GA28693@winnie.fuhr.org> (raw)
In-Reply-To: <BAY106-DAV1977F73043255833DE5957FA060@phx.gbl>
References: <BAY106-DAV1977F73043255833DE5957FA060@phx.gbl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, a_dursun@hotmail.com
Subject: Re: SQL Function Performance
In-Reply-To: <20060214210404.GA28693@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox