🌐 AI搜索 & 代理 主页
Skip to content

Commit e6faf91

Browse files
tglsfdctglsfdc
committed
Redesign the plancache mechanism for more flexibility and efficiency.
Rewrite plancache.c so that a "cached plan" (which is rather a misnomer at this point) can support generation of custom, parameter-value-dependent plans, and can make an intelligent choice between using custom plans and the traditional generic-plan approach. The specific choice algorithm implemented here can probably be improved in future, but this commit is all about getting the mechanism in place, not the policy. In addition, restructure the API to greatly reduce the amount of extraneous data copying needed. The main compromise needed to make that possible was to split the initial creation of a CachedPlanSource into two steps. It's worth noting in particular that SPI_saveplan is now deprecated in favor of SPI_keepplan, which accomplishes the same end result with zero data copying, and no need to then spend even more cycles throwing away the original SPIPlan. The risk of long-term memory leaks while manipulating SPIPlans has also been greatly reduced. Most of this improvement is based on use of the recently-added MemoryContextSetParent primitive.
1 parent 09e98a3 commit e6faf91

File tree

27 files changed

+1909
-1339
lines changed

27 files changed

+1909
-1339
lines changed

‎contrib/spi/refint.c‎

Lines changed: 6 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -190,12 +190,11 @@ check_primary_key(PG_FUNCTION_ARGS)
190190

191191
/*
192192
* Remember that SPI_prepare places plan in current memory context -
193-
* so, we have to save plan in Top memory context for latter use.
193+
* so, we have to save plan in Top memory context for later use.
194194
*/
195-
pplan = SPI_saveplan(pplan);
196-
if (pplan == NULL)
195+
if (SPI_keepplan(pplan))
197196
/* internal error */
198-
elog(ERROR, "check_primary_key: SPI_saveplan returned %d", SPI_result);
197+
elog(ERROR, "check_primary_key: SPI_keepplan failed");
199198
plan->splan = (SPIPlanPtr *) malloc(sizeof(SPIPlanPtr));
200199
*(plan->splan) = pplan;
201200
plan->nplans = 1;
@@ -537,13 +536,12 @@ check_foreign_key(PG_FUNCTION_ARGS)
537536

538537
/*
539538
* Remember that SPI_prepare places plan in current memory context
540-
* - so, we have to save plan in Top memory context for latter
539+
* - so, we have to save plan in Top memory context for later
541540
* use.
542541
*/
543-
pplan = SPI_saveplan(pplan);
544-
if (pplan == NULL)
542+
if (SPI_keepplan(pplan))
545543
/* internal error */
546-
elog(ERROR, "check_foreign_key: SPI_saveplan returned %d", SPI_result);
544+
elog(ERROR, "check_foreign_key: SPI_keepplan failed");
547545

548546
plan->splan[r] = pplan;
549547

‎contrib/spi/timetravel.c‎

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -345,11 +345,10 @@ timetravel(PG_FUNCTION_ARGS)
345345

346346
/*
347347
* Remember that SPI_prepare places plan in current memory context -
348-
* so, we have to save plan in Top memory context for latter use.
348+
* so, we have to save plan in Top memory context for later use.
349349
*/
350-
pplan = SPI_saveplan(pplan);
351-
if (pplan == NULL)
352-
elog(ERROR, "timetravel (%s): SPI_saveplan returned %d", relname, SPI_result);
350+
if (SPI_keepplan(pplan))
351+
elog(ERROR, "timetravel (%s): SPI_keepplan failed", relname);
353352

354353
plan->splan = pplan;
355354
}

‎doc/src/sgml/plpgsql.sgml‎

Lines changed: 51 additions & 68 deletions
Original file line numberDiff line numberDiff line change
@@ -839,12 +839,10 @@ PREPARE <replaceable>statement_name</>(integer, integer) AS SELECT $1 &lt; $2;
839839
and then this prepared statement is <command>EXECUTE</>d for each
840840
execution of the <command>IF</> statement, with the current values
841841
of the <application>PL/pgSQL</application> variables supplied as
842-
parameter values.
843-
The query plan prepared in this way is saved for the life of the database
844-
connection, as described in
845-
<xref linkend="plpgsql-plan-caching">. Normally these details are
842+
parameter values. Normally these details are
846843
not important to a <application>PL/pgSQL</application> user, but
847844
they are useful to know when trying to diagnose a problem.
845+
More information appears in <xref linkend="plpgsql-plan-caching">.
848846
</para>
849847
</sect1>
850848

@@ -919,10 +917,9 @@ my_record.user_id := 20;
919917

920918
<para>
921919
When executing a SQL command in this way,
922-
<application>PL/pgSQL</application> plans the command just once
923-
and re-uses the plan on subsequent executions, for the life of
924-
the database connection. The implications of this are discussed
925-
in detail in <xref linkend="plpgsql-plan-caching">.
920+
<application>PL/pgSQL</application> may cache and re-use the execution
921+
plan for the command, as discussed in
922+
<xref linkend="plpgsql-plan-caching">.
926923
</para>
927924

928925
<para>
@@ -1137,8 +1134,8 @@ EXECUTE <replaceable class="command">command-string</replaceable> <optional> INT
11371134

11381135
<para>
11391136
Also, there is no plan caching for commands executed via
1140-
<command>EXECUTE</command>. Instead, the
1141-
command is prepared each time the statement is run. Thus the command
1137+
<command>EXECUTE</command>. Instead, the command is always planned
1138+
each time the statement is run. Thus the command
11421139
string can be dynamically created within the function to perform
11431140
actions on different tables and columns.
11441141
</para>
@@ -1206,11 +1203,11 @@ EXECUTE 'SELECT count(*) FROM '
12061203
The important difference is that <command>EXECUTE</> will re-plan
12071204
the command on each execution, generating a plan that is specific
12081205
to the current parameter values; whereas
1209-
<application>PL/pgSQL</application> normally creates a generic plan
1210-
and caches it for re-use. In situations where the best plan depends
1211-
strongly on the parameter values, <command>EXECUTE</> can be
1212-
significantly faster; while when the plan is not sensitive to parameter
1213-
values, re-planning will be a waste.
1206+
<application>PL/pgSQL</application> may otherwise create a generic plan
1207+
and cache it for re-use. In situations where the best plan depends
1208+
strongly on the parameter values, it can be helpful to use
1209+
<command>EXECUTE</> to positively ensure that a generic plan is not
1210+
selected.
12141211
</para>
12151212

12161213
<para>
@@ -4103,79 +4100,61 @@ $$ LANGUAGE plpgsql;
41034100
</indexterm>
41044101
As each expression and <acronym>SQL</acronym> command is first
41054102
executed in the function, the <application>PL/pgSQL</> interpreter
4106-
creates a prepared execution plan (using the
4107-
<acronym>SPI</acronym> manager's <function>SPI_prepare</function>
4108-
and <function>SPI_saveplan</function> functions).
4103+
parses and analyzes the command to create a prepared statement,
4104+
using the <acronym>SPI</acronym> manager's
4105+
<function>SPI_prepare</function> function.
41094106
Subsequent visits to that expression or command
4110-
reuse the prepared plan. Thus, a function with conditional code
4111-
that contains many statements for which execution plans might be
4112-
required will only prepare and save those plans that are really
4113-
used during the lifetime of the database connection. This can
4114-
substantially reduce the total amount of time required to parse
4115-
and generate execution plans for the statements in a
4116-
<application>PL/pgSQL</> function. A disadvantage is that errors
4107+
reuse the prepared statement. Thus, a function with conditional code
4108+
paths that are seldom visited will never incur the overhead of
4109+
analyzing those commands that are never executed within the current
4110+
session. A disadvantage is that errors
41174111
in a specific expression or command cannot be detected until that
41184112
part of the function is reached in execution. (Trivial syntax
41194113
errors will be detected during the initial parsing pass, but
41204114
anything deeper will not be detected until execution.)
41214115
</para>
41224116

41234117
<para>
4124-
A saved plan will be re-planned automatically if there is any schema
4125-
change to any table used in the query, or if any user-defined function
4126-
used in the query is redefined. This makes the re-use of prepared plans
4127-
transparent in most cases, but there are corner cases where a stale plan
4128-
might be re-used. An example is that dropping and re-creating a
4129-
user-defined operator won't affect already-cached plans; they'll continue
4130-
to call the original operator's underlying function, if that has not been
4131-
changed. When necessary, the cache can be flushed by starting a fresh
4132-
database session.
4118+
<application>PL/pgSQL</> (or more precisely, the SPI manager) can
4119+
furthermore attempt to cache the execution plan associated with any
4120+
particular prepared statement. If a cached plan is not used, then
4121+
a fresh execution plan is generated on each visit to the statement,
4122+
and the current parameter values (that is, <application>PL/pgSQL</>
4123+
variable values) can be used to optimize the selected plan. If the
4124+
statement has no parameters, or is executed many times, the SPI manager
4125+
will consider creating a <firstterm>generic</> plan that is not dependent
4126+
on specific parameter values, and caching that for re-use. Typically
4127+
this will happen only if the execution plan is not very sensitive to
4128+
the values of the <application>PL/pgSQL</> variables referenced in it.
4129+
If it is, generating a plan each time is a net win.
41334130
</para>
41344131

41354132
<para>
4136-
Because <application>PL/pgSQL</application> saves execution plans
4137-
in this way, SQL commands that appear directly in a
4133+
Because <application>PL/pgSQL</application> saves prepared statements
4134+
and sometimes execution plans in this way,
4135+
SQL commands that appear directly in a
41384136
<application>PL/pgSQL</application> function must refer to the
41394137
same tables and columns on every execution; that is, you cannot use
41404138
a parameter as the name of a table or column in an SQL command. To get
41414139
around this restriction, you can construct dynamic commands using
41424140
the <application>PL/pgSQL</application> <command>EXECUTE</command>
4143-
statement &mdash; at the price of constructing a new execution plan on
4144-
every execution.
4145-
</para>
4146-
4147-
<para>
4148-
Another important point is that the prepared plans are parameterized
4149-
to allow the values of <application>PL/pgSQL</application> variables
4150-
to change from one use to the next, as discussed in detail above.
4151-
Sometimes this means that a plan is less efficient than it would be
4152-
if generated for a specific variable value. As an example, consider
4153-
<programlisting>
4154-
SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;
4155-
</programlisting>
4156-
where <literal>search_term</> is a <application>PL/pgSQL</application>
4157-
variable. The cached plan for this query will never use an index on
4158-
<structfield>word</>, since the planner cannot assume that the
4159-
<literal>LIKE</> pattern will be left-anchored at run time. To use
4160-
an index the query must be planned with a specific constant
4161-
<literal>LIKE</> pattern provided. This is another situation where
4162-
<command>EXECUTE</command> can be used to force a new plan to be
4163-
generated for each execution.
4141+
statement &mdash; at the price of performing new parse analysis and
4142+
constructing a new execution plan on every execution.
41644143
</para>
41654144

41664145
<para>
41674146
The mutable nature of record variables presents another problem in this
41684147
connection. When fields of a record variable are used in
41694148
expressions or statements, the data types of the fields must not
41704149
change from one call of the function to the next, since each
4171-
expression will be planned using the data type that is present
4150+
expression will be analyzed using the data type that is present
41724151
when the expression is first reached. <command>EXECUTE</command> can be
41734152
used to get around this problem when necessary.
41744153
</para>
41754154

41764155
<para>
41774156
If the same function is used as a trigger for more than one table,
4178-
<application>PL/pgSQL</application> prepares and caches plans
4157+
<application>PL/pgSQL</application> prepares and caches statements
41794158
independently for each such table &mdash; that is, there is a cache
41804159
for each trigger function and table combination, not just for each
41814160
function. This alleviates some of the problems with varying
@@ -4186,14 +4165,14 @@ SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;
41864165

41874166
<para>
41884167
Likewise, functions having polymorphic argument types have a separate
4189-
plan cache for each combination of actual argument types they have been
4190-
invoked for, so that data type differences do not cause unexpected
4168+
statement cache for each combination of actual argument types they have
4169+
been invoked for, so that data type differences do not cause unexpected
41914170
failures.
41924171
</para>
41934172

41944173
<para>
4195-
Plan caching can sometimes have surprising effects on the interpretation
4196-
of time-sensitive values. For example there
4174+
Statement caching can sometimes have surprising effects on the
4175+
interpretation of time-sensitive values. For example there
41974176
is a difference between what these two functions do:
41984177

41994178
<programlisting>
@@ -4221,15 +4200,17 @@ $$ LANGUAGE plpgsql;
42214200
<para>
42224201
In the case of <function>logfunc1</function>, the
42234202
<productname>PostgreSQL</productname> main parser knows when
4224-
preparing the plan for the <command>INSERT</command> that the
4203+
analyzing the <command>INSERT</command> that the
42254204
string <literal>'now'</literal> should be interpreted as
42264205
<type>timestamp</type>, because the target column of
42274206
<classname>logtable</classname> is of that type. Thus,
4228-
<literal>'now'</literal> will be converted to a constant when the
4229-
<command>INSERT</command> is planned, and then used in all
4207+
<literal>'now'</literal> will be converted to a <type>timestamp</type>
4208+
constant when the
4209+
<command>INSERT</command> is analyzed, and then used in all
42304210
invocations of <function>logfunc1</function> during the lifetime
42314211
of the session. Needless to say, this isn't what the programmer
4232-
wanted.
4212+
wanted. A better idea is to use the <literal>now()</> or
4213+
<literal>current_timestamp</> function.
42334214
</para>
42344215

42354216
<para>
@@ -4243,7 +4224,9 @@ $$ LANGUAGE plpgsql;
42434224
string to the <type>timestamp</type> type by calling the
42444225
<function>text_out</function> and <function>timestamp_in</function>
42454226
functions for the conversion. So, the computed time stamp is updated
4246-
on each execution as the programmer expects.
4227+
on each execution as the programmer expects. Even though this
4228+
happens to work as expected, it's not terribly efficient, so
4229+
use of the <literal>now()</> function would still be a better idea.
42474230
</para>
42484231

42494232
</sect2>

‎doc/src/sgml/protocol.sgml‎

Lines changed: 15 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -125,9 +125,8 @@
125125
into multiple steps. The state retained between steps is represented
126126
by two types of objects: <firstterm>prepared statements</> and
127127
<firstterm>portals</>. A prepared statement represents the result of
128-
parsing, semantic analysis, and (optionally) planning of a textual query
129-
string.
130-
A prepared statement is not necessarily ready to execute, because it might
128+
parsing and semantic analysis of a textual query string.
129+
A prepared statement is not in itself ready to execute, because it might
131130
lack specific values for <firstterm>parameters</>. A portal represents
132131
a ready-to-execute or already-partially-executed statement, with any
133132
missing parameter values filled in. (For <command>SELECT</> statements,
@@ -692,7 +691,7 @@
692691
the unnamed statement as destination is issued. (Note that a simple
693692
Query message also destroys the unnamed statement.) Named prepared
694693
statements must be explicitly closed before they can be redefined by
695-
a Parse message, but this is not required for the unnamed statement.
694+
another Parse message, but this is not required for the unnamed statement.
696695
Named prepared statements can also be created and accessed at the SQL
697696
command level, using <command>PREPARE</> and <command>EXECUTE</>.
698697
</para>
@@ -722,44 +721,23 @@
722721
</note>
723722

724723
<para>
725-
Query planning for named prepared-statement objects occurs when the Parse
726-
message is processed. If a query will be repeatedly executed with
727-
different parameters, it might be beneficial to send a single Parse message
728-
containing a parameterized query, followed by multiple Bind
729-
and Execute messages. This will avoid replanning the query on each
730-
execution.
724+
Query planning typically occurs when the Bind message is processed.
725+
If the prepared statement has no parameters, or is executed repeatedly,
726+
the server might save the created plan and re-use it during subsequent
727+
Bind messages for the same prepared statement. However, it will do so
728+
only if it finds that a generic plan can be created that is not much
729+
less efficient than a plan that depends on the specific parameter values
730+
supplied. This happens transparently so far as the protocol is concerned.
731731
</para>
732732

733-
<para>
734-
The unnamed prepared statement is likewise planned during Parse processing
735-
if the Parse message defines no parameters. But if there are parameters,
736-
query planning occurs every time Bind parameters are supplied. This allows the
737-
planner to make use of the actual values of the parameters provided by
738-
each Bind message, rather than use generic estimates.
739-
</para>
740-
741-
<note>
742-
<para>
743-
Query plans generated from a parameterized query might be less
744-
efficient than query plans generated from an equivalent query with actual
745-
parameter values substituted. The query planner cannot make decisions
746-
based on actual parameter values (for example, index selectivity) when
747-
planning a parameterized query assigned to a named prepared-statement
748-
object. This possible penalty is avoided when using the unnamed
749-
statement, since it is not planned until actual parameter values are
750-
available. The cost is that planning must occur afresh for each Bind,
751-
even if the query stays the same.
752-
</para>
753-
</note>
754-
755733
<para>
756734
If successfully created, a named portal object lasts till the end of the
757735
current transaction, unless explicitly destroyed. An unnamed portal is
758736
destroyed at the end of the transaction, or as soon as the next Bind
759737
statement specifying the unnamed portal as destination is issued. (Note
760738
that a simple Query message also destroys the unnamed portal.) Named
761-
portals must be explicitly closed before they can be redefined by a Bind
762-
message, but this is not required for the unnamed portal.
739+
portals must be explicitly closed before they can be redefined by another
740+
Bind message, but this is not required for the unnamed portal.
763741
Named portals can also be created and accessed at the SQL
764742
command level, using <command>DECLARE CURSOR</> and <command>FETCH</>.
765743
</para>
@@ -1280,7 +1258,9 @@
12801258
The frontend should also be prepared to handle an ErrorMessage
12811259
response to SSLRequest from the server. This would only occur if
12821260
the server predates the addition of <acronym>SSL</acronym> support
1283-
to <productname>PostgreSQL</>. In this case the connection must
1261+
to <productname>PostgreSQL</>. (Such servers are now very ancient,
1262+
and likely do not exist in the wild anymore.)
1263+
In this case the connection must
12841264
be closed, but the frontend might choose to open a fresh connection
12851265
and proceed without requesting <acronym>SSL</acronym>.
12861266
</para>

0 commit comments

Comments
 (0)