Introduction
A PostgreSQL upgrade can change the way the query planner executes existing SQL. The query itself may remain exactly the same, return the same results, and still become slower after the upgrade.
A query that used an index before an upgrade might start using a sequential scan. A nested loop might become a hash join. PostgreSQL might introduce parallel workers, choose a different join order, or estimate the number of rows differently.
The important point is that a changed execution plan is not automatically a problem. The real question is whether the new plan performs better or worse for the workload.
This article explains how to identify queries whose execution plans change after a PostgreSQL upgrade and how to determine whether the change is actually a performance regression.
Why Query Plans Change After an Upgrade
PostgreSQL's query planner makes decisions based on several factors. A major PostgreSQL upgrade can change the planner itself, while changes in statistics, indexes, configuration, or execution environment can also influence the final plan.
For example:
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 1250
ORDER BY order_date DESC
LIMIT 20;
The query might previously use:
Index Scan using idx_orders_customer_date on orders
After the upgrade, PostgreSQL might choose:
Seq Scan on orders
The SQL has not changed. The planner's cost calculation or the information available to it may have changed.
Common reasons include:
- Planner improvements or changes
- Updated table statistics
- Different row-count estimates
- New or changed indexes
- Different configuration values
- Changes in table data distribution
- Different parallel execution decisions
- Changes in join strategies
- Changes in partition pruning
- Changes in prepared statement behavior
Because of this, comparing SQL text alone is not enough. You need to compare execution plans and runtime behavior.
Start With the Queries That Matter
Do not begin by comparing every query in the application.
Large applications can generate thousands of distinct SQL statements. Most of them will not be important enough to investigate individually.
Start with queries that have one or more of these characteristics:
- High total execution time
- High execution frequency
- High average execution time
- High CPU consumption
- High disk reads
- High temporary file usage
- Critical business functionality
- Frequent production timeouts
- Known performance problems
This makes the investigation manageable.
A query executed once a day and taking 50 milliseconds is usually less important than a query executed hundreds of thousands of times and taking 20 milliseconds.
The second query can consume considerably more database resources.
Start With a Baseline Before the Upgrade
The best comparison starts before the upgrade.
For important queries, capture their execution plans while the database is still running on the existing PostgreSQL version.
A basic plan can be collected with:
EXPLAIN
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 1250
ORDER BY order_date DESC
LIMIT 20;
For a SELECT query where executing the query is safe, you can collect actual execution information:
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 1250
ORDER BY order_date DESC
LIMIT 20;
EXPLAIN shows the plan PostgreSQL intends to use.
EXPLAIN ANALYZE actually executes the query and reports what happened during execution.
For example:
Limit
-> Index Scan using idx_orders_customer_date on orders
Index Cond: (customer_id = 1250)
With ANALYZE, you may also see:
actual time=0.041..0.087
rows=20
Buffers: shared hit=24
That runtime information becomes extremely useful when comparing the same query after the upgrade.
Use pg_stat_statements to Find Important Queries
The pg_stat_statements extension is particularly useful for this type of investigation because it aggregates execution statistics for SQL statements.
For example:
SELECT
queryid,
calls,
total_exec_time,
mean_exec_time,
rows,
shared_blks_hit,
shared_blks_read,
temp_blks_read,
temp_blks_written,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;
You can also look for queries with a high average execution time:
SELECT
queryid,
calls,
mean_exec_time,
total_exec_time,
query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 50;
Or find queries that run frequently:
SELECT
queryid,
calls,
mean_exec_time,
query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 50;
These lists help identify which queries deserve plan comparison.
Query ID Helps Connect the Same Query
When comparing query statistics, you need a way to identify the same query across environments.
pg_stat_statements provides a queryid that can help with this.
For example:
queryid: 482391756123456789
can be associated with a normalized query such as:
SELECT *
FROM orders
WHERE customer_id = $1;
This is more useful than comparing application logs containing different parameter values.
However, do not treat the query ID as an immutable identifier across every possible environment or configuration. Capture the query text and relevant execution details along with it.
Your baseline should contain enough information to identify the query even if the query ID differs.
Compare Plans, Not Just Execution Time
Suppose a query took 200 milliseconds before the upgrade and 300 milliseconds afterward.
That tells you that performance changed, but not why.
Compare the plans.
Before the upgrade:
Index Scan using idx_orders_customer_id on orders
Index Cond: (customer_id = 1250)
After the upgrade:
Seq Scan on orders
Filter: (customer_id = 1250)
That is a significant change.
The next question is why PostgreSQL decided that reading the entire table was cheaper.
Check the estimated and actual row counts.
For example:
Seq Scan on orders
(cost=0.00..25000.00 rows=50)
(actual time=0.050..180.000 rows=52000)
The planner expected 50 rows but found 52,000.
That difference is a strong indication that statistics or data distribution may be influencing the plan.
Watch for Important Plan Changes
Not every line in an execution plan deserves equal attention.
Focus on changes that can materially affect resource usage.
Index Scan to Sequential Scan
This is one of the most obvious changes.
Before:
Index Scan using idx_orders_customer_id on orders
After:
Seq Scan on orders
This can be completely reasonable if the query now needs a large percentage of the table.
But if the query still returns only a few rows from a very large table, investigate further.
Nested Loop to Hash Join
For joins, PostgreSQL may switch from:
Nested Loop
to:
Hash Join
or the other way around.
Neither strategy is inherently better.
A nested loop can be efficient when one side of the join returns very few rows.
A hash join can be better when larger sets need to be matched.
The actual runtime and row counts should determine whether the change is good or bad.
Changes in Join Order
Consider:
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'IN';
The planner may change which table is accessed first.
That can be a major performance factor when one side of the join has become much more selective.
Changes in Parallelism
A plan may start using:
Gather
Workers Planned: 2
where the previous plan was completely serial.
This is not automatically an improvement.
Parallel execution introduces worker overhead and can sometimes make small queries slower.
For large scans or aggregations, however, parallel execution may significantly reduce execution time.
Changes in Sort and Aggregate Strategy
You may also see changes such as:
Sort
becoming:
Incremental Sort
or different aggregation strategies.
These changes should be evaluated using actual execution statistics rather than by assuming that the newer plan is better.
Check Estimated Rows Versus Actual Rows
One of the most useful parts of EXPLAIN ANALYZE is the comparison between estimated and actual rows.
For example:
Index Scan using idx_orders_customer_id on orders
(cost=0.43..125.20 rows=10)
(actual time=0.040..15.200 rows=8500)
The planner estimated 10 rows but received 8,500.
That is a large estimation error.
The planner makes decisions based on estimates. If those estimates are wrong, it can choose an inefficient join strategy, scan method, or memory-intensive operation.
When you find a large difference, check whether statistics are current.
You can manually refresh statistics with:
ANALYZE orders;
For a specific column:
ANALYZE orders (customer_id);
Then run the plan again.
If the plan changes back to the expected access path and performance improves, stale or insufficient statistics were likely part of the problem.
Compare Plans With the Same Data
Plan comparison is only useful when the underlying conditions are reasonably comparable.
Suppose the pre-upgrade database contained:
10 million orders
but the post-upgrade test database contains:
2 million orders
The planner can legitimately choose a different strategy.
The same problem occurs when testing with a small development database.
A query that uses an index on a small test database might use a sequential scan in production because the table is much larger.
For meaningful comparisons, try to keep these conditions consistent:
| Area | Before Upgrade | After Upgrade |
| Database version | Existing version | Target version |
| Data volume | Comparable | Comparable |
| Statistics | Current | Current |
| Indexes | Same | Same unless intentionally changed |
| Configuration | Recorded | Recorded |
| Query parameters | Representative | Representative |
| Hardware | Comparable | Comparable |
| Workload | Similar | Similar |
The closer these conditions are, the more useful the comparison becomes.
Capture Planner Settings
Execution plans can be affected by PostgreSQL configuration.
Useful settings to record include:
SHOW work_mem;
SHOW random_page_cost;
SHOW effective_cache_size;
SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;
SHOW enable_nestloop;
SHOW enable_hashjoin;
SHOW enable_mergejoin;
You should generally avoid changing these settings simply because a plan changed.
First determine why the planner made the decision.
Changing planner settings globally to force one query to behave differently can create problems for other queries.
Check Statistics After the Upgrade
A PostgreSQL upgrade can expose problems with statistics, particularly when the database has been restored, migrated, or otherwise moved into a new environment.
Check when tables were last analyzed:
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_analyze NULLS FIRST;
If important tables have not been analyzed recently, refresh their statistics:
ANALYZE orders;
ANALYZE customers;
For a large database, do this deliberately rather than blindly running maintenance across every table during peak traffic.
Check for Parameter-Sensitive Queries
A query can behave differently depending on the parameter supplied.
For example:
SELECT *
FROM orders
WHERE customer_id = $1;
One customer may have 20 orders while another may have several million.
A plan that works well for the first value may be poor for the second.
Prepared statements add another dimension because PostgreSQL can use custom or generic plans depending on the situation.
If a query shows inconsistent performance after an upgrade, test it with multiple representative parameter values.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 10;
Then compare it with:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 900000;
If the plans or execution times differ significantly, parameter distribution may be part of the problem.
Use EXPLAIN Settings for Deeper Investigation
When diagnosing a difficult regression, additional EXPLAIN options can provide useful information.
For example:
EXPLAIN (
ANALYZE,
BUFFERS,
SETTINGS
)
SELECT
o.id,
o.total_amount
FROM orders o
WHERE o.customer_id = 1250;
BUFFERS helps identify memory and disk activity.
SETTINGS can show planner-related settings that differ from the normal configuration.
For tooling and automated comparison, JSON output can also be useful:
EXPLAIN (
ANALYZE,
BUFFERS,
FORMAT JSON
)
SELECT *
FROM orders
WHERE customer_id = 1250;
JSON plans are easier for automated comparison than raw text.
Build a Plan Regression Report
Instead of manually comparing dozens of plans, create a simple regression report.
A useful report might contain:
| Query | Calls | Old Time | New Time | Old Plan | New Plan | Status |
| Customer lookup | 500K | 3 ms | 4 ms | Index Scan | Index Scan | Monitor |
| Order search | 80K | 20 ms | 150 ms | Index Scan | Seq Scan | Investigate |
| Monthly report | 500 | 900 ms | 600 ms | Hash Join | Hash Join | Improved |
| Customer summary | 50K | 12 ms | 35 ms | Nested Loop | Hash Join | Investigate |
This separates genuine regressions from harmless plan changes.
It also prevents the team from spending time investigating queries that actually became faster.
Common Mistakes
Treating Every Plan Change as a Problem
PostgreSQL may choose a different plan because the new plan is better.
Plan difference alone is not a regression.
Comparing Different Data Volumes
A plan can legitimately change when table size changes significantly.
Always compare similar data conditions.
Ignoring Statistics
A bad plan is often related to bad estimates.
Check statistics before changing indexes or planner configuration.
Forcing an Index Too Early
Do not immediately try to force PostgreSQL to use an index.
First determine why the planner rejected it.
The sequential scan may actually be the correct choice.
Changing Global Planner Settings for One Query
Changing planner settings globally to fix one query can affect hundreds of other queries.
Use such settings for diagnosis first, not as the first production fix.
Using EXPLAIN ANALYZE on Unsafe Statements
EXPLAIN ANALYZE executes the statement.
Be especially careful with:
UPDATE
DELETE
INSERT
Running these statements during troubleshooting can modify production data.
Use plain EXPLAIN when you only need the planned execution path.
Troubleshooting Workflow
When a query becomes slower after an upgrade, follow a consistent process.
- Identify the affected query from application metrics or
pg_stat_statements.
- Capture the current execution plan.
- Compare it with the pre-upgrade baseline.
- Identify the first meaningful plan change.
- Compare estimated and actual row counts.
- Check table and column statistics.
- Compare planner settings.
- Test with representative parameter values.
- Check indexes and table data distribution.
- Re-run the query after statistics or configuration changes.
- Confirm the performance improvement.
- Document the reason for the plan change.
This approach is safer than immediately changing indexes or PostgreSQL configuration.
When to Consider Extended Statistics
Sometimes PostgreSQL has enough information about individual columns but not enough information about relationships between columns.
For example:
SELECT *
FROM customers
WHERE country = 'IN'
AND customer_type = 'Enterprise';
If these columns are correlated, PostgreSQL may estimate the combination incorrectly.
Extended statistics can provide additional information:
CREATE STATISTICS customer_country_type_stats
ON country, customer_type
FROM customers;
Then refresh statistics:
ANALYZE customers;
Afterward, compare the execution plan again.
Do not create extended statistics for every column combination. Use them when there is evidence that the planner is consistently misestimating correlated columns.
Advantages of Comparing Query Plans
Plan comparison provides several practical benefits:
- Finds performance regressions before production impact becomes widespread
- Identifies planner-related changes
- Helps explain unexpected query behavior
- Provides evidence for index and statistics decisions
- Makes upgrade testing more measurable
- Helps distinguish real regressions from harmless plan changes
- Creates a useful baseline for future troubleshooting
It also changes the upgrade conversation from "the application feels slower" to specific evidence about which queries changed and why.
Disadvantages and Trade-Offs
There are also some practical costs.
- Capturing plans for every query can produce a large amount of data.
EXPLAIN ANALYZE executes the query and can add workload.
- Production-like testing requires realistic data.
- Some performance differences are difficult to reproduce outside production.
- Query plans can vary with configuration and parameter values.
- Automated plan comparison requires careful normalization.
- A plan that looks different may still perform better.
For this reason, focus first on high-value queries instead of attempting to compare everything.
A Practical PostgreSQL Upgrade Plan
A useful upgrade process can include three stages.
Before the Upgrade
Identify critical queries and capture:
Query text
Query ID
Execution count
Average execution time
Total execution time
Execution plan
Buffer usage
Important planner settings
Representative parameters
During Upgrade Testing
Run the same workload against the upgraded PostgreSQL environment.
Capture the same information.
Keep data volume and configuration as close as possible to the existing production environment.
After the Upgrade
Compare:
Old plan
New plan
Old execution time
New execution time
Estimated rows
Actual rows
Buffer usage
CPU and I/O behavior
Investigate only meaningful regressions.
Query Plan Regression Checklist
Before considering the upgrade testing complete, verify:
- Critical queries have a pre-upgrade baseline.
- High-frequency queries have been reviewed.
pg_stat_statements data has been captured.
- Important execution plans have been saved.
- Query parameters are representative.
- Test data is comparable.
- Statistics are current.
- Indexes have been verified.
- Planner settings have been recorded.
- Estimated and actual row counts have been compared.
- Index scans and sequential scans have been reviewed.
- Join strategy changes have been reviewed.
- Parallel execution changes have been reviewed.
- Temporary file and buffer activity has been checked.
- Genuine regressions have been separated from improvements.
- Any planner-related changes have been documented.
- Production fixes have been tested before deployment.
Final Thoughts
A PostgreSQL upgrade does not need to change application SQL to change database performance. The planner can choose a different execution strategy for the same query.
The safest way to find these problems is to establish a baseline before the upgrade and compare it with the upgraded environment.
Start with the queries that matter most. Use pg_stat_statements to identify expensive and frequently executed SQL, save representative execution plans, and compare the actual behavior after the upgrade.
When a plan changes, do not assume that the new plan is wrong. Look at execution time, buffer usage, estimated versus actual rows, join strategy, scan type, statistics, and planner settings.
The goal is not to make the new PostgreSQL version reproduce every old execution plan. The goal is to make sure important queries continue to perform well.
A good upgrade test therefore asks two separate questions: did the execution plan change, and did that change create a real performance problem? Keeping those questions separate makes PostgreSQL upgrade troubleshooting much more predictable.
Join the conversation! Your thoughts help the community grow.