PostgreSQL12百万级大表深度嵌套jsonb字段分组统计方案咨询

性能对比结论 #

几百万条数据的场景下，全量拉取到Python做统计的性能大概率远差于优化后的PostgreSQL原生处理：全量拉取需要传输数GB甚至数十GB的json数据，加上Python侧内存加载、遍历解析的开销，远高于数据库本地计算的成本，除非你的SQL写得完全没有做提前过滤。

优化后的SQL实现方案 #

核心优化思路是先过滤再展开，不要全表展开所有json嵌套结构后再做筛选，从源头减少需要处理的数据量。

1. 适配需求的SQL示例 #

-- 如果是统计指定的门店、品牌，先做前置过滤缩小数据集
WITH target_data AS (
    SELECT base
    FROM market
    -- 用jsonb包含操作提前过滤出只带目标门店/品牌的记录，避免全量展开
    WHERE base @@ '$.regions[*].shops[*].id == 11 || $.regions[*].shops[*].brands[*].id == 21'
),
-- 统计门店维度的使用次数
shop_count AS (
    SELECT
        (region->>'id')::INT AS region_id,
        region->>'name' AS region_name,
        (shop->>'id')::INT AS shop_id,
        COUNT(1) AS count_of_using_shop_id
    FROM target_data,
         jsonb_array_elements(base->'regions') AS region,
         jsonb_array_elements(region->'shops') AS shop
    GROUP BY 1,2,3
),
-- 统计品牌维度的使用次数
brand_count AS (
    SELECT
        (region->>'id')::INT AS region_id,
        (shop->>'id')::INT AS shop_id,
        (brand->>'id')::INT AS brand_id,
        COUNT(1) AS count_of_using_brand_id
    FROM target_data,
         jsonb_array_elements(base->'regions') AS region,
         jsonb_array_elements(region->'shops') AS shop,
         jsonb_array_elements(shop->'brands') AS brand
    GROUP BY 1,2,3
)
-- 关联输出结果
SELECT
    sc.region_id,
    sc.region_name,
    sc.shop_id,
    sc.count_of_using_shop_id,
    bc.brand_id,
    bc.count_of_using_brand_id
FROM shop_count sc
JOIN brand_count bc ON sc.region_id = bc.region_id AND sc.shop_id = bc.shop_id;

2. 索引优化 #

给base字段添加jsonb_path_ops类型的GIN索引，相比默认GIN索引体积小3~4倍，针对@>、@@这类包含/匹配操作的查询速度更快：

CREATE INDEX idx_market_base_path_ops ON market USING GIN (base jsonb_path_ops);

如果经常按固定路径查询，还可以加针对性的表达式索引进一步加速，比如专门针对门店id的查询：

CREATE INDEX idx_market_shop_ids ON market USING GIN (jsonb_path_query_array(base, '$.regions[*].shops[*].id'));

其他可选方案 #

如果该统计是高频查询，建议做预聚合处理：

• 给market表加触发器，当数据新增/更新时，自动把嵌套的region、shop、brand信息拆解同步到一张扁平的中间表，中间表字段直接设置为user_id、region_id、region_name、shop_id、brand_id，之后直接对中间表做group by统计，性能会提升数个量级，完全可以满足几百万数据的实时查询需求。

关于Python方案的补充说明 #

如果一定要用Python处理，也不要全量拉取数据：先在SQL层通过WHERE条件过滤掉不包含目标门店/品牌的记录，仅拉取符合条件的base字段到Python侧做解析统计，才能把开销控制在可接受范围。

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