PostgreSQL 教程: 检查要重建的索引

一月 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