使用 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 命令重建索引。