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命令可以非常方便的重建索引,但是需要注意重建索引会消耗性能,一般放在非业务时间段操作为宜。