PostgreSQL 17大数据量下JSONB字段索引未被使用的解决咨询

针对100万行数据可正常使用JSONB表达式索引、1000万行时自动切换为全表扫描的问题，以下是具体排查和解决步骤：

1. 确保统计信息准确并更新 #

PostgreSQL优化器依赖统计信息判断执行计划，生成大量测试数据后可能未自动更新统计，先执行：

ANALYZE auction_jsonb_indexed;

查看表的基本统计状态：

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'auction_jsonb_indexed';

2. 创建JSONB字段表达式的自定义统计对象（核心解决步骤） #

默认情况下，PostgreSQL不对JSONB内部的单个key做针对性统计，优化器无法判断item->>'author' = 'Author 1'的筛选选择性（即该条件返回的行数占比）。需要为该表达式创建自定义统计：

CREATE STATISTICS auction_jsonb_author_stats (ndistinct) ON (item->>'author') FROM auction_jsonb_indexed;

执行完后重新运行ANALYZE auction_jsonb_indexed;让统计生效，此时优化器能精准评估索引扫描的成本。

3. 检查索引选择性，判断是否适合用索引 #

先计算Author 1对应的行数占全表的比例：

SELECT COUNT(*) * 1.0 / (SELECT COUNT(*) FROM auction_jsonb_indexed) 
FROM auction_jsonb_indexed 
WHERE item->>'author' = 'Author 1';

如果占比超过10%-15%，优化器会认为全表扫描（尤其是并行扫描）的效率更高——因为索引扫描需要回表（即使是表达式索引，count(*)场景下虽然可以避免回表，但优化器可能仍有偏差）。这种情况下：

• 若必须使用索引，可尝试创建覆盖索引（count(*)场景下当前索引已足够，此步骤可选）：

  CREATE INDEX jsonb_author_covering ON auction_jsonb_indexed ((item->>'author')) INCLUDE (id);
  

4. 微调优化器成本参数（可选） #

若统计信息已更新但优化器仍倾向全表扫描，可临时调整成本参数，让索引扫描的优先级更高：

-- 临时会话级别调整，测试生效后再考虑全局配置
SET seq_page_cost = 4; -- 提高全表扫描的单页成本（默认值1）
SET random_page_cost = 1.1; -- 降低索引回表的单页成本（默认值4）

调整后重新执行EXPLAIN ANALYZE查看执行计划是否变化。

5. 排查并行扫描的影响 #

1000万行时优化器选择并行全表扫描，可能认为并行执行的成本低于索引扫描。可临时关闭并行测试：

SET max_parallel_workers_per_gather = 0;

若此时执行计划切换为索引扫描，说明并行策略的成本估算影响了选择。可根据实际查询性能，决定是否调整并行相关参数（如max_parallel_workers_per_gather）。

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