云数据库 PostgreSQL 索引失效

一、功能背景

数据库管理员在面临版本发布或非常规运维的场景时，需要一种快速的索引控制手段，在不删除索引的前提下使得指定索引失效，以便在升级验证、灰度测试或紧急排查时灵活评估索引对查询性能的影响，同时避免因索引维护带来的写入开销或触发已知 bug，保障系统在调试过程中的稳定性与数据完整性，提升运维效率。

二、功能介绍

云数据库 PostgreSQL 支持索引失效内核功能，该功能允许用户通过标准 SQL 命令临时禁用或启用索引，且无需修改系统表权限。该功能支持事务回滚、分区表独立控制，且禁用期间索引数据持续维护，启用后无需重建。

三、核心语法

— 禁用索引（立即生效，索引对优化器不可见）ALTER INDEX idx_name UNUSABLE;– 启用索引（无需重建，索引重新对优化器可见）ALTER INDEX idx_name USABLE;

四、适用场景

需要临时禁用或启用索引，以提升运维效率的运维场景。

五、注意事项

·分区表处理：索引 USABLE/UNUSABLE 不支持递归操作，分区表需要逐个处理子分区。

·版本升级：大版本升级时，设置了 UNUSABLE 的索引会被删掉。

·REINDEX 行为：普通表设置成 UNUSABLE 后，REINDEX 会直接变成 USABLE，但分区表的父表会维持 UNUSABLE。

·与 pg_hint_plan 配合：可以结合 pg_hint_plan 指定索引使用策略。

六、支持版本

注意：说明：

注意：你可在控制台实例详情页或执行 SHOW tencentdb_version; 查看当前实例的内核版本。

九、步骤1：创建测试表

— 输入CREATE TABLE test_table (id int, val text);

— 输出CREATE TABLE;

十、步骤2：插入测试数据

— 输入INSERT INTO test_table SELECT i, 'val' || i FROM generate_series(1, 1000) i;

— 输出INSERT 0 1000;

十一、步骤3：创建索引

— 输入CREATE INDEX idx_test_id ON test_table (id);

— 输出CREATE INDEX;

十二、步骤4：更新统计信息

— 输入ANALYZE test_table;

— 输出ANALYZE;

十三、步骤5：查看索引初始状态（indisvalid = true 表示索引有效）

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid = 'idx_test_id'::regclass;

— 输出 index_name | indisvalid ————-+———— idx_test_id | t(1 row);

十四、步骤6：查看当前查询计划（使用 Index Scan）

— 输入EXPLAIN (COSTS OFF) SELECT * FROM test_table WHERE id = 100;

— 输出 QUERY PLAN ————————————— Index Scan using idx_test_id on test_table Index Cond: (id = 100)(2 rows);

十五、步骤7：禁用索引

— 输入ALTER INDEX idx_test_id UNUSABLE;

— 输出ALTER INDEX;

十六、步骤8：确认索引已禁用（indisvalid = false）

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid = 'idx_test_id'::regclass;

— 输出 index_name | indisvalid ————-+———— idx_test_id | f(1 row);

十七、步骤9：查看查询计划（改为 Seq Scan，不再使用索引）

— 输入EXPLAIN (COSTS OFF) SELECT * FROM test_table WHERE id = 100;

— 输出 QUERY PLAN ——————————- Seq Scan on test_table Filter: (id = 100)(2 rows);

十八、步骤10：重新启用索引

— 输入ALTER INDEX idx_test_id USABLE;

— 输出ALTER INDEX;

十九、步骤11：确认索引已启用（indisvalid = true）

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid = 'idx_test_id'::regclass;

— 输出 index_name | indisvalid ————-+———— idx_test_id | t(1 row);

20、步骤12：查看查询计划（恢复使用 Index Scan）

— 输入EXPLAIN (COSTS OFF) SELECT * FROM test_table WHERE id = 100;

— 输出 QUERY PLAN ————————————— Index Scan using idx_test_id on test_table Index Cond: (id = 100)(2 rows);

22、步骤1：创建分区表

— 输入CREATE TABLE test_part (id int, val text) PARTITION BY RANGE (id);

— 输出CREATE TABLE;

23、步骤2：创建分区

— 输入CREATE TABLE test_part_p1 PARTITION OF test_part FOR VALUES FROM (1) TO (500);CREATE TABLE test_part_p2 PARTITION OF test_part FOR VALUES FROM (500) TO (1001);

— 输出CREATE TABLECREATE TABLE;

24、步骤3：插入测试数据

— 输入INSERT INTO test_part SELECT i, 'val' || i FROM generate_series(1, 1000) i;

— 输出INSERT 0 1000;

25、步骤4：创建分区索引（会自动在各分区创建子索引）

— 输入CREATE INDEX idx_test_part_id ON test_part (id);

— 输出CREATE INDEX;

26、步骤5：更新统计信息

— 输入ANALYZE test_part;

— 输出ANALYZE;

27、步骤6：查看所有索引状态（父索引和子索引都有效）

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid::regclass::text LIKE '%test_part%';

— 输出 index_name | indisvalid ————————-+———— idx_test_part_id | t test_part_p1_id_idx | t test_part_p2_id_idx | t(3 rows);

28、步骤7：禁用父索引（注意：子索引不受影响）

— 输入ALTER INDEX idx_test_part_id UNUSABLE;

— 输出ALTER INDEX;

29、步骤8：查看索引状态（父索引禁用，子索引仍有效）

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid::regclass::text LIKE '%test_part%';

— 输出 index_name | indisvalid ————————-+———— idx_test_part_id | f test_part_p1_id_idx | t test_part_p2_id_idx | t(3 rows);

30、步骤9：单独禁用 p1 分区的索引

— 输入ALTER INDEX test_part_p1_id_idx UNUSABLE;

— 输出ALTER INDEX;

31、步骤10：查看索引状态（p1 索引禁用，p2 索引仍有效）

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid::regclass::text LIKE '%test_part%';

— 输出 index_name | indisvalid ————————-+———— idx_test_part_id | f test_part_p1_id_idx | f test_part_p2_id_idx | t(3 rows);

32、步骤11：查询 p1 分区数据的执行计划（使用 Seq Scan）

— 输入EXPLAIN (COSTS OFF) SELECT * FROM test_part WHERE id = 100;

— 输出 QUERY PLAN ——————————————————- Seq Scan on test_part_p1 test_part Filter: (id = 100)(2 rows);

33、步骤12：查询 p2 分区数据的执行计划（使用 Index Scan）

— 输入EXPLAIN (COSTS OFF) SELECT * FROM test_part WHERE id = 900;

— 输出 QUERY PLAN ——————————————————————————- Index Scan using test_part_p2_id_idx on test_part_p2 test_part Index Cond: (id = 900)(2 rows);

35、步骤1：确保索引处于启用状态

— 输入ALTER INDEX idx_test_id USABLE;

— 输出ALTER INDEX;

36、步骤2：查看当前状态

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid = 'idx_test_id'::regclass;

— 输出 index_name | indisvalid ————-+———— idx_test_id | t(1 row);

37、步骤3：开始事务

— 输入BEGIN;

— 输出BEGIN;

38、步骤4：在事务中禁用索引

— 输入ALTER INDEX idx_test_id UNUSABLE;

— 输出ALTER INDEX;

39、步骤5：查看事务中的状态（索引已禁用）

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid = 'idx_test_id'::regclass;

— 输出 index_name | indisvalid ————-+———— idx_test_id | f(1 row);

40、步骤6：回滚事务

— 输入ROLLBACK;

— 输出ROLLBACK;

41、步骤7：查看回滚后的状态（索引恢复为启用）

— 输入SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indexrelid = 'idx_test_id'::regclass;

— 输出 index_name | indisvalid ————-+———— idx_test_id | t(1 row);

43、步骤1：禁用索引

— 输入ALTER INDEX idx_test_id UNUSABLE;

— 输出ALTER INDEX;

44、步骤2：在索引禁用期间插入新数据

— 输入INSERT INTO test_table VALUES (9999, 'new_value');

— 输出INSERT 0 1;

45、步骤3：重新启用索引

— 输入ALTER INDEX idx_test_id USABLE;

— 输出ALTER INDEX;

46、步骤4：验证新数据可以通过索引查询到

— 输入EXPLAIN (COSTS OFF) SELECT * FROM test_table WHERE id = 9999;

— 输出 QUERY PLAN ————————————— Index Scan using idx_test_id on test_table Index Cond: (id = 9999)(2 rows);

47、步骤5：执行查询确认数据存在

— 输入SELECT * FROM test_table WHERE id = 9999;

— 输出 id | val ——+———– 9999 | new_value(1 row);

注意：说明：

注意：索引禁用期间插入的数据会继续维护索引结构，索引启用后无需 REINDEX 即可正常使用。