postgresql查看表和索引的情况,判断是否膨胀的操作

索引膨胀的几个来源：

1 大量删除发生后，导致索引页面稀疏，降低了索引使用效率。

2 PostgresQL 9.0之前的版本，vacuum full 会同样导致索引页面稀疏。

3 长时间运行的事务，禁止vacuum对表的清理工作，因而导致页面稀疏状态一直保持。

查看重复索引

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;