Postgresql 中文操作指南

25.2. Routine Reindexing #

在某些情况下，值得使用 REINDEX 命令或一系列单独的重建步骤定期重建索引。

In some situations it is worthwhile to rebuild indexes periodically with the REINDEX command or a series of individual rebuilding steps.

已变得完全空的 B 树索引页将被收回以供重新使用。但是，空间仍然有可能使用效率低下：如果一个页面上的所有除少数几个索引键之外的键都被删除，则页面仍然分配。因此，最终删除每个范围中大多数但不全部的键的用法模式将导致空间利用不佳。对于这样的用法模式，建议定期重新索引。

B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility of inefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated. Therefore, a usage pattern in which most, but not all, keys in each range are eventually deleted will see poor use of space. For such usage patterns, periodic reindexing is recommended.

尚未很好地研究非 B 树索引中膨胀的可能性。在使用任何非 B 树索引类型时，定期监视索引的物理大小是一个好主意。

The potential for bloat in non-B-tree indexes has not been well researched. It is a good idea to periodically monitor the index’s physical size when using any non-B-tree index type.

另外，对于 B 树索引，新构建的索引比已经多次更新的索引访问速度稍快，因为在新建索引中，逻辑上相邻的页面通常也在物理上相邻。（此考虑不适用于非 B 树索引。）定期重新索引以提高访问速度可能是值得的。

Also, for B-tree indexes, a freshly-constructed index is slightly faster to access than one that has been updated many times because logically adjacent pages are usually also physically adjacent in a newly built index. (This consideration does not apply to non-B-tree indexes.) It might be worthwhile to reindex periodically just to improve access speed.

REINDEX 在所有情况下都可以安全轻松地使用。默认情况下，此命令需要 ACCESS EXCLUSIVE 锁，因此，通常最好使用其 CONCURRENTLY 选项执行该命令，该选项只需要 SHARE UPDATE EXCLUSIVE 锁。

REINDEX can be used safely and easily in all cases. This command requires an ACCESS EXCLUSIVE lock by default, hence it is often preferable to execute it with its CONCURRENTLY option, which requires only a SHARE UPDATE EXCLUSIVE lock.