PostgreSQL 9.6执行NOT类查询时规划器不选择B-tree索引问题咨询

问题1：索引未生效的原因 #

• 首先是回表开销预估：PostgreSQL的查询规划器会对比不同执行路径的开销，你要查询type等不在索引中的字段时，如果走普通B树索引，需要先扫描索引拿到符合条件的行指针，再去堆表中读取对应行的其他字段，属于随机IO操作。你当前表中99%以上的记录都是COMPLETED状态，state != 'COMPLETED'条件返回的结果行数统计上极少，但规划器默认的成本模型会认为：需要扫描整个索引（因为是不等于条件）再回表的开销，比直接顺序扫描全表更高，所以选择了顺序扫描。
• 其次是B树索引对否定条件的支持限制：B树索引天然对等值匹配、前缀匹配、范围匹配支持更好，不等于、NOT这类否定条件需要遍历整个索引树的非目标区间，扫描效率本身低于等值查询。
• 部分索引未生效是因为谓词匹配严格性要求：PostgreSQL 9.6对部分索引的查询匹配要求查询条件和索引的谓词严格对应，你建的部分索引谓词是state NOT IN('COMPLETED', 'FAILED', 'CANCELLED')，但查询条件是state != 'COMPLETED'，二者语义不一致，规划器无法识别可以复用该部分索引。
• 自定义函数索引未生效是因为否定表达式匹配限制：9.6对函数表达式的否定匹配支持度很低，NOT 函数(字段)的条件无法和函数(字段)的表达式索引匹配，只有查询条件完全和索引表达式一致（比如request_is_done(state) = false）才可能触发索引。
• 而state = 'CREATED'能走索引的原因是：统计信息能明确识别CREATED值的出现占比极低，回表开销远低于全表扫描，规划器自然选择走索引。

问题2：确保索引生效的可行方案 #

• 方案1：使用复合索引（适配9.6版本无INCLUDE语法的场景）
  直接把你查询需要用到的字段都加到索引中，变成覆盖索引，避免回表开销：

  CREATE INDEX idx_request_state_covering ON request.request(state, type, 其他需要查询的字段);
  

  建完后不管是等值还是否定条件，只要查询的字段都在索引里，就能触发索引仅扫描，性能会远高于全表扫描。
• 方案2：调整查询逻辑避免否定条件
  就像你测试的那样，直接用IN罗列所有非终止态的值，比如：

  WHERE state IN ('CREATED', 'PAID', 'PROCESSING'...)
  

  等值IN查询完全适配B树索引的特性，规划器的成本预估也更准确，必然会走索引。
• 方案3：严格对齐部分索引的查询谓词
  如果你绝大多数查询都是过滤终止态找未完成的记录，直接建如下部分索引：

  CREATE INDEX request_incomplete_state_index ON request.request (state) WHERE state NOT IN('COMPLETED', 'FAILED', 'CANCELLED');
  

  查询时严格使用和索引谓词完全一致的条件：WHERE state NOT IN('COMPLETED', 'FAILED', 'CANCELLED')，规划器就能匹配到这个极小的部分索引，查询速度极快。
• 方案4：增加冗余字段降低查询复杂度
  新增一个is_done布尔类型的冗余字段，写入/更新数据时自动维护该字段值，直接在该字段上建索引：

  ALTER TABLE request.request ADD COLUMN is_done boolean NOT NULL DEFAULT false;
  CREATE INDEX idx_request_is_done ON request.request(is_done) WHERE is_done = false;
  

  查询时直接用WHERE is_done = false，性能最优，维护成本也很低。

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