优化含内连接与array_agg的DuckDB查询性能

示例数据 #

先创建与业务结构匹配的示例表及测试数据：

-- 核心表t_cons（约20万行）
CREATE TABLE t_cons (
    id INT PRIMARY KEY,
    asset VARCHAR(50),
    year INT,
    rep_period VARCHAR(20),
    -- 自定义运算依赖的参数字段（按需调整）
    calc_param1 NUMERIC,
    calc_param2 NUMERIC
);

INSERT INTO t_cons VALUES
(1, 'ASSET_X', 2023, 'QUARTER_1', 150, 0.6),
(2, 'ASSET_Y', 2023, 'QUARTER_2', 300, 0.4);

-- 流水表t_flows（约120万行）
CREATE TABLE t_flows (
    asset VARCHAR(50),
    year INT,
    rep_period VARCHAR(20),
    variable_index INT,
    -- 自定义运算依赖的流水字段（按需调整）
    flow_val1 NUMERIC,
    flow_val2 NUMERIC
);

INSERT INTO t_flows VALUES
('ASSET_X', 2023, 'QUARTER_1', 501, 200, 40),
('ASSET_X', 2023, 'QUARTER_1', 502, 120, 35),
('ASSET_Y', 2023, 'QUARTER_2', 601, 350, 70),
('ASSET_Y', 2023, 'QUARTER_2', 602, 280, 65);

优化后的单查询 #

将两步查询合并为单SQL，直接完成匹配、筛选、聚合：

SELECT
    c.id,
    c.asset,
    c.year,
    c.rep_period,
    -- 用LIST_PACK聚合符合条件的变量索引和运算结果，返回结构化数组
    LIST_PACK(STRUCT(
        f.variable_index,
        -- 替换为你的自定义运算逻辑
        (f.flow_val1 - c.calc_param1) * c.calc_param2 - f.flow_val2
    )) AS qualified_results
FROM t_cons c
INNER JOIN t_flows f
    ON c.asset = f.asset
    AND c.year = f.year
    AND c.rep_period = f.rep_period
WHERE
    -- 筛选运算结果>0的记录
    (f.flow_val1 - c.calc_param1) * c.calc_param2 - f.flow_val2 > 0
GROUP BY
    c.id, c.asset, c.year, c.rep_period;

执行计划分析 #

通过EXPLAIN ANALYZE查看执行计划，重点关注核心节点：

EXPLAIN ANALYZE
-- 上述完整查询语句

• 连接类型：优先确认使用Hash Join（适合大表连接场景），避免Nested Loop Join（仅小表匹配高效）
• 索引命中：检查t_flows的(asset, year, rep_period)字段是否被用于快速匹配
• 聚合效率：确认LIST_PACK等聚合函数无额外内存开销，执行阶段无冗余计算

性能优化关键措施 #

1. 创建复合索引
   针对t_flows的连接字段创建复合索引，大幅降低跨表匹配的时间：

   CREATE INDEX idx_flows_join_key ON t_flows (asset, year, rep_period);
   

2. 更新统计信息
   让DuckDB优化器准确判断数据分布，生成最优执行计划：

   ANALYZE t_cons;
   ANALYZE t_flows;
   

3. 精简字段
   仅在SELECT和GROUP BY中保留业务必需的字段，减少内存占用和数据传输量

4. 预计算运算结果
   如果自定义运算逻辑复杂，可通过子查询提前计算结果，避免重复计算：

   SELECT
       c.id,
       c.asset,
       c.year,
       c.rep_period,
       LIST_PACK(STRUCT(f.variable_index, f.calc_result)) AS qualified_results
   FROM t_cons c
   INNER JOIN (
       SELECT
           asset, year, rep_period, variable_index,
           -- 提前计算自定义结果
           (flow_val1 - c.calc_param1) * c.calc_param2 - flow_val2 AS calc_result
       FROM t_flows
   ) f
       ON c.asset = f.asset
       AND c.year = f.year
       AND c.rep_period = f.rep_period
   WHERE f.calc_result > 0
   GROUP BY c.id, c.asset, c.year, c.rep_period;
   

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