12.PG数据库-重建索引_pgsql为啥时不时的要重建索引

1.重建索引

(1)Postgresql重建索引是通过REINDEX命令来实现的
语法结构 
REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
注释说明：
INDEX        重新创建指定的索引。
TABLE        重新创建指定表的所有索引。
SCHEMA       重新创建指定 SCHEMA 的所有索引。
DATABASE     重新创建指定 DATABASE 的所有索引。
SYSTEM       重新创建当前数据库中在系统目录上的所有索引。
CONCURRENTLY 并发重新创建索引，不阻止对该表的并发插入、更新或删除。

(2)重建索引的原因
当由于软件bug或者硬件原因导致的索引不可用；
当索引包含许多空的或者近似于空的页，这个在b-tree索引会发生；
检查索引膨胀情况，如果索引膨胀太大, 会影响性能, 建议重建索引,
数据库系统修改了存储参数，需要重建不然就会失效(如修改fillfactor参数);
创建并发索引时失败，遗留了失效的索引

(3)重建索引注意事项
重建索引不同的级别的重构需要不同的权限，比如table那么就需要有这个表的权限即需要有操作索引的权限，如超级用户postgres拥有这个权限。
重构索引的目的是为了当索引的数据不可信时，即对于成本的计算会出现偏差较大，无益于优化器得到最优的执行计划以至于性能优化失败。
重构索引类似于先删除所有再创建一个索引，但是索引的条目是重新开始的。重构时当前索引是不能写的，因为此时有排他锁。
在8.1版本之前REINDEX DATABASE 只包含系统索引，并不是期望的所有指定数据库的索引。7.4版本之前REINDEX TABLE不会自动执行下级TOAST tables。
如果索引在用CONCURRENTLY选项创建失败，该索引保留为一个“invalid”。 
这类索引是无用的，可以方便的用REINDEX来重建它们。注意，只有REINDEX INDEX可以在无效的索引上执行并发创建。 

表和索引使用查看 
PostgreSQL的contrib模块提供了额外的硬盘使用信息查看方式
pgstattuple：
这个模块包含可获得常规行元组数据和索引页面的详细分析的功能
提供了pgstatetuple()和pgstatindex()两个统计表和索引的方法
比较系统表pg_class的表统计信息，这个模块还统计了表中的dead tuples
表未分析前，使用pgstatetuple()函数就能精确查询表的 page 数据，
而此时 pg_class 还没数据，说明 pg_relpages 查询了表的 page 物理文件信息。 

2.重建索引的基本语法如下

REINDEX INDEX [CONCURRENTLY] IDX_ID; 

REINDEX TABLE [CONCURRENTLY]  T_TAB1;

REINDEX SCHEMA USER1;

REINDEX DATABASE MYDATABASE; 

REINDEX SYSTEM MYDATABASE2;

3.总结

使用REINDEX命令可以非常方便的重建索引，但是需要注意重建索引会消耗性能，一般放在非业务时间段操作为宜。