查找并清理 INVALID 索引

命令

SELECT n.nspname AS schema_name,
       c.relname AS index_name,
       t.relname AS table_name,
       pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
       i.indisvalid,
       i.indisready,
       format('DROP INDEX CONCURRENTLY %I.%I;', n.nspname, c.relname) AS fix
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid OR NOT i.indisready;

-- Before dropping: is an index build still in progress?
SELECT pid, relid::regclass AS table_name, index_relid::regclass AS index_name, command, phase
FROM pg_stat_progress_create_index;

作用

列出由失败的 CREATE INDEX CONCURRENTLY 或 REINDEX CONCURRENTLY 遗留的无效索引及其大小，并生成删除命令。

适用场景

在线索引操作失败或被取消后、例行健康检查中，以及大版本升级之前。

注意

正在进行的 CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY 也会显示为 INVALID；删除前请检查 pg_stat_progress_create_index。INVALID 索引不会被查询使用，但如果 indisready = true，它在每次 INSERT/UPDATE 时仍会被维护，即纯粹的开销。可以用 REINDEX INDEX CONCURRENTLY 重建，但除非修复了原因（例如唯一性冲突），否则会再次失败。REINDEX CONCURRENTLY 的残留带有 _ccnew/_ccold 后缀。DROP INDEX CONCURRENTLY 不能在事务块中运行。