Presto性能调优求助：多左连接引发查询超时问题

优化后的查询代码 #

以下是针对需求优化后的查询，仅需扫描一次orders表，彻底解决原查询的性能瓶颈：

WITH orders_agg AS (
    -- 单次扫描orders表，同时统计product_count1和product_count2的分组计数
    SELECT
        product_count1,
        COUNT(*) AS cnt1,
        product_count2,
        COUNT(*) OVER (PARTITION BY product_count2) AS cnt2
    FROM orders
    WHERE product_count1 BETWEEN 1 AND 200 OR product_count2 BETWEEN 1 AND 200
),
bucket_summary AS (
    -- 提取product_count1的统计结果
    SELECT
        product_count1 AS bucket,
        MAX(cnt1) AS child_count,
        0 AS parent_count
    FROM orders_agg
    WHERE product_count1 IS NOT NULL
    GROUP BY product_count1
    UNION ALL
    -- 提取product_count2的统计结果
    SELECT
        product_count2 AS bucket,
        0 AS child_count,
        MAX(cnt2) AS parent_count
    FROM orders_agg
    WHERE product_count2 IS NOT NULL
    GROUP BY product_count2
),
final_counts AS (
    -- 合并同一bucket的统计值
    SELECT
        bucket,
        SUM(child_count) AS child_count,
        SUM(parent_count) AS parent_count
    FROM bucket_summary
    GROUP BY bucket
)
-- 关联buckets，确保1-200所有数值都保留，无匹配项返回0
SELECT
    t.buckets,
    COALESCE(fc.child_count, 0) AS child_count,
    COALESCE(fc.parent_count, 0) AS parent_count
FROM UNNEST(SEQUENCE(1,200,1)) t(buckets)
LEFT JOIN final_counts fc ON t.buckets = fc.bucket
ORDER BY t.buckets;

原查询超时原因 #

1. 重复全表扫描：原查询两次LEFT JOIN百万行的orders表，导致数据库执行两次全表扫描，IO开销直接翻倍。
2. 中间数据集爆炸：两次LEFT JOIN会生成大量冗余中间行（例如某个bucket在o1有1000行匹配、o2有1000行匹配，中间结果会产生100万行），极大增加了分组计算的压力。
3. 逻辑错误：原查询中o2的连接条件写为t.buckets = o2.product_count1，与统计product_count2的需求不符，会导致结果错误。

核心优化点 #

• 单次全表扫描：仅扫描一次orders表完成两个字段的统计，大幅降低IO开销。
• 提前聚合压缩数据：将百万行的orders表压缩为最多400行的小数据集（1-200每个数值对应一行统计），后续与buckets连接时几乎无性能开销。
• 避免笛卡尔积：通过先聚合再连接的方式，彻底规避了原查询中两次连接产生的笛卡尔积问题。
• 保留所有bucket行：通过LEFT JOIN和COALESCE函数，确保1-200中未在orders出现的数值返回0。

额外性能提升建议 #

如果orders表未针对product_count1和product_count2创建索引，建议添加：

CREATE INDEX idx_orders_count1 ON orders(product_count1);
CREATE INDEX idx_orders_count2 ON orders(product_count2);

索引可让数据库使用索引扫描替代全表扫描，进一步缩短查询时间。

内容的提问来源于stack exchange，提问作者J-snow