Cost-First or Sample-First: A Debate Over How Agent SQL Loops Handle Unknown Table Sizes

A drafting agent returns a query that reads fine: three joins, a GROUP BY, a date filter, an ORDER BY on the aggregate. Nothing in the SQL signals danger. The plan underneath it opens with a sequential scan over a table whose row count nobody on the call can state out loud, and the aggregate spills to disk before the first row comes back.

That scenario is a constructed reproduction of a common failure mode, not a customer incident and not a logged event from my own past. I use it because the interesting argument is not whether the query was wrong. The argument is what your loop checks before the query runs, when the planner's row estimates are the only cheap signal you have.

Two credible positions exist, and they disagree about what evidence counts.

The setup: a loop with two gates available

Assume an agent that drafts read-only analytical SQL against a warehouse you do not fully control. Assume you can run EXPLAIN (FORMAT JSON) cheaply and that you have some bounded way to execute a subset of the data. Assume also that you cannot afford to let a bad draft run to completion against production.

Both camps accept those constraints. They diverge on the first gate to apply.

Position A: gate on planner cost before execution

Cost-first advocates argue that EXPLAIN without ANALYZE is the cheapest safety check available. It parses, rewrites, and plans the statement without touching a row, which means an agent can draft twenty candidates and have all twenty rejected or approved for the price of planning.

The gate is a budget on total planner cost combined with structural checks on plan nodes. If a candidate exceeds the budget, or contains a sequential scan on a relation you flag as large, you refuse to execute and ask the model for a rewrite instead.

The appeal is uniformity. One code path, one threshold, no dependence on having a sampled copy of every table, and no risk that the probe itself is the thing that locks a table.

Position B: probe a bounded subset and measure

Sample-first advocates reply that planner cost is an estimate of an estimate, and that the number is only as good as the statistics behind it. EXPLAIN does not run the query, so it cannot see a nested loop that degenerates because two predicates are correlated in a way the planner modelled as independent.

Their counter-proposal is to run the statement shape against bounded input: wrap it in a LIMIT, set a statement_timeout, open a read-only transaction, and measure wall time and rows returned. A probe that returns in under a second on a bounded slice tells you more about plan shape than any cost number.

The probe also catches the failure that cost-gating misses most often, which is a plan whose estimate looked fine and whose runtime explodes on the tail of a skewed distribution.

Where each position actually breaks

Both positions have a failure mode that their own proponents tend to understate, and it is worth stating them in the same place.

1. Cost-first fails on stale or missing statistics. When ANALYZE has not run recently, or when predicates are correlated, planner estimates and actual rows can diverge by orders of magnitude. You can verify that on your own workload by comparing EXPLAIN against EXPLAIN ANALYZE on a staging clone, and the gap is usually uncomfortable.
2. Cost-first is blind to data-shape hazards. A cartesian product between two small-looking relations has a modest cost estimate and a catastrophic row count at a join depth the plan does not surface clearly.
3. Sample-first fails on sampling bias. A LIMIT returns the first rows the executor produces, which for an unordered scan is not a random sample. Skew in the head of the table is different from skew in the tail, so the probe can pass while the full run stalls.
4. Sample-first needs somewhere to run. If the probe touches the production cluster, you have traded a planning read for an execution read, and you have added a lock-contention surface that the cost gate never had.

Neither position wins outright. That is why the useful artifact is not a verdict but a routing rule.

A reproducible gate harness

The harness below implements both gates and routes between them. It is a template written for PostgreSQL and psycopg 3; I have not benchmarked it against your warehouse, so treat the thresholds as values to calibrate rather than defaults to trust.

Step 1 — extract plan cost and flag risky nodes.

import psycopg COST_BUDGET = 50_000.0 # planner cost units; calibrate per warehouse PROBE_TIMEOUT_MS = 3_000 LARGE_RELATIONS = {"events_raw", "sessions_daily", "billing_ledger"} def plan_doc(conn, sql): with conn.cursor() as cur: cur.execute("EXPLAIN (FORMAT JSON) " + sql) doc = cur.fetchone()[0] return doc[0] if isinstance(doc, list) else doc def walk(node): yield node for child in node.get("Plans", []): yield from walk(child) def plan_signals(plan): total = plan["Plan"]["Total Cost"] seq_on_large = any( n.get("Node Type") == "Seq Scan" and n.get("Relation Name") in LARGE_RELATIONS for n in walk(plan["Plan"]) ) return total, seq_on_large 

Step 2 — route the candidate.

def route(conn, sql): total, seq_on_large = plan_signals(plan_doc(conn, sql)) if total > COST_BUDGET: return "rewrite", total if seq_on_large or total > COST_BUDGET * 0.4: return "probe", total return "allow", total 

Step 3 — probe inside a bounded read-only transaction.

def probe(conn, sql, row_cap=100_000): with conn.transaction(): conn.execute("SET TRANSACTION READ ONLY") conn.execute("SET LOCAL statement_timeout = %s", (PROBE_TIMEOUT_MS,)) wrapped = f"SELECT * FROM ({sql}) AS probe_q LIMIT {int(row_cap)}" with conn.cursor() as cur: cur.execute(wrapped) return len(cur.fetchall()) 

Step 4 — log the decision with the inputs that produced it. Store the SQL hash, total cost, flagged relations, route, and probe outcome. The log is what lets you tune COST_BUDGET later using real evidence instead of intuition.

Step 5 — on rewrite, send the failing plan back with the rejection reason. An agent that receives the flagged node type and the cost figure produces materially different second drafts than one told only "query too expensive."

The decision table

The decision rule

Use cost-first as the default gate, because it is free and it runs without touching data. Escalate to sample-first when any of three conditions holds: statistics freshness is unknown, the plan contains a scan on a relation in your large-relation set, or the estimate sits within a factor you calibrate of your budget.

Reject outright when the cost gate fails before the probe, because a plan that is already over budget on an optimistic estimate does not become safer by measuring it. The routing in Step 2 encodes exactly that order.

Where free compute changes the calculus

The economics of this loop are dominated by volume, not by any single query. If drafting is cheap you generate more candidates, and if gating is cheap you can afford to reject more of them, which is the direction you want the ratio to move. This is where free model access and a free server tier are relevant to the workflow rather than to the pitch: a free token allowance makes it practical to request several rewrite candidates per rejected draft, and a free server gives the gate harness a place to live without a paid runner.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

The operator describes a free token allowance and a free server option for the project. The allowance is quoted as roughly ten million tokens, but treat that as a time-sensitive commercial detail and confirm current limits on the project page before you build a budget model on it. Nothing in the harness above depends on which model drafts the SQL, which is deliberate — the gate should outlive the drafter.

Limitations and who should not use this

This approach assumes PostgreSQL-style EXPLAIN output with JSON formatting and a planner that reports total cost in comparable units. Warehouses that expose plan text without stable cost fields will need a different structural signal, and engines without statement_timeout need a connection-level cancellation path instead.

Skip it if your SQL changes already pass through mandatory human review with a semantic layer that constrains what can be generated, because the gate adds a second policy surface with no new coverage. Skip it if you cannot provide a non-production probe target, since probing production reintroduces the contention risk that made you nervous in the first place.

It is also a poor fit for sub-second interactive queries where a single EXPLAIN round trip is a meaningful fraction of the budget. In that case, plan once per query template rather than once per execution.

What to measure before you trust it

Run both gates in parallel for a week and log where they disagree. The disagreements are the interesting data: they show you which of your relations have unreliable statistics, and they tell you whether your COST_BUDGET was set by evidence or by guesswork.

If you want to try the loop without provisioning anything first, the free tier is a reasonable place to run the disagreement log — draft with the free models, host the harness on the free server, and keep the production cluster out of the experiment entirely.