pg_index_utilization.sql

SELECT idstat.relname AS table_name, 
   indexrelname AS index_name, 
   CASE WHEN indexdef ~* 'unique' THEN 'UNIQUE' END,
   pg_size_pretty(pg_relation_size(indexrelname::text)) AS index_size,
   idstat.idx_scan AS times_used,
   n_tup_upd + n_tup_ins + n_tup_del as num_writes,
   indexdef
FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
   JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
ORDER BY num_writes desc, times_used ASC;