A PostgreSQL upgrade does not always cause problems during installation or application startup. Your database may start successfully, all tables may be available, and your application may connect without any errors. However, that does not guarantee that your existing queries will continue to perform exactly as they did before the upgrade.
One area that deserves particular attention is query execution plans. PostgreSQL may choose a different execution plan after an upgrade, even when the SQL statement, indexes, and application code remain unchanged. In some situations, the new plan improves performance because the planner has better information or uses a more efficient strategy. In other situations, the same query may become slower because PostgreSQL chooses a different scan method, join strategy, or sorting approach.
This type of performance change can be difficult to identify if your upgrade testing focuses only on whether queries return the correct results. A query can return exactly the same data while taking considerably longer to execute.
For developers and database administrators, comparing query plans before and after an upgrade is an important part of PostgreSQL migration testing. It helps identify performance regressions before the upgraded database becomes responsible for production traffic.
In this article, we will look at how PostgreSQL query plans work, why they can change after an upgrade, how to compare them, and how to investigate queries that become slower.
Understanding PostgreSQL Query Execution Plans
When an application sends a SQL query to PostgreSQL, the database needs to decide how to retrieve the requested information. It does not simply execute every query by reading all the rows from the relevant tables. Instead, PostgreSQL uses a query planner to evaluate possible execution strategies and select a plan based on estimated costs.
For example, consider an application that retrieves orders belonging to a particular customer:
SELECT *
FROM orders
WHERE customer_id = 1001;
If the orders table contains millions of records and an appropriate index exists on customer_id, PostgreSQL may use that index to locate the matching records. Reading the entire table would usually be unnecessary when only a small number of rows are required.
However, if the query needs to return a large percentage of the table, reading the table sequentially may be more efficient than repeatedly accessing an index and fetching individual rows.
The planner evaluates these possibilities using information such as table statistics, estimated row counts, available indexes, join conditions, and configuration settings. It then produces an execution plan describing the operations PostgreSQL intends to perform.
You can inspect that plan using EXPLAIN:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;
The output may contain an operation such as:
Index Scan using idx_orders_customer_id on orders
This tells you that PostgreSQL intends to use the customer ID index to retrieve matching records.
To understand what actually happens during execution, use EXPLAIN ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 1001;
Unlike a normal EXPLAIN statement, EXPLAIN ANALYZE executes the query and reports actual execution details. The BUFFERS option provides additional information about how PostgreSQL accessed data through its buffer system.
These details are useful when comparing query behavior before and after a database upgrade.
Why Query Plans Can Change After a PostgreSQL Upgrade
A PostgreSQL upgrade can change more than the database server version. The planner's behavior, available features, configuration, extension versions, and database statistics can all influence the execution plan.
PostgreSQL continuously improves its query planner. A newer release may introduce changes that affect how certain queries are optimized. As a result, a query that previously used an index scan might use a sequential scan after the upgrade. Similarly, a query involving multiple tables might use a different join strategy.
Statistics are another important factor. PostgreSQL maintains information about tables and columns so that the planner can estimate how many rows a query will return. These estimates help determine which execution strategy is likely to be efficient.
Consider a query that previously returned approximately 20 rows. The planner might have selected an index scan because it expected only a small number of matching records. If updated statistics indicate that the query could return several thousand rows, PostgreSQL may choose a different strategy.
Configuration changes can also influence planner decisions. Settings related to memory, parallel execution, cost estimates, and query optimization can affect the plan selected for a particular statement.
This is why it is important to compare the complete execution plans instead of assuming that every difference is caused directly by the PostgreSQL version.
Capture Baseline Query Plans Before the Upgrade
The most useful time to collect query plans is before upgrading the production database. Without a baseline, it becomes difficult to determine whether a query became slower because of the upgrade or because something else changed.
Start by identifying the queries that matter most to your application. These are not necessarily the longest SQL statements. A query that takes only a few milliseconds but executes thousands of times per minute may be more important than a reporting query that runs once a day.
Focus on queries used by important application workflows, including login operations, customer searches, order processing, dashboards, reports, and background jobs.
For each important query, capture its execution plan using:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM orders
WHERE customer_id = 1001;
Keep the output along with the SQL statement, parameter values, database version, and relevant test conditions. This information will help you reproduce the same test after the upgrade.
You should also record execution time, planning time, actual rows, and buffer activity. These values provide a useful baseline for identifying changes.
Avoid collecting plans from only one or two queries and treating them as representative of the entire application. Different queries use different planner strategies, and a database upgrade may affect each workload differently.
Compare Execution Plans After the Upgrade
Once the upgraded PostgreSQL environment is ready, execute the same queries using comparable data and parameter values.
For example, suppose the original plan contains:
Index Scan using idx_orders_customer_id on orders
Index Cond: (customer_id = 1001)
After the upgrade, the plan might contain:
Seq Scan on orders
Filter: (customer_id = 1001)
This means PostgreSQL has changed how it accesses the orders table. Instead of using the index to locate matching records, it now scans the table and applies the filter.
A sequential scan is not automatically a problem. If the query returns a large percentage of the table, scanning the table directly may be more efficient. However, if the query is expected to return only a few records from a very large table, the change deserves investigation.
When comparing plans, look at the operations performed, the estimated and actual row counts, buffer usage, execution time, and the amount of work required to return the results.
The following areas deserve particular attention.
Check Estimated Rows Against Actual Rows
PostgreSQL uses estimated row counts when selecting an execution plan. These estimates come from table statistics and help the planner predict how much work each operation will require.
Consider this example:
Index Scan using idx_orders_customer_id on orders
(cost=0.42..35.10 rows=10 width=80)
(actual time=0.020..0.100 rows=850 loops=1)
The planner estimated that the query would return approximately 10 rows, but the actual execution returned 850 rows.
This difference can influence the plan selected for the query. For example, a nested loop join may look inexpensive when PostgreSQL expects only a small number of rows. If the actual result contains thousands or millions of rows, that strategy may require much more work than expected.
When reviewing a plan after an upgrade, compare estimated rows with actual rows at different levels of the plan. Do not look only at the final result node. An inaccurate estimate in an early operation can influence several operations that follow it.
If you find large estimation differences, investigate the table statistics, data distribution, and query conditions before changing indexes or rewriting SQL.
Investigate Changes in Scan Methods
Scan methods describe how PostgreSQL retrieves records from a table or index. A query may use an index scan, sequential scan, bitmap index scan, or another access strategy depending on the query and available data.
An index scan is often useful when a query needs a relatively small number of rows. A sequential scan can be more efficient when a large part of a table needs to be read. A bitmap scan can be useful when PostgreSQL needs to combine index results or retrieve multiple matching rows efficiently.
For example, a query that retrieves a customer's recent orders might use an index:
SELECT id, order_date, total_amount
FROM orders
WHERE customer_id = 1001
ORDER BY order_date DESC
LIMIT 20;
An index that supports the filtering and ordering conditions may help PostgreSQL retrieve the required records without scanning the entire table or performing an expensive sort.
After an upgrade, if this query changes from an index-based plan to a sequential scan followed by a sort, investigate why the planner made that decision.
Check whether the index still exists, whether it is valid, whether statistics are current, and whether the query returns a different number of rows than expected. Also verify that the test database contains representative data.
Do not create additional indexes immediately. First identify the reason for the changed plan and confirm that the new plan causes a real performance problem.
Review Join Strategy Changes
Queries that retrieve information from multiple tables require additional attention because PostgreSQL has several ways to perform joins.
The most common join strategies are nested loop joins, hash joins, and merge joins. Each strategy is suitable for different conditions.
A nested loop join is often effective when one side of the join produces a small number of rows and PostgreSQL can efficiently look up matching records on the other side. A hash join can work well when PostgreSQL needs to match larger sets of records. A merge join can be efficient when the inputs are already ordered or can be processed in a suitable order.
Consider this query:
SELECT
o.id,
o.order_date,
c.name
FROM orders o
JOIN customers c
ON c.id = o.customer_id
WHERE o.status = 'Pending';
Before the upgrade, PostgreSQL might use a nested loop join. After the upgrade, it might select a hash join because its estimates or cost calculations have changed.
That difference does not automatically indicate a regression. A hash join may be more efficient for the current data distribution.
Compare the actual rows, execution time, buffer activity, and other operations in the plan. If the new join strategy performs significantly more work, investigate the row estimates and indexes involved in the join.
Avoid forcing PostgreSQL to use the old join strategy without understanding why the planner selected a different one.
Check Parallel Execution and Memory-Related Operations
PostgreSQL can use parallel workers for operations that are suitable for parallel execution. This can be helpful for large scans, joins, and analytical queries, but parallel execution also introduces coordination overhead.
An execution plan may contain:
Gather
Workers Planned: 2
-> Parallel Seq Scan on orders
After an upgrade, PostgreSQL might choose a different number of workers or decide that parallel execution is no longer beneficial for a particular query.
For reporting workloads, this can affect execution time. For smaller application queries, parallel execution may not provide enough benefit to justify the additional overhead.
You should also inspect sort and hash operations. These operations may require memory, and large operations can use temporary files when they cannot complete entirely in memory.
For example:
Sort Method: external merge
An external merge sort indicates that PostgreSQL used temporary storage during sorting. If a query starts performing larger sorts after an upgrade, it may generate additional disk activity and become slower under load.
Check the complete plan and the relevant memory settings before changing configuration. Increasing memory settings without understanding the workload can create resource pressure when many queries execute concurrently.
Refresh Statistics Before Comparing Results
One common mistake is comparing query plans without checking whether the upgraded database has current statistics.
PostgreSQL uses statistics collected by ANALYZE to estimate how many rows a query will return. If those statistics are outdated or do not represent the current data distribution, the planner may select an inefficient strategy.
You can refresh statistics for the database using:
ANALYZE;
For a specific table, use:
ANALYZE orders;
If a query depends on a column with an uneven distribution of values, the default statistics may not always provide enough detail for accurate estimates. In such cases, reviewing the statistics target for that column may be useful.
For example:
ALTER TABLE orders
ALTER COLUMN customer_id
SET STATISTICS 500;
ANALYZE orders;
A higher statistics target allows PostgreSQL to collect more detailed statistics for that column, although it can increase the work required to collect and store them.
Do not increase statistics targets for every column without a reason. Start with columns involved in important filters, joins, and other queries where row estimates are consistently inaccurate.
Test With Realistic Data and Parameter Values
A query plan is influenced by the data PostgreSQL sees. Testing an upgraded database with a small sample dataset can produce misleading results because the planner may choose a different strategy when tables contain production-sized data.
For example, a customer table with a few hundred records may behave very differently from one containing several million records. The same applies to order history, transaction records, product catalogs, and audit tables.
Parameterized queries need special attention as well.
Consider:
SELECT *
FROM orders
WHERE customer_id = $1;
Different parameter values may return very different numbers of records. One customer might have only a few orders, while another might have a much larger order history.
When testing application queries, use representative parameter values rather than repeatedly testing with a single value.
Also keep the database configuration, indexes, extensions, and test conditions as consistent as possible. Otherwise, you may attribute a plan change to the PostgreSQL upgrade when it was actually caused by another difference between the environments.
A Practical Query Plan Comparison Workflow
A repeatable process makes upgrade testing more reliable and reduces the chance of overlooking important performance problems.
Identify important queries.
Start with queries used by critical application workflows, frequently accessed APIs, reports, and background jobs. Give priority to queries that handle large amounts of data or execute frequently.
Capture baseline plans.
Before upgrading, collect EXPLAIN ANALYZE and BUFFERS output for representative queries. Save the SQL, parameter values, database version, and execution details.
Prepare the upgraded test environment.
Use a representative copy of the database with comparable table sizes, indexes, extensions, and configuration. Avoid comparing plans from databases with substantially different data distributions.
Refresh statistics.
Run ANALYZE after preparing the upgraded database. This helps ensure that the planner is working with current statistics.
Execute the same queries.
Use the same SQL statements and representative parameter values. Repeat important tests when necessary to understand normal execution-time variation.
Compare the plans.
Examine scan methods, join strategies, estimated rows, actual rows, buffer activity, sorting, hashing, parallel execution, and execution time.
Investigate meaningful regressions.
Identify which operation changed and why. Test possible fixes in the upgraded environment before applying them to production.
Monitor after deployment.
Continue monitoring important queries after the upgrade. Some problems appear only under real application concurrency and production traffic.
Common Mistakes During Query Plan Testing
One common mistake is assuming that identical SQL statements will always produce identical execution plans. PostgreSQL is designed to select plans based on available information, and those decisions can change between database versions or environments.
Another mistake is treating every plan change as a performance problem. A new plan may be more efficient even if it looks different from the previous one. Always compare actual execution behavior before deciding that a change needs to be fixed.
Testing with unrealistic data is another source of incorrect conclusions. A query that performs well on a small development database may become expensive when it processes production-sized tables. Use representative data whenever possible.
Developers should also avoid forcing an old plan too quickly. Disabling planner features, changing cost settings, or using extensions that influence plan selection may hide the underlying issue. First investigate row estimates, statistics, indexes, and query conditions.
Finally, do not rely on execution time alone. A query can appear faster during one test while performing more buffer reads or temporary disk operations. Compare the complete plan and repeat tests under comparable conditions.
Advantages and Limitations of Query Plan Comparison
Approach | Advantages | Limitations |
|---|
EXPLAIN | Shows the planned execution strategy without running the query | Does not provide actual execution statistics |
EXPLAIN ANALYZE | Shows actual execution behavior and timing | Executes the query and can affect database resources |
BUFFERS | Helps identify memory and disk access patterns | Requires additional interpretation |
Production-like testing | Provides more realistic performance information | Requires representative data and environment preparation |
Before-and-after comparison | Helps identify changes introduced during an upgrade | Requires baseline plans collected before the upgrade |
Automated plan testing | Can identify unexpected changes during repeated testing | Requires maintenance and careful handling of normal plan differences |
For queries that modify data, use EXPLAIN ANALYZE carefully. It executes the statement, so an INSERT, UPDATE, or DELETE can change database contents. Where appropriate, test inside a transaction and roll it back after execution. Also remember that EXPLAIN ANALYZE adds measurement overhead, so its execution time should not be treated as an exact replacement for normal application timing.
Best Practices for PostgreSQL Upgrade Testing
Keep baseline execution plans for important queries before every major database upgrade. Store the complete output instead of recording only the execution time, because the additional plan details are often needed to understand a regression.
Test both high-frequency application queries and large reporting queries. They have different performance requirements and may respond differently to planner changes.
Refresh statistics before comparing plans, and use representative data and parameter values. Check indexes, join conditions, buffer activity, and temporary file usage when investigating a slower query.
Do not attempt to preserve every old execution plan. PostgreSQL may select a better strategy after an upgrade, and forcing the previous plan can make performance worse.
Finally, make query-plan testing part of your regular database upgrade checklist. It is much easier to investigate a performance difference in a controlled test environment than to discover it after production users begin reporting slow application responses.
Summary
A PostgreSQL upgrade can change how existing SQL queries are executed, even when the application code and database schema remain unchanged. The planner may choose different scan methods, join strategies, sorting operations, or parallel execution settings because of changes in planner behavior, statistics, configuration, or data distribution.
The most reliable way to identify these changes is to capture baseline execution plans before the upgrade and compare them with plans from the upgraded environment. Use EXPLAIN ANALYZE with BUFFERS where appropriate, and examine estimated rows, actual rows, join methods, buffer activity, and execution time.
Remember that a different execution plan does not automatically mean a query has become slower. The objective is to identify meaningful performance regressions and understand their causes before they affect production.
When query-plan comparison becomes a regular part of PostgreSQL upgrade testing, your team can make upgrade decisions with better performance evidence and fewer unexpected database issues.
Arun PottiPosted Nov 22, 2026, 12:36 PM
Good Article.