Why Adding an Index Made Your Query Slower

The instinct is reasonable: query is slow, add an index, query gets fast. Sometimes that's exactly what happens. Then sometimes you add the index and nothing improves — or worse, something else in the system gets slower and you can't immediately connect it back to the index you added three deploys ago.

An index isn't a speed setting you turn on. It's a second copy of your data, sorted differently, that the database has to keep in sync with the original every single time a row changes. You're not getting faster reads for free. You're trading write speed and storage for read speed, and that trade is only worth it if you actually cash in the read side.

What an index actually is

Without an index, finding a row means scanning the table — checking every row, in order, until the database has looked at all of them. For a small table this is fine. For a large one, it's the difference between a query that returns instantly and one that takes seconds.

An index is a separate, sorted structure — usually a B-tree — that maps values in a column to the location of the matching rows. Instead of scanning a million rows, the database can binary-search a sorted structure and jump straight to the ones that match. That's the entire value proposition, and it's a real one.

But that sorted structure doesn't maintain itself. Every INSERT has to slot a new entry into the index at the correct sorted position. Every UPDATE to an indexed column has to remove the old entry and insert a new one. Every DELETE has to remove an entry. None of this is optional — the index has to stay accurate, or it becomes actively harmful, returning wrong results or forcing the database to fall back to a full scan anyway to double-check. So the cost isn't hypothetical: every write to that table now does extra work, for every index on it.

Why the query planner might just... ignore it

Having an index doesn't guarantee the database uses it. Query planners estimate the cost of different ways to execute a query and pick the cheapest one — and sometimes a full table scan is genuinely cheaper than using the index, even though that feels backwards.

This shows up constantly with low-selectivity columns — columns where a value doesn't narrow things down much:

CREATE INDEX idx_users_status ON users (status);

SELECT * FROM users WHERE status = 'active';

If 95% of your users are 'active', this index barely helps. Using it means jumping all over the index, then jumping all over the table to fetch each matching row — random access, scattered across disk or memory. A sequential scan through the whole table in order is often faster than that scattered lookup, even though the sequential scan touches more rows on paper. The planner knows this, checks its statistics on how common each value is, and skips the index entirely. That's not a bug. That's the planner doing its job — the index for this specific query was never going to help, no matter how confidently it got created.

Indexes earn their keep on high-selectivity columns — ones where a given value narrows the result down to a small slice of the table. An index on email for an exact-match lookup is a great candidate: it identifies essentially one row. An index on status, where there are only three possible values across a million rows, usually isn't.

The composite index order trap

A multi-column index is not interchangeable in either direction. CREATE INDEX idx (a, b) builds a structure sorted first by a, then by b within each a. That ordering determines exactly what the index can help with:

CREATE INDEX idx_orders ON orders (customer_id, order_date);

-- Uses the index efficiently
SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 42;

-- Cannot use this index at all
SELECT * FROM orders WHERE order_date > '2026-01-01';

Filtering on order_date alone can't use (customer_id, order_date), for the same reason you can't efficiently find every entry with a given last name in a phone book sorted by first-then-last name — the sort order simply doesn't group last names together. If you need to filter by order_date alone and by the pair together, that's two different access patterns, and it may genuinely need two different indexes — one (order_date), one (customer_id, order_date) — not one clever index doing double duty.

The habit that actually prevents the slowdown

Before adding an index, ask two questions instead of one:

Will this actually get used? Check the column's selectivity — roughly, how many distinct values does it have relative to the table's row count. A boolean column or a status enum with three values is almost never worth indexing alone; it needs to be paired with something more selective, or skipped.

What does this cost on the write side? A table that gets heavy INSERT traffic — an events table, a logs table, an orders table during a sale — pays the indexing cost on every single write. Five indexes on a hot write table isn't five small costs, it's five compounding ones, on every row, all the time. Read-heavy, write-light tables can afford to be indexed more liberally. The reverse can't.

Indexing isn't a one-way lever you pull to "make the database faster." It's a bet that this specific column, filtered this specific way, gets queried often enough and selectively enough to be worth paying for on every write. Most of the time that bet is correct. It's worth actually making the bet on purpose, rather than reaching for CREATE INDEX the moment something feels slow.

Reading query plans and understanding what an index buys you is exactly the kind of thing that clicks faster hands-on. SQL Quest has a lesson built around this — real queries, a real database, in your browser, free.