Skip to main content
View rawEdit

11. Single index analysis

Deep dive into a specific index to verify its usage, size, and whether it is safe to remove. Use this after identifying a candidate in the aggregated view.

When to use​

  • Checkup reports an unused (H002) or redundant (H004) index
  • You need to verify that an index is truly unused before dropping it
  • Checking index bloat and deciding whether to reindex

Key panels​

  • Index size over time — growth trend for this index
  • Scan count over time — how often the index is used
  • Tuples read and fetched — rows accessed through this index
  • Table context — the parent table's size and activity for context

Decision guide​

SituationAction
Zero scans + no dependent queriesSafe to drop. Verify with pg_stat_user_indexes that stats have not been recently reset.
Low scans + critical query depends on itKeep or replace with a more efficient index
High scans + high bloatRun REINDEX CONCURRENTLY to rebuild without locking
Redundant (prefix of another index)Drop the shorter/redundant index

Verifying index usage before dropping​

Before dropping, confirm the stats period covers a representative workload:

-- Check when stats were last reset
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

-- Check index usage
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'your_index_name';

If stats_reset was recent, wait for a full business cycle before deciding.

  • H002 — unused indexes (CLI)
  • H004 — redundant indexes (CLI)
  • F005 — index bloat estimated (monitoring stack)