History of the question
PostgreSQL introduced prepared statements to eliminate parsing and planning overhead for repeatedly executed SQL queries. Early versions always generated custom execution plans tailored to specific parameter values, but this incurred significant CPU costs for complex queries. To optimize this, PostgreSQL 9.2 implemented plan caching with a generic plan mechanism that reuses a single plan structure across multiple executions. While this approach reduces planning latency, it treats all parameters as opaque placeholders during the initial planning phase.
The problem
Partition pruning in PostgreSQL operates at two distinct stages: plan-time pruning, which occurs during query planning when the planner examines partition constraints against literal values, and runtime pruning, which filters partitions during execution using partition-wise joins or append node filtering. Generic plans generated for prepared statements lack concrete parameter values at planning time, rendering plan-time pruning impossible. Consequently, the planner generates a scan across all partitions regardless of the actual parameter values supplied during execution, leading to catastrophic performance degradation on large partitioned tables.
The solution
The resolution requires forcing PostgreSQL to generate custom plans that incorporate actual parameter values during the planning phase. This is achieved by setting the plan_cache_mode configuration parameter to force_custom_plan for the session or specific query, bypassing the generic plan cache entirely. Alternatively, protocol-level workarounds include using the extended query protocol with the Bind message containing literal values rather than parameters, or employing client-side query builders that inline literal values for partition keys while keeping other parameters bound to prevent SQL injection.
-- Force custom plan for this session SET plan_cache_mode = force_custom_plan; -- Or use dynamic SQL with format() to inline literals safely EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
A high-frequency trading analytics platform experienced severe latency spikes every morning when querying intraday price movements. The database stored tick data in a table partitioned by trading session date, containing over 2,000 partitions spanning five years. The application used JDBC prepared statements with ? placeholders for the date parameter to prevent SQL injection and reduce parsing overhead.
The development team initially observed that queries filtering for "today's" data were scanning historical partitions, consuming 45 seconds instead of the expected 300 milliseconds. This performance degradation occurred because the generic plan could not eliminate irrelevant partitions during the planning phase.
One approach involved creating a separate unlogged table for hot data and migrating records nightly. This strategy would have completely bypassed the partitioned table for recent queries, but it introduced complex ETL logic and risked data loss during system crashes.
Another proposal suggested disabling prepared statements globally in the JDBC connection pool. While this would restore plan-time pruning by exposing literal values to the planner, benchmarks revealed a 40% increase in CPU utilization on the database server due to repeated parsing and planning overhead.
The team also evaluated using PostgreSQL's runtime partition pruning capabilities introduced in version 11. However, runtime pruning only eliminates partitions after the executor begins scanning, meaning the planner still allocated resources for all partitions and produced suboptimal join orders that ignored partition boundaries.
Ultimately, the team chose to implement connection-level configuration switching. They configured the connection pool to detect queries targeting partitioned tables and execute SET plan_cache_mode = force_custom_plan before dispatching those specific statements. This preserved the security benefits of parameterized queries for user-input filters while ensuring the partition key values were visible to the planner.
The result reduced query latency to 280 milliseconds and decreased overall database CPU usage by 15%, as the planner could now utilize constraint exclusion to eliminate 1,999 partitions before execution. This optimization allowed the trading platform to meet its strict morning latency requirements without compromising data integrity or security.
How does PostgreSQL decide between generic and custom plans when plan_cache_mode is set to auto?
In auto mode, PostgreSQL plans and executes the query using a custom plan for the first five executions, accumulating the planning cost. After the fifth execution, it compares the average execution time of the generic plan (estimated during the first execution) against the average execution time of the custom plans plus their planning overhead. If the generic plan's estimated cost is less than the custom plan's average cost, the system switches to the generic plan permanently for that prepared statement. Candidates often miss that this comparison includes the planning overhead saved by reusing the generic plan, and that the decision is permanent for the prepared statement's lifetime unless explicitly replanned.
What is the distinction between plan-time and runtime partition pruning in the context of prepared statements?
Plan-time pruning occurs during the planning phase when the planner can prove that certain partitions cannot contain relevant rows based on partition constraints and literal values in the query. Runtime pruning occurs during execution when the executor checks partition constraints against actual parameter values using the executor's filter mechanism. Generic prepared plans support runtime pruning starting from PostgreSQL 11, but they cannot support plan-time pruning because the parameter values are unknown. Candidates frequently confuse these mechanisms, believing that runtime pruning solves all prepared statement partitioning issues, unaware that plan-time pruning is crucial for efficient join planning and index selection.
Why might force_custom_plan fail to resolve partition pruning issues in PostgreSQL versions prior to 10?
Before version 10, PostgreSQL lacked significant support for runtime partition pruning entirely, and prepared statements could not benefit from constraint exclusion even with custom plans if the parameters were passed through the extended query protocol using the Bind message. The planner treated all bound parameters as external to the planning process, requiring explicit literal values in the query string itself to trigger constraint exclusion. This historical limitation means that in legacy systems, even custom plans would scan all partitions, necessitating dynamic SQL generation with EXECUTE ... USING with literals or client-side string concatenation with proper escaping, rather than modern protocol-level parameter binding.