Understanding PostgreSQL Prepared Statements and Plan Caching (Generic vs Custom)

PostgreSQL's PREPARE statement can be used to cache query execution plans, reducing the number of times a plan needs to be generated.

By default, the first five executions of a prepared statement use a custom plan; subsequent executions switch to a generic plan.

In PostgreSQL 14, the pg_prepared_statements view adds two columns, generic_plans and custom_plans , to track how many times each type of plan has been used.

Example of creating and executing a prepared statement:

bill@bill=> PREPARE pr1 AS SELECT * FROM pg_class WHERE relname = $1;
bill@bill=> EXECUTE pr1('t1');
bill@bill=> SELECT * FROM pg_prepared_statements;

After several executions, the view shows the counts of generic and custom plans:

name | statement | prepare_time | parameter_types | from_sql | generic_plans | custom_plans
-----+-----------+--------------+-----------------+----------+--------------+------------
pr1  | PREPARE pr1 AS SELECT * FROM pg_class WHERE relname = $1; | 2021-05-13 10:17:28.429238+08 | {name} | t | 2 | 5