Cost Estimates or Timed Canaries: A Debate for Promoting Agent SQL

On a Tuesday release window, an analytics agent proposed a four-join reporting query against a 40 million row events table. The planner estimated a few thousand cost units because the most selective predicate still used last week's statistics. Staging accepted the plan, then the first canary scanned far more heap pages than any review comment had predicted. Promotion, not generation, became the failure mode: the model wrote plausible SQL that static checks could not refute. This article treats that incident as a decision problem rather than a prompt-engineering story. Two credible camps now argue about the last gate before agent SQL reaches a shared database. One camp trusts PostgreSQL cost estimates as a cheap, lock-free rejector. The other camp insists on timed canaries against representative data, because cost units are not latency and because skew defeats the planner. The sections below compare both positions with a small, labeled harness you can run. The harness is a proposal, not a production benchmark, and it records estimates and wall time without claiming a universal SLO. Reader value sits in the decision rule; any named tool is optional and removable. Why promotion is the bottleneck that tests miss Agent SQL usually fails after it already looks reviewable in a diff. The join graph compiles, the column names exist, and a unit fixture with ten rows returns the expected shape. Those tests do not encode correlation, TOAST size, or the histogram that autovacuum has not updated since the last backfill. Evaluation suites lose bite when models learn the shape of the suite rather than the shape of production data. Query promotion has the same failure mode, only with page cache and random_page_cost instead of exam items. A gate that always passes is not a gate, and a gate that never runs the statement cannot see I/O. The practical question is therefore narrow. Which signal is allowed to veto a parsed, linted candidate, and when is that signal too expensive to collect on every agent attempt? Position A: Planner cost estimates as a promotion gate Cost-based gates start from a simple operational fact: EXPLAIN without ANALYZE never executes the query. That property matters when an agent might emit a nested loop that only explodes after the first million rows. A reviewer can reject a candidate when total cost, estimated rows, or a sequential scan on a large relation crosses a numeric budget. Advocates also note that cost estimates stay comparable when statistics are frozen for the test. Teams can restore a catalog snapshot, run EXPLAIN (FORMAT JSON) , and compare total cost against a stored ceiling. The comparison is deterministic, fast, and free of write locks, which makes it attractive in CI for high-frequency agents. PostgreSQL documents that the planner uses relation statistics to compute startup and total cost in abstract units, not milliseconds. The current EXPLAIN reference is the primary source for that behavior, including ANALYZE as the switch that actually runs the statement (PostgreSQL EXPLAIN). When those statistics lag, the same mechanism will underprice a scan, which is the opening Position B uses. Even so, Position A remains rational for narrow OLTP lookups that must be screened hundreds of times per hour. A cost cap is a filter, not a proof of safety, and cheap filters belong at the earliest layer. Throwing away a bad plan before allocating a rehearsal host is an engineering choice, not a philosophical one. Position B: Timed canaries on a rehearsal server Canary advocates treat cost units as a different quantity from the SLO the pager actually pages on. Wall time, shared buffer hits, and rows actually returned can diverge from the estimate when predicates correlate or when a TOAST table dominates I/O. A rehearsal run with a tight statement_timeout converts that divergence into a binary promote-or-reject signal that CI can store. The second argument is statistical freshness rather than philosophy. Agent SQL often encodes filters the warehouse added this week, so last week's histogram cannot price the plan honestly. Measuring a read-only canary against a subset that preserves skew is then the only test that can fail for the right reason. Realistic API performance work makes the same claim at another layer: the test has to look like production traffic, not like a fixture. Canaries are not free in time or in data hygiene. They need a dataset that is not production, a timeout that is not infinite, and isolation from writers who serve customers. They also need a host you are willing to burn if the agent invents a pathological join, which is the only reason a scratch server belongs in this workflow. Disclosure: This article was prepared as part of MonkeyCode's product outreach. When a team already has a staging replica, that replica is the correct canary target and no extra host is required. When it does not, MonkeyCode's free model access can draft candidate SQL, and the free server option can hold a throwaway rehearsal database for the harness below. Neither option replaces statistics management, anonymized subsets, or the decision rule, and this article does not claim quotas, hardware profiles, or durability. Evidence both camps already accept Both sides agree that agent SQL should not meet production on the first execution of a new text. Both sides also agree that parser-level checks and timeout budgets answer different questions than promotion, so this debate does not reopen those gates. The remaining dispute is which signal may veto a candidate that already parsed and already sat under a statement timeout. Three shared facts constrain any honest comparison of the two camps. First, EXPLAIN without ANALYZE is cheap relative to execution, while EXPLAIN ANALYZE runs the statement and therefore needs a rehearsal role. Second, statement_timeout aborts a canary but does not repair a bad join order for the next agent attempt. Third, frozen statistics make cost comparisons reproducible, and thawed statistics make them honest about today's data. A useful artifact has to record both signals instead of declaring a winner in prose alone. A two-stage promotion harness The following workflow is a proposal. It does not execute against a warehouse until you point the connection string at a scratch database you own. Step 1: Freeze the question, not the model output Store the candidate SQL, the intended read-only role, and the SLO in a small YAML file. Do not let the agent rewrite the SLO after it sees a failing canary, because that loop trains the model to game the gate. Keep the YAML in review so humans change budgets on purpose. # proposal: promo_case.yml - not a live production contract name: events_daily_rollups slo_ms: 1500 max_explain_cost: 250000 statement_timeout_ms: 4000 require_canary_if: estimated_rows_gt: 100000 seq_scan_relations: - events - event_payloads Step 2: Capture a lock-free plan Run EXPLAIN in JSON mode under a role that cannot write. Persist the total cost, planned rows, and node types beside the candidate. This is Position A as a command rather than a manifesto, and it should fail closed if the role is missing. -- proposal: capture_plan.sql SET default_transaction_read_only = on; EXPLAIN (FORMAT JSON, VERBOSE, COSTS) SELECT date_trunc('day', e.created_at) AS day, e.event_type, count(*) AS n FROM events e JOIN accounts a ON a.id = e.account_id WHERE e.created_at >= now() - interval '7 days' AND a.plan = 'enterprise' GROUP BY 1, 2; Step 3: Decide whether a canary is mandatory Apply the YAML thresholds before you spend rehearsal time. If estimated rows stay tiny and no large sequential scan appears, Position A may be sufficient for that candidate. If the plan touches a fact table or the cost sits near the cap, Position B becomes mandatory rather than optional. Step 4: Time a bounded canary On a rehearsal host only, set statement_timeout below human patience and above the published SLO. Record wall time, EXPLAIN ANALYZE buffer totals, and whether the timeout fired. Never point this step at a primary that serves customers, even if the SQL looks like a SELECT . # proposal: promo_harness.py - unexecuted example, scratch DB only import json, os, time import psycopg SQL_PATH = os.environ["CANDIDATE_SQL"] DSN = os.environ["SCRATCH_DSN"] SLO_MS = int(os.environ.get("SLO_MS", "1500")) TIMEOUT_MS = int(os.environ.get("STATEMENT_TIMEOUT_MS", "4000")) MAX_COST = float(os.environ.get("MAX_EXPLAIN_COST", "250000")) ROW_TRIGGER = float(os.environ.get("EST_ROWS_TRIGGER", "100000")) def load_sql(): return open(SQL_PATH, encoding="utf-8").read() def dsn_looks_unsafe(dsn: str) -> bool: lowered = dsn.lower() return any(token in lowered for token in ("prod", "primary", "master")) def explain_only(cur, sql): cur.execute("SET default_transaction_read_only = on") cur.execute("EXPLAIN (FORMAT JSON, COSTS) " + sql) plan = cur.fetchone()[0] if isinstance(plan, str): plan = json.loads(plan) node = plan[0]["Plan"] return float(node["Total Cost"]), float(node.get("Plan Rows", 0)), plan def run_canary(cur, sql): cur.execute("SET default_transaction_read_only = on") cur.execute(f"SET statement_timeout = {TIMEOUT_MS}") started = time.perf_counter() cur.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql) elapsed_ms = (time.perf_counter() - started) * 1000.0 payload = cur.fetchone()[0] if isinstance(payload, str): payload = json.loads(payload) return elapsed_ms, payload[0]["Plan"] def main(): if dsn_looks_unsafe(DSN): raise SystemExit("refusing a DSN that looks like production") sql = load_sql() with psycopg.connect(DSN) as conn: conn.autocommit = True with conn.cursor() as cur: cost, est_rows, _ = explain_only(cur, sql) stage_a = "reject" if cost > MAX_COST else "pass" need_canary = est_rows >= ROW_TRIGGER or cost > MAX_COST * 0.4 result = { "stage_a_cost": cost, "stage_a_est_rows": est_rows, "stage_a": stage_a, "need_canary": bool(need_canary and stage_a == "pass"), } if result["need_canary"]: try: elapsed_ms, plan = run_