PostgreSQL 19 Beta: Detecting EF Core Query Plan Regressions

Introduction

Upgrading PostgreSQL is usually straightforward when an application continues to return the same results.

The harder problem is performance.

A query can produce exactly the same data after a database upgrade while taking significantly longer to execute. For .NET applications using Entity Framework Core, this can be difficult to detect because the problem may not appear in application code at all.

The generated LINQ query may be unchanged. The application may compile successfully. Unit tests may pass. Functional tests may also pass.

But PostgreSQL may choose a different execution plan.

EF Core LINQ
     |
     v
Generated SQL
     |
     v
PostgreSQL Optimizer
     |
     v
Execution Plan
     |
     v
Query Result

A PostgreSQL upgrade can change the optimizer's decisions because planner behavior, statistics handling, cost estimation, indexing behavior, or execution features may change.

For this reason, testing a PostgreSQL upgrade should include query plan regression testing, not just functional validation.

This article explains how to detect query plan regressions in EF Core applications before moving a PostgreSQL upgrade into production.

What Is a Query Plan Regression?

A query plan regression occurs when a query that previously performed well starts using a less efficient execution strategy.

For example, an older environment might produce:

Index Scan
  -> 12 ms

while the upgraded environment produces:

Sequential Scan
  -> 480 ms

The SQL may be identical.

SELECT *
FROM orders
WHERE customer_id = 1842;

The difference is the execution plan selected by PostgreSQL.

A simplified comparison looks like this:

Before Upgrade

Query
 |
 v
Index Scan
 |
 v
Fast Result


After Upgrade

Query
 |
 v
Sequential Scan
 |
 v
Slow Result

That is why application-level testing alone may not catch the problem.

Why EF Core Makes This Important

EF Core applications commonly build queries using LINQ.

For example:

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

EF Core translates this into SQL.

The application developer generally cares about the LINQ expression, while PostgreSQL cares about the resulting SQL and available execution strategies.

The complete pipeline is:

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

A regression can therefore occur at the database planning stage even when nothing changed in the application.

Functional Tests Are Not Enough

Suppose a regression test executes:

var customer = await dbContext.Customers
    .FirstOrDefaultAsync(x => x.Id == 1001);

The test might verify:

Customer exists
Customer name is correct
Customer status is correct

Everything passes.

But the test does not necessarily tell you:

Was an index used?
How many rows were scanned?
How much data was read?
How long did execution take?
Did the planner choose a different join?

Performance regression testing needs additional evidence.

Capture the Generated SQL

The first step is to identify important SQL generated by EF Core.

For example:

var query = dbContext.Orders
    .Where(x => x.CustomerId == customerId)
    .OrderByDescending(x => x.CreatedAt)
    .Take(20);

var sql = query.ToQueryString();

This allows you to inspect the SQL produced by the LINQ expression.

A useful baseline stores:

Query Identifier
Generated SQL
Database Version
Execution Plan
Execution Time
Rows Returned

The SQL itself becomes part of the regression dataset.

Identify Critical Queries

Do not attempt to benchmark every query in an application first.

Start with queries that matter most.

Typical candidates include:

High-traffic API queries
Large table scans
Complex joins
Reporting queries
Search queries
Pagination queries
Aggregations
Queries with JSON fields
Queries using multiple indexes
Queries with sorting
Queries using Include
Queries with subqueries

A simple prioritization model can be:

Priority =
Traffic
×
Execution Cost
×
Business Importance

This gives you a practical shortlist.

Capture the Baseline Before the Upgrade

Before changing PostgreSQL versions, execute your benchmark workload against the current production-like database.

Capture:

Query
Execution Time
Planning Time
Rows
Buffers
Plan Shape
Indexes Used

For example:

Query: CustomerOrders

Planning Time: 0.42 ms
Execution Time: 8.7 ms

Plan:
Index Scan
  -> Index: IX_orders_customer_id

This becomes your baseline.

Use EXPLAIN ANALYZE Carefully

PostgreSQL provides detailed execution-plan information through EXPLAIN.

A simplified example is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 1842
ORDER BY created_at DESC
LIMIT 20;

This provides much more information than simply measuring application execution time.

Important signals include:

Planning Time
Execution Time
Rows
Buffers
Scan Type
Join Type
Sort
Index Usage

The important word here is ANALYZE.

It executes the query.

That means you should be careful when testing statements that modify data.

For write operations, use a controlled environment or an appropriate transaction strategy.

Understand Common Plan Changes

A regression can appear in many forms.

Index Scan to Sequential Scan

Before:
Index Scan

After:
Seq Scan

This can be a major problem for large tables.

Nested Loop to Hash Join

Neither join strategy is automatically better.

The correct question is whether the new strategy is appropriate for the actual data distribution.

Before:
Nested Loop

After:
Hash Join

A plan change is not automatically a regression.

Sort Added to the Plan

A query that previously used an index to satisfy ordering may begin sorting rows explicitly.

Index Scan
   |
   v
Result

becomes:

Seq Scan
   |
   v
Sort
   |
   v
Result

For large datasets, that can materially increase execution cost.

Plan Shape Matters More Than Exact Text

Execution-plan output can contain details that change between runs.

For example:

actual time=4.123..4.987

should not normally be compared as an exact string.

Instead, extract meaningful properties:

Scan Type
Join Type
Index Name
Estimated Rows
Actual Rows
Sort Operations
Buffer Reads

A structured representation is easier to compare.

Conceptually:

{
  "scan": "Index Scan",
  "index": "IX_orders_customer_id",
  "estimatedRows": 20,
  "actualRows": 20,
  "executionMs": 8.7
}

Then your regression system can compare properties rather than raw text.

Compare Estimated and Actual Rows

One of the most useful signals is the difference between estimated and actual rows.

Suppose the planner estimates:

Estimated Rows: 20
Actual Rows:    20

That is generally a healthy estimate.

Now consider:

Estimated Rows: 10
Actual Rows:    500,000

That is a significant cardinality estimation problem.

The planner may choose an inefficient strategy because it believes the result set is much smaller than it really is.

Why Statistics Matter

PostgreSQL relies on table statistics when selecting plans.

Consider:

Actual distribution:
Status = Active     95%
Status = Suspended   4%
Status = Closed      1%

If statistics do not accurately represent that distribution, the optimizer can make poor decisions.

After an upgrade, benchmark environments should therefore have realistic statistics.

Do not compare:

Production-like data

with:

Small empty test database

and expect the execution plans to be representative.

Use Production-Like Data

Query plans depend heavily on:

Table size
Data distribution
Indexes
Statistics
Correlation
Selectivity
Number of rows
Join cardinality

For example:

Development:
10,000 orders

Production:
500,000,000 orders

A query that looks perfect in development may behave very differently at production scale.

For upgrade testing, data volume matters.

Test the Same Dataset

A useful migration test environment looks like:

Current PostgreSQL
        |
        v
Identical Dataset
        |
        v
Benchmark Queries


Upgraded PostgreSQL
        |
        v
Same Dataset
        |
        v
Same Benchmark Queries

This isolates the database-version change.

If both environments have different datasets, it becomes difficult to determine whether a plan change is caused by the PostgreSQL upgrade or by data differences.

Compare Query Plans Automatically

Manual inspection does not scale.

Suppose you have 500 important queries.

You want:

Baseline Plan
      |
      v
Upgrade Plan
      |
      v
Plan Diff
      |
      v
Regression Score

A simple regression record could contain:

{
  "queryId": "orders-by-customer",
  "baselineMs": 8.7,
  "upgradeMs": 12.4,
  "baselineScan": "Index Scan",
  "upgradeScan": "Index Scan",
  "baselineRows": 20,
  "upgradeRows": 20
}

This may be acceptable.

Now consider:

{
  "queryId": "orders-by-customer",
  "baselineMs": 9.1,
  "upgradeMs": 487.3,
  "baselineScan": "Index Scan",
  "upgradeScan": "Seq Scan"
}

That deserves immediate investigation.

Define Regression Thresholds

Not every performance difference should fail the upgrade.

Database execution time naturally varies.

A practical benchmark can define thresholds such as:

Green:
< 10% slower

Yellow:
10–25% slower

Red:
> 25% slower

However, percentage alone is not enough.

Consider:

Query A:
10 ms -> 12 ms

Query B:
2,000 ms -> 2,400 ms

Both are 20% slower.

But the second may have a much greater production impact.

Use both:

Relative Regression
+
Absolute Regression

Watch for Plan Changes With Large Impact

Some plan changes deserve special attention:

Index Scan -> Sequential Scan

Nested Loop -> Cartesian-like expansion

Index-backed ORDER BY -> Explicit Sort

Small estimated result -> Huge actual result

Low buffer reads -> Large buffer reads

These changes can indicate a meaningful regression even when average latency has not yet crossed your threshold.

Benchmark Warm and Cold Conditions

Database caching affects results.

A query may behave differently when relevant pages are already in memory.

Therefore, distinguish:

Cold Cache

from:

Warm Cache

For example:

Cold:
120 ms

Warm:
8 ms

The benchmark should consistently define which condition it is measuring.

For production-like workloads, warm-cache performance is often highly relevant, but cold-cache behavior can expose different problems.

Measure P50, P95, and P99

Average latency can hide outliers.

Suppose:

Average: 30 ms
P95:     180 ms
P99:     1,200 ms

The average looks acceptable.

The tail does not.

For API workloads, capture:

P50
P95
P99

before and after the upgrade.

A plan regression that only affects a subset of parameter values may appear primarily in P95 or P99.

Test Different Parameter Values

Parameter-sensitive queries can behave differently depending on input.

For example:

WHERE customer_id = @customerId

may be fast for one customer and expensive for another.

Benchmark:

Small customer
Medium customer
Large customer
Very large customer

This is especially important when data distribution is highly uneven.

A query that looks healthy using a random parameter may still have serious production problems.

Test Pagination Queries

Pagination is a common source of database performance problems.

For example:

var orders = await dbContext.Orders
    .OrderByDescending(x => x.CreatedAt)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToListAsync();

Compare:

Page 1
Page 10
Page 100
Page 1,000
Page 10,000

The query plan may look acceptable at the beginning of the table but become expensive for deep pages.

Keyset pagination may behave differently:

var orders = await dbContext.Orders
    .Where(x => x.CreatedAt < lastCreatedAt)
    .OrderByDescending(x => x.CreatedAt)
    .Take(pageSize)
    .ToListAsync();

The benchmark should represent the actual application workload.

Test EF Core Include Queries

EF Core queries using navigation loading can generate complex SQL.

For example:

var customers = await dbContext.Customers
    .Include(x => x.Orders)
    .ThenInclude(x => x.Items)
    .Where(x => x.IsActive)
    .ToListAsync();

These queries can involve:

Multiple joins
Large intermediate result sets
Duplicate rows
Sorting
Complex materialization

Plan regression testing should include representative queries using relationships.

Test Projection Queries

Projection can produce very different SQL from loading complete entities.

For example:

var result = await dbContext.Orders
    .Where(x => x.CustomerId == customerId)
    .Select(x => new
    {
        x.Id,
        x.CreatedAt,
        x.Total
    })
    .ToListAsync();

This may allow PostgreSQL to read less data than:

var result = await dbContext.Orders
    .Where(x => x.CustomerId == customerId)
    .ToListAsync();

Benchmark both when they represent real application patterns.

Test Aggregation Queries

Reporting and dashboard workloads often use aggregation.

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY customer_id;

These queries may use:

Hash Aggregate
Group Aggregate
Sort
Parallel Execution

Plan changes here can have a large effect because aggregation may process millions of rows.

Test Join-Heavy Queries

A common production query might look conceptually like:

Customers
    |
    +---- Orders
            |
            +---- OrderItems
                    |
                    +---- Products

The optimizer has multiple possible join strategies.

A benchmark should record:

Join order
Join type
Estimated rows
Actual rows
Buffers
Execution time

A small change in cardinality estimation can produce a very different plan.

Test Index Dependency

Create a list of critical indexes used by application queries.

For example:

IX_orders_customer_id
IX_orders_created_at
IX_orders_customer_created_at

Then verify that the expected indexes are still being used where appropriate.

Do not automatically fail when an index is not used.

The optimizer may legitimately find a better plan.

The test should ask:

Did the new plan materially worsen performance?

Don't Treat Every Plan Change as a Regression

This is one of the most important points.

Suppose:

Before:
Nested Loop
Execution: 120 ms

After:
Hash Join
Execution: 80 ms

The plan changed.

But performance improved.

That is not a regression.

Likewise:

Before:
Index Scan
Execution: 100 ms

After:
Seq Scan
Execution: 70 ms

A sequential scan may be better for a query returning a large percentage of a table.

Therefore:

Plan Change != Regression

Instead:

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

Build an EF Core Benchmark Harness

A simple benchmark application can execute representative queries.

public async Task<QueryResult> RunCustomerOrdersAsync(
    int customerId)
{
    var stopwatch = Stopwatch.StartNew();

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

    stopwatch.Stop();

    return new QueryResult(
        orders.Count,
        stopwatch.Elapsed);
}

The harness can record:

Query ID
Parameters
Execution Time
Rows Returned
Database Version

Plan data can be collected separately through database-side instrumentation.

Run the Upgrade in Parallel

A practical migration process is:

Current Version
      |
      v
Capture Baseline
      |
      v
Clone Dataset
      |
      +------------------+
      |                  |
      v                  v
Current DB          PostgreSQL Upgrade
      |                  |
      v                  v
Benchmark           Benchmark
      |                  |
      +--------+---------+
               |
               v
           Compare
               |
               v
         Regression Report

This makes the upgrade decision evidence-based.

Create a Regression Report

A useful report might contain:

Query	Before	After	Change	Plan Change	Status
Customer Orders	8 ms	9 ms	+12%	None	Pass
Invoice Search	15 ms	17 ms	+13%	None	Pass
Order History	22 ms	41 ms	+86%	Index → Seq	Fail
Revenue Report	840 ms	790 ms	-6%	Join changed	Pass
Customer Search	12 ms	13 ms	+8%	None	Pass

This gives the migration team a clear list of queries requiring investigation.

Investigate Regressions Systematically

When a regression appears, check:

1. Generated SQL
2. Execution plan
3. Statistics
4. Indexes
5. Data distribution
6. Query parameters
7. Database configuration
8. PostgreSQL version behavior

Do not immediately rewrite application code.

The problem may be entirely in planning or statistics.

Refresh Statistics

One common investigation step is ensuring statistics are current.

If the benchmark dataset has recently been loaded or changed significantly, stale statistics can distort plan selection.

The benchmark environment should therefore use realistic statistics before comparison.

A useful test process is:

Load Dataset
    |
    v
Prepare Statistics
    |
    v
Run Benchmark

rather than benchmarking immediately after bulk loading.

Test With Realistic Database Configuration

A query plan can be affected by configuration.

Your benchmark should keep relevant database settings consistent between environments wherever possible.

Otherwise, you may accidentally test:

Old Version + Configuration A

against:

New Version + Configuration B

and incorrectly attribute every difference to the PostgreSQL upgrade.

The goal is to isolate variables.

Add Regression Gates to CI/CD

Once the benchmark is stable, it can become part of the upgrade pipeline.

Pull Request
     |
     v
Build
     |
     v
Unit Tests
     |
     v
Integration Tests
     |
     v
Database Benchmark
     |
     v
Plan Comparison
     |
     +---- Pass
     |
     +---- Investigate

Not every query should necessarily block a deployment.

Critical regressions should.

Define Practical Gates

For example:

Fail if:

Critical query > 25% slower
OR
P95 > defined limit
OR
Sequential scan appears on a large critical table
OR
Buffer reads increase beyond threshold
OR
End-to-end API latency exceeds limit

These rules should be based on application behavior rather than arbitrary numbers.

Common Mistakes

Testing Only Query Results

Correct results do not prove good performance.

Comparing Raw Plan Text

Plan output contains dynamic values that make textual comparison noisy.

Using Tiny Test Databases

Small datasets can produce completely different plans.

Testing Only Average Latency

Tail latency often reveals regressions first.

Ignoring Data Distribution

The same query can behave very differently for different parameter values.

Treating Every Plan Change as Bad

A different plan can be faster.

Forgetting Statistics

Poor statistics can make an otherwise healthy query look like an optimizer problem.

Benchmarking Different Datasets

This makes it difficult to identify the cause of a plan change.

Ignoring EF Core SQL Generation

The LINQ expression is not the SQL PostgreSQL executes.

Best Practices

Capture a Baseline

Record plans and performance before the upgrade.

Use Production-Like Data

Plan behavior depends heavily on data volume and distribution.

Benchmark Critical Queries

Prioritize queries that matter to users and business operations.

Compare Structured Plan Information

Focus on scan types, joins, rows, buffers, and timing.

Test Multiple Parameters

Do not benchmark only one representative value.

Measure P95 and P99

Tail behavior can reveal problems hidden by averages.

Investigate Plan Changes With Performance Data

A plan change alone is not necessarily a regression.

Automate the Comparison

A repeatable benchmark is much more useful than manual inspection.

Add Upgrade Gates

Critical query regressions should be detected before production.

A Practical PostgreSQL Upgrade Checklist

Before moving the upgraded database to production:

[ ] Identify critical EF Core queries
[ ] Capture generated SQL
[ ] Capture baseline execution plans
[ ] Record baseline latency
[ ] Prepare production-like data
[ ] Refresh database statistics
[ ] Run identical workload against new version
[ ] Compare scan strategies
[ ] Compare join strategies
[ ] Compare estimated vs actual rows
[ ] Compare buffer usage
[ ] Measure P50/P95/P99
[ ] Test multiple parameter values
[ ] Test pagination workloads
[ ] Test aggregation queries
[ ] Test join-heavy queries
[ ] Investigate significant regressions
[ ] Automate regression reporting
[ ] Define production rollback criteria

Conclusion

A PostgreSQL upgrade should be treated as both a compatibility exercise and a performance experiment. EF Core applications can continue producing correct results while the database optimizer makes different decisions underneath them, and those decisions can have a significant impact on production latency and resource consumption.

The safest approach is to capture a baseline before the upgrade, run the same representative workload against the new PostgreSQL version, compare structured execution-plan information, and investigate meaningful changes in latency, row estimates, buffer usage, scans, and joins.

Most importantly, do not reject an upgrade simply because execution plans changed. PostgreSQL may choose a different plan because it found a better strategy. The real signal is whether the new behavior improves or degrades the application's workload.

For .NET teams, the combination of EF Core query capture, production-like data, PostgreSQL execution-plan analysis, and automated regression thresholds provides a practical way to detect problems before users experience them. A database upgrade is much safer when query performance is measured as part of the release process rather than discovered after deployment.