PostgreSQL 13中JSONB列仅索引扫描未触发原因问询

用户执行的SQL查询如下：

select ((option ->> 'fruit_id'))
from table1 t1 
where ((option ->> 'fruit_id')) = '1'
limit 10

该查询从JSONB列option中提取fruit_id的值，且已在表达式(option ->> 'fruit_id')上创建了B-tree索引。根据官方文档，此场景应支持仅索引扫描（index-only scan），但实际仅触发普通索引扫描（index scan），执行VACUUM后仍无变化。需要排查原因，同时确认JSONB索引是否存在仅索引扫描的特殊限制。

原因分析与解决方向 #

• 可见性映射未完全标记全可见页面
  仅索引扫描的核心前提是PostgreSQL能通过可见性映射（VM）确认索引对应的表数据页面中所有元组对当前事务可见。即便执行了VACUUM，如果表中仍有未清理的死元组，或者VM未更新标记，优化器会因无法确认可见性而选择普通索引扫描。可以通过以下方式验证：

  • 查询SELECT relallvisible FROM pg_class WHERE relname = 'table1';，若relallvisible远小于表的总页面数，说明VM未完全标记。
  • 执行VACUUM ANALYZE table1;强制更新VM和统计信息后再查看执行计划。

• 索引与查询的表达式不严格匹配
  确保索引创建语句中的表达式和查询中的表达式完全一致，包括括号、引号、空格等细节。例如索引必须是：

  CREATE INDEX idx_table1_fruit_id ON table1 ((option ->> 'fruit_id'));
  

  如果索引定义存在细微差异（比如多了不必要的嵌套括号、使用双引号而非单引号），优化器无法匹配到该索引，更无法触发仅索引扫描。

• 统计信息过时导致成本估算偏差
  当表的统计信息过时，优化器可能错误评估仅索引扫描的成本，认为普通索引扫描更高效。执行ANALYZE table1;更新统计信息后，重新生成执行计划观察是否变化。

• JSONB索引无特殊限制
  PostgreSQL对JSONB的表达式索引本身没有阻止仅索引扫描的特殊限制，只要索引包含查询所需的全部数据（此场景下就是fruit_id的字符串值），且满足仅索引扫描的可见性前提，就可以触发。问题并非来自JSONB类型本身。

• LIMIT子句的成本影响
  当使用LIMIT 10时，优化器可能认为普通索引扫描能更快定位到前10个匹配元组，无需等待可见性映射的检查，因此选择普通索引扫描。可以尝试去掉LIMIT子句后重新查看执行计划，验证是否会触发仅索引扫描。

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