PG dup indexes

A problem that occurs in our environment with several different databases where manual changes have been made, then automated schema changes are applied is duplicate indexes.
This isn't a major problem per-se, but does incur overhead for DML and of course takes up additional disk space and requires more work when the autovacuum maintains the table.
So, here's how you can find 'em and decide which to get rid of based on usage denoted in the idx_scan column:


SELECT
ct.relname AS tablename,
ci.relname AS indexname,
s.idx_scan, s.idx_tup_read, s.idx_tup_fetch,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS indexsize,
pg_get_indexdef(i.indexrelid)
FROM pg_index i
JOIN ( SELECT COUNT(0), indrelid, indkey, indpred, indexprs
FROM pg_index
GROUP BY 2,3,4,5
HAVING COUNT(0)>1) AS x
ON (x.indrelid=i.indrelid AND x.indkey=i.indkey)
JOIN pg_class ct ON ct.oid=i.indrelid
JOIN pg_class ci ON ci.oid=i.indexrelid
JOIN pg_stat_all_indexes s ON i.indexrelid=s.indexrelid
ORDER BY ct.relname, i.indkey, s.idx_scan;