Finding unused indexes consuming space and write overhead

Every index must be updated on every INSERT, UPDATE, and DELETE — unused indexes impose pure write overhead and waste storage without benefiting any query.

Diagnose it

-- Indexes with zero scans since last stats reset, excluding constraints:
SELECT s.schemaname,
       s.relname                                          AS table_name,
       s.indexrelname                                     AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid))    AS index_size
FROM pg_stat_all_indexes s
WHERE s.schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
  AND s.idx_scan = 0
  AND NOT EXISTS (
      SELECT 1 FROM pg_index i
      WHERE i.indexrelid = s.indexrelid
        AND (i.indisprimary OR i.indisunique OR i.indisexclusion)
  )
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

Always confirm stats have been accumulating for a sufficient period — check
stats_reset in pg_stat_all_indexes or
pg_stat_database before trusting idx_scan = 0.

Why it happens

Unused indexes are created during development, for queries that were later removed, or as
duplicates that the planner passes over. They take storage (sometimes gigabytes for large
tables), add write latency, slow autovacuum (which must update every index), and increase
checkpoint write volume.