PostgreSQL Prepared Statements: When Plans Go Astray

The Problem: Degrading Performance of Prepared Statements

Prepared statements in PostgreSQL are designed to offer a performance advantage by allowing the database to parse, plan, and optimize a query once and then execute it multiple times with different parameter values. This caching of the execution plan can significantly reduce overhead. However, a common and often perplexing issue arises when these same prepared statements begin to perform worse after repeated executions, particularly with varying parameter inputs. This performance degradation is not due to a fault in the application logic or a direct database bug, but rather a nuanced consequence of PostgreSQL's internal query plan caching mechanism encountering shifts in data distribution or cardinality.

The core of the problem lies in how PostgreSQL manages execution plans for prepared statements. When a statement is first prepared, PostgreSQL generates an initial execution plan. For the first few executions (typically five), the database attempts to create 'custom' plans tailored to the specific parameters provided. After this initial phase, it calculates an average cost of these custom plans and generates a 'generic' plan. Subsequent executions then use this generic plan if its estimated cost is reasonably close to the average cost of the custom plans. The issue surfaces when the data distribution changes or when parameters with significantly different selectivity are used, causing the initially optimal plan to become suboptimal. The generic plan, designed to be a 'good enough' compromise, might perform poorly for specific, high-cardinality parameter values that were not well-represented during the initial planning phase.

Understanding PostgreSQL's Plan Caching Behavior

PostgreSQL's plan caching strategy aims to balance the overhead of planning with the benefits of reusing optimized execution plans. The default behavior, often referred to as 'auto' mode for plan_cache_mode, initiates a learning period. During the first five executions of a prepared statement, PostgreSQL generates custom plans, each optimized for the specific bound parameters. It then calculates the average cost of these custom plans.

Following these initial executions, PostgreSQL generates a generic plan. This generic plan is intended to be a single, reasonably efficient plan that can serve a wide range of parameter values. The decision to switch from generating custom plans to exclusively using the generic plan is based on a heuristic: if the estimated cost of the generic plan is not significantly higher than the average cost of the custom plans, PostgreSQL opts for the generic plan. This prevents constant replanning, which itself incurs a performance cost. Subsequent executions will then use this cached generic plan.

The Role of Data Skew

Data skew is a primary instigator of this performance problem. Consider a table where a few accounts (e.g., account_id = 1) have millions of associated records, while most accounts have only a handful. When a prepared statement filters on account_id, the selectivity of the query changes drastically depending on the parameter value.

If the prepared statement is first executed with parameters for low-volume accounts, PostgreSQL might generate an efficient plan. However, when it later executes with a parameter for a high-volume account (like account_id = 1), the same generic plan might involve inefficient operations, such as a full table scan or a poorly chosen index scan. The planner, having learned from lower-cardinality inputs, might not have sufficient statistics or awareness to select the optimal plan for the skewed data. The generic plan, which is a compromise, fails to account for the extreme data distribution, leading to slower query execution for specific parameter sets.

Custom vs. Generic Plans

The distinction between custom and generic plans is crucial. Custom plans are generated for specific parameter values and are typically highly optimized for those exact inputs. They are what you observe when running EXPLAIN (ANALYZE, BUFFERS) EXECUTE ... with a set of parameters. Generic plans, on the other hand, are designed to be more general. They use parameter placeholders (like $1, $2) and are cached by the query planner to be reused across multiple executions, irrespective of the specific parameter values.

PostgreSQL's decision to favor a generic plan is driven by a cost-benefit analysis. The cost of planning a new custom plan for every execution can quickly outweigh the potential performance gains, especially for queries that are executed frequently. The generic plan, even if slightly less optimal for certain parameter sets, provides a stable and predictable performance baseline by avoiding the planning overhead. The heuristic aims to find a sweet spot, but with skewed data, this balance can tip towards suboptimal performance for certain query invocations.

When the Generic Plan Wins (and Costs You)

The critical point where performance can degrade is when PostgreSQL decides that the generic plan is 'good enough' and ceases to explore custom plans. This typically happens after the initial five custom plan executions. If the average cost of these custom plans is not significantly higher than the estimated cost of the generic plan, the system locks in the generic plan. For the rest of its cached life, the prepared statement will use this generic plan.

If, during these early executions, the parameters used happened to be for low-cardinality accounts (e.g., account_id = 45001 in the example), the generic plan might be optimized for such cases. When a high-cardinality parameter (e.g., account_id = 1) is later used, the plan might default to inefficient operations like a bitmap heap scan followed by a sort, rather than a more direct index scan that would be faster for fetching a small, ordered subset of data from a large, indexed relation.

Troubleshooting and Observation

To diagnose this issue, one can examine PostgreSQL's internal statistics. The pg_prepared_statements system view provides valuable insights into the behavior of cached plans. Columns like generic_plans and custom_plans indicate how many times PostgreSQL has generated or utilized generic versus custom plans. Observing an increase in generic_plans while custom_plans remain stagnant can be a telltale sign that the system has settled on a generic plan.

Furthermore, using EXPLAIN (ANALYZE, BUFFERS) on both the prepared statement with specific parameters and an equivalent non-prepared query can reveal differences in the execution plans. If the prepared statement's plan differs and appears less efficient, especially with operations like bitmap scans when an index scan would be preferred for ordering and limiting, it strongly suggests the plan caching mechanism has locked in a suboptimal plan. The plan_cache_mode setting in PostgreSQL can be explicitly set to force_custom_plan or force_generic_plan to override the default behavior and test hypotheses.

Mitigation Strategies

Several strategies can be employed to mitigate the performance degradation caused by suboptimal generic plans. One is to explicitly set the plan_cache_mode. For queries known to benefit from highly specific plans, setting plan_cache_mode = force_custom_plan will ensure that PostgreSQL always generates a new plan for each execution, albeit at the cost of increased planning overhead. Conversely, if a generic plan is consistently good enough for most use cases, setting plan_cache_mode = force_generic_plan can prevent PostgreSQL from deviating.

A more nuanced approach, particularly effective for queries with significant data skew, is to create separate prepared statements for different parameter scenarios. For instance, one statement could be prepared for high-volume accounts, and another for low-volume accounts. This requires application-level logic to determine which prepared statement to execute based on the input parameters. While this adds complexity to the application, it allows the database to maintain highly optimized plans for distinct data access patterns, avoiding the compromise of a single generic plan.

Extended Statistics and Planner Hints

PostgreSQL's query planner relies on statistics to make informed decisions about execution plans. For tables with skewed data distributions, the default statistics might not be sufficient for the planner to accurately estimate cardinalities and choose the best plan. In such cases, creating extended statistics can significantly improve the planner's accuracy. Extended statistics allow the planner to understand correlations between columns, which is vital for queries involving multiple conditions on skewed data.

For example, if you have an index on (account_id, event_type, created_at DESC), and you frequently query on account_id and event_type, creating statistics on these columns together can help the planner understand their joint distribution. While PostgreSQL does not have explicit query hints like some other databases (without extensions like pg_hint_plan), ensuring that your database has up-to-date and comprehensive statistics is the primary way to influence the planner's choices. Running ANALYZE regularly, especially after significant data changes, is crucial.

When This Approach Does Not Fit

This issue and its mitigation strategies are most relevant to applications that heavily utilize prepared statements with parameters that exhibit significant variability in cardinality or data distribution. If your queries are simple, do not involve parameters, or consistently operate on data with uniform distribution, you are unlikely to encounter this specific performance problem. Furthermore, the overhead of managing multiple prepared statements or forcing custom plans might outweigh the benefits for queries that are executed infrequently or where absolute micro-optimization is not critical.

It's also important to note that PostgreSQL versions are continuously updated, and the planner's heuristics and capabilities evolve. While the fundamental principles of plan caching and data skew remain, newer versions might handle these scenarios more gracefully. Always consider your PostgreSQL version and its specific performance characteristics. For scenarios where extreme control over query plans is required and the built-in planner proves insufficient, external solutions or entirely different architectural patterns might be necessary, though these typically come with their own set of complexities and maintenance burdens.

This article draws on PostgreSQL prepared statement becomes slower after repeated executions because the optimal plan changes and answers to it (1, 2), on the Stack Exchange network, licensed under CC BY-SA 4.0.