如何高效实现AWS Redshift大表逆透视与层级递归查询

问题描述 #

现有Redshift表结构及测试数据如下：

CREATE TABLE some_schema.some_table (
    row_id int
    ,productid_level1 char(1)
    ,productid_level2 char(1)
    ,productid_level3 char(1)
);

INSERT INTO some_schema.some_table
VALUES
    (1, 'a', 'b', 'c')
    ,(2, 'd', 'c', 'e')
    ,(3, 'c', 'f', 'g')
    ,(4, 'e', 'h', 'i')
    ,(5, 'f', 'j', 'k')
    ,(6, 'g', 'l', 'm');

需求：给定一个产品ID，返回去重的单列结果集，包含该ID及其所有子孙节点（子孙定义为同一行中层级高于该ID的节点，以及这些节点的后代）。例如输入'c'，预期返回'c'、'e'、'f'、'g'、'h'、'i'、'j'、'k'、'l'、'm'。

实际表包含约300万行数据、20个层级，原使用CROSS JOIN LATERAL做逆透视的查询已运行3分钟未返回，需实现响应时间<15秒的高效查询。

优化解决方案 #

核心思路 #

1. 高效逆透视：用UNION ALL替代CROSS JOIN LATERAL拆分层级，Redshift对UNION ALL的列存扫描优化更友好，避免Lateral Join的行级计算开销。
2. 递归CTE构建父子关系：先建立所有节点的直接父子关联，再从目标ID递归遍历所有后代，同时在递归过程中去重。

具体查询代码 #

WITH unpivoted AS (
    -- 拆分所有层级的父子关系：levelN的父节点是levelN-1
    SELECT productid_level1 AS parent_id, productid_level2 AS child_id FROM some_schema.some_table
    UNION ALL
    SELECT productid_level2 AS parent_id, productid_level3 AS child_id FROM some_schema.some_table
    -- 针对20个层级，继续添加UNION ALL直到level20：
    -- SELECT productid_levelN AS parent_id, productid_levelN+1 AS child_id FROM some_schema.some_table
),
recursive_descendants AS (
    -- 初始节点：目标ID自身
    SELECT 'c' AS product_id
    UNION ALL
    -- 递归遍历所有后代
    SELECT up.child_id
    FROM recursive_descendants rd
    JOIN unpivoted up ON rd.product_id = up.parent_id
    -- 避免重复处理已访问节点
    WHERE NOT EXISTS (SELECT 1 FROM recursive_descendants rd2 WHERE rd2.product_id = up.child_id)
)
-- 最终去重返回结果
SELECT DISTINCT product_id FROM recursive_descendants;

额外性能优化建议 #

• 表结构优化：如果该查询是高频操作，建议将各层级的产品ID设置为排序键（SORT KEY），Redshift会按排序键优化扫描效率；若节点分布均匀，可将产品ID设为分布键（DIST KEY），减少跨节点数据传输。
• 物化视图：如果层级数据更新不频繁，可预创建包含所有父子关系的物化视图，查询时直接基于物化视图递归，进一步缩短响应时间。
• 递归CTE优化：Redshift的递归CTE默认有迭代次数限制，20个层级完全足够，无需调整；WHERE NOT EXISTS的过滤逻辑可提前终止重复路径的递归，减少无效计算。

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