Fixture EXPLAIN or Captured Plans: A Debate for SQL Review Agents

A review agent cleared a reporting query because the fixture database returned a nested-loop plan in under twenty milliseconds. The same query shape hit production later that day and chose a sequential scan across a skewed events table. The difference was not the SQL text; it was the evidence the agent was allowed to trust. Fixture EXPLAIN output and captured plans answer different questions, and mixing them quietly creates false confidence.

This article treats that conflict as a structured debate rather than a quiet tooling preference. One position says a SQL review agent should always obtain a fresh EXPLAIN from a disposable fixture. The other position says the agent should only read captured plans from the runtime that will execute the statement. A later decision rule chooses between them using evidence quality, not using model branding or demo latency.

The failure this debate is about

Consider a pull request that adds a dashboard query joining events to accounts on account_id. The fixture loader inserts a few thousand uniform rows, so PostgreSQL prefers an index nested loop. The agent then reports that no sequential scan is present and the statement looks safe to merge. Production histograms are skewed, the same predicate matches many recent events, and the planner picks another join order.

Captured plans fail in the opposite direction when the sample does not represent daytime bind values. A plan taken during a quiet night, or against a replica with untouched default statistics, can bless a fragile query. The agent then argues with developers using evidence that is already stale relative to autovacuum. Both evidence sources remain legitimate in review, but they are not interchangeable artifacts for blocking merges.

Position A: Fresh fixture EXPLAIN as the primary artifact

Advocates of fixture EXPLAIN want a review that stays reproducible on every pull request without cluster access. They load committed schema, constraints, and a published row-count recipe, then run EXPLAIN without ANALYZE. Writers never execute, and the agent receives JSON plans that CI can diff when a finding is disputed. The operational claim is simple: if two reviewers cannot replay the plan, the finding is only an anecdote.

This position is strongest when the question is structural rather than statistical inside the planner output. Missing indexes, implicit casts that block index use, and SELECT * on wide JSON columns appear on tiny fixtures. The position becomes weak when cardinality, correlation, or partial-index predicates dominate estimated cost. A fixture that lies about data shape will make the agent sound certain while remaining wrong.

Position B: Captured plans from the target runtime only

Advocates of captured plans treat the planner as an environment-specific compiler that reads local statistics. They record EXPLAIN FORMAT JSON on a staging replica, a canary, or a CI restore of last night's statistics. The agent is forbidden from inventing a plan that the target cluster would not actually choose. The empirical claim is to review the plan you will pay for, not the plan a laptop invented.

This position is strongest for high-volume reads where n_distinct and correlation decide join order. It is weakest when capture is incomplete, autovacuum has not run, or bind values differ from production. A single captured plan is a sample of one execution shape, not a distribution over the workload. Review policies that block on one hash without recording bind types will create noisy false positives.

Evidence each side can actually collect

The debate stops being philosophical once both sides write down the files they can actually produce. Fixture work produces schema files, seed scripts, and EXPLAIN JSON committed next to the query text. Captured-plan work produces a fingerprint, bind parameter types, stats_reset timestamps, and the PostgreSQL major version. If a side cannot produce those files during review, it should not win a blocking argument.

The following fingerprint helper is a labeled, unexecuted sketch for CI, not a latency benchmark. It keeps node types and relation names, and it drops estimated costs so fixture size cannot dominate the hash.

# Proposal: fingerprint EXPLAIN JSON without executing DML.
# Unexecuted example; run only against fixtures or read-only replicas.

import hashlib
import json

KEEP_KEYS = ("Node Type", "Relation Name", "Index Name", "Join Type", "Strategy")

def walk(node, acc):
    if not isinstance(node, dict):
        return
    acc.append(tuple(node.get(k) for k in KEEP_KEYS))
    for child in node.get("Plans") or []:
        walk(child, acc)

def fingerprint(explain_json: str) -> str:
    plan = json.loads(explain_json)[0]["Plan"]
    acc = []
    walk(plan, acc)
    blob = json.dumps(acc, separators=(",", ":")).encode()
    return hashlib.sha256(blob).hexdigest()[:16]

A matching capture statement should stay read-only, and it should never add ANALYZE for writers. Point the same shape at a fixture DSN and, when policy allows, at a staging replica that already has representative statistics.

-- Labeled example: costs only, no execution.
EXPLAIN (FORMAT JSON, COSTS TRUE, ANALYZE FALSE, VERBOSE FALSE)
SELECT a.id, count(*)
FROM events e
JOIN accounts a ON a.id = e.account_id
WHERE e.created_at >= now() - interval '1 day'
GROUP BY a.id;

# Labeled example: capture both artifacts as files the agent may read.
psql "$FIXTURE_DSN" -Atc "EXPLAIN (FORMAT JSON, ANALYZE FALSE) $SQL" > fixture_plan.json
psql "$STAGING_DSN" -Atc "EXPLAIN (FORMAT JSON, ANALYZE FALSE) $SQL" > captured_plan.json
psql "$STAGING_DSN" -Atc "SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();" > stats_reset.txt

A practical workflow in numbered steps

The numbered workflow below keeps the debate testable inside ordinary pull requests on a data team. It uses a PostgreSQL instance that you already operate for fixtures or for restored statistics. When CI needs a model to label structural versus statistical findings, a scratch runtime is enough.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode is an open-source project with free model access and a free server option for that classifier. Those two availability claims host the agent; they do not create planner statistics the cluster never collected.

1. Freeze the statement text, bind types, and search_path in the pull request, because planner output is otherwise meaningless.
2. Build a fixture database from committed DDL and a documented seed recipe, then capture EXPLAIN JSON without ANALYZE.
3. If policy allows, capture EXPLAIN JSON from staging or from a statistics restore that matches the target major version.
4. Fingerprint both plans with the helper above, and store the hashes plus the raw JSON as review artifacts.
5. Send the model a bounded packet: statement, schema excerpt, both fingerprints, and a short list of disallowed node types.
6. Require the agent to label every finding as structural or statistical, and reject unlabeled findings before merge.
7. Apply the decision rule in the next section before the agent is allowed to block a merge on statistical grounds.

A minimal packet builder, labeled as a proposal, keeps the review model from swallowing entire information_schema dumps. Do not paste production rows, and do not ask the model to invent histograms that were never captured.

# Proposal: bound the review packet; unexecuted example.

def build_packet(sql, schema_excerpt, fixture_fp, captured_fp, captured_age_hours):
    return {
        "sql": sql,
        "schema_excerpt": schema_excerpt[:4000],
        "fixture_fingerprint": fixture_fp,
        "captured_fingerprint": captured_fp,
        "captured_age_hours": captured_age_hours,
        "disallowed_nodes": ["Seq Scan on events"],
        "rules": [
            "Label each finding structural or statistical.",
            "Never recommend EXPLAIN ANALYZE on INSERT, UPDATE, DELETE, or MERGE.",
            "If fingerprints differ, do not auto-rewrite SQL.",
        ],
    }

Decision table

The table below is a policy object that should be versioned next to the agent prompt. Teams can argue about a row in review, instead of arguing about a model tone in chat. It is not a quality score, and it does not claim a latency improvement for either evidence source.

Condition	Prefer fixture EXPLAIN	Prefer captured plan	Block merge?
Finding is implicit cast or missing index	Yes	Optional contrast	Yes, if the fixture reproduces it
Fingerprints match	Either	Either	Only for a listed disallowed node
Fingerprints differ and captured plan is under 24h old	Contrast only	Primary	Yes, if the captured plan has a disallowed node
Captured plan older than 24h or stats_reset is unknown	Primary	Discard	No statistical block
Statement is DML	Fixture EXPLAIN only	Replica EXPLAIN without ANALYZE	Always block ANALYZE
Seed data cannot encode skew	Do not claim costs	Required	Human review, no auto-block

The decision rule

Apply the following rule without slogans and without hidden fallbacks inside the agent prompt text. If the finding is structural and the fixture plan reproduces it, the fixture wins and the agent may block. If the finding is statistical, the captured plan wins only when age, major version, and bind types are known. Otherwise the agent may comment, and it must not block the merge on statistical cost arguments.

If the two fingerprints disagree, the agent must not rewrite SQL, because the wrong plan trains a new incident. If DML is involved, drop ANALYZE and drop any suggestion that requires executing the write on a shared cluster. That split also clarifies what a scratch model runtime can honestly do during a SQL review. It can classify structural versus statistical findings from a bounded packet, and it cannot invent missing histograms.

Limitations and who should not use this

This approach does not estimate latency, and it does not replace pg_stat_statements sampling on serving clusters. Fixture EXPLAIN will understate skew, correlation, and partial index usefulness, so it must not block on cost. Captured plans will overfit one bind value, which is why a single hash is not a workload summary. Treat both artifacts as evidence with known failure modes, not as a substitute for owned query operations.

Do not use this debate as a merge gate if generated SQL text is unstable across equivalent requests. Skip it when RLS policies differ between fixture and runtime, or when the statement must not leave the production network. Do not send production row samples to any shared model host when columns can contain secrets or identifiers. Teams that cannot restore statistics, and also cannot build honest fixtures, should keep SQL review human.

The method also refuses a common shortcut that makes agents sound more certain than the evidence. Adding ANALYZE to settle a review turns the agent into a load generator, and sometimes into a locker of rows. If the only way to settle the debate is to execute the query, the agent has left review. That next workflow belongs to operations, with different owners, different budgets, and explicit rollback rules.

What to keep when the models change

Model routing will keep changing, and free inference tiers will keep changing around whatever host you choose. The durable artifact remains the pair of fingerprints plus the structural-versus-statistical label on each finding. If a future model disagrees with a previous comment, rerun the packet against the same JSON files. Do not rerun the query to break a tie, because that restarts the original production risk this debate exists to avoid.