如何在PostgreSQL中通过单条查询重建多个索引？

我可以逐个执行命令重建索引，示例如下：

database=# REINDEX INDEX CONCURRENTLY indexname1;
...
database=# REINDEX INDEX CONCURRENTLY indexname2;
...

但我想通过单条查询完成多个索引的重建，尝试了以下两种写法均报错：

写法一： #

database=# REINDEX INDEX CONCURRENTLY indexname1 indexname2;
ERROR:  syntax error at or near "indexname2"
LINE 1: REINDEX INDEX CONCURRENTLY indexname1 indexname2;
                                                    ^

写法二： #

database=# REINDEX INDEX CONCURRENTLY indexname_*
ERROR:  syntax error at or near "*"
LINE 1: REINDEX INDEX CONCURRENTLY indexname_*
                                             ^

解决方案 #

PostgreSQL的REINDEX INDEX CONCURRENTLY语法不支持直接指定多个索引或使用通配符匹配，要批量执行可以通过动态SQL生成并执行重建命令：

• 生成所有目标索引的重建语句（以匹配indexname_前缀为例）：

SELECT 'REINDEX INDEX CONCURRENTLY ' || quote_ident(indexrelname) || ';'
FROM pg_stat_user_indexes
WHERE indexrelname LIKE 'indexname_%';

这条查询会输出每条索引对应的REINDEX命令，你可以复制这些命令批量执行。

• 如果要直接在数据库中执行生成的命令，可以使用DO语句（注意：CONCURRENTLY无法在事务块中执行，DO本身是单事务，索引较多时建议用生成命令后逐条执行的方式）：

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT quote_ident(indexrelname) AS idx_name
        FROM pg_stat_user_indexes
        WHERE indexrelname LIKE 'indexname_%'
    LOOP
        EXECUTE 'REINDEX INDEX CONCURRENTLY ' || rec.idx_name || ';';
    END LOOP;
END $$;

另外，如果目标索引都属于同一个表，直接用REINDEX TABLE CONCURRENTLY tablename;可以重建该表的所有索引，这种方式更简洁。

内容的提问来源于stack exchange，提问作者AntonioK