Introduction After a minor PostgreSQL version upgrade, queries that ran efficiently may suddenly slow down due to changes in the query planner's cost estimation or statistics interpretation. This is especially common when upgrading between PostgreSQL 14.x to 15.x or 15.x to 16.x families.
Symptoms - Queries that previously completed in milliseconds now take seconds or minutes - `EXPLAIN ANALYZE` shows different join strategies (e.g., nested loop replaced by hash join or vice versa) - CPU usage spikes on the PostgreSQL server without increased workload - `pg_stat_statements` shows significantly higher `mean_exec_time` for specific queries after the upgrade date
Common Causes - Updated planner cost constants (`random_page_cost`, `effective_cache_size`) no longer reflect actual hardware - Statistics targets are insufficient for new planner behavior, leading to poor cardinality estimates - Default changes in `default_statistics_target` or `jit_above_cost` thresholds between versions - Table statistics were not re-analyzed after the upgrade
Step-by-Step Fix 1. **Identify regressed queries using pg_stat_statements**: ```sql SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20; ```
- 1.Compare execution plans before and after by running EXPLAIN ANALYZE:
- 2.```sql
- 3.EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
- 4.SELECT o.order_id, c.customer_name, SUM(oi.quantity * oi.unit_price) AS total
- 5.FROM orders o
- 6.JOIN customers c ON o.customer_id = c.customer_id
- 7.JOIN order_items oi ON o.order_id = oi.order_id
- 8.WHERE o.created_at > '2025-01-01'
- 9.GROUP BY o.order_id, c.customer_name;
- 10.
` - 11.Refresh table statistics with increased statistics target:
- 12.```sql
- 13.ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
- 14.ALTER TABLE order_items ALTER COLUMN order_id SET STATISTICS 500;
- 15.ANALYZE orders;
- 16.ANALYZE order_items;
- 17.ANALYZE customers;
- 18.
` - 19.Adjust planner cost constants if SSD storage is used:
- 20.```sql
- 21.ALTER SYSTEM SET random_page_cost = 1.1;
- 22.ALTER SYSTEM SET effective_cache_size = '6GB';
- 23.SELECT pg_reload_conf();
- 24.
` - 25.Use pg_hint_plan as a temporary override for critical queries:
- 26.```sql
- 27./*+ NestLoop(o c) HashJoin(oi o) */
- 28.SELECT o.order_id, c.customer_name
- 29.FROM orders o
- 30.JOIN customers c ON o.customer_id = c.customer_id
- 31.JOIN order_items oi ON o.order_id = oi.order_id;
- 32.
` - 33.Reset to defaults if a specific parameter caused the regression:
- 34.```sql
- 35.ALTER SYSTEM RESET random_page_cost;
- 36.SELECT pg_reload_conf();
- 37.
`