The Index That Made Our Queries Slower

The index was added to fix a slow query. It fixed that query. It made 14 others slower. Four weeks later, when the regression was traced back, the index had been live long enough that removing it felt risky. The post-mortem question: why did adding an index make unrelated queries slower?

The Alert

A week after the index was added to orders(status, created_at), dashboards showed a 40% increase in p95 execution time across order-related queries. The queries that got slower did not use the new index at all. They used a different index. But the planner had changed its plan for them anyway.

The Discovery

Find queries that changed plan after the index was added

PostgreSQL

-- Check planner's current choice for affected queries:
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.*, c.name
FROM   orders    o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.customer_id = 12047
  AND  o.created_at > now() - INTERVAL '90 days';

-- The new index: (status, created_at) — visible to the planner
-- The correct index: (customer_id, created_at) — should be preferred
-- But the planner now considers the new index as a candidate
-- and its cost estimate for a bitmap index scan combining both indexes
-- was wrong — underestimating the cost of the merge.

The planner was combining the new (status, created_at) index with the existing (customer_id, created_at) index via a BitmapAnd plan. This was more expensive than a simple index scan on (customer_id, created_at) alone, but the planner's cost model estimated it would be cheaper. The index had not broken the existing queries. It had given the planner a new option that looked better than it was.

Incident Timeline

Time	Event
Day 0	Index added: CREATE INDEX ON orders(status, created_at)
Day 1-7	Gradual p95 increase across order queries. Not immediately noticed.
Day 7	40% p95 increase confirmed in monitoring. Investigation begins.
Day 7 + 2h	EXPLAIN ANALYZE run on affected queries. BitmapAnd plan identified.
Day 7 + 3h	SET enable_bitmapscan = off to test — queries return to fast plan.
Day 7 + 4h	Decision: drop the new index. The original slow query gets a better fix.
Day 7 + 4h	DROP INDEX. Queries return to baseline within one plan cache cycle.

Root Cause

Adding an index changes the planner's option set for every query on that table, not just queries that explicitly use the new index. The planner evaluates combinations of indexes (BitmapAnd, BitmapOr) and can choose a combination that its cost model estimates as cheaper but that is actually more expensive at runtime — especially when row count estimates are off.

The Fix

Test index impact on ALL queries before committing

PostgreSQL

-- Before adding any index, capture current plans for the top 20 queries:
SELECT query, calls, total_exec_time, mean_exec_time
FROM   pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Add the index, re-run EXPLAIN ANALYZE on all 20 queries
-- If any plan changes, compare estimated vs actual costs
-- If a previously good query now uses a BitmapAnd: investigate before releasing

Prevention

Any new index on a table over 10M rows now goes through a pre/post plan comparison on the top 30 queries for that table. The index is created as INVALID using a non-concurrent build in a test environment first, plans captured, then enabled. If any query's p95 time increases by more than 10%, the index does not ship.

Marcus Reid

Staff DBA · PostgreSQL · 14 years · QueryTuning

Marcus has been managing large PostgreSQL deployments for 14 years. He writes about index strategy, statistics, and the counterintuitive behaviour that only shows up at production scale.

The Index That Made Our Queries Slower — and Why the Planner Chose It Anyway