REINDEX — 重建索引
REINDEX [ (option
[, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ]name
REINDEX [ (option
[, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [name
] whereoption
can be one of: CONCURRENTLY [boolean
] TABLESPACEnew_tablespace
VERBOSE [boolean
]
REINDEX
使用存储在索引表中的数据重建索引,替换索引的旧副本。使用 REINDEX
有以下几种情况
索引已损坏,不再包含有效数据。尽管理论上这种情况永远不会发生,但由于软件错误或硬件故障,索引在实践中可能损坏。 REINDEX
提供了一种恢复方法。
索引变得““膨胀””,即包含许多空页面或几乎为空的页面。在 PostgreSQL 中,这种情况可能会发生在 B 树索引上,这取决于某些不太常见的访问模式。 REINDEX
提供了一种通过写入新版本的索引(不包含死页面)来减少索引空间消耗的方法。有关更多信息,请参见 第 24.2 节。
您已更改某个索引的存储参数(如填充因子),并希望确保更改已完全生效。
如果索引构建在 CONCURRENTLY
选项的情况下失败,则该索引会保留为““无效””。此类索引无用,但使用 REINDEX
重新构建它们很方便。注意,只有 REINDEX INDEX
能够在无效索引上执行并发构建。
INDEX
重新创建指定的索引。在与分区索引一起使用时,REINDEX
的此表单不能在事务块内执行。
TABLE
重新创建指定表的所有索引。如果该表有一个附属的““TOAST””表,该表也会被重新索引。在与分区表一起使用时,REINDEX
的此表单不能在事务块内执行。
SCHEMA
重新创建指定架构的所有索引。如果该架构的表有一个附属的““TOAST””表,该表也会被重新索引。还将处理共享系统目录上的索引。不能在事务块内执行此 REINDEX
表单。
DATABASE
重新创建当前数据库中的所有索引,但系统目录除外。系统目录上的索引未处理。不能在事务块内执行此 REINDEX
表单。
SYSTEM
重新创建当前数据库中系统目录上的所有索引。包括共享系统目录上的索引。不处理用户表上的索引。不能在事务块内执行此 REINDEX
表单。
name
要重新建立索引的特定索引、表或数据库的名称。索引和表名称可以由模式限定。目前,REINDEX DATABASE
和 REINDEX SYSTEM
只可以重新建立当前数据库的索引。它们的参数是可选的,并且必须与当前数据库的名称相匹配。
CONCURRENTLY
当使用此选项时,PostgreSQL 将在不采用任何对表上的并发插入、更新或删除操作进行阻止的锁的情况下重建索引;同时标准索引重建将在完成之前锁定对表的操作(但不阻止读取)。在使用此选项时需要关注一些注意事项 — 如下文所述,请参见同时重建索引。
对于临时表,REINDEX
始终是非并发操作,因为没有其他会话可以对其进行访问,非并发重新编制索引的成本较低。
TABLESPACE
指定将在新的表空间上重新建立索引。
VERBOSE
在重新建立每个索引时打印进度报告。
boolean
指定是否应该启用或禁用所选选项。你可以编写 TRUE
、ON
或 1
来启用选项,编写 FALSE
、OFF
或 0
来禁用选项。boolean
值也可以省略,在这种情况下假设为 TRUE
。
new_tablespace
将重建索引所在的表空间。
如果你怀疑用户表上的索引已损坏,你可以使用 REINDEX INDEX
或 REINDEX TABLE
简单地重新建立该索引或表上的所有索引。
如果你需要从系统表上的索引损坏中恢复,情况会变得更加困难。在这种情况下,对于系统来说重要的是,它不会使用任何可疑索引本身。(事实上,在这种情况下你可能会发现由于依赖损坏的索引,服务器进程在启动时立即崩溃。)为了安全地恢复,必须使用 -P
选项启动服务器,该选项会阻止它使用索引进行系统目录查找。
解决此问题的一种方法是关闭服务器并使用带有 -P
选项的命令行启动单用户 PostgreSQL 服务器。然后可以签发 REINDEX DATABASE
、REINDEX SYSTEM
、REINDEX TABLE
或 REINDEX INDEX
,具体取决于您要重建的量。如有疑问,请使用 REINDEX SYSTEM
选择重建数据库中的所有系统索引。然后退出单用户服务器会话并重新启动常规服务器。请参阅 postgres 参考页了解更多有关如何与单用户服务器界面进行交互的信息。
或者,可以通过在命令行选项中包含 -P
来启动常规服务器会话。这个方法因不同的客户端而异,但在所有基于 libpq 的客户端中,有可能在启动客户端之前将 PGOPTIONS
环境变量设置为 -P
。请注意,尽管这种方法不要求锁定其他客户端,但最好还是在修复完成之前阻止其他用户连接到损坏的数据库。
REINDEX
与删除并重新创建索引类似,因为索引内容将从头开始重新构建。但是,锁定注意事项有所不同。REINDEX
会锁定索引父表的写入,但不锁定索引的读取。它还对正在处理的特定索引采用 ACCESS EXCLUSIVE
锁定,这会阻止尝试使用该索引的读取。特别是,无论查询是什么,查询计划程序都会尝试对表的每个索引采用 ACCESS SHARE
锁定,因此 REINDEX
几乎所有查询都将被阻止,除了计划已缓存并且不使用此索引的一些准备好的查询。而 DROP INDEX
会暂时对父表采用 ACCESS EXCLUSIVE
锁定,从而阻止写入和读取。随后的 CREATE INDEX
会锁定写但不会锁定读,因为索引不存在,所以没有读取将尝试使用它,这意味着不会进行阻塞,但读取可能会强制进入昂贵的顺序扫描。
在 REINDEX
运行时,search_path 会暂时更改为 pg_catalog, pg_temp
。
重新编制单个索引或表需要对数据库拥有MAINTAIN
权限。请注意,尽管对已分区的索引或表执行REINDEX
需要对已分区的表拥有MAINTAIN
权限,但此类命令在处理各个分区时会跳过权限检查。重新编制模式或数据库需要拥有该模式或数据库的所有权,或拥有pg_maintain
角色的权限。特别注意,这使得非超级用户可以重建其他用户拥有的表的索引。但作为一个特殊例外,REINDEX DATABASE
、REINDEX SCHEMA
和REINDEX SYSTEM
将会跳过共享目录中的索引,但前提是用户没有该目录的MAINTAIN
权限。
已分区的索引或已分区的表重新编制可在REINDEX INDEX
中获得支持或在REINDEX TABLE
中分别获得支持。指定已分区关系的每个分区将在单独的事务中重新编制。处理已分区表或索引时,无法在事务块内使用这些命令。
将TABLESPACE
子句与REINDEX INDEX
或REINDEX TABLE
一起在已分区索引或表上使用时,只会更新子分区表的表空间引用。由于已分区索引未更新,建议对其另外使用ALTER TABLE ONLY
,以便任何附加的新分区都能继承新的表空间。如果失败,它可能未将所有索引移动到新表空间。重新运行命令将重建所有子分区,并将先前未处理的索引移动到新表空间。
如果将SCHEMA
、DATABASE
或 SYSTEM
与 TABLESPACE
一起使用,则将跳过系统关系,并且只会生成一条WARNING
。TOAST 表中的索引会重建,但不会移动到新表空间。
重建索引会干扰数据库的常规操作。通常PostgreSQL 会锁定正在重建其索引的表并禁止写入,并通过对表进行单次扫描来执行整个索引的构建。其他事务仍然可以读取该表,但如果它们尝试插入、更新或删除表中的行,则直到索引重建完成,它们会一直被阻止。如果该系统是实时生产数据库,这可能会产生严重的影响。非常大的表可能需要花费很多时间才能编入索引,而即使是对于较小的表,索引重建也可能禁止写入者进入的时间段对生产系统而言过长。
PostgreSQL 支持在对写进行最少锁定时重新构建索引。此方法通过指定
的 REINDEX
选项来调用。当使用此选项时,PostgreSQL 必须对每个需重建的索引对表进行两次扫描,并且等待所有可能使用该索引的现有事务终止。此方法所需总工作量比标准索引重建多,并且需要花费大量时间才能完成,因为它需要等待可能修改索引的未完成事务。然而,由于它允许在重建索引时继续执行正常操作,此方法对在生产环境中重建索引非常有用。当然,索引重建产生的额外 CPU、内存和 I/O 负载可能会减慢其他操作。CONCURRENTLY
以下步骤在并发重新索引中发生。每个步骤在单独的事务中运行。如果存在要重建的多个索引,则每个步骤会在移动到下一步之前遍历所有索引。
向目录
添加新的临时索引定义。此定义将用于替换旧索引。对正在重新索引的索引及其关联的表在会话级别获取 pg_index
锁定,以防止在处理时对架构进行任何修改。SHARE UPDATE EXCLUSIVE
为每个新索引进行第一次构建索引的遍历。构建索引后,它的标记
会切换到 “true”,以使其准备好插入,并在执行构建的事务结束后对其他会话可见。此步骤对每个索引在单独的事务中进行。pg_index.indisready
然后进行第二次遍历以添加在第一次遍历运行时添加的元组。此步骤也对每个索引在单独的事务中进行。
引用该索引的所有约束都会更改为引用新索引定义,并更改索引的名称。此时,
会对新索引切换至 “true”,对旧索引切换至 “false”,并且执行缓存无效化,导致引用旧索引的所有会话都会失效。pg_index.indisvalid
旧索引在等待可能引用旧索引的正在运行查询完成后,会将
切换至 “false” 以防止任何新的元组插入。pg_index.indisready
删除旧索引。释放用于索引和表的
会话锁。SHARE UPDATE EXCLUSIVE
如果重建索引时出现问题,如唯一索引中发生唯一性冲突,则 REINDEX
命令将失败,但会另外留下一个 “无效” 新索引,附加于已存在的索引。由于该索引可能不完整,因此它在查询中将被忽略;但它仍然会消耗更新开销。 psql \d
命令会将此类索引报告为 INVALID
postgres=# \d tab Table "public.tab" Column | Type | Modifiers --------+---------+----------- col | integer | Indexes: "idx" btree (col) "idx_ccnew" btree (col) INVALID
如果标记为 INVALID
的索引附加了后缀 ccnew
,则它对应在并发操作过程中创建的瞬态索引,建议的恢复方法是使用 DROP INDEX
将其删除,然后再次尝试 REINDEX CONCURRENTLY
。如果无效索引附加了后缀 ccold
,则它对应无法删除的原始索引;建议的恢复方法是直接删除此索引,因为已正确完成重建。
普通索引构建允许同时对同一张表构建其他普通索引,但同一张表一次只能进行一次并发索引构建。在这两种情况下,与此同时不允许对表进行其他类型的架构修改。另一个区别在于,普通 REINDEX TABLE
或 REINDEX INDEX
命令可在事务块中执行,但 REINDEX CONCURRENTLY
不行。
与任何长时间运行的事务一样,对一张表的 REINDEX
可能会影响哪些元组可以被其他任何表的并发 VACUUM
删除。
REINDEX SYSTEM
不支持 CONCURRENTLY
,因为无法并发重新索引系统编录。
此外,排它约束的索引无法并发重新索引。如果在此命令中直接指定了此类索引,则会引发错误。如果对具有排它约束索引的表或数据库进行并发重新索引,则将跳过这些索引。(可以通过不使用 CONCURRENTLY
选项来重新索引这些索引。)
每个运行 REINDEX
的后端都会在 pg_stat_progress_create_index
视图中报告其进度。有关详细信息,请参阅 第27.4.4节。
重建单个索引
REINDEX INDEX my_index;
重建表 my_table
上的所有索引
REINDEX TABLE my_table;
重建特定数据库中的所有索引,而不信任系统索引已经有效
$export PGOPTIONS="-P"
$psql broken_db
... broken_db=> REINDEX DATABASE broken_db; broken_db=> \q
在重新索引时不阻塞相关关系上的读写操作,为一张表重建索引
REINDEX TABLE CONCURRENTLY my_broken_table;
SQL 标准中没有 REINDEX
命令