PostgreSQL Prepared Statements

PostgreSQL, as a robust open-source database system, boasts numerous capabilities and features. However, its design lacks a global cache, unlike some other RDBMS like Oracle. This absence necessitates each backend session to parse queries individually, impacting the overall database performance, especially when dealing with multiple small but distinct queries.

To address this challenge, PostgreSQL introduces prepared statements. These allow for parameterizing queries using the PREPARE statement. For example:

PREPARE plane(text) AS
SELECT * FROM aircrafts WHERE aircraft_code = $1;

One of the key advantages of using prepared statements is the significant reduction in the risk of SQL injections.

Planning of Prepared Statements

The PostgreSQL planner maintains custom plans based on actual values passed to bind variables during execution. There are two types of plans: custom plans that consider specific values and generic plans that either take bind variables or no parameters at all.

The planner follows a rule to switch to generic plans. During the first five executions of parameterized prepared statements, the planner uses actual values for average cost calculations. If, starting from the fifth execution, the generic plans prove more efficient than the custom plans, the planner opts to keep the generic plan and skips the optimization stage for subsequent executions.

Consider the example:

explain EXECUTE plane('763');
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on aircrafts_data ml  (cost=0.00..1.39 rows=1 width=52)
   Filter: ((aircraft_code)::text = '763'::text)
(2 rows)

After the fifth execution, the planner switches to a generic plan:

explain EXECUTE plane('763');
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on aircrafts_data ml  (cost=0.00..1.39 rows=1 width=52)
   Filter: ((aircraft_code)::text = $1)
(2 rows)

As it is in the example, it does not differ from the custom ones except parameter as a bind value. In addition, their costs are the same, but it skips optimization stage for the planning and reduces the overhead.

Plan Cache Management

To override the planner’s decisions, you can use plan_cache_mode. For instance:

set plan_cache_mode = 'force_custom_plan';
SET
demo=# explain EXECUTE plane('CN1');
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on aircrafts_data ml  (cost=0.00..1.39 rows=1 width=52)
   Filter: ((aircraft_code)::text = 'CN1'::text)
(2 rows)

This ensures that custom plans are always used.

Monitoring Prepared Statements Usage

The usage of prepared statements can be monitored using pg_prepared_statements:

select name, generic_plans, custom_plans from pg_prepared_statements ;
 name  | generic_plans | custom_plans
-------+---------------+--------------
 plane |             5 |            6
(1 row)

This provides insights into the number of generic and custom plans for each prepared statement.

Feel free to reach out if you have any questions or suggestions for improvement!

Thanks!

Reference: PostgreSQL 14 Internals by Egor Rogov