/* Finds indices that have not been used (the ones with 0 scans) */

SELECT
 schemaname,
 relname,
 indexrelname,
 idx_scan,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
 pg_stat_user_indexes i
 JOIN pg_index USING (indexrelid)
WHERE
 indisunique IS false
ORDER BY idx_scan,relname;