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:

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.