Unused indexes waste write performance and storage

Indexes that are never used (idx_scan = 0) still impose write overhead on every INSERT, UPDATE, and DELETE operation, and consume storage. Large unused indexes significantly degrade write performance for no benefit.

Identify unused indexes: SELECT indexrelname, relname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelid NOT IN (SELECT indexrelid FROM pg_index WHERE indisprimary) ORDER BY pg_relation_size(indexrelid) DESC; Drop unused indexes after confirming they are not needed: DROP INDEX index_name;