explain the query before running it" agent pattern

TL;DR

Make the agent produce three things before executing: the SQL, a plain-English explanation of what each clause does, and the database EXPLAIN plan which it must read. The two explains catch different mistakes: the English one catches wrong intent, the plan catches wrong cost. It works because generation is cheap and execution is expensive, sometimes irreversibly so, and the most common agent disaster is a cross join on a billion-row table run just to check something.

"explain the query before running it"

Use this when

• An agent runs analytics queries against large tables
• Query cost or runtime is a real concern
• You want the agent's intent verified before it touches data
• Reviewing agent work and need the reasoning in inspectable form

Not for

• Trivial queries on tiny tables where the ceremony costs more than the query
• Write operations, which need stricter gates than explanation
• Engines with no EXPLAIN equivalent, where only the English half applies

Steps

1. Require the agent's response to follow the three-part shape:

INTENT: monthly revenue by product category for Q3, excluding test orders.
SQL: SELECT category, SUM(amount_cents)/100.0 AS revenue ...
PLAN CHECK: explain below, then execute only if estimated rows < 1M.

Expected output: every proposed query arrives with its intent stated separately from its implementation, so a reviewer can spot intent errors without reading SQL.

1. Have the agent run EXPLAIN and read the plan itself:

EXPLAIN (ANALYZE FALSE, COSTS TRUE)
SELECT c.category, SUM(o.amount_cents)/100.0 AS revenue
FROM orders o JOIN products p ON o.product_id = p.id
WHERE o.ordered_at BETWEEN '2026-07-01' AND '2026-09-30'
  AND o.is_test = false
GROUP BY c.category;

Expected output: the plan shows the join strategy and estimated row counts. The agent must summarize it in one sentence, hash join on 2M rows, before proceeding.

1. Check the estimate against a threshold and refuse over it:

estimated_rows = parse_plan_rows(explain_output)
assert estimated_rows < 1_000_000, f"too broad: {estimated_rows} rows, narrow the filters"

Expected output: queries estimated above the threshold never execute. The agent must add filters or aggregation instead of hoping the warehouse is fast.

1. Execute, then compare actual rows against the estimate:

-- after execution, the runner records both numbers
-- estimate: 84,000 rows | actual: 83,912 rows | ratio: 1.0  -> healthy
-- estimate: 84,000 rows | actual: 4,200,000 rows | ratio: 50 -> investigate

Expected output: a logged estimate-vs-actual pair per query. Ratios far from 1.0 mean the planner was blind, usually stale statistics, and the next similar query gets extra scrutiny.

1. Keep the explanation with the result so the answer is auditable:

ANSWER: Q3 revenue was $4.21M across 6 categories.
EVIDENCE: intent + SQL + plan summary above; actual rows 83,912.

Expected output: anyone reading the answer later can see what was asked, what ran, and what it cost, without rerunning anything.

Variant phrasings

agent SQL planning pattern

The pattern is propose, explain twice, then execute. Skipping either explanation removes one class of mistake detection.

LLM explain before execute database

The database EXPLAIN is the non-negotiable half: English explanations can be confidently wrong, but the plan is the engine telling you what it will actually do.

two-phase query agent

Phase one is pure reasoning with zero side effects. Phase two executes only what phase one justified. Agents that interleave the two are the ones that surprise you.

Why it happens

Agents generate SQL fluently and execute it eagerly, which inverts the safe order of operations: thinking should be cheap and doing expensive, but for an agent both feel equally free. Forcing the explanation first separates intent errors (wrong question, wrong filter) from execution errors (wrong cost, wrong scale), and each gets caught by a different check. The plan-reading step additionally trains the agent, over many iterations, to write queries whose plans look sane on the first try.

Edge cases

• EXPLAIN itself can be slow on some engines for very complex queries; use estimated costs without ANALYZE to keep it cheap.
• Plans lie when statistics are stale; the estimate-vs-actual check in step 4 is what catches this, so do not skip it.
• Read-only contexts still benefit: the pattern is about cost and correctness, not just write safety.
• Some agents will write a plausible English explanation for broken SQL; the plan check is the backstop, never rely on prose alone.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstpGhO6PRl1CSxEEyOhYZHg