14.4. Autoprepare#
14.4. Autoprepare #
Support for implicit preparation of frequently executed queries
(autoprepare) has been added to the server core.
When the feature is enabled, the backend caches the plan of a query on
its own and reuses that plan on subsequent executions, so parse
analysis, rewriting and planning are skipped. In the simple query
protocol the query text is still parsed, since the raw parse tree is
needed to find the cache entry; in the extended query protocol a cache
hit skips the processing of the Parse message
completely. No application changes are required:
explicit PREPARE
and EXECUTE
statements are not needed.
The implementation consists of two parts — for simple and extended protocols:
For the simple query protocol the query is generalized at the raw parse tree level: literals are replaced with parameters, and the cache key is the generalized parse tree together with the types of the extracted parameters. Queries that differ only in their literal values therefore share a single cache entry.
For the extended query protocol unnamed prepared statements are cached; the cache key is the query text together with the parameter types. Literals are not generalized in this case, because the parameters are supplied by the application itself.
The cache is private to the backend process and is discarded together with the session; nothing is shared between sessions.
14.4.1. Configuration #
The feature is controlled by four configuration parameters. All of them
can be set in postgresql.conf, with
ALTER SYSTEM, per database or role with
ALTER DATABASE ... SET and
ALTER ROLE ... SET, or in a session with the
SET command.
14.4.1.1. autoprepare_threshold #
Number of times a query must be executed before it is prepared implicitly.
| Type | integer |
| Default |
0
|
The default value is 0, which disables autoprepare
completely; this parameter is the main switch of the feature. The plan
is built at the execution at which the counter reaches this value, so
with 1 a query is prepared already at its first
execution.
14.4.1.2. autoprepare_limit #
Maximal number of queries kept in the autoprepare cache of a session.
| Type | integer |
| Default |
113
|
The default
value is 113; 0 means an
unlimited number of autoprepared queries. The limit counts all
tracked queries of both protocols, including those that have not
reached autoprepare_threshold yet and so have no
plan. Entries are evicted in
least recently used order. Too large a number of prepared queries can
cause backend memory overflow and slow down execution because of the
increased lookup time.
14.4.1.3. autoprepare_memory_limit #
Maximal size of memory used by autoprepared queries, in kilobytes.
| Type | integer |
| Default |
0
|
The default value is 0, which means that there is
no memory limit. Calculating the memory used by prepared queries adds
some extra overhead, so a non-zero value of this parameter may cause
some slowdown; autoprepare_limit is a much cheaper
way to limit the number of autoprepared statements.
14.4.1.4. autoprepare_for_protocol #
Sets the protocol used for submitting queries that could be autoprepared.
| Type | enum |
| Default |
all
|
Autoprepare is performed only for the queries received
through the selected protocol. Valid values are
simple, extended and
all; the default value is all.
14.4.1.5. Fragment of postgresql.conf #
The corresponding fragment of
postgresql.conf:
#autoprepare_threshold = 0 # 0 disables autoprepare #autoprepare_limit = 113 # 0 means unlimited #autoprepare_memory_limit = 0 # in kB; 0 means no limit #autoprepare_for_protocol = all # simple, extended, or all
14.4.2. Enabling the feature #
Autoprepare is disabled by default. To enable it for the whole instance, set a non-zero threshold and reload the configuration:
ALTER SYSTEM SET autoprepare_threshold = 2; SELECT pg_reload_conf();
To enable it only for a particular database, role or session, use
ALTER DATABASE ... SET,
ALTER ROLE ... SET or SET
respectively. Setting autoprepare_threshold back to
0 disables the feature and drops the cache.
14.4.3. Monitoring #
The contents of the cache of the current session are shown by the
pg_autoprepared_statements view, which is
built on top of the pg_autoprepared_statement()
function:
Table 14.1. pg_autoprepared_statements Columns
| Column | Type | Description |
|---|---|---|
statement | text |
The query string as it was submitted by the client. The generalized
form is not shown, so literals are visible for the simple query
protocol, while for the extended query protocol the parameter
placeholders written by the application
($1, $2 and so on) are visible.
|
parameter_types | regtype[] | The types of the extracted or supplied parameters. |
exec_count | bigint | Number of executions of the query in this session. |
used_memory | bigint | Amount of memory occupied by the cached plan. |
Only queries whose plan is actually cached are shown. A query is
counted internally from its first execution, but it appears in the view
no earlier than the execution at which
exec_count reaches
autoprepare_threshold. Entries for which planning
failed or which turned out to be unsuitable for autopreparing are
not shown either.
14.4.4. Cache invalidation #
The whole cache of autoprepared queries is reset when:
DISCARD ALLorDISCARD PLANSis executed;a configuration parameter is changed with
SET,RESETorALTER SYSTEM;a DDL command is executed;
the system caches are invalidated completely.
This behaviour is intentionally conservative: any change of the environment that can affect the meaning or the plan of a query drops the cache instead of invalidating individual entries.
14.4.5. Limitations #
The following queries are never autoprepared, and the following literals are never replaced with parameters:
utility statements and transaction control statements such as
BEGINandCOMMIT;SELECT ... INTO;in the simple query protocol, in a
SELECTstatement literals are replaced with parameters only in the target list, theWHERE,VALUESandWITHclauses and in the arms of set operations; literals inside type names, type casts, sort and grouping clauses and array constructors are left as is, because parameterizing them would change the meaning of the query. InINSERT,UPDATE,DELETEandMERGEstatements the whole raw parse tree is traversed;null and boolean literals are never replaced with parameters, and neither are literals of an expression all of whose operands are literals, since such an expression is the same for all queries;
in the extended query protocol only unnamed prepared statements are cached, since named statements are already reused by the application itself.
Note
As with an explicit PREPARE, a cached plan may
become generic, which can be undesirable for queries over heavily
skewed data. This is governed by the standard
plan_cache_mode parameter.