PostgreSQL prepared statements pick a generic plan after ANALYZE on a partitioned table, causing 10× slower execution

Environment

PostgreSQL 13 on Ubuntu 20.04. Partitioned parent table with ~24 monthly partitions; Java application uses a connection pool and issues parameterized queries via JDBC using prepared statements. ANALYZE is run nightly for statistics updates.

Symptoms

Following nightly ANALYZE, a class of parameterized SELECT queries executed via prepared statements become ~10× slower. Running the same SQL with literal values or as a simple (unprepared) query returns the fast plan. The slowdown persists until prepared statements are deallocated or the app reconnects.

Observations

EXPLAIN (ANALYZE, VERBOSE) for the prepared (generic) plan shows a broad sequential scan across many partitions and high estimated row counts, while the simple/custom plan (or literal query) uses an index / partition elimination and completes quickly. The planner chooses a generic cached plan for prepared statements because of varying parameter values, and that generic plan is suboptimal for the majority of actual parameter ranges after updated statistics.

Already attempted

Deallocated prepared statements in the application (closed and reopened connections), which restored performance. Running EXPLAIN on the simple form shows the optimal plan. Temporarily disabling connection pooling forces reprepare and resolves the issue until the next ANALYZE. No schema changes preceded the slowdown.

Question

How can I keep the performance benefits of prepared statements while avoiding persistent selection of a bad generic plan after ANALYZE on a partitioned table? Which configuration or application changes are recommended and reversible?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Planner uses generic cached plans for prepared statements that are not appropriate after statistics change 100%

Application connection pooling prevents re-preparing statements with current statistics 95%

Skewed data distribution across partitions causing large differences between optimal custom and generic plans 85%

Summary

PostgreSQL can create a cached 'generic' plan for prepared statements to save prepare time; that plan does not take account of specific parameter values, so if table statistics change (ANALYZE) and data distribution is skewed across partitions, the generic plan can become suboptimal. This is a known tradeoff between planning time and plan quality. Solutions include forcing the planner to create custom plans, changing application prepare behavior, or tuning planner settings.

Suggested Fixes

Diagnostic step 1

Collect EXPLAIN (ANALYZE, VERBOSE) for both the prepared (generic) and simple/custom plans to confirm differences and capture estimated vs actual row counts.

Diagnostic step 2

Modify the application to avoid long-lived prepared statements for highly selective queries (e.g., reprepare per-connection or use parameter passing mode that forces custom plans), or explicitly close and reprepare statements after maintenance windows.

Diagnostic step 3

Test setting plan_cache_mode = 'force_custom_plan' (if available in your PostgreSQL minor release) in a safe staging environment to evaluate impact; this forces per-execution planning at the cost of CPU.