Building an AI workflow that proves its SQL advice before trusting it

A language model can sound persuasive when it explains why a SQL query is slow. That is not the same as knowing whether the suggested index will be used, whether a rewrite returns the same rows, or whether PostgreSQL will actually run the query faster.

The project targeted a real developer workflow: inspect a slow query, read the schema and existing indexes, study `EXPLAIN ANALYZE`, form a hypothesis, test a rewrite or index, verify correctness, measure performance, and retry when the evidence says the idea did not work.

The baseline was intentionally fair rather than weak. It received the SQL, schema, indexes, execution plan and data-distribution context, then returned one structured recommendation. What it could not do was execute, measure, repair malformed output, or revise its answer after PostgreSQL disagreed.

The first step was designing a benchmark that would expose the difference between plausible advice and verified optimisation. The database modelled an e-commerce/order-management system with customers, products, orders, order items, payments, shipments, support tickets and audit logs.

The full benchmark used realistic data volume: 100,000 customers, 500,000 orders, 1,500,000 order items and 2,000,000 audit logs, with deterministic synthetic generation so results could be reproduced from a clean environment.

The 15 benchmark cases covered common PostgreSQL traps: functions on indexed columns, missing composite indexes, correlated subqueries, deep offset pagination, low-selectivity index traps, JSONB indexing, `NOT IN` semantics, and sort spills caused by constrained `work_mem`.

The final architecture kept the agentic loop deliberately small. One optimisation agent produced a structured recommendation, the system repaired malformed structured output once, then executed the candidate inside a controlled PostgreSQL benchmark environment.

A deterministic verifier judged the answer using database evidence: result equivalence, execution time, plan changes, index usage, and temp-file or disk-spill behaviour. PostgreSQL evidence decided whether a candidate was accepted, not the confidence of the model's explanation.

When a concrete candidate failed verification, the measured evidence was returned to the optimiser for one final retry. That loop caught cases where an index looked reasonable but PostgreSQL did not use it, then allowed the next candidate to remove the unnecessary schema change and keep the useful rewrite.

Safety was part of the design. The workflow allowed controlled `SELECT`, `EXPLAIN`, `EXPLAIN ANALYZE`, `CREATE INDEX`, `DROP INDEX` and `SET LOCAL` operations in the benchmark database, while excluding destructive operations from the optimisation path.

On the full PostgreSQL benchmark, the single-LLM baseline passed 7 of 15 cases. SQL Performance Doctor passed 10 of 15, increasing verified optimisation success from 46.7% to 66.7%.

A repeated medium-profile stability check gave a more conservative view of run-to-run behaviour: the baseline passed 22 of 45 case-runs, while the final workflow passed 26 of 45. That is a +8.9 percentage point improvement, reported without a statistical significance claim.

The project also produced useful negative evidence. A later experiment that challenged `no_change` conclusions recovered no failures and reduced the score, so it was removed from the final architecture rather than kept for complexity's sake.

The strongest outcome is not only the score. It is the engineering pattern: wrap AI judgement in deterministic verification, measure it against the system of record, and preserve the evidence needed to explain both successes and failures.