PostgreSQL 查看重复索引

在进行数据表结构的设计和 SQL 优化的过程中，有时候会遇到这样的场景，索引被重复创建，那么如何找出这些被重复创建的索引呢，下面这个语句可以帮助到我们。

WITH sub AS ( SELECT indrelid::regclass AS relname,
              indexrelid::regclass AS idx,
                         (indrelid::text || indclass::text || indkey::text || COALESCE(indexprs::text, '') || COALESCE(indpred::text, '')) AS KEY
            FROM pg_index )
SELECT relname,
      (array_agg(idx))[1] idx1,
            pg_get_indexdef((array_agg(idx))[1]) idx1_def,
            (array_agg(idx))[2] idx2,
            pg_get_indexdef((array_agg(idx))[2]) idx2_def,
            (array_agg(idx))[3] idx3,
            pg_get_indexdef((array_agg(idx))[3]) idx3_def
FROM sub
GROUP BY relname, KEY
HAVING count(*) > 1;

备注：找出这些重复索引，然后根据需要进行删除。