Simple PostgreSQL Index Optimization Query

Indexes that are never used consume disk space and slow down INSERT/UPDATE/DELETE operations. This simple query identifies unused indexes that may be candidates for deletion.

Run this query to find indexes that haven't been used since the last database restart or statistics reset.

sql
SELECT  relname AS table_name,  indexrelid::regclass AS index_name,  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,  idx_scanFROM  pg_stat_user_indexesWHERE  idx_scan = 0  AND schemaname = 'public' -- Adjust schema if neededORDER BY  pg_relation_size(indexrelid) DESC;

Note: Always verify with EXPLAIN ANALYZE before dropping an index in production!