We Let an AI Agent Add an Index. Reads Stayed Slow. Writes Got a Permanent Tax.

Day 11 — The Index Bet

Here's the trap: your AI agent will suggest "add an index" before you've even finished describing the slow query, and it will be right often enough that you stop checking. That's exactly when it costs you — you can ship a change that makes a hot table measurably worse while it feels like you just optimized it.

The dashboard that got slower

Last month a teammate asked their agent to speed up a dashboard query hitting our orders table — a few hundred million rows, filtered by status. The agent came back fast and confident:

Agent: I've analyzed the slow query. It filters on `orders.status`
without an index, forcing a sequential scan. I've added:

  CREATE INDEX idx_orders_status ON orders(status);

This should significantly speed up the WHERE status = 'pending'
lookups used by the dashboard.

Reasoning: indexes turn O(n) scans into O(log n) lookups.
Any column used in a WHERE clause benefits from an index.

It sounded right, so it shipped. The dashboard query didn't get faster. But every INSERT into orders — the busiest table in the system, thousands of writes a minute — got slower, because now each one had to maintain a second B-tree on the side.

What EXPLAIN ANALYZE actually showed

We ran EXPLAIN ANALYZE before and after. Before: a sequential scan, as expected. After: still a sequential scan. Postgres's planner looked straight at the new index and ignored it.

-- status has 4 possible values across 400M rows:
-- 'pending' ~ 35%, 'active' ~ 40%, 'done' ~ 20%, 'cancelled' ~ 5%

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

 Seq Scan on orders  (cost=0.00..8500000 rows=140000000)
   Filter: (status = 'pending')
   Rows Removed by Filter: 260000000
 Planning Time: 0.4 ms
 Execution Time: 4210.2 ms

-- index exists, planner ignored it. Why? Because 35% of rows
-- match. Reading 35% of the table via random index lookups is
-- SLOWER than one sequential pass. The planner did the math
-- correctly. The agent didn't.

Net effect: zero read benefit, plus a permanent write tax on the one table we can least afford to slow down. That's the whole incident in one sentence.

The real mental model: an index is a bet, not a button

An index is a sorted side-structure — almost always a B-tree — that lets the database jump straight to matching rows instead of scanning every one. It's a second copy of (part of) your data, and the database has to keep it in sync forever.

• ▹Read side: turns a linear scan (O(n)) into an O(log n) tree descent plus one fetch per matching row — cheap when few rows match, but once matches climb toward a large slice of the table, you're effectively doing another full scan the slow way (random I/O instead of sequential).
• ▹Write side: every INSERT/UPDATE/DELETE touching an indexed column now updates the index too. More indexes means more work per write, always, unconditionally — there's no scenario where this cost doesn't apply.
• ▹Storage side: an index is real bytes on disk — for wide or multi-column indexes, sometimes rivaling the table itself.
• ▹Net bet: you're trading a guaranteed write cost plus storage for a conditional read speedup. Miss the condition, and you've paid the cost for nothing.

Why AI agents default to 'just add an index'

"Add an index" is one of the most repeated pieces of database advice on the internet — it's in every SQL tutorial, every Stack Overflow thread, every performance blog post ever written. That makes it an extremely high-probability completion the moment a model reads "slow query." What's missing from that pattern is the part that actually decides whether it works: the cardinality of the column, the selectivity of the predicate, the write-path cost on that specific table. The agent isn't running a query planner in its head — it's completing the statistically most common next sentence after "slow query" + "fix." That's pattern completion, not cost-based reasoning, and it's precisely the gap between a plausible-sounding fix and a verified one.

Three ways an index quietly fails to help

• ▹Low cardinality: a status/boolean/enum column where any single value matches a big chunk of rows — the planner correctly prefers a sequential scan over thousands of random index lookups.
• ▹Function-wrapped column: `WHERE LOWER(email) = 'x'` won't touch a plain index on `email` — the index stores raw values, not the function's output, unless you build a matching expression index.
• ▹Leading wildcard / prefix mismatch: `LIKE '%gmail.com'` can't use a standard B-tree index at all (it's sorted by prefix, not suffix); `LIKE 'a%'` can, `LIKE '%a'` can't.

The verification habit for the AI era

Treat "this index will speed it up" exactly the way you'd treat a junior engineer's confident guess: plausible, not proven. The habit that actually protects you:

• ▹Run EXPLAIN ANALYZE on the real query before the change — note the plan type (Seq Scan vs Index Scan) and the actual execution time, not the estimated cost.
• ▹Apply the index in staging or during a low-traffic window, then run EXPLAIN ANALYZE again — confirm the planner actually switched to an index scan, don't assume it.
• ▹Check column cardinality yourself: `SELECT status, count(*) FROM orders GROUP BY status;` — if any value covers more than roughly 10-15% of rows, be skeptical the planner will ever use an index for it.
• ▹Estimate write-path cost during the table's busiest hours — every index adds work to every INSERT/UPDATE, and on a hot table that's real money. A single EXPLAIN on the read query will never show you this side of the ledger.

This is the same discipline you'd apply to any agent-written code: it proposes, you demand the evidence the proposal implies. "It should help" is a hypothesis. EXPLAIN ANALYZE is the experiment.

Close

Agents propose with confidence; engineers confirm with evidence. Tomorrow's concept leans on the same instinct — don't trust a plausible explanation, trust a measured one.

Extend your knowledge

• ▹Run EXPLAIN ANALYZE on a real slow query in your own hot table today, before you let an agent anywhere near it — write down the plan type and execution time as your baseline.
• ▹Read Postgres's own docs on 'Using EXPLAIN' and 'Index Types' to see how the planner actually picks a plan.
• ▹Check pg_stat_user_indexes (or your DB's equivalent) on a table you own — look for indexes with near-zero scans that are still quietly paying write cost.
• ▹Next time an agent proposes an index, ask it directly: 'what's the cardinality of this column and the write frequency on this table?' See if it can answer, or just restates the suggestion back at you.