PostgreSQL JSONB字段非等值过滤未触发索引问题排查

DevOps 小满

2026-7-11

问题

我有一张PostgreSQL数据库中的mytable表，包含存储JSONB数据的data字段，示例数据如下：

{
  ...
  "myfield": "value1"
}

myfield的取值可能是value1、value2、value3，也可能为null或不存在。

我需要查询myfield不等于value1的行，使用的SQL语句：

select * from mytable where data->>'myfield' <> 'value1'

为加速查询，创建了以下部分索引：

CREATE INDEX "idx_mytable_myfield" ON "mytable" USING btree (
  ((data->>'myfield'::text) COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST)
) WHERE 
  (data->>'myfield'::text)::text <> 'value1'::text

但查询并未使用该索引，且查询选择性高，顺序扫描速度明显更慢。如果将查询和索引改为使用IN而非<>，索引可以正常被使用，但后续新增取值时需要修改查询和索引：

select * from mytable where (data->>'myfield'::text)::text in ('value2'::text,'value3'::text)

我已经执行了以下操作：

每次修改索引后都执行了ANALYZE mytable；
查询具有高选择性；
使用PostgreSQL 12.14版本；
因性能和索引大小问题，不想创建非部分索引或全data字段的GIN索引。

尝试为索引和查询添加额外条件（data非空、myfield非空），但没有效果。执行EXPLAIN分析显示走顺序扫描：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS)
SELECT  
    *
FROM
    mytable
WHERE
    "data" ->> 'myfield'::text <> 'нет'

执行结果：

Seq Scan on set10.mytable  (cost=0.00..2041.22 rows=86911 width=35) (actual time=19.098..35.258 rows=74 loops=1)
  Output: "jiraKey", data, created
  Filter: ((mytable.data ->> 'myfield'::text) <> 'нет'::text)
  Rows Removed by Filter: 87274
  Buffers: shared hit=731
Planning Time: 0.051 ms
Execution Time: 35.278 ms

实验发现问题出在<>操作符，优化器不会选用索引。但禁用顺序扫描后，索引可以正常使用，说明优化器判断错误：

SET enable_seqscan = false;
EXPLAIN (ANALYZE,BUFFERS, COSTS, VERBOSE)
select 
    *
FROM    
    mytable
WHERE
    "data"->>'myfield' <> 'нет'

执行结果：

Bitmap Heap Scan on set10.mytable  (cost=30.20..2064.86 rows=86911 width=35) (actual time=0.024..0.098 rows=74 loops=1)
  Output: "jiraKey", data, created
  Recheck Cond: ((mytable.data ->> 'myfield'::text) <> 'нет'::text)
  Heap Blocks: exact=52
  Buffers: shared hit=53
  ->  Bitmap Index Scan on mytable_myfield_idx  (cost=0.00..8.47 rows=86911 width=0) (actual time=0.013..0.014 rows=74 loops=1)
        Buffers: shared hit=1
Planning Time: 0.059 ms
Execution Time: 0.128 ms

请问我哪里操作有误？能否通过改写查询或索引解决该问题？

分析与解决

问题根源

PostgreSQL优化器对<>操作符的部分索引选择性判断存在偏差，尤其是当查询返回的行数被估算远高于实际行数时（你的执行计划里估算返回86911行，但实际只有74行），优化器会认为顺序扫描更高效，而忽略索引的存在。

另外，你创建的索引定义存在冗余：(data->>'myfield'::text)已经是text类型，无需再做::text转换；同时索引的WHERE条件和查询条件完全一致，但优化器没有正确关联两者的匹配关系。

可行方案

1. 修正索引定义，简化冗余语法

先删除原有索引，重新创建更简洁的部分索引：

DROP INDEX IF EXISTS "idx_mytable_myfield";
CREATE INDEX "idx_mytable_myfield" ON "mytable" 
USING btree ((data->>'myfield') ASC NULLS LAST)
WHERE (data->>'myfield') <> 'value1';

简化后的索引语法更清晰，减少优化器解析时的歧义，同时保持原有部分索引的特性（只包含符合条件的行，控制索引大小）。

2. 改写查询，引导优化器选择索引

可以将<>查询拆分为多个明确的条件组合，让优化器更容易识别到可以使用部分索引：

SELECT * FROM mytable
WHERE 
  (data->>'myfield' IS NULL OR data->>'myfield' = 'value2' OR data->>'myfield' = 'value3')
  OR (data ? 'myfield' = FALSE); -- 处理myfield不存在的情况

这种写法和<> 'value1'逻辑等价，但优化器能更好地匹配到部分索引的条件，同时避免了<>操作符带来的估算偏差。如果后续新增取值，只需要在OR条件里追加新值即可，比全量修改IN列表更灵活。

3. 调整优化器参数（临时方案）

如果不想修改查询或索引，可以临时调整优化器参数，降低顺序扫描的优先级，强制优化器优先考虑索引：

-- 会话级临时设置，只对当前会话生效
SET random_page_cost = 1; -- 降低随机页访问的成本估算，让索引扫描更有竞争力
SET enable_seqscan = OFF; -- 完全禁用顺序扫描（不推荐长期使用）

注意：enable_seqscan = OFF是强制手段，可能会导致其他查询的执行计划变差，建议只在特定查询前临时设置，执行完成后恢复默认值。

4. 更新统计信息，修正行数估算

虽然你已经执行了ANALYZE，但可以尝试更细致的统计信息收集，让优化器更准确地判断行数：

ANALYZE VERBOSE mytable;

如果表数据分布极不均匀，还可以调整default_statistics_target参数后再执行ANALYZE：

SET default_statistics_target = 1000; -- 提高统计信息采样率
ANALYZE mytable;

更准确的行数估算会帮助优化器做出正确的执行计划选择。

总结

优先选择修正索引定义+改写查询的方案，这是最稳定的长期解决方案；临时调整参数可以作为应急手段，但不适合长期使用。优化器的判断偏差主要来自于对<>操作符的行数估算错误，通过明确的条件组合或更准确的统计信息，可以引导优化器正确选择索引扫描。

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