Queries slowed over weeks though EXPLAIN shows the index is still used — diagnose the cause
A hot orders table takes heavy UPDATE/DELETE traffic. A lookup that used to be instant has slowly degraded over weeks, yet the index is still chosen. Read this plan and diagnose why it is slow.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;
Index Scan using orders_user_id_idx on orders
(cost=0.43..2841 rows=12 width=240)
(actual time=0.05..38.7 rows=12 loops=1)
Index Cond: (user_id = 42)
Buffers: shared hit=9 read=4120
Planning Time: 0.20 ms
Execution Time: 38.92 msDiagnose the cause and say how you would fix it.
The index is still chosen, yet it returns 12 rows while touching 4120 buffers — the signature of bloat: MVCC dead tuples from the heavy UPDATE/DELETE traffic accumulated faster than autovacuum could reclaim them, so the heap and index are full of dead pages the scan must wade through. Fix: make autovacuum more aggressive on this table, run VACUUM, and REINDEX (or pg_repack) to rebuild the bloated index.
- ✗Reading 'index is used' as proof the plan is fine, ignoring the rows-vs-buffers gap
- ✗Blaming stale statistics or a missing composite index instead of bloat
- ✗Assuming heavy buffer reads always mean an undersized cache
- →Which
pg_stat_user_tablescolumns confirm the table has too many dead tuples? - →Why does
REINDEX CONCURRENTLYmatter on a table that cannot take downtime?
What the plan shows
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;
Index Scan using orders_user_id_idx on orders
(cost=0.43..2841 rows=12 width=240)
(actual time=0.05..38.7 rows=12 loops=1)
Index Cond: (user_id = 42)
Buffers: shared hit=9 read=4120
Planning Time: 0.20 ms
Execution Time: 38.92 msDiagnosis
The plan is the right one — it is an Index Scan, the index is used. But note the gap: the query returns 12 rows while touching 4120 buffers (read=4120). Reading thousands of pages to return a dozen rows is the signature of bloat.
The table takes heavy UPDATE/DELETE traffic. MVCC leaves dead row versions behind after every change; they piled up faster than autovacuum could reclaim them. The heap and the index itself are now full of dead pages the scan must wade through, even though only 12 live rows match.
✅ Fix:
- make
autovacuummore aggressive on this table (lowerautovacuum_vacuum_scale_factor, raiseautovacuum_vacuum_cost_limit); - run
VACUUMto reclaim dead tuples for reuse; REINDEX CONCURRENTLYorpg_repackto rebuild the bloated index without downtime.
Confirm with pg_stat_user_tables.n_dead_tup and the pgstattuple extension.
