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.

When this feature is enabled, queries submitted via both simple and extended query protocols could be autoprepared. The extended protocol supports the transmission of parameterized queries, and autopreparing of such queries incurs less overhead. On the other hand, queries received via different protocols can be very diverse in nature, and autopreparing is ineffective for some query types. Hence, the autoprepare_for_protocol parameter allows to enable/disable the statement autopreparing on the protocol level.

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:

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.

Note that the cache of autoprepared statements is fully cleaned up when executing the following commands:

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.