PostgreSQL 19 Beta Migration: Detecting Query Plan Regressions in .NET

Introduction

Upgrading a database engine is rarely just a matter of changing the version number and running the application.

A PostgreSQL upgrade can change optimizer decisions, execution plans, statistics behavior, index selection, join strategies, and query execution characteristics. Most queries may continue working exactly as expected, while a small number of important queries can suddenly become slower.

For a .NET application using EF Core, this creates an additional challenge. Developers usually work with LINQ rather than handwritten SQL, so a query that looks unchanged in application code can produce different database behavior after an upgrade.

This is why query plan regression testing should be part of a PostgreSQL migration strategy.

The objective is not to prove that every query has exactly the same execution plan before and after the upgrade. PostgreSQL is free to choose a different plan when that plan is estimated to be better.

The real objective is to identify cases where a changed plan results in a meaningful degradation in execution time, I/O, CPU usage, or overall application latency.

What Is a Query Plan Regression?

A query plan regression occurs when a query performs worse after a database or configuration change, often because the optimizer chooses a less efficient execution strategy.

Consider:

Before Upgrade

Query
  |
  v
Index Scan
  |
  v
Fast Result

After an upgrade:

Query
  |
  v
Sequential Scan
  |
  v
Large Amount of Data
  |
  v
Slower Result

The SQL itself may be identical.

The difference is the execution plan.

A regression can also occur without changing from an index scan to a sequential scan.

For example:

Before:
Nested Loop

After:
Hash Join

The new plan might be faster for some data distributions and slower for others.

Therefore, plan comparison must always be combined with actual performance measurements.

Why PostgreSQL Upgrades Can Change Plans

The PostgreSQL optimizer evaluates several possible execution strategies.

It considers factors such as:

Table statistics
Estimated row counts
Index availability
Data distribution
Join cardinality
Selectivity
Cost estimates
Sort requirements
Available memory
Query structure

A new PostgreSQL version can change optimizer behavior or execution details.

Even if the schema and application code remain unchanged:

Same Application
       |
       v
Same SQL
       |
       +------> PostgreSQL Version A
       |
       +------> PostgreSQL Version B

the selected plan can differ.

Why EF Core Makes This Important

EF Core applications commonly express queries as LINQ:

var orders = await db.Orders
    .Where(x => x.CustomerId == customerId)
    .OrderByDescending(x => x.CreatedAt)
    .Take(50)
    .ToListAsync();

The database does not execute this LINQ expression directly.

The application generates SQL:

LINQ
 |
 v
EF Core Query Translation
 |
 v
SQL
 |
 v
PostgreSQL Optimizer
 |
 v
Execution Plan

Therefore, a migration test needs to inspect both sides:

Application Query
+
Generated SQL
+
PostgreSQL Execution Plan
+
Runtime Performance

Build a Query Inventory Before Migration

Do not begin a migration benchmark by randomly selecting queries.

Create a query inventory.

Useful candidates include:

High-traffic API queries
Queries with large tables
Complex joins
Aggregations
Sorting and pagination
JSONB queries
Full-text searches
Queries using multiple indexes
Reporting queries
Queries with historically high latency
Queries responsible for significant database load

A simple inventory could look like:

Query	Area	Frequency	Complexity	Priority
Customer lookup	API	High	Low	High
Order history	API	High	Medium	Critical
Revenue report	Reporting	Medium	High	High
Audit search	Admin	Low	High	Medium

This gives the migration benchmark a practical focus.

Capture the Baseline

Before upgrading PostgreSQL, capture the current behavior.

For every important query, record:

SQL
Execution Plan
Execution Time
Planning Time
Rows Returned
Rows Examined
Buffer Activity

Where available, also capture:

CPU
I/O
Calls
Total Time
Average Time
P95
P99

The baseline becomes the reference against which the upgraded environment is evaluated.

Use EXPLAIN ANALYZE

PostgreSQL provides execution-plan information through EXPLAIN.

A useful diagnostic command is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
FROM orders
WHERE customer_id = 1001;

This combines the estimated plan with actual execution information.

Important fields include:

Planning Time
Execution Time
Actual Rows
Estimated Rows
Scan Type
Join Type
Buffer Hits
Buffer Reads

For migration testing, store these results rather than relying on a screenshot or manual inspection.

Estimated Rows vs Actual Rows

One of the most useful signals in an execution plan is the difference between estimated and actual row counts.

For example:

Estimated Rows: 100
Actual Rows:    25,000

That is a significant estimation error.

The optimizer may choose an inappropriate strategy because it believes the query will return far fewer rows than it actually does.

This can lead to:

Bad Cardinality Estimate
        |
        v
Poor Plan Choice
        |
        v
Higher Execution Cost

Therefore, migration testing should capture cardinality differences rather than comparing plan names alone.

Plan Changes That Deserve Investigation

Some changes should immediately attract attention.

Index Scan to Sequential Scan

Before:
Index Scan

After:
Seq Scan

This is especially important when the query is expected to be highly selective.

Nested Loop to Hash Join

This is not automatically a regression.

The change should be investigated if execution time or resource consumption increases.

Increased Sort Cost

A query might start performing an explicit sort or spill more work to disk.

Increased Rows Removed by Filter

A plan that examines many more rows before filtering can indicate reduced selectivity or a less effective access path.

Increased Buffer Reads

More physical reads can indicate that the new plan is touching significantly more data.

Not Every Plan Change Is a Regression

This distinction is critical.

Suppose the old plan is:

Nested Loop

and the new plan is:

Hash Join

If execution time changes from:

80 ms -> 45 ms

the plan changed, but performance improved.

Therefore:

Plan Change != Regression

Instead:

Plan Change
+
Performance Degradation
+
Meaningful Resource Increase
=
Potential Regression

Define a Regression Threshold

Before running the migration benchmark, define what constitutes a regression.

For example:

Critical:
> 50% slower

Warning:
20–50% slower

Informational:
< 20% slower

These are example thresholds, not universal standards.

For a latency-sensitive API, even a smaller increase might matter.

For an offline reporting job, a larger difference might be acceptable.

The threshold should therefore be based on business and technical requirements.

Compare Percentiles, Not Just Averages

Suppose a query produces:

Baseline P50:  30 ms
Baseline P95:  60 ms
Baseline P99:  90 ms

New P50:       32 ms
New P95:       95 ms
New P99:       240 ms

The average may look acceptable.

The P99 result tells a different story.

This is why migration testing should compare:

for important workloads.

Query Plan Fingerprints

A practical migration test can create a simplified fingerprint for each plan.

For example:

Index Scan
 -> Nested Loop
 -> Index Scan

can become:

IndexScan|NestedLoop|IndexScan

The exact representation is less important than consistency.

Then compare:

Baseline Fingerprint
vs.
PostgreSQL 19 Fingerprint

If the fingerprints differ, mark the query for deeper analysis.

This avoids manually reviewing every unchanged plan.

Example .NET Benchmark Model

A simple result type can store migration metrics.

public sealed record QueryPlanBenchmark(
    string QueryName,
    string DatabaseVersion,
    string PlanFingerprint,
    double P50Ms,
    double P95Ms,
    double P99Ms,
    long AvgRows,
    long SharedReads,
    long SharedHits,
    bool RegressionDetected);

The benchmark system can produce one record per query and database version.

Comparing Results in C#

After collecting baseline and upgraded results, group them by query.

var comparison = results
    .GroupBy(x => x.QueryName)
    .Select(group =>
    {
        var baseline = group.Single(x => x.DatabaseVersion == "baseline");
        var upgraded = group.Single(x => x.DatabaseVersion == "upgraded");

        return new
        {
            group.Key,
            P95ChangePercent =
                ((upgraded.P95Ms - baseline.P95Ms) / baseline.P95Ms) * 100,
            PlanChanged =
                baseline.PlanFingerprint != upgraded.PlanFingerprint,
            BufferReadChange =
                upgraded.SharedReads - baseline.SharedReads
        };
    });

The important part is not the exact implementation.

The benchmark should make regressions automatically discoverable.

Test Against the Same Dataset

A migration benchmark is only meaningful when the data is comparable.

Ideally:

Production-like Snapshot
        |
        +----> PostgreSQL Baseline
        |
        +----> PostgreSQL 19

Both databases should use the same:

Schema
Rows
Indexes
Statistics strategy
Query workload

Otherwise, a plan difference may simply be caused by different data.

Statistics Matter

PostgreSQL relies heavily on statistics for query planning.

After restoring or loading benchmark data, statistics should be updated appropriately.

Conceptually:

Load Data
   |
   v
Update Statistics
   |
   v
Run Baseline
   |
   v
Run Upgrade Test

If one environment has fresh statistics and another does not, the benchmark may measure statistics quality rather than PostgreSQL version behavior.

Keep Configuration Consistent

Do not accidentally compare different environments.

Keep relevant settings consistent where appropriate:

Database Configuration
Connection Settings
Memory Configuration
Parallelism
Workload
Hardware
Storage

The objective is:

Version = Primary Variable

not:

Version
+
Hardware
+
Configuration
+
Dataset
+
Provider
= Unknown

Include EF Core in the Test

Database-level plan testing is necessary but not sufficient.

A .NET migration should also validate:

LINQ Query
    |
    v
Generated SQL
    |
    v
PostgreSQL Plan
    |
    v
EF Core Materialization
    |
    v
API Response

Measure the application-level request as well.

For example:

var stopwatch = Stopwatch.StartNew();

var result = await db.Orders
    .Where(x => x.CustomerId == customerId)
    .OrderByDescending(x => x.CreatedAt)
    .Take(50)
    .ToListAsync();

stopwatch.Stop();

Console.WriteLine(
    $"Query completed in {stopwatch.ElapsedMilliseconds} ms");

This captures the experience of the application, not just the database engine.

Detect Translation Changes

A PostgreSQL upgrade does not necessarily change EF Core SQL generation, but the migration test should still verify generated SQL.

Capture:

var sql = db.Orders
    .Where(x => x.CustomerId == customerId)
    .OrderByDescending(x => x.CreatedAt)
    .Take(50)
    .ToQueryString();

Compare the SQL across the application versions being tested.

If the SQL changed, you now have two variables to investigate:

EF Core SQL Change
+
PostgreSQL Plan Change

That distinction can save considerable debugging time.

Parameterized Queries Matter

A query may behave differently for different parameter values.

For example:

Customer A -> 10 orders
Customer B -> 5,000,000 orders

The same query structure may have very different optimal plans depending on the data distribution.

Therefore, benchmark representative parameter values.

A good dataset should include:

Rare Value
Typical Value
Highly Frequent Value
Boundary Value

This can expose regressions that a single test parameter would miss.

Pagination Queries

Pagination is particularly important in .NET APIs.

Consider:

var orders = await db.Orders
    .Where(x => x.CustomerId == customerId)
    .OrderByDescending(x => x.CreatedAt)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToListAsync();

Test several page depths.

For example:

Page 1
Page 10
Page 100
Page 1,000

A query that performs well on the first page can degrade significantly at deeper offsets.

Join-Heavy Queries

Include queries that join multiple tables.

For example:

Customers
   |
   +--> Orders
          |
          +--> OrderItems

A change in join strategy can have a significant impact on application performance.

Capture:

Join Type
Estimated Rows
Actual Rows
Execution Time
Buffer Activity

for these queries.

Aggregation Queries

Reporting workloads should also be included.

Examples:

COUNT
SUM
AVG
GROUP BY
ORDER BY

These queries can behave differently from simple point lookups.

For example:

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;

The optimizer may choose different scan, aggregation, or parallel execution strategies after an upgrade.

Detecting Regressions Automatically

A migration pipeline can turn benchmark results into a quality gate.

Conceptually:

Build
  |
  v
Deploy Test Environment
  |
  v
Load Dataset
  |
  v
Run Query Suite
  |
  v
Compare Baseline
  |
  +---- No Significant Regression ---> Pass
  |
  +---- Regression ------------------> Investigate

A simple rule could be:

bool regression =
    upgraded.P95Ms > baseline.P95Ms * 1.30;

Again, the threshold should be determined by the application's performance requirements.

Example Regression Report

A useful report might look like:

Query	Plan Changed	P95 Before	P95 After	Change	Status
Customer Lookup	No	18 ms	19 ms	+5.6%	Pass
Order History	Yes	42 ms	47 ms	+11.9%	Pass
Revenue Report	Yes	180 ms	265 ms	+47.2%	Investigate
Audit Search	No	95 ms	97 ms	+2.1%	Pass

The value of this report is that it directs engineering attention to the queries that actually changed materially.

What to Do When a Regression Is Found

Do not immediately add a new index or force a plan.

First determine the cause.

Investigate:

1. Did the SQL change?
2. Did the execution plan change?
3. Did estimated rows change?
4. Did actual rows change?
5. Did statistics change?
6. Did index usage change?
7. Did buffer reads increase?
8. Did parallelism change?
9. Did parameter values influence the result?
10. Is the regression reproducible?

Only after identifying the cause should you consider remediation.

Common Remediation Options

Depending on the root cause, possible solutions include:

Updating statistics
Improving an index
Rewriting a query
Changing data access patterns
Adjusting database configuration
Revisiting pagination strategy
Reducing unnecessary columns
Addressing data distribution problems
Updating application/provider versions

Avoid forcing a particular execution plan unless there is a strong and well-understood reason.

Common Mistakes

Comparing Only Query Text

Identical SQL does not guarantee identical execution behavior.

Treating Every Plan Change as a Regression

A different plan can be faster.

Measuring Only Average Latency

Tail latency can hide important production problems.

Using Different Datasets

Different data distributions can completely change optimizer decisions.

Ignoring Statistics

Bad or inconsistent statistics can make a version comparison meaningless.

Testing Only One Parameter

Parameter distribution can affect query plans significantly.

Benchmarking Only Simple Queries

Complex joins, aggregation, pagination, and reporting queries can behave very differently.

Automatically Forcing the Old Plan

The new optimizer may have selected a different plan for a valid reason.

Best Practices

Build a query inventory before migration.
Prioritize high-value and high-frequency queries.
Capture baseline execution plans.
Store generated SQL from EF Core.
Test against the same dataset.
Keep hardware and configuration consistent.
Refresh statistics consistently.
Test representative parameter distributions.
Compare P50, P95, and P99 latency.
Capture EXPLAIN ANALYZE and buffer statistics.
Detect plan changes automatically.
Investigate estimated-versus-actual row differences.
Test joins, pagination, aggregation, and JSONB workloads.
Separate database-level performance from end-to-end API latency.
Use regression thresholds appropriate to the application's SLA.

Frequently Asked Questions

Does a PostgreSQL version upgrade always change query plans?

No. Many queries may retain the same execution plan. The important point is that the optimizer is allowed to make different decisions after an upgrade.

Is a changed execution plan automatically a problem?

No. A changed plan is a signal for investigation. The actual performance and resource usage determine whether it is a regression.

Should I compare execution time or execution plans?

Both. The plan explains how PostgreSQL executed the query, while timing tells you whether the change mattered.

Why are estimated rows important?

The optimizer relies on cardinality estimates when selecting plans. Large estimation errors can lead to inefficient execution strategies.

Should EF Core queries be included in the migration benchmark?

Yes. Production .NET applications ultimately depend on the complete path from LINQ to generated SQL to PostgreSQL execution and EF Core materialization.

Can a regression be fixed by adding an index?

Sometimes, but not always. Adding indexes without understanding the root cause can increase storage and write costs without addressing the underlying issue.

Conclusion

A PostgreSQL migration should be treated as a performance validation exercise, not simply a database replacement.

For .NET applications using EF Core, the most reliable approach is to establish a baseline of important queries, capture their generated SQL and execution plans, run the same workload against the upgraded PostgreSQL environment, and compare both performance and plan behavior.

The key distinction is that a changed query plan is not itself a regression. A regression exists when the changed behavior produces a meaningful degradation in execution time, resource consumption, or application-level performance.

By automating query-plan comparison and using realistic datasets, representative parameters, percentile latency, buffer statistics, and objective regression thresholds, teams can detect problematic PostgreSQL upgrade behavior before it reaches production.