PostgreSQL Prepared Statements: When Plans Degrade

The Prepared Statement Performance Paradox

Prepared statements in PostgreSQL are designed to offer performance benefits by allowing the database to parse and plan a query once and then execute it multiple times with different parameters. This approach reduces overhead and can significantly speed up repetitive queries. However, a counter-intuitive issue can arise: a prepared statement that initially performs well can become progressively slower after repeated executions, especially when dealing with data that has a skewed distribution.

This phenomenon occurs because PostgreSQL's query planner attempts to be efficient not only in executing queries but also in managing the cache of execution plans for prepared statements. When a prepared statement is executed for the first few times, PostgreSQL generates 'custom' plans tailored to the specific parameters provided. Over time, it evaluates whether a 'generic' plan, which aims to be reasonably efficient across a wider range of parameter values, would be a better long-term choice for cache management. This decision-making process, while generally effective, can fall prey to data skew, leading to the selection of a suboptimal plan for certain parameter sets.

The core of the problem lies in the planner's heuristics for transitioning from custom, highly specific plans to a single, generic plan. When the data distribution is uneven, a generic plan that performs adequately for common parameter values may be disastrously slow for rarer, but still valid, parameter combinations. Understanding how and why this transition occurs is key to diagnosing and resolving such performance degradations.

Understanding PostgreSQL's Plan Caching Strategy

PostgreSQL employs a sophisticated plan caching mechanism to optimize the execution of prepared statements. When a prepared statement is first executed, PostgreSQL generates a custom execution plan that is highly optimized for the specific parameter values used in that initial execution. This custom plan is then cached.

As the prepared statement is executed more times, PostgreSQL keeps track of the number of custom plans generated and their associated estimated costs. After a certain threshold (typically five custom executions), it begins to compare the average cost of these custom plans against the estimated cost of a single generic plan. If the generic plan's cost is not significantly higher than the average custom plan cost, PostgreSQL may decide to switch to using the generic plan for all subsequent executions. This heuristic aims to reduce planning overhead by avoiding the generation of new custom plans repeatedly.

The PostgreSQL source code, specifically in src/backend/utils/cache/plancache.c, outlines this logic. The function choose_custom_plan makes the decision. It prioritizes custom plans for the first five executions or if plan_cache_mode is set to force_custom_plan. After that, it compares plansource->generic_cost against avg_custom_cost. If generic_cost is less than avg_custom_cost, it returns false, opting for the generic plan.

The Impact of Data Skew

Data skew is a significant factor that can disrupt PostgreSQL's plan caching strategy. When a table's data is unevenly distributed, certain parameter values will be far more common than others. For instance, a table of invoice_events might have 99% of events for account_id = 1 and the remaining 1% spread across thousands of other account_ids. An index on account_id would be highly effective for account_id = 1 but less so for other values.

In such scenarios, a generic plan, which is designed to offer a reasonable compromise across all possible parameter values, will likely perform poorly for the very common values. The planner, aiming for efficiency, might pick a plan that is slightly worse on average but avoids the overhead of generating and switching between multiple custom plans. However, for highly skewed data, the "average" plan can be vastly inferior for the most frequent query patterns.

If the initial executions of a prepared statement happen to use parameters that do not represent the most common data skew, the average custom plan cost might be artificially low. When PostgreSQL then switches to a generic plan, it might be a plan that was never actually tested during those initial low-cost custom plan executions, leading to a performance cliff for the majority of real-world queries.

Diagnosing Plan Changes with EXPLAIN ANALYZE

To understand why a prepared statement's performance has degraded, EXPLAIN (ANALYZE, BUFFERS) is an invaluable tool. This command not only shows the planned execution strategy but also executes the query and provides actual runtime statistics, including I/O and CPU usage.

When run on a prepared statement with specific parameters, EXPLAIN ANALYZE EXECUTE prepared_statement_name(param1, param2) will reveal the actual query plan being used. Comparing the output of this command for a slow execution against an earlier, faster execution (or against an equivalent non-prepared query) can highlight differences in the chosen execution plan. Look for changes in the node types (e.g., Index Scan vs. Bitmap Heap Scan), the order of operations, and the estimated vs. actual row counts.

Specifically, you might observe that a previously efficient Index Scan has been replaced by a Bitmap Heap Scan, which can be slower if it doesn't preserve the required order or if it involves more I/O for fetching data. The BUFFERS option is crucial as it shows buffer hits and reads, indicating how much data was read from disk versus cache, a key performance indicator.

The Role of plan_cache_mode

PostgreSQL provides the plan_cache_mode parameter, which allows administrators to influence how the planner caches plans for prepared statements. This parameter can be set globally, per session, or even per transaction using SET LOCAL plan_cache_mode = ....

The possible values are:

• auto (default): PostgreSQL uses the default heuristic described above, switching between custom and generic plans.
• force_custom_plan: PostgreSQL will always generate and use a custom plan for each unique set of parameters. This can be beneficial for highly variable workloads but increases planning overhead.
• force_generic_plan: PostgreSQL will always use a generic plan, even if it means sacrificing some optimization for specific parameter values. This is useful when a generic plan is known to be 'good enough' for all parameter combinations.

For the problem of skewed data causing slow prepared statements, setting plan_cache_mode to force_custom_plan can prevent the switch to a suboptimal generic plan. However, this comes at the cost of increased planning time for every unique parameter set. Conversely, setting it to force_generic_plan might be a viable option if a good generic plan can be found that is acceptable for all common parameter values.

Monitoring Prepared Statement Plans

To gain deeper insight into the plan caching behavior, PostgreSQL's system catalog offers valuable information. The pg_prepared_statements view provides details about prepared statements, including counters for generated plans.

Key columns to monitor include:

• plans_created: The total number of plans created for this prepared statement.
• plans_used: The number of times cached plans were reused.
• custom_plans: The number of custom plans generated.
• generic_plans: The number of generic plans generated.

By querying this view, you can observe if and when PostgreSQL is switching between custom and generic plans. For example, if generic_plans starts to increase after a period of custom_plans usage, it indicates the planner has made a decision to favor generic plans. This monitoring can help correlate observed performance changes with the planner's behavior.

Additionally, understanding the num_custom_plans and total_custom_cost from the CachedPlanSource structure (as seen in plancache.c) can provide finer-grained diagnostics, though direct access to these internal counters is not typically exposed through standard SQL views.

Alternatives to Standard Prepared Statements

When dealing with skewed data and the limitations of automatic plan caching, several alternative strategies can be employed:

• Separate Prepared Statements: If distinct parameter value ranges consistently benefit from different query plans, consider creating separate prepared statements for these distinct scenarios. For example, one prepared statement for high-volume account_ids and another for low-volume ones. This allows the planner to optimize each specific case independently.
• Dynamic SQL (with caution): For extremely complex scenarios or when specific query hints are needed, dynamic SQL can be used. However, this moves the planning and caching logic to the application layer, requiring careful management to avoid re-planning on every execution, which negates the benefits of prepared statements.
• Application-Level Caching: Cache query results in the application layer if the data does not change frequently. This bypasses the database's query planner and execution entirely for subsequent requests.

The decision to use separate prepared statements is often the most direct way to address skewed data issues within PostgreSQL's framework, allowing specific optimizations for high-cardinality and low-cardinality parameters without resorting to application-level complexity.

The Limits of this Approach

While manipulating plan_cache_mode, using separate prepared statements, or implementing application-level caching can mitigate performance issues arising from plan degradation, these approaches are not without their trade-offs. Forcing custom plans can increase CPU load on the database server due to more frequent query planning. Creating numerous prepared statements for different parameter ranges can lead to a proliferation of statements in the cache, potentially consuming more memory and increasing the complexity of application code that needs to manage them.

Furthermore, the effectiveness of these solutions heavily relies on the ability to accurately identify distinct query patterns and their associated data distributions. If the data profile shifts or if new common parameter values emerge, the strategy might need to be re-evaluated and adjusted. The PostgreSQL planner is designed to adapt, and bypassing its automatic mechanisms requires a deep understanding of the application's query workload and data characteristics.

This issue is particularly relevant in PostgreSQL versions where the planner's heuristics for plan caching have not yet been refined to handle extreme data skew as gracefully as later versions might. Always ensure your PostgreSQL instance is updated to benefit from ongoing performance improvements in the query planner.

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.