PostgreSQL中如何为NOT IN查询创建可命中的有效索引

API 探路者

2026-8-27

PostgreSQL NOT IN查询不走索引的解决方案

现有索引不生效的核心原因

普通B树索引对NOT IN这类否定过滤条件的支持优先级极低。PostgreSQL优化器会基于表统计信息估算执行成本：当NOT IN过滤后需要返回的行数占全表比例超过15%~20%时，走B树索引需要先扫描索引拿到匹配行位置，再回表取整行数据，会产生大量随机IO，成本远高于全表顺序扫描的连续IO。从你给出的执行计划看，全表数据量约1.6万行，过滤后返回16116行，说明DELETED、SUCCESS两个状态的行总占比不足2%，这种场景下全表扫描本身就是优化器选择的最优路径，执行时间仅12ms，已经属于非常好的性能水平。
你创建的原生GIN索引对当前场景完全无效。原生GIN索引是为数组、全文检索、JSONB等复合值类型设计的，不支持text/varchar这类普通标量类型的常规比较查询，除非提前启用btree_gin扩展，否则该索引不会被普通等值/不等值查询调用。

不需要做任何索引调整，全表扫描已经是性能最优的选择，强制走索引反而会因为大量随机回表IO拖慢查询速度。

可以选择以下方案让查询命中索引，避免全表扫描：

方案1：创建适配固定查询条件的部分索引，这是性能最高的方案
部分索引只会存储符合过滤条件的行，索引体积极小，查询时直接扫描索引即可拿到所有目标数据，不需要扫描全表或者回表过滤：
```
CREATE INDEX idx_expenses_state_valid ON expenses (state)
WHERE state NOT IN ('DELETED','SUCCESS');
```
只要你的查询过滤条件和索引定义中的WHERE子句匹配，优化器会自动选择该索引，性能远高于普通全量索引。
方案2：如果需要灵活支持不同的NOT IN值组合，不想为每个固定条件建部分索引，可以创建支持标量类型的GIN索引
首先启用btree_gin扩展（需要数据库超级用户权限），让GIN索引支持普通标量值的查询：
```
CREATE EXTENSION IF NOT EXISTS btree_gin;
-- 删除之前无效的原生GIN索引，重建支持标量查询的GIN索引
DROP INDEX IF EXISTS testttt2;
CREATE INDEX idx_expenses_state_gin ON expenses USING GIN(state);
```
这类GIN索引对多值组合的IN/NOT IN查询适配性更好，适合查询条件经常变化的场景，缺点是索引写入维护成本比普通B树索引高。
方案3：改写查询为等价的正向条件匹配，复用现有普通B树索引
可以把NOT IN改写为正向等值组合，引导优化器走你已经创建的普通B树索引testttt，但这种写法的灵活度较低，需要枚举state字段所有需要返回的取值，不适合枚举值较多的场景。

注意：不要盲目追求"查询必须命中索引"，优化器选择执行路径的核心判断标准是综合IO、CPU成本最低，而非刻意走索引。如果过滤后返回的行占全表比例较高，全表扫描的性能会显著优于索引扫描。

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