Duplicate indexes

duplicateindexes.sql

SELECT
	pg_size_pretty (
		SUM (pg_relation_size(idx)) :: BIGINT
	) AS SIZE,
	(ARRAY_AGG(idx)) [ 1 ] AS idx1,
	(ARRAY_AGG(idx)) [ 2 ] AS idx2,
	(ARRAY_AGG(idx)) [ 3 ] AS idx3,
	(ARRAY_AGG(idx)) [ 4 ] AS idx4
FROM
	(
		SELECT
			indexrelid :: regclass AS idx,
			(
				indrelid :: TEXT || E'\n' || indclass :: TEXT || E'\n' || indkey :: TEXT || E'\n' || COALESCE (indexprs :: TEXT, '') || E'\n' || COALESCE (indpred :: TEXT, '')
			) AS KEY
		FROM
			pg_index
	) sub
GROUP BY
	KEY
HAVING
	COUNT (*) > 1
ORDER BY
	SUM (pg_relation_size(idx)) DESC;