Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitce734aa

Browse files
committed
Doc: improve PREPARE documentation, cross-referencing to plan_cache_mode.
The behavior described in the PREPARE man page applies only for thedefault plan_cache_mode setting, so explain that properly. Rewritesome of the text while I'm here. Per suggestion from Bruce.Discussion:https://postgr.es/m/20190930155505.GA21095@momjian.us
1 parent7e0fb16 commitce734aa

File tree

2 files changed

+50
-43
lines changed

2 files changed

+50
-43
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 14 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -5318,23 +5318,21 @@ SELECT * FROM parent WHERE key = 2400;
53185318
<listitem>
53195319
<para>
53205320
Prepared statements (either explicitly prepared or implicitly
5321-
generated, for example in PL/pgSQL) can be executed using custom or
5322-
generic plans. A custom plan is replanned for a new parameter value,
5323-
a generic plan is reused for repeated executions of the prepared
5324-
statement. The choice between them is normally made automatically.
5325-
This setting overrides the default behavior and forces either a custom
5326-
or a generic plan. This can be used to work around performance
5327-
problems in specific cases. Note, however, that the plan cache
5328-
behavior is subject to change, so this setting, like all settings that
5329-
force the planner's hand, should be reevaluated regularly.
5330-
</para>
5331-
5332-
<para>
5333-
The allowed values are <literal>auto</literal>,
5321+
generated, for example by PL/pgSQL) can be executed using custom or
5322+
generic plans. Custom plans are made afresh for each execution
5323+
using its specific set of parameter values, while generic plans do
5324+
not rely on the parameter values and can be re-used across
5325+
executions. Thus, use of a generic plan saves planning time, but if
5326+
the ideal plan depends strongly on the parameter values then a
5327+
generic plan may be inefficient. The choice between these options
5328+
is normally made automatically, but it can be overridden
5329+
with <varname>plan_cache_mode</varname>.
5330+
The allowed values are <literal>auto</literal> (the default),
53345331
<literal>force_custom_plan</literal> and
5335-
<literal>force_generic_plan</literal>. The default value is
5336-
<literal>auto</literal>. The setting is applied when a cached plan is
5337-
to be executed, not when it is prepared.
5332+
<literal>force_generic_plan</literal>.
5333+
This setting is considered when a cached plan is to be executed,
5334+
not when it is prepared.
5335+
For more information see <xref linkend="sql-prepare"/>.
53385336
</para>
53395337
</listitem>
53405338
</varlistentry>

‎doc/src/sgml/ref/prepare.sgml

Lines changed: 36 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -127,40 +127,49 @@ PREPARE <replaceable class="parameter">name</replaceable> [ ( <replaceable class
127127
<title>Notes</title>
128128

129129
<para>
130-
Prepared statements can use generic plans rather than re-planning with
131-
each set of supplied <command>EXECUTE</command> values. This occurs
132-
immediately for prepared statements with no parameters; otherwise
133-
it occurs only after five or more executions produce plans whose
134-
estimated cost average (including planning overhead) is more expensive
135-
than the generic plan cost estimate. Once a generic plan is chosen,
136-
it is used for the remaining lifetime of the prepared statement.
137-
Using <command>EXECUTE</command> values which are rare in columns with
138-
many duplicates can generate custom plans that are so much cheaper
139-
than the generic plan, even after adding planning overhead, that the
140-
generic plan might never be used.
130+
A prepared statement can be executed with either a <firstterm>generic
131+
plan</firstterm> or a <firstterm>custom plan</firstterm>. A generic
132+
plan is the same across all executions, while a custom plan is generated
133+
for a specific execution using the parameter values given in that call.
134+
Use of a generic plan avoids planning overhead, but in some situations
135+
a custom plan will be much more efficient to execute because the planner
136+
can make use of knowledge of the parameter values. (Of course, if the
137+
prepared statement has no parameters, then this is moot and a generic
138+
plan is always used.)
141139
</para>
142140

143141
<para>
144-
A generic plan assumes that each value supplied to
145-
<command>EXECUTE</command> is one of the column's distinct values
146-
and that column values are uniformly distributed. For example,
147-
if statistics record three distinct column values, a generic plan
148-
assumes a column equality comparison will match 33% of processed rows.
149-
Column statistics also allow generic plans to accurately compute the
150-
selectivity of unique columns. Comparisons on non-uniformly-distributed
151-
columns and specification of non-existent values affects the average
152-
plan cost, and hence if and when a generic plan is chosen.
142+
By default (that is, when <xref linkend="guc-plan-cache_mode"/> is set
143+
to <literal>auto</literal>), the server will automatically choose
144+
whether to use a generic or custom plan for a prepared statement that
145+
has parameters. The current rule for this is that the first five
146+
executions are done with custom plans and the average estimated cost of
147+
those plans is calculated. Then a generic plan is created and its
148+
estimated cost is compared to the average custom-plan cost. Subsequent
149+
executions use the generic plan if its cost is not so much higher than
150+
the average custom-plan cost as to make repeated replanning seem
151+
preferable.
152+
</para>
153+
154+
<para>
155+
This heuristic can be overridden, forcing the server to use either
156+
generic or custom plans, by setting <varname>plan_cache_mode</varname>
157+
to <literal>force_generic_plan</literal>
158+
or <literal>force_custom_plan</literal> respectively.
159+
This setting is primarily useful if the generic plan's cost estimate
160+
is badly off for some reason, allowing it to be chosen even though
161+
its actual cost is much more than that of a custom plan.
153162
</para>
154163

155164
<para>
156165
To examine the query plan <productname>PostgreSQL</productname> is using
157-
for a prepared statement, use <xref linkend="sql-explain"/>, e.g.
158-
<command>EXPLAIN EXECUTE</command>.
166+
for a prepared statement, use <xref linkend="sql-explain"/>, for example
167+
<programlisting>
168+
EXPLAIN EXECUTE <replaceable>stmt_name</replaceable>(<replaceable>parameter_values</replaceable>);
169+
</programlisting>
159170
If a generic plan is in use, it will contain parameter symbols
160-
<literal>$<replaceable>n</replaceable></literal>, while a custom plan will have the
161-
supplied parameter values substituted into it.
162-
The row estimates in the generic plan reflect the selectivity
163-
computed for the parameters.
171+
<literal>$<replaceable>n</replaceable></literal>, while a custom plan
172+
will have the supplied parameter values substituted into it.
164173
</para>
165174

166175
<para>
@@ -221,7 +230,7 @@ PREPARE usrrptplan (int) AS
221230
EXECUTE usrrptplan(1, current_date);
222231
</programlisting>
223232

224-
Note that the data type of the second parameter is not specified,
233+
In this example, the data type of the second parameter is not specified,
225234
so it is inferred from the context in which <literal>$2</literal> is used.
226235
</para>
227236
</refsect1>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp