Define not used DB indexes

Pavel Vrublevskij, Pavel Vrublevskij

How to know which DB indexes are not used? For PostgreSQL is simple, use the following query to find not used indexes. After analyze don't forget to remove those indexes.

SELECT pg_size_pretty(pg_relation_size((schemaname || '.' || relname)::regclass)), *
FROM pg_stat_all_indexes
ORDER BY pg_relation_size((schemaname || '.' || relname)::regclass) DESC, idx_scan ASC;