Unused Index

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.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Indexes with zero scans since last stats reset, on tables with recent activity
SELECT
  s.schemaname,
  s.relname                   AS table_name,
  s.indexrelname              AS index_name,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
  s.idx_scan,
  s.idx_tup_read,
  s.idx_tup_fetch
FROM pg_stat_user_indexes s
JOIN pg_class c ON c.oid = s.relid
WHERE s.idx_scan = 0
  AND c.reltuples > 1000
  AND s.schemaname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY pg_relation_size(s.indexrelid) DESC;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Stage 1: disable the index and monitor for a week before dropping
UPDATE pg_index SET indisvalid = false
  WHERE indexrelid = '<index_name>'::regclass;

-- Stage 2: drop once confirmed safe
DROP INDEX CONCURRENTLY <index_name>;

Worked example

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.