In Postgres 9.2 we improved the logic of when generic plans are used by
EXECUTE. We weren't sure how well it would work, and the docs included
a vague description on when generic plans are chosen.
I have gotten a few questions lately about how prepared statements are
handled with multiple executions so I have updated the PREPARE manual
page with the attached patch to more clearly explain generic plans and
when they are chosen.
I would like to apply this to the 9.6 docs.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
new file mode 100644
index dbce8f2..fce6e96
*** a/doc/src/sgml/ref/prepare.sgml
--- b/doc/src/sgml/ref/prepare.sgml
*************** PREPARE <replaceable class="PARAMETER">n
*** 127,140 ****
<title>Notes</title>
<para>
! If a prepared statement is executed enough times, the server may eventually
! decide to save and re-use a generic plan rather than re-planning each time.
! This will occur immediately if the prepared statement has no parameters;
! otherwise it occurs only if the generic plan appears to be not much more
! expensive than a plan that depends on specific parameter values.
! Typically, a generic plan will be selected only if the query's performance
! is estimated to be fairly insensitive to the specific parameter values
! supplied.
</para>
<para>
--- 127,147 ----
<title>Notes</title>
<para>
! Prepared statements can optionally use generic plans rather than
! re-planning with each set of supplied <command>EXECUTE</command> values.
! This occurs immediately for prepared statements with no parameters;
! otherwise it occurs only after five or more executions produce plans
! plus planning costs that are, on average, cheaper than the generic plan.
! A generic plan assumes each value supplied to <command>EXECUTE</command>
! is one of the column's distinct values and that column values are
! uniformly distributed. For example, if statistics records three
! distinct column values, a generic plan assumes a column equality
! comparison will match 33% of processed rows. Column statistics
! also allows generic plans to accurately compute the selectivity of
! unique columns. Comparisons on non-uniformly-distributed columns and
! specification of non-existent values affects the average plan cost,
! and hence if and when a generic plan is chosen. Once a generic plan is
! chosen, it is used for the remaining lifetime of the prepared statement.
</para>
<para>
*************** PREPARE <replaceable class="PARAMETER">n
*** 142,148 ****
for a prepared statement, use <xref linkend="sql-explain">.
If a generic plan is in use, it will contain parameter symbols
<literal>$<replaceable>n</></literal>, while a custom plan will have the
! current actual parameter values substituted into it.
</para>
<para>
--- 149,157 ----
for a prepared statement, use <xref linkend="sql-explain">.
If a generic plan is in use, it will contain parameter symbols
<literal>$<replaceable>n</></literal>, while a custom plan will have the
! supplied parameter values substituted into it.
! The row estimates in the generic plan reflect the selectivity
! computed for the parameters.
</para>
<para>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
