PostgreSQL 11.2 部分索引使用一段时期后自动失效问题咨询

你遇到的问题核心是PostgreSQL优化器行计数估算严重偏差，加上索引回表成本的计算误差，导致优化器错误选择顺序扫描：

• 从执行计划看，优化器预估符合条件的行有~600万行（占全表15%），但实际只有6万行，差了100倍。优化器默认认为符合条件的行分布非常密集，顺序扫描只要扫少量页面就能凑够LIMIT 100的结果，因此判定顺序扫描成本低于索引扫描。
• COUNT查询能走索引是因为可以走仅索引扫描（不需要回表取数据），成本远低于顺序扫描；而你查询id、name的时候需要回表取堆元组，优化器计算随机IO回表的成本后，更倾向于选顺序扫描。
• 重建索引临时生效的原因：刚重建的索引会更新pg_class系统表中该索引的reltuples计数（和实际7万多的匹配行数一致），此时优化器能拿到更准确的匹配行数，会选择索引。但运行一段时间后，日常VACUUM ANALYZE不会主动更新部分索引的元组计数统计，统计信息逐步老化，估算偏差又会变大，就回到顺序扫描的情况。
• 后期重建索引也不生效的原因：全表统计信息中两个布尔字段的联合占比估算偏差已经过大，即使索引元组计数正确，优化器依然会高估匹配行数，判定顺序扫描成本更低。

1. 检查字段统计信息精度：执行如下SQL查看两个布尔字段的统计值是否和实际占比一致：

SELECT attname, n_distinct, most_common_vals, most_common_freqs 
FROM pg_stats 
WHERE tablename = 'my_table' AND attname IN ('action_performed', 'should_still_perform_action');

正常情况下action_performed=true且should_still_perform_action=false的联合占比应该只有0.18%左右（7万/4000万），如果统计出来的频率远高于这个值，就是统计信息精度不足。
2. 检查部分索引的元组计数：执行如下SQL看索引的reltuples是否和实际的7万匹配行数一致：

SELECT relname, reltuples FROM pg_class WHERE relname = 'my_index';

3. 检查IO成本参数配置：执行show random_page_cost;查看参数值，默认值4是针对机械硬盘的配置，如果你的存储是SSD，该值过高会导致优化器低估索引扫描的性价比。

1. 永久修复：创建覆盖索引（优先推荐） #

你当前的索引需要回表取id、name，是优化器不愿意选索引的核心原因之一，直接创建覆盖索引，完全避免回表，优化器会优先选择该索引：

-- 用CONCURRENTLY创建不会锁表，适合生产环境
CREATE INDEX CONCURRENTLY my_index_covering ON my_table 
USING btree (action_performed_at DESC)
INCLUDE (id, name) -- 直接包含查询需要的字段，不需要回表
WHERE should_still_perform_action = false AND action_performed = true;

注意：部分索引的谓词已经固定了两个布尔字段的取值，不需要再把这两个字段放到索引键中，能大幅减少索引体积，提升查询效率。

2. 修正统计信息偏差 #

如果不想重建索引，可以通过提升统计精度修正优化器的估算误差：

• 提升单字段统计目标：

ALTER TABLE my_table ALTER COLUMN action_performed SET STATISTICS 1000;
ALTER TABLE my_table ALTER COLUMN should_still_perform_action SET STATISTICS 1000;

• 创建多列扩展统计，让优化器知道两个字段不是独立分布的，不会直接相乘计算占比：

CREATE STATISTICS my_table_action_stats (dependencies, ndistinct) 
ON action_performed, should_still_perform_action 
FROM my_table;

执行完以上操作后执行ANALYZE my_table;更新统计信息即可。

3. 调整IO成本参数 #

如果使用SSD存储，把random_page_cost调整为1~1.5，让优化器更倾向于选择索引：

-- 全局生效，需要重启数据库
ALTER SYSTEM SET random_page_cost = 1.1;
-- 也可以会话级别临时生效，不需要重启
SET random_page_cost = 1.1;

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