使用 REINDEX 识别并修复 PostgreSQL 索引问题

PostgreSQL 索引可能由于各种原因而损坏，包括软件错误、硬件故障或意外的重复数据。REINDEX 允许您在这种情况下重建索引。

您可以重建定义中不包含 UNIQUE 的损坏索引。您可以对表的所有索引 (REINDEX TABLE) 以及整个数据库中的所有索引 (REINDEX DATABASE) 运行 REINDEX 命令。有关 REINDEX 命令的更多信息，请参阅 PostgreSQL 文档。

此命令将创建一个新索引来替换旧索引

REINDEX INDEX <index-name>;

复制

注意

当您使用 REINDEX 时，表会被锁定，您可能无法使用数据库，直到操作完成。

在某些情况下，您可能需要手动与旧索引同时构建第二个索引，然后删除旧索引

CREATE INDEX CONCURRENTLY test_index_new ON table_a (...);
DROP INDEX CONCURRENTLY test_index_old;
ALTER INDEX test_index_new RENAME TO test_index;

复制

UNIQUE 索引作用于一个或多个列，其中这些列的组合在表中是唯一的。当索引损坏或禁用时，表中会出现重复的物理行，从而破坏了索引的唯一性约束。当您尝试重建非唯一索引时，REINDEX 命令会失败。要解决此问题，请首先从表中删除重复行，然后重建索引。

要识别冲突的重复行，您需要运行一个查询，该查询计算索引定义中包含的每列组合的行数。

例如，此 route 表有一个 unique_route_index 索引，它根据 source 和 destination 列的组合定义唯一行

CREATE TABLE route(
    source TEXT,
    destination TEXT,
    description TEXT
    );

CREATE UNIQUE INDEX unique_route_index
    ON route (source, destination);

复制

如果 unique_route_index 已损坏，您可以使用此查询在 route 表中找到重复行

SELECT
    source,
    destination,
    count
FROM
    (SELECT
        source,
        destination,
        COUNT(*) AS count
    FROM route
    GROUP BY
        source,
        destination) AS foo
WHERE count > 1;

复制

该查询按索引中定义的相同 source 和 destination 字段对数据进行分组，并过滤掉出现次数超过一次的任何条目。

通过手动删除或合并条目来解决行中存在问题（重复）的条目，直到不存在重复项。删除所有重复条目后，您可以使用 REINDEX 命令重建索引。