The index that made the query slower, not faster

Adding an index is usually presented as a free win: the query was slow, now there’s an index, now it’s fast, done. That was true for the query I was actually trying to fix. It was not true for the table it lived on, and I didn’t find out until a few days later, when a completely different part of the system got slower for no reason anyone had touched.

The query that got faster

A reporting endpoint filtered a heavily-inserted events table by status, and without an index on that column, Postgres was doing a full sequential scan across a few million rows every time someone loaded that report. Adding CREATE INDEX idx_events_status ON events(status) was the obvious move, and it worked exactly as advertised: the report went from a few seconds to a few milliseconds. That part of the story is exactly as boring as it sounds, which is why I didn’t think much more about it at the time.

What broke instead

That events table wasn’t a quiet table, it was taking a few hundred inserts a second from a background ingestion job. Every one of those inserts now had to update two indexes instead of one, the existing primary key index and the new status one. Individually that’s a small cost, but at that insert rate it added up to a measurable, sustained increase in write latency on the ingestion path, one that showed up a few days later as the ingestion job slowly falling behind its queue during peak hours, a completely different symptom in a completely different part of the system from the one I’d touched.

The second, weirder effect took longer to trace: an unrelated query that filtered by a different, low-cardinality boolean column started running slower too. Postgres’s query planner had started using the new status index for that query as well, because the planner picks a plan based on table statistics, and the statistics happened to make the new index look like a plausible option for a query it was actually worse for, a sequential scan would have been cheaper for that particular filter’s real selectivity. Nobody asked the planner to do that. It just had a new option available and picked it based on numbers that turned out to be a bad fit for that specific query’s actual data distribution.

Checking the real cost with EXPLAIN ANALYZE

The way to actually see this, instead of guessing, is EXPLAIN (ANALYZE, BUFFERS) on both sides of the trade: the query you’re optimizing for, and the write path plus any other query touching the same table.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE status = 'pending';
-- confirms the new index is actually being used, and by how much it helps

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO events (status, payload) VALUES ('pending', '{}');
-- shows the extra index maintenance cost per write, easy to miss if you only ever check reads

Running the insert plan before and after adding the index is the step I skipped the first time, because it felt like checking something that couldn’t possibly matter for a single INSERT. It matters once you multiply that per-row cost by a few hundred inserts a second, which is exactly the kind of multiplication that doesn’t show up in a one-off manual test.

Why this keeps happening

Query optimization advice is almost always framed from the reader’s side: this query is slow, here’s an index, now it’s fast. That framing is correct as far as it goes, but it silently assumes the table is read-mostly, which most examples are, because a read-heavy demo table is easier to reason about than a table taking real write load. The moment a table gets hundreds of writes a second, every index on it is doing work on every single one of those writes, whether or not that write’s query ever benefits from the index.

The planner side is even easier to miss, because it’s not something you touch directly. You add an index for query A, and the planner independently decides it’s also useful for query B, C, and D, using the same table statistics that were correct enough when there was one index and stopped being a clean fit once there were two.

The actual takeaway

An index isn’t a gift you give to one slow query, it’s a standing cost you impose on every future writer to that table, and a new option you hand to the planner for every other query touching it, whether you intended that or not. Before adding one, it’s worth running EXPLAIN ANALYZE on the write path too, not just the read you’re trying to fix, and checking back on the table’s other queries a few days later, since the planner’s decision to actually use the new index for something else doesn’t always show up on day one.