Greenplum聚合JSONB分类键求和触发切片数超限问题咨询

• 业务表基础属性：日增量900万行，单国家日均200万行，分区规则为data_date做一级分区、country做二级子分区
• 核心字段：category_tree为JSONB类型，存储多层级分类树结构；sold为数值类型，存储单条记录的销量；data_date为日期类型分区字段
• 统计要求：单次查询即可覆盖近7天及以上时间范围，对category_tree所有层级的分类键做聚合，最终输出category_id（分类ID）、data_date（统计日期）、sum_sold（对应分类对应日期的销量总和）三个字段，结果需覆盖所有分类层级。

方案1：UNION ALL拼接各层级聚合SQL #

实现逻辑为枚举所有分类ID，为每个分类ID单独写一段聚合逻辑，通过UNION ALL拼接所有SQL片段，单段SQL示例如下：

SELECT 'a' AS category_id, data_date, sum(sold) AS sum_sold
FROM table
WHERE CAST((category_tree::jsonb#>'{"a"}') AS jsonb) IS NOT NULL 
AND data_date>= '2022-07-01' 
AND data_date <= '2022-07-09'
GROUP BY data_date
UNION ALL

方案2：jsonb_object_keys提取固定路径键聚合 #

实现逻辑为通过jsonb_object_keys函数提取指定JSON路径下的分类键做聚合，存在无法自动覆盖全部分类层级的缺陷，SQL示例如下：

SELECT jsonb_object_keys(category_tree::jsonb#'{"a", "b"}') as category_id, 
data_date, sum(sold) AS sum_sold 
FROM table 
WHERE data_date>= '2022-07-01' 
AND data_date <= '2022-07-09' 
AND CAST((category_tree::jsonb#'{"a", "b", "c"}') AS jsonb) IS NOT NULL
GROUP BY data_date

触发报错 #

使用UNION ALL方案时触发两类Greenplum侧报错：

1. too many range table entries：范围表条目数量超出数据库限制
2. ERROR: at most 50 slices are allowed in a query, current number: 177：单查询最多允许50个执行切片，当前查询切片数达177，官方提示为rewrite your query or adjust GUC gp_max_slices，即需要重写查询或调整gp_max_slices参数配置。

不建议通过直接调大gp_max_slices参数规避报错：该参数是Greenplum的查询资源保护阈值，强行调大后，上百段UNION ALL拼接的SQL会生成大量执行切片，查询过程会占用大量网络、内存资源，极易引发集群阻塞，影响其他业务正常运行。

最优实现方式是通过递归CTE一次性遍历指定分区范围的数据，递归打平所有层级的分类键，仅做一次表扫描即可完成全层级聚合，SQL如下：

WITH RECURSIVE category_parse AS (
    -- 锚点：提取第一层级分类键
    SELECT 
        t.data_date,
        t.sold,
        k.node_key AS category_id,
        t.category_tree -> k.node_key AS child_node
    FROM 替换为实际业务表名 t,
         LATERAL jsonb_object_keys(t.category_tree) AS k(node_key)
    WHERE t.data_date >= CURRENT_DATE - INTERVAL '7 day' -- 可按需调整查询时间范围
      -- 若需统计指定国家数据，放开下方注释即可利用二级分区裁剪提升性能
      -- AND t.country = '替换为实际国家编码'
    UNION ALL
    -- 递归逻辑：逐层向下提取子分类键，直到无JSON对象子节点为止
    SELECT 
        cp.data_date,
        cp.sold,
        k.node_key AS category_id,
        cp.child_node -> k.node_key AS child_node
    FROM category_parse cp,
         LATERAL jsonb_object_keys(cp.child_node) AS k(node_key)
    WHERE jsonb_typeof(cp.child_node) = 'object'
)
-- 最终按分类、日期聚合销量
SELECT 
    category_id,
    data_date,
    SUM(sold) AS sum_sold
FROM category_parse
GROUP BY category_id, data_date;

该方案优势：

• 仅扫描指定时间/国家范围内的分区数据，不会产生大量UNION ALL带来的范围表超限、切片数超限问题
• 自动递归遍历所有分类层级，无需手动枚举分类ID、手动指定每层JSON路径，后续分类层级新增或调整无需修改SQL
• 性能远高于UNION ALL拼接方案，常规7天范围的查询在现有数据规模下可在分钟级完成
• 递归深度受数据库max_recursion_depth参数控制，常规业务分类层级多为3-5级，完全在默认参数支持范围内，无需额外调整配置。

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