Detect and remove duplicated indexes in PostgreSQL

In Postgres, you can create multiple indexes on same table and identical list of columns. While it is useful for maintenance (you can create new index and drop the old one, for example) it is NOT advisable in long term.

Here is a catalog query which will detect and print duplicate indexes:

WITH dup AS (
	SELECT x.indrelid, x.indkey, i.relam, md5(x.indpred)
	FROM pg_index x
	JOIN pg_class i ON i.oid = x.indexrelid
	GROUP BY 1,2,3,4
	HAVING count(*) > 1
)
SELECT
	n.nspname AS schemaname,
	c.relname AS tablename,
    i.relname AS indexname,
	/*am.amname, x.indnatts, x.indkey,
    ARRAY(
        SELECT pg_get_indexdef(x.indexrelid, k + 1, true)
        FROM generate_subscripts(x.indkey, 1) as k
        ORDER BY k
    ) AS indkeyname,
	x.indpred, */
    pg_get_indexdef(x.indexrelid) AS indexdef,
    pg_size_pretty(c.relpages::int8 * current_setting('block_size')::int) AS tablesize,
    pg_size_pretty(i.relpages::int8 * current_setting('block_size')::int) AS indexsize
FROM pg_index x 
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_am AS am ON i.relam = am.oid
JOIN dup ON (
	dup.indrelid = x.indrelid
	AND dup.indkey = x.indkey
	AND dup.relam = i.relam
	AND dup.md5 IS NOT DISTINCT FROM md5(x.indpred))
ORDER BY
	n.nspname, c.relname, dup.md5 nulls first, i.relname
;