Detect duplicate indexes in Postgres 9+

duplicate-indexes.sql

with indexes as (
  select
      t.relname as table_name,
      i.relname as index_name,
      array_agg(a.attname order by position(cast(a.attnum as text) in cast(ix.indkey as text)) asc) as column_names
  from
      pg_class t,
      pg_class i,
      pg_index ix,
      pg_attribute a
  where
      t.oid = ix.indrelid
      and i.oid = ix.indexrelid
      and a.attrelid = t.oid
      and a.attnum = ANY(ix.indkey)
      and t.relkind = 'r'
  group by
      t.relname,
      i.relname
  order by
      t.relname,
      i.relname
)
select d.table_name, i.index_name, i.column_names, d.index_name as duplicate_index_name, d.column_names as duplicate_index_columns
from indexes d
join indexes i
  on i.table_name = d.table_name
  and i.index_name != d.index_name
  and i.column_names @> d.column_names
  and i.column_names[1] = d.column_names[1]
  and (i.column_names != d.column_names or i.index_name < d.index_name);