Find and drop unused indexes
pg_stat_user_indexes records how often each index is scanned. A non-constraint index at idx_scan = 0 is pure cost, here idx_tariff_base_rate (~4.4 MB) has never been used. The primary key also shows 0 scans in this window, but you keep it: it enforces uniqueness.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
Indexes accumulate over years, added for a query that changed, or speculatively. Each one still costs write time, storage, and cache. The database itself tracks which are dead weight.
Meridian's tariff_zones has four indexes. Two show idx_scan = 0, but only idx_tariff_base_rate is a drop candidate. The primary key also sits at zero scans and must stay.
Simple terms
Indexes pile up over the years, added for a query that has since changed, or kept "just in case". Each one still costs write time, disk, and cache. PostgreSQL quietly counts how many times every index has actually been used; a non-constraint index sitting at zero scans is carrying real size while earning nothing. Those are the ones to drop, never the primary key just because its scan counter is quiet.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Indexing runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Fixes these errors
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.