When Runtime Plans Enter the Prompt: A Structured Debate for SQL Review Bots

The following scene is a composite on-call pattern, not a personal claim about one employer. A checkout query crossed its p95 budget during a mid-afternoon spike, and the reviewer copied the plan. The paste included actual rows, shared hit counts, and a filter on a customer email that should never leave the database host. The nested loop looked cheap in EXPLAIN and expensive in EXPLAIN ANALYZE, so the reviewer wanted a second opinion from a model. That single paste created a durable contract question: should runtime plans enter the model context at all?

Static SQL text is easy to version, redact, and replay, but it lies about selectivity after the last ANALYZE. Runtime plans tell the truth about this host, this cache, and this parameter set, and they also leak occupancy. Teams that already run cheap review jobs feel this tension every night, because the cost of calling a model is no longer the scarce resource. The scarce resource is the legality and usefulness of the bytes you are willing to ship.

Why the input contract is the real review product

Most SQL review discussions start with the model, the prompt template, or the merge-gate. Those pieces matter, yet they inherit whatever the collector already decided to send. A bot that sees only canonical SQL cannot comment on a bad nested loop that the optimizer chose for one tenant. A bot that sees a raw EXPLAIN ANALYZE can comment on that loop, and it can also memorize an email, a row count that implies revenue, or a bitmap heap scan that fingerprints a table size.

The debate below treats that collector decision as the product surface. Evidence is qualitative and operational, not a leaderboard of model vendors, because vendor scoreboards go stale within a week. The artifact is a redaction pipeline you can run on a snapshot, plus a decision rule that does not require believing either camp in full.

Position A: Runtime plans are the only honest signal

Advocates of plan ingestion argue that SQL review without cardinality is costume jewelry. The text WHERE status = 'paid' AND created_at > $1 is legal, indexable, and still a sequential scan when paid is ninety percent of the table. A reviewer who never sees actual rows will praise the predicate and miss the scan. In that view, shipping a sanitized plan is a duty, not an optional debug flag.

They also argue that estimated EXPLAIN is a weak compromise, because estimates follow stale statistics and ignore buffer cache warmth. A free review box can pull EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) from a restored snapshot and still miss production cache behavior, but it will not miss a three-million-row hash join. For teams whose incidents are join-order mistakes, that snapshot plan is closer to production than any schema dump.

A third claim is about developer behavior rather than optimizer theory. Engineers already paste plans into chat products during incidents, usually with literals intact. A review bot that refuses plans does not reduce leakage; it only moves leakage into unmanaged tools. Position A therefore prefers a controlled collector that strips identifiers and keeps actual rows, shared hit blocks, and node types.

Position B: Runtime plans are a telemetry leak

The opposing camp treats EXPLAIN ANALYZE as production telemetry, closer to a slow-query log than to source code. Actual row counts reveal table growth. Buffer hits reveal whether the working set fits in memory. Filter values, even when they look like statuses, sometimes embed tenant keys. Once those bytes land in a model provider log, your retention policy is no longer the database retention policy.

Position B also distrusts the debugging value of a single plan. One ANALYZE run is a sample, not a distribution, and models overweight the sample they can see. A query that is cheap at noon and brutal after a batch job will teach the bot the noon plan. Teams then merge an index that helps the sample and hurts the night load. Static SQL plus schema keeps the bot in the realm of invariants: missing joins, implicit casts, unparameterized IN lists, and unbounded DELETE.

A further objection is operational coupling. Collecting EXPLAIN ANALYZE on a live primary can block, write to temp, or perturb cache. Collecting it on a replica still executes the query. Snapshot restores are safer, yet they require a refresh job, disk, and a definition of “recent enough.” If the review queue is supposed to be cheap, that restore job is often more expensive than the model call. Position B therefore keeps the bot on canonical SQL, fingerprints, and schema, and leaves plans to humans.

Evidence that does not need a vendor benchmark

Neither position needs a private leaderboard to stay honest. PostgreSQL documents that EXPLAIN ANALYZE actually runs the statement, which is why EXPLAIN ANALYZE on UPDATE is not a read-only act. The same documentation notes that BUFFERS reports hits and reads that describe this cache, not a logical cost. Those two facts already split the camps: one side wants the cache-aware number, and the other side refuses to export cache-aware numbers.

Query fingerprints from pg_stat_statements supply a third evidence class. The fingerprint is stable across bind values, which is good for grouping and bad for selectivity. If your review corpus is fingerprints, Position A will say you discarded the only number that mattered. If your corpus is full text, Position B will say you exported the customer. The artifact below does not pick a winner; it makes the trade visible in a file you can diff.

Artifact: redact the plan, then classify what remains

The script treats EXPLAIN (FORMAT JSON) as untrusted input. It walks the plan tree, drops Output lists that often contain column values, replaces Filter and Index Cond strings with a placeholder, and keeps node type, join type, Actual Rows, and Shared Hit Blocks. That mix is a proposal, not a compliance certification. Label it as unexecuted policy until your security group reviews the field list.

# explain_redact.py — proposal for a collector gate, not a certified anonymizer
from __future__ import annotations

import json
import re
from typing import Any

KEEP_KEYS = {
    "Node Type",
    "Parent Relationship",
    "Join Type",
    "Parallel Aware",
    "Async Capable",
    "Relation Name",
    "Schema",
    "Alias",
    "Plan Rows",
    "Plan Width",
    "Actual Rows",
    "Actual Loops",
    "Shared Hit Blocks",
    "Shared Read Blocks",
    "Startup Cost",
    "Total Cost",
}

DROP_KEYS = {
    "Output",
    "Filter",
    "Index Cond",
    "Recheck Cond",
    "Hash Cond",
    "Merge Cond",
    "Join Filter",
    "One-Time Filter",
    "Function Name",
    "Function Call",
}

LITERAL = re.compile(r"'([^']*)'")
EMAIL = re.compile(r"[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}", re.I)


def scrub_text(value: str) -> str:
    value = EMAIL.sub("<redacted-email>", value)
    value = LITERAL.sub("'<redacted>'", value)
    return value


def redact_plan(node: dict[str, Any]) -> dict[str, Any]:
    out: dict[str, Any] = {}
    for key, value in node.items():
        if key == "Plans" and isinstance(value, list):
            out[key] = [redact_plan(child) for child in value if isinstance(child, dict)]
            continue
        if key in DROP_KEYS:
            continue
        if key not in KEEP_KEYS:
            continue
        if isinstance(value, str):
            out[key] = scrub_text(value)
        else:
            out[key] = value
    return out


def classify(redacted: dict[str, Any]) -> str:
    text = json.dumps(redacted)
    if "<redacted-email>" in text:
        return "block-pii"
    actual = int(redacted.get("Actual Rows") or 0)
    if actual >= 1_000_000:
        return "review-volume"
    if redacted.get("Node Type") in {"Seq Scan", "Nested Loop"}:
        return "review-shape"
    return "allow-static-only"


def main() -> None:
    payload = json.loads(open("plan.json", encoding="utf-8").read())
    plan = payload[0]["Plan"] if isinstance(payload, list) else payload["Plan"]
    redacted = redact_plan(plan)
    print(json.dumps({"class": classify(redacted), "plan": redacted}, indent=2))


if __name__ == "__main__":
    main()

Save a fixture as plan.json from EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT .... Run python explain_redact.py and keep the classifier output next to the original SQL fingerprint. The point is not that this field list is complete. The point is that every retained key is now an explicit decision you can argue about in review.

Numbered workflow for a nightly collector

1. Restore a recent logical snapshot to a throwaway instance, and refuse EXPLAIN ANALYZE on a primary that serves users. Snapshot age should be an advertised SLO, because Position A collapses if the snapshot is weeks behind the incident.
2. Collect pg_stat_statements fingerprints with mean time, calls, and query text with constants replaced by $n where the extension allows it. Store the fingerprint as the stable identity of the review item.
3. For the slowest N fingerprints, run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) only when the statement is classified read-only by a parser you control. Skip UPDATE, DELETE, REFRESH MATERIALIZED VIEW, and anything with VOLATILE functions.
4. Pass the JSON through explain_redact.py, persist the class, and drop the raw plan unless the class is review-shape or review-volume. block-pii never leaves the snapshot host.
5. Send the model only what the class permits: redacted plan plus fingerprint for the review classes, or canonical SQL plus schema for allow-static-only. Record the class in the review ticket so humans can audit the input contract.
6. Require a human to apply rewrites. The bot may attach a patch, an index suggestion, or a request for new statistics, and it may not open a connection that can run DDL.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. The collector above is a good fit for MonkeyCode's free model access and free server option when you want the redaction job and the language-model pass on a box you do not size yourself. The decision rule still holds if that runner is replaced by any host that can execute Python, reach a snapshot, and call a model API.

Decision table

Signal you actually have	PII class of literals	Statement family	Input the bot may see	Human gate
Fingerprint only	unknown	any	canonical SQL + schema	required
Estimated EXPLAIN	no literals	read-only SELECT	estimated plan, no buffers	required
EXPLAIN ANALYZE on snapshot	redacted cleanly	read-only SELECT	redacted runtime plan	required
EXPLAIN ANALYZE on primary	any	any	nothing; collector must fail closed	n/a
Runtime plan with emails or keys	present	any	blocked by block-pii	security review
Write statement	any	DML/DDL	static SQL only, never ANALYZE	required

Read the table as a fail-closed policy. Missing evidence is not permission to upgrade the input. If you cannot prove the statement is read-only, you do not get ANALYZE. If you cannot prove literals are gone, you do not get a model call.

A decision rule, not a preference

Use runtime plans when all four conditions hold at the same time. The statement is parsed as read-only. The plan comes from a snapshot or a dedicated replica, never from a primary. The redactor drops Output, filter strings, and literal values, and a classifier would not emit block-pii. The review ticket stores the class, so a later auditor can see that the bot was not given raw telemetry.

Use static SQL, fingerprints, and schema when any condition fails. That includes regulated datasets, plans you cannot restore onto an isolated host, and queries whose filters are the product. Position A remains available as an incident tool for a human who already has production access. It does not become the default corpus for a queued bot.

If you need a single sentence for a design doc, use this one. Runtime plans are evidence for a human-owned incident, and they become model input only after a snapshot, a read-only gate, and a field-level redaction that you can diff.

Limitations and who should not run this

The redactor is pattern-based, and pattern-based redaction fails on novel encodings, bytea dumps, and JSON payloads stuffed into text columns. It also keeps relation names, which can be sensitive in multi-tenant catalogs that encode customer identifiers in table names. Teams that cannot publish relation names should not ship even the redacted tree.

EXPLAIN ANALYZE still executes the query. A snapshot that is not isolated from production credentials is not a snapshot for this purpose. Replica ANALYZE can also perturb cache and compete for I/O, which means the “free” review box is not free if it shares disks with the primary. The workflow is a poor fit for OLTP systems that cannot restore a recent copy, and for organizations whose model-vendor contracts do not state retention.

Do not use this approach to justify automatic index builds, automatic statistics changes, or automatic query rewrites. Cheap model access lowers the cost of commentary, not the cost of a bad lock. People who need a guaranteed anonymizer, a certified privacy budget, or a vendor-neutral benchmark should stop at the classifier and skip the model call.

Readers who already collect fingerprints can put the redaction function in front of the model they already call, and treat the class column as the review policy. The interesting disagreement is not which camp sounds stricter. The interesting disagreement is which keys in KEEP_KEYS your team can defend in writing.