PostgreSQL 13.6 jsonb字段布尔类型转换索引无法命中问题

问题场景 #

环境：PostgreSQL 13.6（Cloud SQL）
jsonb类型字段使用中存在如下索引命中差异：

• 布尔值以字符串形式"true"/"false"存储，按字符串匹配查询时，索引可正常命中
• 将jsonb提取的文本值转换为原生布尔类型查询时，索引无法生效

未命中索引的测试用例 #

索引创建语句：

CREATE INDEX IF NOT EXISTS my_index_name 
ON my_table (field_1, field_2, ((additional_information->>'my_boolean')::bool));

对应查询语句：

SELECT *
FROM public.my_table
WHERE my_table.field_1=2644
  AND (my_table.field_2 IS NOT NULL)
  AND (my_table.additional_information->>'my_boolean')::boolean=FALSE

执行计划为全表顺序扫描，耗时139.721ms：

Seq Scan on my_table (cost=0.00..42024.26 rows=66494 width=8) (actual time=0.169..139.492 rows=2760 loops=1)
  Filter: ((field_2 IS NOT NULL) AND (field_1 = 2644) AND (NOT ((additional_information ->> 'my_boolean'::text))::boolean))
  Rows Removed by Filter: 273753
  Buffers: shared hit=14400 read=22094
Planning Time: 0.464 ms
Execution Time: 139.721 ms

正常命中索引的测试用例 #

创建文本匹配的表达式索引：

CREATE INDEX IF NOT EXISTS my_index_name_text 
ON my_table (field_1, field_2, (additional_information->>'my_boolean'));

对应查询改为字符串匹配：

SELECT *
FROM public.my_table
WHERE my_table.field_1=2644
  AND (my_table.field_2 IS NOT NULL)
  AND (my_table.additional_information->>'my_boolean' = 'true')

执行计划为索引扫描，耗时仅7.241ms：

Index Scan using my_index_name_text on my_table (cost=0.42..5343.80 rows=665 width=8) (actual time=0.211..7.123 rows=2760 loops=1)
  Index Cond: ((field_1 = 2644) AND (field_2 IS NOT NULL) AND ((additional_information ->> 'my_boolean'::text) = 'false'::text))
  Buffers: shared hit=3469
Planning Time: 0.112 ms
Execution Time: 7.241 ms

根因分析 #

PostgreSQL表达式索引的匹配规则是语法结构完全匹配，而非语义等价匹配，该场景下索引失效有两个核心原因：

1. 类型转换写法不一致：索引定义中使用::bool做类型转换，查询中使用::boolean做转换，虽然二者是类型别名，但解析后的表达式树存在差异，无法直接匹配索引。
2. 查询条件被规划器自动改写：查询中(xxx)::boolean = FALSE的写法会被规划器自动改写为NOT ((xxx)::boolean)，和索引中存储的纯类型转换表达式结构完全不同，直接导致索引匹配失败。

排查步骤 #

1. 通过\d+ my_index_name查看索引的实际定义，确认索引存储的表达式结构细节。
2. 对照执行计划中的Filter/Index Cond条件，对比查询条件被改写后的结构和索引表达式的差异。
3. 执行ANALYZE my_table;更新表统计信息，排除统计信息失真导致规划器误选执行计划的干扰。

解决方案 #

方案1：对齐表达式写法（改动最小） #

保留现有布尔转换索引，将查询语句的写法和索引定义完全对齐，统一转换写法、避免触发NOT改写：

SELECT *
FROM public.my_table
WHERE my_table.field_1=2644
  AND my_table.field_2 IS NOT NULL
  -- 统一用::bool转换，直接匹配小写布尔值，避免写法差异触发改写
  AND (my_table.additional_information->>'my_boolean')::bool = false;

该方案不需要重建索引，仅需调整查询语句写法即可命中索引。

方案2：使用jsonb原生布尔类型（性能最优） #

不要将布尔值以字符串形式存储在jsonb中，直接存储jsonb原生布尔类型，使用->操作符（而非->>提取文本）取值创建索引：

-- 创建原生jsonb布尔值索引
CREATE INDEX IF NOT EXISTS my_index_name_jsonb_bool
ON my_table (field_1, field_2, (additional_information->'my_boolean'));

-- 查询时直接匹配jsonb布尔值，无需类型转换
SELECT *
FROM public.my_table
WHERE my_table.field_1=2644
  AND my_table.field_2 IS NOT NULL
  AND my_table.additional_information->'my_boolean' = 'false'::jsonb;

该方案不存在类型转换的匹配问题，索引命中率稳定，查询性能优于文本转布尔的方案，适合新业务或可清洗存量数据的场景。

方案3：沿用文本匹配索引（零改造成本） #

如果存量数据已经全部为字符串形式的"true"/"false"，不需要做类型转换，直接使用已验证可用的文本匹配索引即可，查询时直接判断字符串值，性能和布尔转换方案无差异，还能彻底规避类型转换带来的索引匹配问题。

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