一月 23, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中检查哪些索引需要重建。
目录
介绍
虽然很少会出于优化性能的目的，去重建 B 树索引，但有些数据库还是可以从重建索引中获得可衡量的性能提升。这些工作负载具有以下特征：
- 索引严重碎片化：SQL 工作负载在表上有大量的 DML 操作，索引出现大量删除的叶块。
- 频繁使用索引扫描访问计划：SQL 工作负载涉及大量的索引扫描（索引范围扫描和仅索引扫描）。
检查索引需要合并/重建的规则，取决于您特定的索引状态。
哪些索引需要重建？
不能一概而论地说，很少会为提高性能而进行索引重建，它取决于许多因素，最重要的是应用程序的特征。
- 在需要添加和删除大型数据集的科学应用（临床、实验室）中，重建索引的需求是“常见的”。
- 相反，在很少更新或删除数据行的系统中，索引重建很少能提高性能。
- 在执行批处理 DML 作业的系统中，索引重建“经常”可以提高 SQL 性能。
通常，当满足以下条件时，索引就需要重建了：
- 索引中膨胀的百分比超过 50%。
- 索引高度超过 4 个层级。
当索引出现倾斜时，索引的某些部分会比其他部分更频繁地被访问。由于这种倾斜，可能会发生磁盘块争用，从而造成 SQL 性能瓶颈。因此，定期检查索引，以确定它们是否已倾斜并可能需要重建，这一点很重要。
请记住，删除表行时不会以物理方式删除 B 树索引节点，也不会从索引中删除条目。相反，PostgreSQL 会“逻辑地”删除索引条目，并在索引树中留下“死”节点，如果需要另一个相邻条目，则可以重用这些节点。
但是，当删除大量相邻行时，PostgreSQL 不太可能有机会重用已删除的叶行，而这些行代表了索引中浪费的空间。除了浪费空间之外，大量已删除的叶节点，还会让仅索引扫描运行更长时间。
B 树索引高度
索引的高度是指索引在行插入后生成的层级数。当向表中添加大量行时，PostgreSQL 的索引可能会生成新的层级以容纳新行。
B 树索引在三个层级内可以支持数百万个条目。任何已生成到第 4 级的 B 树索引，在执行大量删除操作后，都可能会从索引重建受益，以将索引恢复到其原始状态。
pageinspect 模块提供函数让你从低层次观察数据库页面的内容，这对于获取某些内部信息很有用。所有这些函数只能被超级用户使用。
bt_metap()将为您指向根节点，并且bt_page_stats()对于根节点会显示层级btpo_level。通过在几个索引页上使用bt_page_items()，您可以看到每个索引页有多少个子页。
bt_metap返回关于一个B树索引元页的信息。例如：
test=# SELECT * FROM bt_metap('large_table_index');
-[ RECORD 1 ]-------------+-------
magic | 340322
version | 4
root | 1
level | 0
fastroot | 1
fastlevel | 0
last_cleanup_num_delpages | 0
last_cleanup_num_tuples | 230
allequalimage | f
bt_page_stats返回有关 B 树索引单一页面的总体信息。例如：
test=# SELECT * FROM bt_page_stats('large_table_index', 1);
-[ RECORD 1 ]-+-----
blkno | 1
type | l
live_items | 224
dead_items | 0
avg_item_size | 16
page_size | 8192
free_size | 3668
btpo_prev | 0
btpo_next | 0
btpo_level | 0
btpo_flags | 3