Old school in an age of new school. Except the old school isn't nostalgia. It's the part that stopped failing loudly enough to teach anyone.
Everyone who understands database indexing learned it the same way: an application fell over on a dataset you could feel, early, on your own machine, attached to the query that caused it. Correctness and scalability failed together. They don't any more. Generated code clears the bar that used to trigger the lesson, because it returns the right rows.
So I measured whether agents still get indexing right. They mostly do — 8 of 10 sessions declared composite indexes with correct column order, for access patterns nobody described to them.
Then I removed the indexes from the starting schema and ran the identical experiment. Same model, same four features, same frozen expectations. It added the right index 24 times out of 24 when the schema already had indexes, and 0 times out of 24 when it didn't.
The model isn't ignorant. It's conformist. It read the room and matched it.
That absolute zero belongs to one harness, and I'd rather say so here than let it travel unqualified. Run through a smaller one, three tools scored 4, 12 and 11 out of 12 on the same stripped schema. The mechanism replicates; the number doesn't.
Then I put one sentence into a CLAUDE.md file — declare the index that serves your query, or say why none is needed — and re-ran that smaller harness. The tool that had scored 4 of 12 came back 11 of 12.
So the standard is the control, and the standard is writable.
Start with what the database does without one.
Ask for a store's inventory on a given date and, with no index, the engine reads every row in the table and checks each against your filter. Ninety-four thousand rows, one at a time, to hand back a few hundred. It gets the right answer. It just does the maximum possible work to get there.
An index is a second structure the database maintains beside the table: the columns you search by, kept in sorted order, each entry pointing back at the row it came from. Sorted means the engine can jump — halve the remaining range, halve it again — instead of walking. That is the index at the back of a book. You don't read the book to find every mention of micro-partitions; you look it up in an ordered list and it tells you the pages.
Two consequences follow, and they're the whole of it.
The first is that an index serves the question you built it for, and not others. A composite index on (store_id, snapshot_date) is sorted by store first, then by date within each store. That is a phone book ordered by surname then first name: excellent for "all the Smiths", excellent for "the Smiths called John", useless for "everyone called John". Column order isn't decoration. It decides which questions get the jump and which get the walk.
The second is that the book's index doesn't write itself. Every insert, update and delete has to maintain every index on that table, and each one occupies real storage. Reads get faster; writes get slower and the database gets bigger. Nobody sends you a bill for it, which is exactly why it gets skipped.
So: a claim about how the data will be read, paid for on every write and in storage.
Not "makes queries fast." The definition has to carry the cost, because the cost is what makes indexing a judgment rather than a checklist item.
The reference schema in this experiment has 17 tables and 4 indexes. Three are composite, and the column order encodes the access pattern: (store_id, snapshot_date) because a store's inventory is read as a date range, (user_id, status) because assignments are filtered to active. Fifteen other foreign key columns are deliberately left unindexed.
Four, not nineteen. The restraint is the skill.
That schema isn't invented for this piece. It comes from a sibling project — an application-level access-control model I built on Sigma's free tier, over their sample retail dataset. I hand-authored those four indexes, and I knew the access patterns because I had just written the queries. The nine access-control tables are out of scope for these trials, which is why the comparison below is against two indexes rather than four.
That project is also where this one started. I've built full-stack applications for a long time and have been working in Sigma recently, and every layer between you and the database is a layer where the mechanism stops being visible. Agents are the newest one. The question underneath this piece is the one I'd ask of any abstraction: what is it doing on my behalf, and would I notice if it stopped?
Three engines, and the index decision moves further from you at every step.
SQLite, on 94,500 rows, plans it like this:
SEARCH inventory_daily USING INDEX idx_inventory_store (store_id=? AND snapshot_date>?)You declared the index; the planner names it back to you. That is the world the fundamental was learned in.
DuckDB, on identical data, produces a physical plan that is byte-identical before and after creating the equivalent index. You can declare it, and for an analytical scan the optimizer doesn't want it — it leans on automatic per-partition metadata instead. Both plans are committed in the repository and re-run in under a minute.
Only the narrow claim: DuckDB does support CREATE INDEX, builds ART indexes, and uses them for point lookups and constraint enforcement. "DuckDB has no indexes" is false. What's demonstrated is that for this scan, on this data, the index changed nothing.
Snowflake goes a step further. On a standard table you cannot declare a secondary index at all — CREATE INDEX exists, but the reference page is scoped to hybrid tables, a separate row-oriented type. And as of Snowflake Optima, the engine may build one for you: Optima Indexing watches your workload and creates what the documentation calls hidden indexes, "not user-declarable," maintained "on a best-effort basis, without requiring user intervention," with no configuration, on generation 2 and Adaptive warehouses. You find out by reading a Query Profile.
Declared and used. Declared and ignored. Not declarable, and possibly created on your behalf without your knowing.
"Add an index" is engine-specific advice wearing the costume of a universal rule.
That write cost is not a constant. The engine's profile decides whether the trade is worth making at all, which is why "index everything" fails in some engines far harder than others.
A columnar store built on immutable partitions makes a single-row change mean rewriting a partition. That cost doesn't go away with a bigger machine.
State this as architecture, never as a product verdict. No vendor documentation I could find gives a concrete cost figure for single-row DML on standard tables, so this piece states no number. And Snowflake ships hybrid tables and Snowpipe Streaming precisely for row-oriented write-heavy work, so "Snowflake is bad at writes" would be both a product claim and a dated one.
The feedback loop is gone, not the fundamental.
An N+1 doesn't announce itself at 200 rows in development. It announces itself at 200,000 rows in production, detached from the moment anyone could have learned from it.
This isn't a claim that agents are careless. It's that two things which used to fail together now don't, and everything that lived in that gap is unenforced.
The setup: describe a retail inventory domain in business terms, ask for a schema and four queries, and never use the words index, performance, fast, slow, or scale. Then count what came back.
The control first, because without it the count means nothing. A schema declaring relations and zero indexes, compiled by the ORM itself, emits zero CREATE INDEX statements — verified on all three major versions the trials installed. So every index in a trial is attributable to the session, not the framework.
Ten trials, Claude Opus 5: 8 of 10 declared explicit indexes on the target tables. Two declared none, and their transcripts never mention indexing at all.
Three more tools, ten trials each. Claude Opus 4.8 averaged 10.4 explicit indexes without the production hint. Codex averaged 6.2, but bimodally — its five trials were 3, 3, 7, 9, 9, so the mean describes none of them. Gemini 3.6-flash declared zero explicit indexes in five of five trials without the hint, and 9 to 11 in five of five with it. One sentence about production scale flipped it completely.
Gemini 3.6-flash is a fast tier sitting beside two frontier tiers. That gap is a tier difference, not evidence that one vendor is worse than another, and it matters because cost pressure pushes production traffic toward exactly that tier.
The first version of this section reported that every session declared composite indexes, averaging 14.0 rising to 18.2.
Both numbers were wrong. The count included CREATE UNIQUE INDEX rows the ORM emits mechanically from uniqueness constraints, and indexes on tables outside the experiment. Two of the ten sessions had declared nothing at all — their "composite index" was the uniqueness constraint my own prompt had dictated by describing the table's grain.
Corrected, in-scope and explicit only: 4.0 and 5.2, not 14.0 and 18.2. The effect was overstated about threefold.
It was internally consistent, reproducible, and backed by committed DDL. It was also wrong, and it was caught only because something independent went and checked it.
That is this article's own thesis happening inside its own evidence.
The chain is Product to ProductLine to ProductFamily to ProductType, plus Product to Brand. "Show my store's inventory with product and brand name" is the most natural request anyone would make of this data, and the naive implementation issues one query per row — against 31,500 rows for a store manager.
The honest result: scanning ten independently generated codebases plus one promoted application, zero N+1 patterns in any page-serving query code. Nested selects in a single query, groupBy with an in-memory join, parallel fetches for independent lookups. Three independent reads agree.
The scanner did find 20 candidates: 13 real, 7 false, a 35% false-positive rate. Every real one is a create call inside a loop in a seed script — one-time setup, not per-request code. Calling that a user-facing N+1 would be the same overclaim as the index count.
Credit where it's due. On this task, these tools wrote query code that avoids the pattern.
Creation is the easy case, and it's the case benchmarks measure. Real work is accumulation: features arrive across sessions and nobody re-derives the access patterns.
So I ran a second experiment. Start from a generated project that already had 10 explicit indexes. Add four features, one at a time, each introducing an access pattern the existing indexes don't serve — including one that inverts the fact table's leading column, so the existing composite is useless for it. Two arms: one continuous session, and a fresh session per feature to simulate a handoff.
The expected index for each feature was written down and committed before any session ran. Without that, any index the model happens to produce can be rationalized afterwards.
Result: 24 of 24 increments got a schema-appropriate index — 21 of them the exact tuple in the right column order, 3 with the right columns in a different shape. Both arms alike. Lost context was not the mechanism.
That falsified my hypothesis. It also didn't explain why practitioners keep finding missing indexes in real codebases.
So I ran it again with one variable changed: the same baseline project with only the index declarations deleted. Same generator, same code style, same queries, same model, same features, same frozen expectations. Every uniqueness and primary key constraint left intact, because those are correctness, not performance.
0 of 24.
Not one index added, in either arm, across all three trials. And the final schemas contain zero index declarations after four features that each demanded one.
One session explained itself:
"none of the existing pages add DB indexes... so I stayed consistent and didn't add one... an @@index([adjustedAt]) on InventoryAdjustment would speed the month-range scan — say the word and I'll add it."
It named the correct index. It knew why the query needed one. It withheld it to match the surrounding convention.
That isn't hallucination, incompetence, or misalignment. It's deference. And reviewing the output wouldn't catch it, because the output is fine — it's the absence that's wrong, and absences don't show up in a diff.
Two checks on that number, because 0 of 24 deserves suspicion.
First, contamination. This repository contains a skill that scans for exactly these problems, so I re-ran the cells from a temporary directory outside any repository, where it is invisible. No degradation: 7 of 8 there against 6 of 8 inside. Worth keeping the narrow lesson — a skill sitting in a repository is not ambient guidance. It was never invoked.
Second, tiers. Through a smaller harness — one arm, three trials — the absolute zero does not replicate. Claude Opus 4.8 scored 4 of 12, Gemini 3.6-flash 12 of 12, Codex 11 of 12. So "0 of 24" is a fact about one harness and one condition, not a law, and I'd rather say so than let a clean number travel further than it earned. What did replicate is the mechanism: transcripts naming the right index and declining it, citing existing style.
Yes, and this is the part I'd act on tomorrow.
Same stripped baseline, same harness, same model, same frozen expectations as the 4-of-12 cell above. One file added, containing one sentence:
When you add or change a query, declare the database index that serves its access pattern, or state explicitly why no new index is needed.
11 of 12.
Nobody ignored it. Eleven took the first branch and declared the index. One took the second and argued its way out, well enough that I had to think about it:
"The existing @@unique([snapshotDate, storeId, productId]) is backed by an index whose leading column snapshotDate serves the filter and whose second column storeId serves the grouping... isStockout is just a residual predicate on rows the index has already narrowed. So no new index is needed."
I graded that a miss anyway, because the expectation was frozen before the run, and moving a rubric after seeing data is how you get the 14.0 that turned out to be 4.0.
The conformity refusal disappeared entirely. Not one session declined on the grounds that the codebase had no indexes.
This doesn't overturn the earlier finding — it explains it. The model was never ignorant. It matched whatever signal was most salient, and with nothing else in the room that was the surrounding schema. Give it one line and the line wins. Both behaviors are the same behavior.
Limits matter here more than anywhere: three trials, one model, and the rule names the very thing being measured. This shows an explicit instruction is followed, not that the model has judgment about indexing.
The classical version assumes a query set that's stable, reviewed, and changes at the speed of a release. You enumerate the access patterns and index for them.
Agent-generated code breaks that from both directions. New access patterns appear faster than anyone re-derives the index set, and agents also add indexes on their own initiative, which is write amplification nobody chose.
But the deeper problem is the one the experiment found. The index set doesn't drift randomly. It stays exactly where you left it, because the agent is reading it as the specification.
It isn't only the code that arrives unindexed. Most of your schema was never written by a person either.
Start with the migrations directory, because that one is already in this experiment. Every trial's DDL was emitted by the ORM, not typed by anyone, and the control run pins down exactly what it does on its own: from a schema declaring relations and no indexes, zero CREATE INDEX. But from a uniqueness constraint it emits CREATE UNIQUE INDEX mechanically, every time. That is generated schema making an access-path decision — a real index, with real write cost — and nobody reviewed it as one. It is also what fooled me into reporting 14.0 when the answer was 4.0.
Then look at the tables you didn't add at all. Your auth library's session and account tables. Your job queue. Your audit log. Each arrived with whatever indexes its author chose, tuned for a workload that isn't yours, and you inherited both the indexes and their absences without making a decision.
But sort those by how the generator works, because I had the two halves backwards.
A deterministic generator emits schema from a specification you can read — the ORM from the schema you declared, a governed BI model from a definition somebody modeled on purpose. The queries that will hit those tables come from that same declaration, so the access patterns are derivable, and so is the index set. That is the tractable case, and arguably more tractable than hand-written code, where the access patterns live in whatever a hundred developers happened to write.
An agent is the other kind. Nothing sits upstream of the artifact. The access pattern exists only in the query it improvised this session.
So the governed side is where this is solvable, and mostly unsolved. Sigma's materialization guide, to pick the one I've built on, lists "Materialized tables can be indexed or tuned for specific query patterns" among the advantages, in the passive voice, naming nobody. That's a gap in the guidance rather than a flaw in the architecture, and worth closing precisely because the architecture makes it closable: if the tool knows the query it generates, the index is derivable rather than guessed.
Hold that beside Optima and the ends meet. One platform derives your index and doesn't tell you. The other could derive it and leaves it unassigned. The failure isn't automation — automation with a readable specification is the good case. It's automation whose output nobody can trace back to a decision, which is the agent's problem arriving through a dependency instead of a pull request.
This project's own measurement is the first example. Gathering these numbers took about thirty minutes per run because trials execute one at a time, and the obvious move was to run five at once and finish in six. That would have been faster and worse: concurrent sessions can be throttled or served differently, so a degraded result correlating with position in the pool is a bias hidden inside numbers that still look fine.
Trading a visible cost for an invisible defect is the same move as buying hardware instead of making an access-pattern decision.
The general case: an index changes the shape of the work, while hardware buys a constant factor that next year's growth eats. And an N+1 doesn't respond to hardware at all — it's round trips, not compute. A faster server runs every one of the queries, slightly faster.
No dollar figures here, and no speedup multiples. Nothing was benchmarked. There are real cases where more hardware is the right answer, including write-heavy tables where index maintenance costs more than it saves. The claim is about hardware bought instead of a decision.
Not "be more careful." You're rebuilding the feedback loop the tooling removed.
Two mechanisms, and the experiments above rank them. The cheaper one is the standing instruction: one sentence in the file your agent already reads, moving 4 of 12 to 11 of 12. The more durable one is a scan you cannot forget to run — a skill wired to fire at session start and again after context compaction. Both hooks were fired and observed, with output captured. Compaction is the load-bearing one: after a compaction the specific reasoning — "we decided to prefetch here" — is exactly what's gone.
Report the scanner's limits alongside its findings: a 35% false-positive rate, all from one misclassification, plus a disclosed list of what it structurally cannot see. "The scanner found nothing" and "there is nothing to find" are different claims, and this piece only makes the first.
One schema, one domain, one ORM. Five trials per cell at creation, three per arm at expansion — raw counts, never percentages, because "2 of 3" isn't 67% of anything.
The tools differ in scaffolding, permissions and logging, so this compares these tools as invoked here, not the underlying models in isolation. Codex's model identifier could not be independently verified: its account entitlement rejects explicit model pins and the session log records only an internal slug, so its column reflects a CLI default that self-reports as GPT-5.
The trials ran on a subscription where usage isn't metered per token. The measured cost of ten Claude trials was $9.75 in API-equivalent pricing — not a charge here, but real to anyone re-running this on metered billing.
And one I found late, which belongs here rather than in a footnote. I disclosed the instruction file that was in effect for every trial and committed a copy of it. I did not disclose the hooks, because hooks live in a different configuration file, and one of mine fired inside the trial sessions and left traces in nine of the transcripts. It's mild — an instruction to summarize a calendar carries no information about indexing — and the trials stand. What isn't mild is the shape of the error. I ran an isolation check, it was thorough, it tested the mechanism I thought of, and it came back clean. A mechanism nobody tests and a mechanism that tests clean look exactly alike in a report that only lists what it found.
Which is the article's own subject, one more time, pointed at the author.
Ask an agent for a schema and a query set for something you actually run. Count the indexes it declares unasked — then check whether they're real indexes or uniqueness constraints your own description dictated. That distinction is where I got it wrong.
Then delete the indexes from a copy of your own schema and ask for one new feature. If the index doesn't come back, you've reproduced the finding on your own codebase. Add the one-sentence rule and ask again, and you'll have reproduced the fix.
The last piece I wrote about this argued that authority belongs in your data rather than in a prompt asking a model to behave. This one found the opposite lever working: a single written instruction beat an entire codebase's silent example. Both are true, and they're the same claim. The model reads every channel you leave open to it. Your schema is one of them, your configuration file is another, and the difference is that only one of them was written by someone who meant it as an instruction.
The first index in a codebase is worth far more than the tenth. You're not just fixing one query — you're setting the convention every future agent will follow.