Skip to Content

Define not used DB indexes in PostgreSQL

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; 
Reasons why you may choose to split your environment
Infrastructure architecture