14.6. Autoprepared Statements
Postgres Pro Enterprise provides the autoprepare mode that can implicitly prepare frequently used statements to eliminate the cost of their compilation and planning on each subsequent execution.
While providing slightly lower performance gains than explicitly prepared statements, this mode enables you to get prepared plans for cases where explicit PREPARE commands are not supported, such as using pgbouncer with a pooling level other than session, working with distributed partitioned tables, or running database applications that are not designed to execute prepared statements.
By default, the autoprepare mode is switched off. To enable this feature, assign a non-zero value to the autoprepare_threshold configuration variable, which sets the minimal number of times a statement should be executed to get autoprepared. Once the same query with different literal values is repeated the specified number of times, Postgres Pro builds a generic plan for this query, replacing all constant literals with parameters. The generic plan will be chosen for execution if the estimated time of this plan is lower than an average time of the customized plans. Just like for explicitly prepared statements, generic plans get invalidated when a catalog change occurs.
If your application issues many different statements, autopreparing them all can cause memory overflow unless you impose any cache restrictions. It is especially important when running multiple active clients as the cache of autoprepared statements is local to the backend. To avoid cache bloat, you can do the following:
Limit the number of autoprepared statements per backend using the autoprepare_limit parameter. This setting is recommended for workloads with multiple simple queries.
Limit the amount of memory that can be allocated for autoprepared statements on a backend using the autoprepare_memory_limit parameter. This setting can incur some overhead when calculating the amount of cache used for autoprepared statements, so it is only recommended for workloads that contain complex queries for which limiting the number of autoprepared statements may be ineffective.
In both cases, most frequently used queries are kept in memory using the LRU strategy. If both parameters are set, Postgres Pro enforces the first reached limit.
You can check all autoprepared statements available in the current session in the pg_autoprepared_statements system view. It shows the original text of the query, types of the extracted parameters that replace literals, and the query execution counter.