PostgreSQL含jsonb字段与GROUP BY的慢查询优化求助

性能瓶颈分析 #

从执行计划可以看出，开销主要集中在两个环节：

1. 全表扫描后逐行展开jsonb的results字段，过滤符合category条件的键值对，10万条原始数据展开后生成了91万行中间结果
2. 对91万行明细数据直接按key分组聚合，同时生成json数组，计算开销较高
   你之前调整work_mem没有明显效果，是因为当前查询的排序环节仅用了10MB左右内存，不存在内存不足的问题，瓶颈不在配置参数。

可尝试的优化方案 #

• 第一层：改写查询逻辑，降低聚合阶段数据量
  不要直接对全量明细做聚合，先做两层聚合减少中间数据：第一层先按key+日期预聚合单日统计值，再对预聚合后的结果做全局聚合，能把聚合阶段处理的数据量从91万降到几千行，大幅降低开销。改写参考：

  WITH daily_agg AS (
      SELECT 
          r.key,
          dr.date::date as dt,
          SUM((r.value->>'counter')::int) as daily_count,
          COUNT(*) as daily_docs
      FROM data_reportfile dr
      CROSS JOIN jsonb_each(dr.analysis_result->'results') r
      WHERE dr.date >= '1960-1-1'
        AND dr.analysis_done IS TRUE
        AND r.value->>'category' = 'general'
      GROUP BY r.key, dr.date::date
  )
  SELECT 
      key,
      SUM(daily_count) as count_key,
      SUM(daily_docs) as count_documents,
      json_agg(json_build_object('date', dt, 'count_key', daily_count)) as dates
  FROM daily_agg
  GROUP BY key
  ORDER BY count_documents DESC
  LIMIT 20;
  

• 第二层：新增适配场景的索引
  你之前建的普通date索引在大范围日期筛选场景下不会被优化器选用（筛选范围覆盖大部分数据时，走索引回表开销比全表扫描更高），可新增两类索引：

  1. 带固定过滤条件的组合索引，适配必传的analysis_done筛选：

     CREATE INDEX idx_reportfile_date_done ON data_reportfile (date) INCLUDE (analysis_result) WHERE analysis_done IS TRUE;
     

  2. jsonb路径索引，提前过滤没有general分类结果的行，避免无效json展开：
     先在WHERE条件中新增AND analysis_result @? '$.results.* ? (@.category == "general")'，再建对应索引：

     CREATE INDEX idx_reportfile_results_category ON data_reportfile USING GIN (analysis_result jsonb_path_ops) WHERE analysis_done IS TRUE;
     

• 第三层：长期优化，结构化拆分高频查询字段
  如果你经常需要对results下的键值做筛选统计，可新增一张结构化关联表data_report_results，字段包含：report_id（关联主表主键）、keyword、category、counter、date，主表写入analysis_result时同步把results下的所有键值对写入这张关联表。
  结构化表的查询性能会比jsonb解析高5~10倍，同时支持任意维度的筛选分组，完全适配你提到的多筛选条件查询场景，不需要依赖缓存或物化视图。

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