问题:如何删除 postgres 中的所有无效索引?

使用此查询:

我有数百个无效索引:

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

 public  | post_aggregates_pkey_ccnew_ccnew_ccnew1
 public  | post_aggregates_post_id_key_ccnew_ccnew_ccnew1
 public  | idx_post_aggregates_stickied_hot_ccnew_ccnew_ccnew1
 public  | idx_post_aggregates_hot_ccnew_ccnew_ccnew1
...

它们似乎没有被使用,我不知道为什么要创建它们(在我看来它们不应该保留),因为原始索引仍然存在。

解答

您需要动态命令在函数内或匿名代码块。

do $$
declare
    rec record;
begin
    for rec in
        select relnamespace::regnamespace as namespace, relname
        from pg_index i
        join pg_class c on c.oid = i.indexrelid
        where not indisvalid
    loop
        execute format('drop index %s.%s', rec.namespace, rec.relname);
        -- optionally:
        -- raise notice '%', format('drop index %s.%s', rec.namespace, rec.relname);
    end loop;
end $$;

Postgres 在CREATE TABLE或ALTER TABLE中创建或更改表约束时自动创建索引。除此之外,它从不自行创建索引。

无效索引的最可能原因是不小心使用了CREATE [UNIQUE] INDEX CONCURRENTLY命令。在并行事务中执行命令时,极有可能出现死锁,从而导致命令失败并留下无效索引。同时创建唯一索引时,违反唯一性也可能导致失败。

并发索引应该受到意识到这些问题的管理员的严格控制,特别是当它定期自动执行时。

在文档中阅读更多内容。