rihad wrote:
Hi, I'm planning to use prepared statements of indefinite lifetime in a
daemon that will execute same statements rather frequently in reply to
client requests.
This link:
http://www.postgresql.org/docs/8.3/static/sql-prepare.html
has a note on performance:
In some situations, the query plan produced for a prepared statement
will be inferior to the query plan that would have been chosen if the
statement had been submitted and executed normally. This is because when
the statement is planned and the planner attempts to determine the
optimal query plan, the actual values of any parameters specified in the
statement are unavailable. PostgreSQL collects statistics on the
distribution of data in the table, and can use constant values in a
statement to make guesses about the likely result of executing the
statement. Since this data is unavailable when planning prepared
statements with parameters, the chosen plan might be suboptimal.
I don't understand why postgres couldn't plan this:
SELECT foo.e, foo.f
FROM foo
WHERE pk=$1 AND b=$2 AND status='1' AND c <= $3;
to be later executed any slower than
SELECT foo.e, foo.f
FROM foo
WHERE pk='abcabc' AND b='13' AND status='1' AND c <= '2007-11-20 13:14:15';
Can I help it make more educated guesses? In what scenarios could
prepared statements turn around and bite me, being slower than simple
queries? Is this a real problem in practice? Should I "refresh" prepared
statements from time to time? If so, how? Only by deallocating them and
preparing anew? Any knob to tweak for that?
Okay, enough questions :)
Thank you for any insights.
From http://www.postgresql.org/docs/8.3/static/protocol-flow.html I
just read that "This possible penalty is avoided when using the unnamed
statement, since it is not planned until actual parameter values are
available."
Since I'm using Perl's DBI/pg, in postmaster's logs I can see that DBI's
prepare() seems to using named prepared statements:
Nov 7 15:57:46 sol postgres[1685]: [2-1] LOG: execute dbdpg_1:
Nov 7 15:57:46 sol postgres[1685]: [2-2] SELECT
...
is there any way to tell it to use unnamed prepared statements? I
understand this is not a strictly PostgreSQL question so sorry if I'm
off the topic.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
