Unused Index
pg_views: pg_stat_user_indexes, pg_indexes, pg_class
Full detection logic, thresholds, and guided fixes ship in the product.
Book a demo →Overview
Postgres tracks every time an index is used via pg_stat_user_indexes.idx_scan. An index with zero scans since the last statistics reset is a candidate for removal—it is consuming disk space, slowing every INSERT/UPDATE/DELETE on the table, and increasing checkpoint write pressure without providing any read benefit.
Common causes are: a replaced composite index (both old and new exist), a column migrated out of query patterns, or a premature optimisation index that was never exercised in production.
insightral flags unused indexes on tables with over 1,000 live rows and at least one write since stats reset. Before dropping, confirm stats haven't been reset recently via pg_stat_user_indexes.last_autovacuum.
See this rule in action
Full detection logic, thresholds, and guided fixes ship in the product. The licensed agent runs this check — and all others — continuously against your database, surfaces findings with AI explanations, and surfaces a guided remediation path inside the dashboard.
Book a demo →Common False Positives
- Stats were recently reset with SELECT pg_stat_reset() — all idx_scan counts start at zero; check pg_stat_bgwriter.stats_reset timestamp.
- Indexes used only by infrequent batch jobs (e.g., monthly reports) that haven't run yet since the stats reset window.
Unused Index — and 100+ more checks — in the licensed product
Full detection logic, thresholds, and guided fixes ship in the product.
Book a demo →Last updated: 2026-05-13 · View the rule library