Last reviewed: 2026-09-12

Direct answer

A reliable coding agent query plan regression testing gate compares stable plan properties, not raw plan text or a single wall-clock measurement. For every important query touched by a patch, capture a baseline from the parent revision and a candidate from the proposed revision under the same database version, schema, statistics, fixture data, and parameter profile. Normalize both plans into facts such as access method, relation, index use, join shape, explicit sort, estimated rows, actual rows, and storage work. Then apply a small contract owned by the application team.

The gate should fail when a candidate introduces a disallowed full scan, loses an expected indexed search, adds an avoidable sort, produces a severe estimate-versus-actual mismatch, or exceeds a calibrated runtime budget. It should report an inconclusive result when the environment or evidence is invalid. An operator can then distinguish a real regression from fixture drift instead of accepting a newly generated baseline automatically.

Keep correctness and performance separate. Existing tests must still prove that the query returns the right rows. The plan gate answers a different question: did the patch change how the database obtains those rows in a way that violates an explicit performance contract?

Who this is for

This workflow is for engineers reviewing agent-written SQL, indexes, query-builder code, object-relational mappings, or schema changes. It is most useful when a pull request can pass functional tests while changing access paths, join order, sorting, or the amount of data read.

It also suits platform teams that operate several database engines. The shared policy can remain engine-neutral, while a small adapter translates each engine’s plan output into the same normalized evidence model. Database specialists still own thresholds and exceptions; the coding agent can generate evidence, but it should not approve its own baseline changes.

Key takeaways

  • Compare the parent revision and candidate in matched environments.
  • Store machine-readable plans where the engine provides them, but assert semantic properties rather than complete serialized output.
  • Run execution-based analysis only for explicitly allowlisted statements in an isolated database.
  • Treat timing as supporting evidence. Plan shape, rows read, row-estimate error, sorts, spills, and distribution changes are often more actionable.
  • Make missing fixtures, stale statistics, parser errors, and timeouts produce an inconclusive result that blocks the merge.
  • Require human approval for a contract or baseline update.

Define a query contract

Give each important query a stable identifier and describe the behavior that matters. This illustrative contract avoids coupling the gate to every cost value or incidental plan field:

query_id: orders-by-customer
statement_class: read
engine: postgresql
engine_major: 18
fixture_set: medium-skew-v2
required:
  relation: orders
  access: indexed
forbidden:
  - full_scan_on_orders
  - explicit_sort
limits:
  estimate_error_ratio: 8
  execution_ms_p95: 40

Calibrate limits with repeated runs on representative fixtures. A threshold copied from another query or machine is not a useful contract. Record who owns the query, why each rule exists, and when the fixture was last reviewed.

Operator workflow: happy path

  1. Inspect the patch and map changed SQL, indexes, and data-access code to query identifiers. If the change is part of a migration, use the database-migration review checklist alongside the plan gate.
  2. Build separate baseline and candidate databases from the parent and proposed revisions. Pin the database version and extensions, load the same fixture, refresh planner statistics in both, and verify matching schema and fixture identifiers.
  3. Run correctness tests first. Stop if result shape, ordering guarantees, or returned values differ.
  4. Capture non-executing plans for every registered query. For PostgreSQL, a machine-readable capture can start with:
EXPLAIN (FORMAT JSON)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 25;
  1. For allowlisted read queries, collect execution statistics in an isolated clone. Never infer from the word EXPLAIN that a command is a dry run; execution-based variants run the target statement.
  2. Normalize engine output. Preserve node families, parent-child relationships, relations, access methods, indexes, joins, sorts, estimated and actual rows, and available storage or network counters. Drop volatile timing details from structural snapshots.
  3. Evaluate the contract and emit both the normalized delta and the original plan artifacts. Repeat runtime samples when a threshold uses a percentile.
  4. Pass only when correctness, environment validation, structural rules, and calibrated budgets all pass. A reviewer checks the evidence before merge.

Operator workflow: error path

Suppose the baseline uses an indexed access path, while the candidate adds a full scan and an explicit sort. The gate should produce a focused failure rather than thousands of lines of plan text:

{
  "event": "query_plan_gate",
  "run_id": "run-42",
  "query_id": "orders-by-customer",
  "before": {
    "access": "indexed",
    "sort": "none"
  },
  "after": {
    "access": "full_scan",
    "sort": "explicit"
  },
  "verdict": "fail",
  "reason_codes": ["FULL_SCAN_ADDED", "SORT_ADDED"]
}

The operator first checks database version, schema, fixture identity, and statistics freshness. If those inputs differ, rebuild both sides and classify the original run as inconclusive. If the delta reproduces, return the query identifier, normalized delta, and plan artifacts to the patch author or coding agent. After the SQL or index is corrected, rerun the complete gate. If the new plan is intentional, the query owner must document the reason and approve a narrowly scoped contract change; replacing the baseline merely because the candidate failed defeats the gate.

Log evidence without logging data

A useful sanitized event contains identifiers and aggregate plan facts, not raw parameter values or result rows:

{
  "event": "query_plan_gate",
  "run_id": "run-42",
  "query_id": "orders-by-customer",
  "revision": "candidate",
  "engine": "postgresql-18",
  "fixture_set": "medium-skew-v2",
  "node_types": ["Limit", "Index Scan"],
  "relations": ["orders"],
  "indexes": ["orders_customer_created_idx"],
  "estimated_rows": 25,
  "actual_rows": 25,
  "execution_ms": 12.4,
  "raw_sql_logged": false,
  "parameter_values_logged": false,
  "result_rows_logged": false,
  "verdict": "pass",
  "reason_codes": []
}

If relation or index names are sensitive in your environment, map them to stable aliases before exporting artifacts. Keep full plans in access-controlled CI storage, apply a retention period, and avoid copying downloadable diagnostic links into broad logs.

Sources checked

  • The PostgreSQL guide to using EXPLAIN describes plans as trees of scan, join, aggregation, and sort nodes. It explains estimated costs and row counts, notes that sampled statistics and platform-dependent costs can cause variation, and recommends XML, JSON, or YAML when software will analyze the output.
  • The SQLite EXPLAIN QUERY PLAN documentation explains SCAN, SEARCH, index use, nested scans, and temporary sorting structures. It also warns that the output format can change between releases, which is a strong reason to avoid byte-for-byte snapshots.
  • The CockroachDB EXPLAIN ANALYZE reference states that the command executes its target and can therefore modify or delete data. It also documents execution evidence including actual row counts, planning and execution time, storage rows and bytes read, network use, contention, memory, and temporary disk usage.

Together, these sources support a cross-engine design with engine-specific capture adapters, semantic normalization, explicit execution safety, and controlled evidence retention.

Contract details to verify

Before enabling a merge-blocking gate, verify the following details for every registered query:

  1. Statement identity: Use a stable query identifier and record the source file or query-builder operation that produces it. Do not make raw SQL text the only identity because harmless formatting can change it.
  2. Correctness oracle: Define expected result shape, ordering, and invariants. A faster plan that changes results is still wrong.
  3. Execution class: Mark the statement as read-only, mutating, lock-acquiring, or uncertain. Only an explicit read allowlist should enter the automated execution path. Everything else receives non-executing inspection or a dedicated disposable test.
  4. Environment identity: Record engine and extension versions, schema revision, fixture set, data scale, distribution profile, and statistics state. Compare baseline and candidate only after these fields match.
  5. Normalized vocabulary: Define how each engine maps its nodes to concepts such as full scan, indexed access, join, explicit sort, materialization, and distributed work. Preserve unknown nodes and fail with an adapter error instead of silently dropping them.
  6. Structural rules: State which large relations must use selective access, which queries may sort, and which join or distribution changes require review. Avoid declaring that every full scan is bad; a small table or broad query may legitimately use one.
  7. Numeric budgets: Set tolerances for estimate error, actual rows read, execution time, memory, network use, or temporary disk only when the engine exposes them and the fixture can reproduce them. Use ranges or ratios rather than exact equality.
  8. Approval ownership: Name the reviewer who can accept an intentional plan change. The evidence-producing agent may propose a contract edit, but a separate owner should approve it.
  9. Artifact policy: Retain the query identifier, environment manifest, normalized baseline, normalized candidate, rule results, and restricted raw plans. Exclude parameter values, returned rows, and private diagnostic locations from general logs.

Plan this harness as an integration test, not a string-matching unit test. A disposable service integration-test workflow helps keep the database version, fixtures, and cleanup behavior reproducible.

Failure modes

Snapshotting raw output. Whitespace, costs, timing, node details, or an engine upgrade can change while the meaningful access strategy remains acceptable. Parse supported structured output where possible and compare a versioned semantic model. SQLite explicitly warns consumers not to depend on its displayed output format.

Treating estimated cost as elapsed time. PostgreSQL describes planner costs as arbitrary units governed by cost parameters, and its estimates can vary with sampled statistics and the platform. Use costs to understand planner choices, not as a universal millisecond budget.

Using an unrepresentative fixture. A tiny or uniformly distributed data set can favor a scan that production-scale or skewed data would not. Maintain boundary fixtures for common, rare, empty, and high-fan-out parameter profiles.

Running analysis on a mutating statement. Execution-based analysis is not automatically safe. The CockroachDB reference explicitly warns that its analysis command executes the target and may modify or delete data. Classify statements before capture and use isolated disposable databases for any uncertain path.

Ignoring statistics drift. If one side has refreshed statistics and the other does not, the comparison measures setup drift. Refresh both consistently and record the state before collecting plans.

Trusting one warm timing. Cache state, concurrent work, and startup effects can move a single measurement. Use repeated samples and a documented aggregation rule, while retaining structural evidence that explains a slowdown.

Overfitting to one index name. An engine may choose a different valid index after a schema change. Prefer intent such as selective indexed access on a relation unless a particular index is itself part of the contract.

Auto-approving a new baseline. A tool that overwrites expected evidence whenever a patch changes the plan records regressions instead of catching them. Require a visible delta, rationale, and independent approval.

FAQ

Is every sequential or full scan a regression?

No. A full scan may be appropriate for a small relation or a query that needs most rows. The contract should flag scans only where fixture scale, selectivity, and query intent make them suspicious. The operator then reviews the actual plan evidence.

Should execution time be a hard merge gate?

Only when the environment is controlled and the threshold is based on repeated representative runs. Otherwise, use timing as review evidence and gate on less volatile facts such as access method, rows read, estimate error, explicit sorts, or temporary disk use.

Can the coding agent update the expected plan?

It can generate a proposed normalized baseline and explain the delta. It should not approve or silently install that baseline. A query owner should decide whether the change is intentional and whether the contract still expresses the application’s performance requirement.

How should an adapter handle a new plan node?

Preserve the unknown node in the artifact and return an inconclusive adapter result. Silently mapping an unfamiliar node to a harmless category can create a false pass. Add a reviewed mapping, version the normalizer, and rerun both baseline and candidate.

Can this work with SQLite even though its output can change?

Yes, but the adapter must be version-aware. Extract supported concepts such as SCAN, SEARCH, index use, and temporary sorting, retain the engine version, and avoid treating the command-line rendering as a permanent serialization contract.

Reader next step

Choose three high-impact read queries affected by recent SQL or index work. Give each a stable identifier, build one representative fixture, capture baseline and candidate plans in matched disposable databases, and write one structural rule per query. Add correctness checks before plan comparison and make every invalid environment produce an inconclusive result. Once that small set is reliable, expand by query risk rather than raw query count.