DEV Community

晖莫
晖莫

Posted on

Why the index I added made my query slower

I added CREATE INDEX ON events (status) on a Friday. By Monday the dashboard query that looks for pending events was slower than before I touched it. The index was not corrupt, the query was not wrong, and nothing had been deployed in between. The planner had just picked a plan that costs more on this table.

The plan changed, not the data

status has four values, and pending is most of the rows, especially right after a backlog. Postgres will still reach for an index when the predicate is not selective, because the planner decides from the row estimate in pg_statistic, which is built by sampling the table during ANALYZE. If the estimate says "few rows", it chooses a bitmap index scan or a plain index scan. Then it fetches heap pages in index order, one random read at a time, rechecks visibility on every tuple, and throws away most of what it read. A sequential scan reads the same pages in physical order and never opens the index at all.

Two more versions of the same mistake caught me earlier.

A composite index on (tenant_id, created_at) did nothing for the queries that filtered on created_at alone. Postgres cannot cheaply skip a leading column, so the index sat there costing writes and disk while the plan stayed sequential. Put the column you range-filter on first.

Stale statistics after a bulk load. I inserted a large batch and ran the app immediately. Autovacuum had not analyzed the table yet, so the estimates were from before the load and the planner was reasoning about a table that no longer existed.

Index bloat is the third one. After enough updates and deletes, index pages fill with dead entries, the index grows past what the live data warrants, and index-only scans stop being index-only because the visibility map is not set. Check pg_stat_user_indexes.idx_scan for indexes nothing reads, and pg_stat_all_tables.n_dead_tup with last_autovacuum for tables autovacuum is losing ground on.

Confirming it with EXPLAIN (ANALYZE, BUFFERS)

Guessing is how I got here. Run the actual query:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, created_at
FROM events
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;
Enter fullscreen mode Exit fullscreen mode

Read four things. The node type: Seq Scan turning into Bitmap Heap Scan or Index Scan is the change you are hunting. The rows= on the estimated line against the rows= on the actual line for the same node, where a wide gap means the planner was working from bad statistics. Rows Removed by Filter, where a count close to the rows returned means the index is discarding nearly everything it read. And Heap Fetches under an Index Only Scan, where a high count means the visibility map is stale, usually after heavy writes.

Buffers tell you more than wall-clock time. shared hit and shared read count the 8 kB pages the plan touched. A plan that touches more pages loses on a cold cache even when its estimated cost looks lower. Run the statement twice: the first run shows reads, the second shows hits, and neither is the same thing as the cost model.

For a real comparison, capture EXPLAIN (ANALYZE, BUFFERS) before the index exists and again after, on the same data, and compare buffer counts. Do not trust the estimated cost column on its own.

What to do instead

Make the index match the query, or drop it. A partial index is usually the answer when the hot predicate is a small slice of the table:

CREATE INDEX CONCURRENTLY events_pending_created_at_idx
ON events (created_at DESC)
WHERE status = 'pending';
Enter fullscreen mode Exit fullscreen mode

That index holds only the rows the query wants, so it stays small, and it can serve the ORDER BY as well as the filter. CONCURRENTLY avoids blocking writes, at the cost of a slower build and a possible invalid index if the build fails — check pg_index.indisvalid before assuming it worked.

The rest of the toolbox: put the filtered column first in a composite index, add INCLUDE columns when you want an index-only scan, run ANALYZE yourself after a bulk load rather than waiting for autovacuum, and use CREATE STATISTICS when two columns are correlated and the single-column estimates mislead the planner. Then verify the fix the same way you found the problem. After the change, EXPLAIN (ANALYZE, BUFFERS) should show fewer shared buffers than the plan it replaced. If it does not, the index is not paying for its write cost, and DROP INDEX CONCURRENTLY is the honest fix.

Top comments (0)