PostgreSQL中JSONB字段GIN索引未被使用的异常问题

环境说明 #

Mac OS X 12.6系统，通过Homebrew安装的PostgreSQL 14.5版本。

测试场景1：test1表（GIN索引正常生效） #

创建表及GIN索引：

CREATE TABLE test1 (
  id SERIAL PRIMARY KEY,
  data JSONB,
  column1 VARCHAR
);

CREATE INDEX ON test1 USING GIN (data);

执行查询：

EXPLAIN SELECT * FROM test1 WHERE data @> '"foo"';

查询计划显示GIN索引被正常使用。

测试场景2：test2表（未使用GIN索引） #

新增两个字段创建test2表及相同的GIN索引：

CREATE TABLE test2 (
  id SERIAL PRIMARY KEY,
  data JSONB,
  column1 VARCHAR,
  column2 VARCHAR,
  column3 VARCHAR
);

CREATE INDEX ON test2 USING GIN (data);

执行相同查询：

EXPLAIN SELECT * FROM test2 WHERE data @> '"foo"';

查询计划显示走全表扫描，未使用GIN索引。

原因分析 #

1. 成本估算变化：PostgreSQL的查询优化器基于成本选择执行计划。test2表比test1表多了两个VARCHAR字段，单条记录的存储空间更大，导致回表（从索引定位到实际数据行）的IO成本上升。当表数据量较小时，优化器会认为全表扫描的总开销低于“索引扫描+回表”的开销，因此选择全表扫描。
2. 统计信息不足：新创建的表没有足够的统计数据，优化器无法精准估算索引扫描和全表扫描的实际成本，也可能导致其优先选择全表扫描。

解决方法 #

• 插入足量测试数据：当表中数据量达到一定规模（如数千条以上），优化器会重新评估成本，此时会倾向于选择GIN索引扫描，因为索引扫描的效率优势会随着数据量增长而凸显。
• 更新统计信息：手动执行ANALYZE test2;，让PostgreSQL更新表的统计数据，帮助优化器更准确地计算不同执行计划的成本，从而做出更合理的选择。
• 临时强制使用索引（不推荐常规场景）：可以使用索引提示指定使用目标索引，例如（需替换为实际索引名称）：

  EXPLAIN SELECT * FROM test2 WHERE data @> '"foo"' INDEX test2_data_idx;
  

  注意：这种方法仅适合临时验证，长期使用会绕过优化器的智能决策，不推荐作为常规方案。

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