How to Compare PostgreSQL Query Plans Before and After a Major Upgrade

A PostgreSQL major-version upgrade can improve database performance, introduce new capabilities, and provide access to updated query optimization techniques. However, an upgrade does not guarantee that every query will run faster.

Some queries may become slower because the new version chooses a different execution plan. Changes in optimizer behavior, statistics, cost estimates, available indexes, and database configuration can affect how PostgreSQL executes SQL statements.

For applications that depend on predictable database performance, these differences matter. A query that previously completed in milliseconds might begin consuming more CPU, reading additional rows, or performing an expensive sort after an upgrade.

The safest approach is to capture representative query plans before the upgrade and compare them with plans from the upgraded environment. This helps developers identify performance regressions, understand why the optimizer made a different decision, and correct problems before moving the application into production.

This article explains how to capture PostgreSQL execution plans, compare their important properties, identify common regressions, and validate query performance during a major-version upgrade.

Understand How PostgreSQL Query Plans Work

PostgreSQL uses a query planner to determine how SQL statements should access data and perform operations.

For example, when executing a query that joins two tables, PostgreSQL must decide how to access each table, which indexes to use, how to join the results, and whether sorting or aggregation is required.

The same SQL statement can produce different execution plans depending on the database version, table statistics, available indexes, configuration settings, and estimated costs.

Consider this query:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount
FROM orders AS o
WHERE o.customer_id = 1250
ORDER BY o.order_date DESC
LIMIT 20;

PostgreSQL might use an index to locate the customer's orders and retrieve the newest records. Alternatively, it might scan a larger portion of the table and sort the matching rows.

Which plan performs better depends on factors such as the number of matching rows, the available indexes, the table's physical organization, and the cost estimates calculated by the planner.

An execution plan shows the operations PostgreSQL intends to perform. An actual execution plan also includes measurements collected while running the query.

Comparing these plans before and after an upgrade helps answer three important questions:

Did PostgreSQL choose a different execution strategy?
Did the amount of work performed by the query change?
Did the query's actual execution time or resource consumption increase?

These questions are more useful than comparing execution times alone because they help identify the reason behind a performance difference.

Capture the Baseline Before the Upgrade

The first step is to collect execution plans from the existing PostgreSQL version.

Choose queries that represent important application workloads. Focus on SQL statements that run frequently, consume significant database resources, or support critical user operations.

Examples include:

Queries that retrieve customer or order information.
Reports that aggregate large amounts of data.
Queries that join multiple tables.
Database operations executed by background workers.
Queries with complicated filters or sorting.
SQL statements that already have performance problems.

Do not limit the investigation to the slowest query in the application. A frequently executed query that takes a few milliseconds can have a substantial cumulative impact on database resources.

Use EXPLAIN to Inspect the Planned Execution

The PostgreSQL EXPLAIN command displays the execution plan selected by the query planner.

For example:

EXPLAIN
SELECT
    order_id,
    order_date,
    total_amount
FROM orders
WHERE customer_id = 1250
ORDER BY order_date DESC
LIMIT 20;

The output may contain operations such as:

Limit
  ->  Sort
        Sort Key: order_date DESC
        ->  Seq Scan on orders
              Filter: (customer_id = 1250)

This is an illustrative plan. Actual output depends on the database schema, data distribution, indexes, and PostgreSQL version.

The example indicates that PostgreSQL scans the orders table, filters rows by customer ID, sorts the matching results, and returns at most 20 rows.

A different plan might use an index to retrieve the matching orders directly. That could reduce the amount of data PostgreSQL needs to process.

However, a sequential scan is not automatically a problem. If a query needs a large percentage of a table, scanning the table sequentially may be more efficient than accessing many individual index entries.

Use EXPLAIN to understand the chosen strategy rather than assuming that one operation is always better than another.

Use EXPLAIN ANALYZE to Measure Actual Execution

EXPLAIN shows the selected plan and estimated costs. To measure actual execution, use EXPLAIN ANALYZE.

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    order_id,
    order_date,
    total_amount
FROM orders
WHERE customer_id = 1250
ORDER BY order_date DESC
LIMIT 20;

This provides additional information, including:

Actual execution time.
Estimated and actual row counts.
Number of loops for plan operations.
Buffer hits and reads.
Time spent in individual plan nodes.
Additional information about operations such as sorting.

The BUFFERS option helps show how the query interacts with PostgreSQL's buffer cache and underlying storage.

Be careful when using EXPLAIN ANALYZE. PostgreSQL actually executes the query, so the measurement can consume CPU, perform disk reads, and place load on the database.

For SELECT statements, this usually means reading data and consuming resources. For statements that modify data, EXPLAIN ANALYZE executes the modification. Use an appropriate test environment or a carefully controlled transaction when investigating data-changing statements.

Also, execution time reported by EXPLAIN ANALYZE does not necessarily represent the complete time experienced by an application. Network latency, connection acquisition, client-side processing, and other application overhead may not be included.

Record the Database Environment

A query plan is meaningful only when you understand the environment in which it was generated.

Before upgrading, record the PostgreSQL version and relevant configuration.

You can retrieve the server version with:

SELECT version();

You can also inspect selected settings:

SELECT
    name,
    setting,
    unit,
    source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'effective_cache_size',
    'random_page_cost',
    'seq_page_cost',
    'max_parallel_workers_per_gather'
)
ORDER BY name;

These settings influence memory availability, estimated I/O costs, and parallel query execution.

Record the settings from both environments and identify intentional differences. A plan change might result from a configuration difference rather than a change in the query optimizer itself.

Also record:

Table and index definitions.
Approximate table sizes.
Relevant constraints.
Available indexes.
Statistics freshness.
Database extensions.
Hardware and storage characteristics.
Whether the database was under load during testing.

A major-version upgrade can change planner behavior, but the environment must be controlled before attributing a regression to the new version.

Compare Query Plans After the Upgrade

After upgrading the test environment, execute the same representative SQL statements and collect new plans.

Keep the SQL text, parameter values, schema, and test conditions as consistent as possible.

For example, run the same EXPLAIN ANALYZE command against the upgraded database and save the output separately.

You can then compare the old and new plans using the following properties.

Property	What to compare	Why it matters
Scan method	Sequential, index, or bitmap scan	Reveals changes in data access strategy
Join method	Nested loop, hash join, or merge join	Can explain changes in join performance
Estimated rows	Planner's expected row count	Helps identify changes in estimates
Actual rows	Rows processed during execution	Reveals the amount of real work performed
Loops	Number of executions of a plan node	Repeated operations can multiply costs
Sort operations	Sorting method and memory behavior	May reveal additional processing
Buffers	Cache hits and reads	Helps investigate data access and I/O
Execution time	Measured query duration	Indicates whether the query became faster or slower
Planning time	Time spent creating the plan	Helps identify planning overhead

Do not compare only the total execution time. Two plans can have similar durations under one test condition while having different resource requirements.

Likewise, a plan that looks more complicated is not necessarily slower. The actual workload and measurements determine whether the change matters.

Compare Estimated and Actual Row Counts

One of the most useful checks is the difference between estimated and actual row counts.

Suppose the old plan estimates that a filter returns 100 rows and actually returns approximately 110. The estimate is reasonably close.

After the upgrade, the planner estimates 100 rows but the query actually returns 50,000.

This difference can influence the optimizer's decision about which scan or join strategy to use.

When estimates are significantly inaccurate, investigate statistics, data distribution, parameter values, and correlations between columns.

Do not assume that the PostgreSQL version alone caused the difference. The upgraded environment may have different statistics or may not have completed the expected analysis operations.

Understand Common Plan Changes

Certain execution-plan differences deserve particular attention during an upgrade.

Sequential Scan Replaces an Index Scan

Suppose a query previously used an index to retrieve a small set of rows but now performs a sequential scan.

This might indicate a regression, especially if the table is large and the query selects very few rows.

However, PostgreSQL may choose a sequential scan when it estimates that reading the table directly is cheaper than accessing the index and then retrieving table rows.

Investigate:

Whether the index still exists.
Whether the query predicate matches the index definition.
Whether statistics accurately describe the table.
Whether the query returns a larger percentage of rows than expected.
Whether relevant cost settings differ between environments.

Do not force index usage as the first response. Correcting inaccurate statistics or changing an unsuitable index may be a better solution.

Nested Loop Replaces a Hash Join

Nested loop joins can perform well when the outer input contains few rows and the inner side can be accessed efficiently.

However, they can become expensive when the outer input contains many rows and the inner operation executes repeatedly.

A plan might show a nested loop with a large loop count. Inspect the actual rows and execution time for the inner node.

A hash join may be more efficient for some large joins, but it requires memory and can involve additional work to build the hash table.

The correct strategy depends on the data and available resources. Avoid assuming that replacing a nested loop with a hash join will always improve performance.

Sorting Becomes More Expensive

A query that previously used an index to satisfy an ORDER BY clause may now require an explicit sort.

Check whether the relevant index still exists and whether its column order matches the query requirements.

Also inspect the sort method and memory behavior reported by EXPLAIN ANALYZE. A sort that spills to disk may be substantially more expensive than one that completes in memory.

Increasing work_mem globally is not always the right solution because this setting can apply to multiple operations and concurrent queries. Review the workload and memory requirements before changing it.

Parallel Execution Changes

PostgreSQL can use parallel query execution for eligible operations.

After an upgrade, a query might use a different number of workers or choose a different parallel plan.

Compare the planned and launched workers, the amount of work performed by each worker, and the total execution time.

A parallel plan is not automatically faster. Worker startup, coordination, data distribution, and available CPU resources all affect performance.

Use Consistent Testing Conditions

A reliable comparison requires more than executing the same SQL statement twice.

Differences in cache state, database load, statistics, and data volume can produce misleading results.

Use Equivalent Data and Statistics

Where practical, test against equivalent database snapshots.

The tables should contain comparable data, and relevant indexes, constraints, and statistics should be present.

After upgrading a test environment, review whether statistics need to be refreshed. PostgreSQL documentation and upgrade procedures should guide the appropriate maintenance operations for the selected upgrade method.

For example, ANALYZE collects statistics that help the planner estimate row counts and costs:

ANALYZE orders;

For a larger database, analyze the relevant tables rather than running maintenance indiscriminately without considering the workload.

If statistics are stale or materially different, the comparison may reflect those differences instead of the new version's planning behavior.

Repeat the Measurements

Run important queries several times under comparable conditions.

The first execution may involve disk reads that later executions avoid because data is cached. Concurrent database activity can also influence timings.

Record the individual results and compare the overall pattern rather than selecting the fastest run from each environment.

For production-critical queries, test with representative parameter values. A query that behaves well for one customer may perform differently for a customer with millions of records.

Separate Planning and Execution Performance

PostgreSQL reports planning and execution times separately when using EXPLAIN ANALYZE.

If execution time increases, investigate the work performed by the plan.

If planning time increases, examine query complexity, the number of relations involved, and other factors that affect optimization.

A planning-time increase may be important for applications that execute complex SQL statements very frequently, even when each statement processes relatively little data.

Common Mistakes to Avoid

Comparing Different Queries

Even small differences in predicates, parameter values, or ordering can lead to different plans.

Use the same SQL and equivalent inputs when comparing versions.

Treating Estimated Cost as Actual Execution Time

Planner cost is an internal estimate, not a measurement in milliseconds.

Use actual execution data to determine whether a query has become slower.

Adding Indexes Without Investigating the Cause

An additional index may improve one query but increase storage requirements and the cost of INSERT, UPDATE, and DELETE operations.

Inspect the plan and existing indexes before introducing new ones.

Ignoring Statistics

A major upgrade does not eliminate the need for accurate statistics.

If the optimizer's row estimates are poor, investigate statistics and data distribution before changing query structure or forcing a particular plan.

Testing Only Small Datasets

A plan that performs well on a development database may behave poorly against production-scale data.

Use representative data volumes and realistic query parameters when evaluating important workloads.

Ignoring Application-Level Performance

A query can execute quickly in a database console but perform poorly in an application because of connection delays, network latency, repeated queries, or result processing.

Measure database execution and application response time separately.

Best Practices for Major-Version Upgrade Testing

Follow a structured process to reduce the risk of database performance regressions.

Identify high-impact and frequently executed SQL statements.
Capture baseline plans with EXPLAIN and EXPLAIN ANALYZE.
Record the PostgreSQL version, relevant configuration, schema, indexes, and statistics state.
Upgrade a representative test environment using the planned upgrade procedure.
Run equivalent queries against the upgraded environment.
Compare scan methods, join strategies, estimated and actual rows, buffer activity, and execution time.
Repeat measurements under comparable conditions.
Investigate significant plan changes before modifying indexes or configuration.
Validate fixes with realistic data and application-level tests.
Document the results and establish performance acceptance criteria before production rollout.

For a large application, automate the capture of plans for selected queries where practical. Store the results with the PostgreSQL version and test configuration so that future comparisons remain meaningful.

Avoid building a process that treats every plan difference as a failure. PostgreSQL may legitimately choose a different plan that performs equally well or better. Focus on changes that affect application performance, resource usage, or operational reliability.

Summary

A PostgreSQL major-version upgrade can change query plans even when application SQL remains unchanged. These changes may improve performance, but they can also introduce regressions when the optimizer selects a less efficient scan, join, sort, or parallel execution strategy.

Capture baseline plans before upgrading, use EXPLAIN ANALYZE to measure actual execution, and record the database configuration and statistics state. After upgrading, compare equivalent queries and investigate differences in row estimates, execution methods, buffer activity, and timing.

Do not assume that a different plan is automatically a problem or that adding an index will solve every slowdown. Validate the underlying cause, make targeted changes, and repeat the measurements under realistic conditions.

A disciplined plan-comparison process helps database teams identify regressions before production deployment and make PostgreSQL upgrades more predictable.