Diagnoses Postgres incidents from the stats views. No model decides
what's wrong — deterministic rules and EXPLAIN (GENERIC_PLAN) do.
That last number is the one that matters. A tool that invents problems on a working database is worse than one that misses them — nobody reads its alerts by week three.
Ask a model "what's wrong with this database?" and it will tell you something, because it is trained to be helpful rather than to say "nothing." Detection has to be deterministic; the model belongs above that layer, explaining and fixing, not deciding.
The scenario set is built to punish the alternative. On four of the five faults the obvious answer is wrong:
| fault | pattern-matching says | actually correct |
|---|---|---|
| missing_index | add an index | ✓ add an index |
| plan_regression | add an index | ✗ run ANALYZE — the schema is fine |
| bloat | the table is just large | ✗ dead tuples, autovacuum is off |
| lock_contention | kill the slow queries | ✗ they're victims; one holder is at fault |
| n_plus_1 | nothing is slow, it's healthy | ✗ 1000 calls at 0.04 ms each |
| healthy | find something anyway | ✗ the answer is no finding |
Two naive baselines, scored against the same six cases rather than asserted
— python -m evals.baselines reproduces this:
| approach | score |
|---|---|
| always name something — for a database, usually an index | 1 / 6 |
| slowest statement over 10 ms and a large table → missing index | 2 / 6 |
| deterministic detectors | 6 / 6 |
The second baseline is right twice: it names the one genuine index problem, and it stays quiet on the healthy database. On the other four it returns nothing at all — none of those faults presents as a slow query. Stale statistics, bloat, a lock holder and an N+1 loop are invisible to any heuristic that ranks by duration, which is what most dashboards do.
Both of these are a slow query against a multi-million-row table. They need opposite fixes, and the agent separates them from the stats alone. Real output, unedited:
order_items has 2,500,000 rows and the planner has no index for this predicate, so it scans the whole table on every call. Creating product_id as a hypothetical index and re-planning drops the estimated cost from 32,591 to 645 (51×), and the planner chooses it.
300,000 rows in orders have changed since the last ANALYZE (23% of the table). autovacuum is disabled on this table, so nothing will refresh them on its own.
An index on the second one would cost write throughput and fix nothing.
missing_index stays silent there because it requires
slow and a confirmed sequential scan on a large
table — not slowness alone.
Called 1,000 times at 0.044 ms per call. Each call is fast; the cost is the round trips. This is an application loop issuing one query per parent row.
The slowest query in that database is 0.044 ms. Every dashboard sorted by duration shows a clean bill of health.
pg_stat_statements normalizes queries to
WHERE product_id = $1, so a plain EXPLAIN can't run
them. GENERIC_PLAN (PG16+) plans them anyway — so the agent reads
the planner's real choice instead of guessing from timings, and never replays
anything against your data.
Every finding carries the evidence it fired on, so it can be checked rather than trusted. The connection is read-only with a statement timeout, so it's safe to point at a production replica.
Anything can print “add an index on product_id.” Before it says
that, this builds the candidate as a hypothetical index —
no disk write, no lock, no ACCESS EXCLUSIVE on a 2.5M row table
— re-plans the query, and reads what the planner actually decides.
| candidate | estimated cost | verdict |
|---|---|---|
| order_items(product_id) | 32,409 → 205 158× | recommend |
| users(country) | 4,772 → 3,303 1.4× | decline |
| events(event_type) | 35,602 → 27,179 1.3× | decline |
The useful surprise: “does the planner use it” is a weak test. The planner adopted all three — it will take almost any index that shaves a little cost. But an index is paid for on every INSERT and UPDATE forever, so the margin decides, not adoption. The bar is 3×.
So a suggestion has four possible outcomes rather than one: verified (with the delta), marginal (adopted, not worth building), refused (the planner won't use it, so an index is the wrong answer), and unverified — labelled as such rather than quietly implied.
Most Postgres instances don't have pg_stat_statements installed
and most logins aren't superuser. Every detector declares what it needs and
is skipped with a reason rather than silently returning
nothing — because “no findings” and “couldn't look” are different answers
and a tool that conflates them is worse than useless.
Run that first against anything unfamiliar and you know which detectors are live before you trust a clean report. The three catalog-only detectors — stale statistics, bloat, lock contention — work on any Postgres with no extensions at all.
Five failure modes, not a complete taxonomy — real Postgres also fails through connection exhaustion, transaction wraparound, replication lag and bad migrations. Detection is read-only and advisory: it proposes fixes and never applies them. The sandbox is a synthetic e-commerce schema on a single Postgres 16 instance, so absolute timings reflect the machine that produced them; the ratios are the signal, not the milliseconds.
This page is a static snapshot — Hugging Face reserves Docker Spaces for PRO accounts. The interactive version and the full scenario sandbox run locally in two commands: