Scenario
An engineering team upgrades a production database from PostgreSQL 13 to PostgreSQL 15 using pg_upgrade. The next day, three critical reporting queries that ran in under 1 second are now taking 15–30 seconds each. No schema or data changes were made. The queries haven’t changed either — only the engine version changed.
How to Identify
Conditions:
- Queries that were fast before upgrade are now slow after
pg_upgrade
EXPLAIN shows a different plan than what was used in the prior version (e.g., Hash Join replaced by Nested Loop)- Statistics may not reflect actual data distribution post-upgrade (planner cost model differs between major versions)
pg_stat_user_tables shows last_analyze predating the upgrade or being NULL- Planner parameter defaults changed between versions (e.g.,
random_page_cost, jit, enable_partitionwise_join)
Analysis Steps
-- 1. Check when statistics were last gathered (stale after upgrade)
SELECT relname, last_analyze, last_autoanalyze,
n_live_tup, n_dead_tup, n_mod_since_analyze
FROM pg_stat_user_tables
ORDER BY last_analyze ASC NULLS FIRST
LIMIT 20;
-- 2. Run EXPLAIN ANALYZE on the slow query and compare with expected plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name, SUM(o.amount)
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - INTERVAL '30 days'
GROUP BY o.id, c.name;
-- 3. Check key planner parameters that differ between PG versions
SELECT name, setting, source
FROM pg_settings
WHERE name IN (
'random_page_cost', 'seq_page_cost', 'effective_cache_size',
'jit', 'jit_above_cost', 'enable_partitionwise_join',
'enable_partitionwise_aggregate', 'plan_cache_mode',
'default_statistics_target'
)
ORDER BY name;
-- 4. Check if JIT compilation is active and adding overhead for short queries
SELECT name, setting FROM pg_settings
WHERE name IN ('jit', 'jit_above_cost', 'jit_optimize_above_cost');
-- 5. Temporarily disable JIT to test if it is the cause of regression
SET jit = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name, SUM(o.amount)
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - INTERVAL '30 days'
GROUP BY o.id, c.name;
Pitfalls
pg_upgrade migrates the raw statistics binary files, but the planner’s cost model changes between major versions — old statistics may produce different plan choices even if the data hasn’t changed- New planner improvements in newer PostgreSQL versions can paradoxically produce worse plans for certain data distributions (the optimizer is smarter in general but not always for your specific case)
- JIT (Just-In-Time compilation) was introduced in PG 11 and is ON by default; for OLTP short queries, JIT overhead can exceed its benefit — check
jit_above_cost
- Do not rely on
EXPLAIN output from the old version as a guarantee of what the new version will do — cost constants changed
VACUUM ANALYZE after upgrade is mandatory, not optional — statistics files from older versions use different internal format assumptions
Resolution Approach
Plan regressions after pg_upgrade fall into three categories: