PostgreSQL jsonb列GIN索引失效：同查询不同列表现差异

云上老周

2026-8-1

问题描述

我的表clinical.variable_instances_versioned有两个jsonb列：etl_value和user_value，两者都创建了基于jsonb_path_ops的GIN索引：

"ix_variable_instance_versioned_etl_value" gin (etl_value jsonb_path_ops)
"ix_variable_instance_versioned_user_value" gin (user_value jsonb_path_ops)

针对user_value的查询能正常使用索引，执行计划如下：

=> explain analyze select count(*) from clinical.variable_instances_versioned where user_value @? '$.coding[*].code ? (@ like_regex "C" flag "i")';
                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1650284.49..1650284.50 rows=1 width=8) (actual time=691.730..691.731 rows=1 loops=1)
   ->  Bitmap Heap Scan on variable_instances_versioned  (cost=1649812.91..1650284.19 rows=118 width=0) (actual time=118.388..691.377 rows=4268 loops=1)
         Recheck Cond: (user_value @? '$."coding"[*]."code"?(@ like_regex "C" flag "i")'::jsonpath)
         Rows Removed by Index Recheck: 1248999
         Heap Blocks: exact=58317 lossy=35532
         ->  Bitmap Index Scan on ix_variable_instance_versioned_user_value  (cost=0.00..1649812.88 rows=118 width=0) (actual time=107.017..107.017 rows=184334 loops=1)
               Index Cond: (user_value @? '$."coding"[*]."code"?(@ like_regex "C" flag "i")'::jsonpath)
 Planning Time: 0.098 ms
 Execution Time: 693.030 ms
(9 rows)

但针对etl_value的完全相同查询却没有使用索引，执行计划如下：

=> explain analyze select count(*) from clinical.variable_instances_versioned where etl_value @? '$.coding[*].code ? (@ like_regex "C" flag "i")';
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3558858.31..3558858.32 rows=1 width=8) (actual time=12911.026..12912.472 rows=1 loops=1)
   ->  Gather  (cost=3558858.10..3558858.31 rows=2 width=8) (actual time=12910.955..12912.467 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=3557858.10..3557858.11 rows=1 width=8) (actual time=12908.707..12908.707 rows=1 loops=3)
               ->  Parallel Seq Scan on variable_instances_versioned  (cost=0.00..3556075.62 rows=712989 width=0) (actual time=0.053..12871.096 rows=509619 loops=3)
                     Filter: (etl_value @? '$."coding"[*]."code"?(@ like_regex "C" flag "i")'::jsonpath)
                     Rows Removed by Filter: 30108635
 Planning Time: 0.188 ms
 Execution Time: 12912.500 ms
(10 rows)

请问这是什么原因？

原因分析

核心原因是PostgreSQL的查询优化器基于成本估算，认为针对etl_value的查询使用全表扫描比索引扫描更划算，具体可以从这几点拆解：

结果集规模差异
从执行计划的行数估算和实际返回值来看：
- user_value查询预计返回118行，实际返回4268行，属于小结果集，索引扫描能快速定位匹配行，再回表获取数据的成本远低于全表扫描。
- etl_value查询预计返回712989行，实际返回约152万行（3个worker各返回509619行），结果集占比很高。此时优化器判断：先扫描索引再回表的IO成本，反而不如直接全表扫描过滤来得低。
统计信息可能过时
如果表的etl_value列数据近期有大量更新，而统计信息未及时更新，优化器拿到的行数估算可能偏差较大，导致错误选择执行计划。可以执行ANALYZE clinical.variable_instances_versioned;更新统计信息后再测试。
jsonb_path_ops索引的特性限制
jsonb_path_ops类型的GIN索引是针对特定键值匹配优化的，当查询的匹配条件（这里的正则匹配）会命中大量行时，索引的过滤优势会消失。优化器会自动选择成本更低的全表扫描方式。

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