postgresql-索引碎片处理

识别/回收索引碎片

create extension pgstattuple;
postgres=# SELECT * FROM pgstatindex('idx_fragmented');
-[ RECORD 1 ]------+----------
version            | 4
tree_level         | 2
index_size         | 539639808
root_block_no      | 290
internal_pages     | 189
leaf_pages         | 42739
empty_pages        | 0
deleted_pages      | 22945
avg_leaf_density   | 73.1
leaf_fragmentation | 37.18

碎片率达到37.18%,REINDEX索引，回收不必要的空间。

整个schema的索引碎片检查

SELECT a.indexrelname, b.* FROM pg_stat_user_indexes a, LATERAL pgstatindex(indexrelname) b where a.schemaname='xxx' and b.leaf_fragmentation>30 ORDER BY leaf_fragmentation DESC; 

但在生产环境中，要执行reindex之类的昂贵操作是非常困难的。由于该操作阻止正在执行的DML语句，因此在业务高峰期间进行重建是完全不可能的，该操作通常需要在停机时执行。而在PostgreSQL 12版之后， 新增了REINDEX CONCURRENTLY选项，它可以在线重建不会阻塞DML操作。最后建议在REINDEX操作后再执行ANALYZE更新一下统计信息。

检查损坏索引

INDEX 和 数据是一体的,到底是数据损坏还是索引损坏的问题. 要验证这个问题,有一个比较笨的方法,就是通过pg_dump的方式把表导出,如果这个表被导出的情况下,则证明表的索引已经损坏了；

如果觉得这样的操作对于大表不是太合理,可以通过查询 explain analyze 的方式来进行,查看查询中的数据结果是否一致

set enable_indexscan='off';

如果两次查询的结果是不一致的,使用索引和不使用索引的结果是不同的. 这已经能证明索引出了问题。

也可以用pg_catcheck 来进行系统的数据的完整性的检查.