14.4. Autoprepare#

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

ColumnTypeDescription
statementtext 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_typesregtype[]The types of the extracted or supplied parameters.
exec_countbigintNumber of executions of the query in this session.
used_memorybigintAmount 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 ALL or DISCARD PLANS is executed;

  • a configuration parameter is changed with SET, RESET or ALTER 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 BEGIN and COMMIT;

  • SELECT ... INTO;

  • in the simple query protocol, in a SELECT statement literals are replaced with parameters only in the target list, the WHERE, VALUES and WITH clauses 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. In INSERT, UPDATE, DELETE and MERGE statements 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.