I let a model suggest Postgres indexes, then made the database mark its work

Ask any model for an index and you will get one. That is the problem. It has no way to tell you that the query you pasted is already as fast as it is going to get, so it gives you a CREATE INDEX line, and the line looks reasonable, and it lands in a pull request where nobody can evaluate it either.

I wanted to know how many of those suggestions are actually good. Not plausible, good. So I built something small that takes the model's answer and makes Postgres mark it.

The check that everybody leaves out

The obvious way to grade an index is to time the query before and after. Here is why that is not enough, from an actual run:

[ignored by planner] CREATE INDEX ... ON events (user_id) WHERE created_at > ...
                     14.2 ms -> 12.19 ms  (1.17x, index used: False)

That index made the query 17% faster. It is also completely useless. The planner never touched it, and the improvement is noise on a 14 ms query. If you graded by the clock you would keep it, and you would carry the write cost of that index forever.

So every suggestion gets asked two questions, not one:

1. Did the query get meaningfully faster?
2. Did the planner actually choose the new index?

You get the second answer for free. It is in the plan. Walk the EXPLAIN output, collect every Index Name node, and check whether the thing you just built is in there. An index that exists but is never used is not a slower index, it is a no.

Making it safe to be wrong

The mechanism is one Postgres property: you can create an index inside a transaction and roll it back.

cur.execute('BEGIN')
cur.execute(ddl)            # CREATE INDEX ...
cur.execute('ANALYZE')
after = measure(cur, sql)   # EXPLAIN (ANALYZE, BUFFERS)
cur.execute('ROLLBACK')     # the index never existed

That single fact is what makes the whole idea work. The model can propose nonsense, and the database will evaluate it honestly and then forget it. The tool snapshots pg_indexes before and after the entire run and refuses to exit quietly if they differ.

The one casualty is CONCURRENTLY, which cannot run inside a transaction. The tool strips it, because the rollback is the safety property and I would rather lose the keyword than the guarantee. When you apply a verified index for real, you put it back.

Two small decisions mattered more than I thought they would. I take the fastest of five runs instead of the mean, because the fastest run is the warm-cache floor and it barely moves between runs, whereas the mean wanders enough to swamp a real 30% gain. And ANALYZE has to run after the index is built, otherwise the planner is deciding whether to use it while holding stale statistics, and it will sometimes decline for the wrong reason.

The setup

Everything ran on Postgres 18 on this laptop. Three tables, a million and a half rows, with the bulk of it in an events table of 1.2M rows and 104 MB, big enough that a sequential scan is something you can feel.

The eight queries are the boring ones you actually write. Look up a user by email. Twenty most recent events for one user. Pending orders since a date. Revenue by country. Nothing clever, because clever queries are not where teams lose time.

Each one goes over with the schema, the indexes that already exist, and its own EXPLAIN (ANALYZE, BUFFERS) output. The model gets to propose three indexes. Then the referee takes over.

What came back

Before the caveats, the wins were real. These are the six queries that improved, at the best verified speedup any model achieved:

None of those are surprising to anyone who has tuned Postgres by hand, and that
is the point. The models are good at the ordinary case, which is most cases.

Three things jump out of that table.

The keep rate barely moves. Every model that answered landed between 58% and 67% verified, across an 18x price range. The expensive model is not better at this. It is the same job: read a plan, notice a filter, propose a matching index.

The same six queries improved, every time. Seven of the nine answered on all eight queries, and all seven improved exactly the same six. Not five for one and seven for another. Which queries are index problems is a property of the queries, not of the model you ask.

The output token column is where the money went. Look at mistral-3-14B against nemotron-3-ultra-550b. Mistral produced 16 verified indexes using 482 output tokens and seven seconds of model time, for $0.0013. Nemotron produced seven, using 14,196 output tokens and 134 seconds, for $0.0297. That is 23 times the cost for less than half the result, and the difference is not intelligence, it is that one of them thinks out loud and bills you for it.

Nemotron also returned an empty answer on three of the eight queries, and that is the same problem wearing a different hat. It was not declining. It was filling its entire 3000 token output budget with reasoning and running out of room before writing a single CREATE INDEX. I checked, on a shorter prompt it answers fine, using 838 tokens with 3,192 characters of reasoning behind them. Feed it the schema and a plan as well and it never reaches the answer. Those three blank responses were billed in full.

The router row is worth a look too. DigitalOcean exposes routing endpoints that pick a model for you, and I included the software-engineering one expecting it to be the sensible default. It came back blank on three queries for the same reason, took 245 seconds across the run, and cost more than picking Mistral myself and getting nearly twice the verified indexes. Letting something else choose the model is only a saving if the thing choosing knows what you are about to ask it for.

The two queries nothing could fix

I did not expect this one, and it ended up being the best argument for the whole exercise.

Two of the eight were never improved by anything. Between them the models proposed 41 indexes for those two queries. Not one survived. Some were ignored by the planner, the rest were not faster by enough to count.

Both aggregate across most of a table and then join. Revenue by country walks 300k orders; the other ranks team-plan customers by what they spent. An index helps when it lets you skip rows, and these queries do not skip rows, they need almost all of them. Reading the table start to finish is not the planner failing. It is the planner being right.

Not one model said so. They cannot. "No index will help this query" is the correct answer and it is not the kind of answer a model asked for indexes will produce. The database said it instead, by refusing every suggestion.

What makes it worse is that the models agreed with each other. Asked about the revenue query, three of them independently proposed almost the identical pair: a covering index on orders (status, placed_at) carrying the columns being summed, and a covering index on users (id) carrying country. Reasonable-looking, near-identical, and all of them rejected.

If you had asked two models and taken the consensus, you would have shipped both. Agreement between models is not verification. It mostly tells you they were trained on the same advice.

That is the difference between a suggestion and a recommendation, and it is not a thing you can prompt your way out of.

Two things a reader asked that I had to go and measure

Baptiste asked in the comments whether a cached plan could sabotage this. If the
query runs as a prepared statement, Postgres may execute a generic plan built
without knowing the parameter, and a generic plan could ignore the index I just
built, marking a genuinely useful index as unused.

It does not, and the reason is that the cached plan never survives to be reused.
Creating an index invalidates the plans that depend on that table, so the
statement is replanned. I prepared the query, executed it seven times to get past
the generic-plan threshold, then built the index inside the transaction:

Forced generic behaves like the others.

There is a real version of the worry though, and it points the opposite way. My
harness never prepares anything. It runs EXPLAIN on a literal string, so it
always grades the custom plan for one particular value. The risk is not a false
"unused". It is a true "used" for user_id = 12345 that does not hold for
whatever value is actually hot in production. Grade with a representative value,
not a tidy one.

One thing I would not have guessed: point this at a prepared statement and the
rollback destroys it. Once the cached plan has been rebuilt against the
uncommitted index, ROLLBACK takes the prepared statement with it, and the next
EXECUTE gets prepared statement "p" does not exist.

The second question was whether ANALYZE inside the transaction gives fresh
enough statistics on a big table. For a plain b-tree it is doing nothing at all:
the index build already sets reltuples and relpages, the column statistics
already exist, and the verdict is identical with or without it. It earns its
place on expression indexes, where the statistics do not exist until it runs. An
index on lower(kind) across 1.2M rows:

Until it runs there are zero rows in pg_statistic for that expression, so the
planner falls back to a default guess that is 33 times off. It did not flip this
plan. An error that size flips join orders in bigger queries, so it stays.

What I got wrong on the way

Two of them, and both were mine rather than the model's.

The first cost me an hour. My cheapest model kept returning nothing at all, and I assumed the endpoint was broken. It was not. Several of the cheap models are reasoning models, and they spend their output budget thinking before they answer. My cap of 1200 output tokens was being consumed entirely by reasoning, so the content came back empty, and the reasoning tokens bill at the output rate. I paid full price for a blank response. Raising the cap to 3000 fixed it, and the model then used about 540 tokens, of which roughly 340 were thinking.

The second was worse. I ran the tool's own test suite against the same database the experiment was running on, and the test fixture opens with DROP TABLE. Postgres deadlocked, refused both, and nothing was lost. I got a clean error instead of a ruined dataset because two things happened to be holding locks at the same moment. It should have been a separate database and now it is.

Run it yourself

The tool is oceanforge/pg-index-referee on GitHub, MIT licensed.

git clone https://github.com/oceanforge/pg-index-referee
cd pg-index-referee && pip install -e .

export DATABASE_URL="postgres://user:pass@host:5432/dbname"
export DO_INFERENCE_KEY="..."

pg-index-referee --queries examples/queries.sql --apply

If you would rather reproduce my numbers than generate your own, examples/setup.sql builds the identical dataset, seeded deterministically. And --apply only prints what survived. It will not create anything, because adding an index to a live table is a decision you should make with CONCURRENTLY in hand, and the referee had to strip that to get its rollback.

Point it at a replica or a restored dump where you can. Building an index in a rolled-back transaction is still real work and takes real locks, which the deadlock above taught me in the most direct way available.

Any OpenAI-compatible endpoint works, so DO_INFERENCE_BASE moves it wherever you like. I used DigitalOcean's serverless inference because one key reaches the whole price range, which is the only reason the comparison above was a couple of hours rather than a week of signups.

What I would take from this

The suggestion is the cheap part. Nine models, eight queries, 151 proposed indexes, every one of them built and measured and thrown away: five and a half cents, all in. The expensive part has always been deciding which suggestions to trust, and that is the part people were doing by hand, in review, from a diff, without a plan in front of them.

What changed my mind is that the judgement is automatable too, and it is automatable by the thing that was going to have to live with the decision anyway. Postgres will build the index, tell you whether it would bother using it, and forget the whole thing, in about a second. It will also tell you the one answer no model asked for indexes will ever volunteer, which is that your query does not have an index problem and you should go and look somewhere else.