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.