On Postgres Plan Cache Mode Management

Can the generic plan switch method provide better performance guarantees?

Having attended PGConf.DE'2025 and discussed the practice of using Postgres on large databases there, I was surprised to regularly hear the opinion that query planning time is a significant issue. As a developer, it was surprising to learn that this factor can, for example, slow down the decision to move to a partitioned schema, which seems like a logical step once the number of records in a table exceeds 100 million. Well, let's figure it out.

The obvious way out of this situation is to use prepared statements, initially intended for reusing labour-intensive parts such as parse trees and query plans. For more specifics, let's look at a simple table scan with a large number of partitions (see initialisation script):

EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF)
SELECT * FROM test WHERE y = 127;

/*
...
   ->  Seq Scan on l256 test_256
         Filter: (y = 127)
 Planning:
   Buffers: shared hit=1536
   Memory: used=3787kB  allocated=4104kB
 Planning Time: 61.272 ms
 Execution Time: 4.929 ms
*/

In this scenario involving a selection from a table with 256 partitions, my laptop's PostgreSQL took approximately 60 milliseconds for the planning phase and only 5 milliseconds for execution. During the planning process, it allocated 4 MB of RAM and accessed 1,500 data pages. Quite substantial overhead for a production environment! In this case, PostgreSQL has generated a custom plan that is compiled anew each time the query is executed, choosing an execution strategy based on the query parameter values during optimisation. To improve efficiency, let's parameterise this query and store it in the 'Plan Cache' of the backend by executing PREPARE:

PREPARE tst (integer) AS SELECT * FROM test WHERE y = $1;
EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF) EXECUTE tst(127);

/*
...
   ->  Seq Scan on l256 test_256
         Filter: (y = $1)
 Planning:
   Buffers: shared hit=1536
   Memory: used=3772kB  allocated=4120kB
 Planning Time: 59.525 ms
 Execution Time: 5.184 ms
*/

The planning workload remains the same since a custom plan has been used. Let's force the backend to generate and use a 'generic' plan:

SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF) EXECUTE tst(127);

/*
...
  ->  Seq Scan on l256 test_256
         Filter: (y = $1)
 Planning:
   Memory: used=4kB  allocated=24kB
 Planning Time: 0.272 ms
 Execution Time: 2.810 ms
*/

The first time the query is executed, a generic execution plan is created (we are using forced mode here to keep the example straightforward). This process requires resources nearly equivalent to those needed for building a custom plan. However, when the query is executed again, the generic plan can be quickly retrieved from the cache. As a result, the time spent preparing the query plan drops to just 0.2 ms, memory usage is only 24 KB, and no data page reads are required. It seems we have a clear benefit!

However, my suggestion to use the PREPARE command has often been met with rejection and scepticism. This is primarily due to the problems that arise with generic plans in practice, particularly regarding their updating (replanning) and switching to a custom plan type. To gain a clearer understanding of how the generic plan mechanism is structured and to explore the root of these issues, I decided to investigate the history of this project. Additionally, I aimed to experiment with a new publication format, such as a mailing list review.

What’s wrong with a generic plan?

Upon examining the Git history of PostgreSQL, it appears that the concept of the plan cache was introduced in 2007 with commit b9527e9. At that time, it was decided that each prepared query in PostgreSQL should be executed exclusively using a generic plan, thereby avoiding unnecessary time spent on rebuilding the plan. Unlike Oracle, SQL Server, DB2 and other colleagues in the shop (see link, link, link, and link), PostgreSQL constructs the generic plan with the 'total uncertainty' concept, without utilising any specific 'reference' parameter value. For instance, in the example mentioned, the constant '127' is set aside during the creation of the generic plan.

Due to its limited ability to estimate scan selectivities, the optimiser often depends on default 'magic' values of certain predefined constants. Consequently, a generic plan is often of lower quality compared to a custom one. Let me provide another example to illustrate this point more clearly (see the reproduction script):

EXPLAIN
SELECT * FROM test_2
WHERE
  start_date > '2025-06-30'::timestamp - '7 days'::interval;
/*
 Index Scan using test_2_start_date_idx on test_2  (rows=739)
   Index Cond: (start_date > '2025-06-23 00:00:00'::timestamp)
*/

PREPARE tst3(timestamp) AS SELECT * FROM test_2
  WHERE start_date > $1 - '7 days'::interval;

EXPLAIN EXECUTE tst3('2025-06-30'::timestamp);
/*
 Seq Scan on test_2  (rows=333333)
   Filter: (start_date > ($1 - '7 days'::interval))
*/

Offhand, here are some key reasons to consider: the lack of a constant in the inequality operator results in a filter estimate of 33%; for range filters, the default value is set at 0.5% of the total number of rows in the table; with the equality operator, using MCV statistics is not possible, so we must rely solely on the ndistinct value. Additionally, in certain situations, it is not feasible to use partial indexes.

Let's turn to the origins

The absence of alternatives resulted in a significant decline in performance and the infrequent use of the generally practical PREPARE/EXECUTE statement construct. In 2011, a discussion began that ultimately led to the e6faf91 commit, which introduced a simple automatic technique for switching between custom and generic plan variants.

This discussion began with the pressing issue that prepared statements were executed exclusively using generic plans (Mark Mielke, link). While these plans were rebuilt each time an invalidation signal was received, such as after executing the ANALYZE or ALTER TABLE commands, the quality of the planning was noticeably inferior.

Several ideas were proposed to address this problem:

1. Periodically, replan the generic plan (Jeroen Vermeulen, link).

2. Introduce a threshold for the 'planning/execution time' ratio - If the criterion value is greater than 100, then use only the generic plan; if less than 0.01, then only the custom plan. (Bart Samwel link. Yeb Havinga opposes (link) this idea - an objective criterion should not contain the 'time' parameter). However, Jeroen Vermeulen and Greg Stark (link) supported this idea with the clause that the difference between planning and execution times should be significant, amounting to orders of magnitude.

3. Track the standard deviation (stddev) value of various parameters for executing a specific query plan, which will enable estimating the probability of how long the query will take to plan and execute next time (Greg Stark, link).

4. Build several custom and generic plans, and make a choice based on the cost ratio (Tom Lane, link).

5. Abandon generic plans altogether, while reducing the cost of replanning by preserving the PlannerInfo optimiser 'cache' and replanning only that part of the jointree / subquery where the parameters are actually used (Yeb Havinga, link).

6. Use generic plans, but introduce a replanning criterion - whether the parameter value falls within the MCV or not (Robert Haas (link, link), supported by Jeff Davis).

Interestingly, the idea of re-optimisation was already being discussed back then (Richard Huxton, link). At that time, it was more of a dream, but by the 2020s, the code infrastructure had matured enough to allow us to implement a similar concept in a short time (see replan). The approach of detecting, generalising, and caching frequently arriving statements through a simple protocol, which we implemented in sr_plan, is also explicitly described here (Robert Haas, link), along with Yeb Havinga's idea of achieving this through a method similar to the then non-existent queryId (link).

At the same time, in 2011, Simon Riggs introduced the concept of a one-shot plan. The primary idea behind this type of plan is to inform the DBMS that a query plan will be created, executed immediately, and subsequently destroyed upon completion. This approach allows for the application of additional optimisations that are not relevant when there is no connection between the planning and execution phases.

To support this idea, Simon provided an example involving the calculation of stable functions, which would enable more efficient execution of partition pruning. Additionally, Bruce Momjian highlighted another potential optimisation that could be implemented in a one-shot plan: analysing the buffer cache to assess the effectiveness of using a specific index.

Meanwhile, Tom Lane was developing a similar feature, motivated by complaints about regressions in dynamic SQL queries (link, link). However, his approach was different from Simon Riggs' original concept. Tom Lane's idea focused on unifying the mechanisms of SPI, PREPARE, and the extended protocol through the use of a plan cache. As a result, Riggs' original idea did not receive much further development, though it was discussed later on (link, link).

The concept of tracking the planning and execution time of queries did not gain traction due to objections from Tom Lane, who argued against using this time characteristic, as it is inherently unpredictable and can behave inconsistently across different systems.

In 2017, Pavel Stehule raised the need for explicit control over the type of plan selected when invoking the plan cache. This discussion led to the introduction of the plan_cache_mode parameter, which has two options: force_generic_plan and force_custom_plan. These options are designed specifically for using generic and custom plan types, respectively.

What stands out to me as a developer is the emphasis on several key concepts from the Postgres core that emerged during these discussions.

1. Tom Lane pointed out that in the absence of a general solution, we should develop heuristics. Providing users with such solutions through an additional GUC is a poor idea and ultimately a compromise.

2. Greg Stark and Pavel Stehule emphasised that the predictability of execution is more important than speed.

3. Tom Lane also noted that the ability to switch between different query plan types is valuable, provided it is controlled on a per-query basis.

Outcomes

Analysing the history of feature creation, the opinions expressed within the community, and the current knowledge base on generic plans' usage experience, I conclude that many of the current problems stem from the following issues:

1. Unstable Performance. Generic plan performance may vary significantly based on different sets of input parameter values. This suggests a need to switch to a custom plan type. However, PostgreSQL cannot automatically detect and switch plans because it lacks any statistics on the query execution. The current state of the kernel code enables a straightforward implementation to track various execution parameters, including the average and standard deviation. But, before we proceed with a community's proposal, we must address a fundamental question: should the PostgreSQL kernel have a feedback system from the executor to the optimiser?

2. Outdated custom/generic cost proportion. When a plan is invalidated, for instance, due to updated table statistics, the generic plan is rebuilt, and its cost is recalculated. However, this does not happen for the custom plan. Since the custom plan's cost is not recalculated, the value stored in the plan cache may significantly differ from reality due to gradual changes in table contents. This discrepancy can often lead to situations where a generic plan is utilised, even though the efficiency of a custom plan is apparent and could be determined by the planner during replanning.

3. Inadequate plan costs. A common issue arises when erroneous estimates make the query plan costs irrelevant to the actual workload. Consequently, the choice between custom and generic plans becomes largely a matter of chance.

What can we propose?

After many years of development and testing of the code, can we generate any new ideas? As usual, there are two separate solution designs: one is an in-core part for the community, and the other is an extensible code, which may even include a core patch that could be incorporated into a Postgres fork.

For the core version, we can consider the option of resetting the custom plan statistics on the cached plan, similar to what we do for the generic plan in the event of a plan invalidation call. This would trigger a new plan selection cycle from scratch. This approach is easily justified because statistics form the basis for calculating plan costs. When they change, it's comparable to switching to a different coordinate system, making it necessary to recalculate all costs.

The second option is somewhat more controversial: we could introduce a new 'referenced' mode for the generic plan creation process. This mode would use current constants as reference values for the planner. While it may not offer any fundamental advantages, it would provide users with a familiar tool for influencing the query plan, especially for those migrating from SQL Server.

As usual, it makes sense to implement an in-core 'plan switching hook' to leverage the plan switching method within an extension.

If we extend our coding options into the enterprise domain, we can explore more sophisticated plan-switching techniques. For instance, we could track statistics on the planning and execution time for each plan, compare their relative weight with cost values, and make decisions about replanning or even forcing a specific type of plan. An even better alternative could be to use a more stable parameter, such as the number of pages read.

To be more objective, you can check the project, which includes a draft for an automated system to manage plan types, as well as another branch outlining a draft for switching between forced modes.

Have you ever faced issues when using generic plans? Does it make sense to develop a comprehensive system for switching plans, or is it enough to implement an extension that enables each specific prepared statement to monitor its state and update it manually using SQL tools like pg_stat_statements?

References

Hackers' mailing lists threads:

1. Avoiding bad prepared-statement plans. , 2010-02

2. Restructuring plancache.c API , 2010-11

3. One-Shot Plans , 2011-06

4. Transient plans versus the SPI API , 2011-08

5. why do we need two snapshots per query? , 2011-11

6. dynamic SQL - possible performance regression in 9.2 , 2012-12

7. PoC plpgsql - possibility to force custom or generic plan , 2017-01

8. The logic behind comparing generic vs. custom plan costs , 2025-03

9. inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) ,2025-05

Main commits:

• b9527e9 - first attempt to the feature's design, 2007-03

• e6faf91 custom plans introduction, 2011-09

• 94afbd5 - one-shot entries, 2013-01

• 2aac339 - more sophisticated planning cost model, 2013-09

• f7cb284 - plan_cache_mode setting, 2018-07

THE END.
June 29, 2025. Madrid, Spain.