10. Aggregated index analysis
Audit all indexes in your database. Find unused indexes wasting disk and slowing writes, redundant indexes that duplicate coverage, and bloated indexes that need maintenance.
When to use
- Checkup reports unused indexes (H002) or redundant indexes (H004)
- You want to reclaim storage and reduce write overhead
- Periodic index health audit
- After application changes that may have made indexes obsolete
Key panels
- Largest indexes by size — indexes ranked by disk usage
- Indexes by scan count — ranked by usage; zero-scan indexes are candidates for removal
- Unused indexes — indexes with zero scans since stats reset
- Index bloat estimates — indexes with high estimated bloat percentage
- Redundant indexes — indexes whose key columns are a prefix of another index
What good looks like
- No large indexes with zero scans (unless recently created)
- Index usage aligns with the query workload
- Bloat percentages are within acceptable ranges
- No redundant indexes
What to investigate
| Signal | Next step |
|---|---|
| Large index with zero scans | Drill into Single index analysis to confirm; then check query workload |
| Redundant index found | Verify with Checkup H004; compare key columns; drop the redundant one |
| High index bloat | Consider REINDEX CONCURRENTLY or drop and recreate |
| Sudden drop in index usage | Check if application queries changed; verify index validity |
Related Checkup checks
- H001 — invalid indexes (CLI)
- H002 — unused indexes (CLI)
- H004 — redundant indexes (CLI)
- F005 — index bloat estimated (monitoring stack)