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 (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers