PostgreSQL 19 Beta 3: Detecting JSON Query Plan Regressions in Production-Like Workloads

Introduction

A database query can return the correct result and still become a production problem.

As applications grow, the same query may start consuming more CPU, reading more data, or taking longer to complete. These changes are particularly important when an application depends heavily on JSON data, because JSON queries can behave very differently depending on the operators, indexes, data distribution, and query shape involved.

PostgreSQL provides detailed execution plans that make this behavior observable.

For PostgreSQL 19 Beta 3, a useful testing approach is to create production-like JSON workloads and compare execution plans before and after a change. Instead of looking only at query duration, the benchmark should identify changes in plan structure, row estimates, scan methods, buffer usage, and resource consumption.

The goal is straightforward:

Detect query-plan regressions before they become production incidents.

What Is a Query-Plan Regression?

A query-plan regression happens when a query starts using a less efficient execution strategy than before.

For example, an earlier plan might use:

Index Scan
    |
    v
Small number of rows

while a later plan might use:

Sequential Scan
    |
    v
Large number of rows

Both plans can return exactly the same result.

The difference is how much work PostgreSQL performs to produce that result.

A regression can therefore appear as:

Higher execution time
More buffer reads
More CPU consumption
More rows processed
Increased temporary-file usage
Different join strategy
Different index usage

Why JSON Queries Need Careful Testing

JSON data is flexible, which makes it attractive for applications where the structure changes frequently.

For example:

CREATE TABLE products
(
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data jsonb NOT NULL
);

A row might contain:

{
  "category": "laptop",
  "brand": "Example",
  "price": 85000,
  "specifications": {
    "ram": 16,
    "storage": 512
  }
}

A query can access nested properties:

SELECT id
FROM products
WHERE data->'specifications'->>'ram' = '16';

The query is simple.

The execution behavior can become much more complicated as the table grows.

Create a Production-Like Dataset

A useful benchmark should not use only ten rows.

Create enough data to represent the workload you want to understand.

For example:

CREATE TABLE products
(
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data jsonb NOT NULL
);

Populate test data:

INSERT INTO products (data)
SELECT jsonb_build_object(
    'category',
    CASE
        WHEN g % 4 = 0 THEN 'laptop'
        WHEN g % 4 = 1 THEN 'phone'
        WHEN g % 4 = 2 THEN 'tablet'
        ELSE 'monitor'
    END,
    'brand',
    'Brand-' || (g % 20),
    'price',
    (100 + (g % 5000)),
    'specifications',
    jsonb_build_object(
        'ram',
        CASE
            WHEN g % 3 = 0 THEN 8
            WHEN g % 3 = 1 THEN 16
            ELSE 32
        END,
        'storage',
        256 + (g % 4) * 256
    )
)
FROM generate_series(1, 100000) AS g;

The numbers here are only for creating a repeatable test dataset.

A real benchmark should use a distribution that resembles the production workload.

Establish a Baseline

Before making any changes, capture the current execution plan.

Use:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM products
WHERE data->'specifications'->>'ram' = '16';

This provides more information than a simple execution time.

The output can contain information about:

Scan type
Estimated rows
Actual rows
Planning time
Execution time
Buffer activity

The baseline becomes the reference point for future comparisons.

Why EXPLAIN ANALYZE Matters

EXPLAIN shows the plan PostgreSQL intends to use.

EXPLAIN ANALYZE executes the query and reports what actually happened.

That distinction is important.

For example:

Estimated rows: 1,000
Actual rows:    50,000

A large difference may indicate that PostgreSQL's estimates do not match the real data distribution.

That can influence later plan decisions.

Capture Buffer Usage

Add:

BUFFERS

to the analysis.

For example:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM products
WHERE data->'specifications'->>'ram' = '16';

Buffer statistics help show how much database-page activity the query generated.

This can be especially useful when execution time changes because of environmental factors.

A query that becomes slower while reading substantially more buffers deserves investigation.

Establishing a JSON Index

If a JSON query is executed frequently, an index may improve retrieval.

For example:

CREATE INDEX idx_products_data
ON products
USING GIN (data);

Then rerun the query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM products
WHERE data @> '{"specifications":{"ram":16}}';

Notice that the query uses a different JSON operator.

That is important.

Do not assume that creating an index automatically improves every JSON query.

The query shape and index type must match the workload.

Comparing Query Shapes

These two queries may represent similar business requirements:

SELECT id
FROM products
WHERE data->'specifications'->>'ram' = '16';

and:

SELECT id
FROM products
WHERE data @> '{"specifications":{"ram":16}}';

The database may choose different plans.

A benchmark should therefore compare the actual queries used by the application rather than a simplified version created only for demonstration.

What Should Be Compared?

A useful plan comparison includes:

Metric	Baseline	New Plan
Scan type	Record	Record
Estimated rows	Record	Record
Actual rows	Record	Record
Planning time	Record	Record
Execution time	Record	Record
Shared buffer hits	Record	Record
Shared buffer reads	Record	Record
Temporary activity	Record	Record

Do not focus on a single metric.

A small execution-time difference does not necessarily indicate a meaningful regression.

Detecting Scan Changes

One of the clearest regression signals is a change in scan strategy.

For example:

Baseline:
Bitmap Index Scan
      |
      v
Bitmap Heap Scan

Later:

New:
Seq Scan

This does not automatically mean the new plan is wrong.

A sequential scan can be the correct choice when a large percentage of rows match the condition.

The important question is whether the new plan is appropriate for the workload.

Testing Different Selectivity Levels

JSON query behavior can change depending on how many rows match.

Test highly selective conditions:

Matches 0.1% of rows

and less selective conditions:

Matches 50% of rows

The database may reasonably choose different plans.

A production-like benchmark should therefore include multiple selectivity levels.

Testing Nested JSON

Nested structures should also be included.

For example:

SELECT id
FROM products
WHERE data @>
      '{"specifications":{"storage":512}}';

Test different nesting levels:

Top-level property
Nested property
Array element
Multiple conditions

This helps reveal where query-plan behavior changes.

Testing Multiple Conditions

Real applications often filter JSON using multiple properties.

For example:

SELECT id
FROM products
WHERE data @> '{
    "category": "laptop",
    "specifications": {
        "ram": 16
    }
}';

Capture the plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM products
WHERE data @> '{
    "category": "laptop",
    "specifications": {
        "ram": 16
    }
}';

Then compare it with the baseline.

Testing Data Distribution

Production data is rarely perfectly uniform.

For example:

category = laptop     -> 5%
category = phone      -> 60%
category = monitor    -> 20%
category = tablet     -> 15%

A query against phone may behave differently from a query against laptop.

A benchmark should include common and uncommon values.

Otherwise, the test may miss plan changes caused by data skew.

Updating Statistics

PostgreSQL uses statistics to estimate query behavior.

After loading or significantly changing test data, update statistics:

ANALYZE products;

Then rerun the query.

This is important because stale statistics can make a benchmark misleading.

If you compare plans before and after a data change without controlling statistics, you may attribute the difference to PostgreSQL when the actual cause is the statistics state.

Testing Query Plans After Schema Changes

A regression can appear after changes such as:

Adding an index
Removing an index
Changing column definitions
Changing JSON structure
Loading substantially different data
Updating PostgreSQL
Updating application query patterns

A good compatibility suite should capture representative plans before and after these changes.

Automating Plan Capture

You can store query plans as test artifacts.

For example:

benchmarks/
    baseline/
        query-001.json
        query-002.json
    candidate/
        query-001.json
        query-002.json

JSON output from EXPLAIN can make automated comparison easier:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id
FROM products
WHERE data @> '{"specifications":{"ram":16}}';

The test system can then compare selected plan attributes.

Do Not Compare Plans as Raw Text

Raw plan text can change because of harmless formatting differences.

Instead, extract important properties:

Node Type
Estimated Rows
Actual Rows
Actual Total Time
Buffer Reads
Buffer Hits

This makes automated comparison more stable.

Creating a Regression Threshold

Not every change should fail the build.

For example, you might define a test policy such as:

Critical query:
Execution time regression > defined threshold
AND
Plan quality worsens
    |
    v
Review required

The threshold should be based on the application's requirements and benchmark stability.

Do not choose an arbitrary percentage and treat it as a universal PostgreSQL rule.

Testing Under Repeated Execution

A single execution can be misleading.

Run important queries repeatedly.

For example:

Warm-up
   |
   +--> Run 1
   +--> Run 2
   +--> Run 3
   +--> Run 4
   +--> Run 5

The warm-up run can help separate initial effects from repeated execution behavior.

Record the results rather than relying on one timing measurement.

Common Mistakes

Comparing Only Execution Time

Execution time can vary because of cache state and system load.

Use plan structure and buffer metrics as additional evidence.

Ignoring Actual Rows

Estimated rows can look reasonable while actual row counts are very different.

Testing Only One JSON Shape

Nested objects, arrays, and multiple conditions can behave differently.

Using Unrealistic Data

A tiny uniform dataset does not represent a production workload.

Forgetting ANALYZE

Stale statistics can produce misleading plans.

Assuming an Index Is Always Better

PostgreSQL may correctly choose a sequential scan for a query that returns a large portion of the table.

Troubleshooting Plan Regressions

When a regression appears, investigate in this order:

Compare the scan strategy.
Compare estimated and actual row counts.
Check statistics.
Check index availability.
Check data distribution.
Compare query shape.
Check buffer activity.
Repeat the query under controlled conditions.

For example:

Execution Time Increased
        |
        v
Plan Changed?
        |
       / \
     Yes  No
      |    |
      v    v
Analyze  Check workload
Plan     and environment

This avoids immediately changing indexes without understanding the cause.

Production-Like Workload Design

A meaningful benchmark should reflect how the application actually queries JSON.

Include:

Frequent queries
Slow queries
Highly selective filters
Broad filters
Nested JSON conditions
Multiple predicates
Different data distributions

If possible, capture representative query shapes from application telemetry and recreate them against sanitized benchmark data.

Best Practices

Capture a Baseline

Store representative execution plans before making changes.

Use Production-Like Data

Data distribution can influence query plans significantly.

Compare Multiple Signals

Look at plan structure, rows, buffers, and execution time.

Keep the Environment Controlled

Run comparisons under similar conditions.

Test Different Selectivity

A query can behave differently depending on how many rows match.

Refresh Statistics

Run ANALYZE after significant test-data changes.

Automate Regression Detection

Store structured plan output and compare important attributes automatically.

Investigate Before Changing Indexes

A plan difference is a signal to investigate, not automatically proof that an index is missing.

Advantages

Helps identify query-plan changes before production deployment.
Provides objective evidence for database optimization decisions.
Works well with automated regression testing.
Makes JSON query behavior easier to analyze.
Can detect regressions that simple functional tests cannot find.

Disadvantages

Execution plans can vary with data and environment.
Benchmarking requires realistic datasets.
Some performance differences are difficult to reproduce.
A plan that looks more complex is not necessarily slower.
Maintaining plan-regression tests adds database engineering work.

Conclusion

JSON queries can be powerful, but their performance depends heavily on query shape, indexes, statistics, data distribution, and the execution plan selected by PostgreSQL.

That makes query-plan regression testing an important part of database engineering.

The most useful approach is to establish a baseline using EXPLAIN (ANALYZE, BUFFERS), build a production-like dataset, test representative JSON queries, and capture structured plan information. When a change is introduced, compare the new plan against the baseline rather than looking only at the final query result.

Pay particular attention to scan strategies, estimated versus actual rows, buffer activity, and changes in execution behavior.

Most importantly, do not treat every plan change as a regression. PostgreSQL may legitimately choose a different plan when data distribution, selectivity, or statistics change.

A real regression exists when the new behavior produces an unacceptable increase in resource consumption or latency for the workload that matters.

By making these checks part of an automated database validation process, teams can catch problematic query-plan changes before they reach production and make PostgreSQL upgrades, schema changes, and JSON query optimizations much easier to evaluate.