将RedShift查询改为子查询后性能骤降的原因排查

问题背景 #

原查询在RedShift中可快速返回659行数据：

select
-- a list of fields
from
Table1 as t1
inner join Table2 as t2 on t2.id=t1.accountid
inner join Table3 as t3 on t3.subscriptionid=t1.id
inner join Table4 as t4 on t4.id=t3.orderid
inner join Table5 as t5 on t3.id=t5.actionid
inner join Table6 as t6 on t5.rateid=t6.id
inner join Table7 as t7 on t7.rateid=t6.id
where
t4.status='Completed'
and TRUNC(t4.createddate) >= TRUNC(SYSDATE) - 1
and t7.islastvalue='TRUE'
and t7.effectivestartdate <= t3.effectivedate
and t7.effectiveenddate >= t3.effectivedate
and t1.iscurrentversion ='true'

但当该查询被工具自动包装为带TOP 1的子查询后，运行耗时长达数小时（无法阻止此包装操作）：

select top 1 * from 
(
select
-- a list of fields
from
Table1 as t1
inner join Table2 as t2 on t2.id=t1.accountid
inner join Table3 as t3 on t3.subscriptionid=t1.id
inner join Table4 as t4 on t4.id=t3.orderid
inner join Table5 as t5 on t3.id=t5.actionid
inner join Table6 as t6 on t5.rateid=t6.id
inner join Table7 as t7 on t7.rateid=t6.id
where
t4.status='Completed'
and TRUNC(t4.createddate) >= TRUNC(SYSDATE) - 1
and t7.islastvalue='TRUE'
and t7.effectivestartdate <= t3.effectivedate
and t7.effectiveenddate >= t3.effectivedate
and t1.iscurrentversion ='true'
) as "_"

补充信息：失败查询的执行计划显示子查询已完成，但父查询无法返回结果，疑惑为何性能差异如此巨大。

性能差异原因分析 #

• 查询优化器的执行策略反转：RedShift优化器对返回全量结果和仅返回1行的查询会采用完全不同的策略。原查询需要返回所有659行，优化器会优先选择高效的过滤+连接逻辑，比如利用索引快速定位符合条件的数据、采用哈希连接减少匹配开销、并行扫描分散计算压力；而TOP 1查询会触发“尽早返回”的优化逻辑，尝试逐个匹配数据找到第一行，但这种策略在多表连接场景下反而会失效——比如它可能放弃高效的哈希连接，改用嵌套循环逐个扫描，或者调整表的连接顺序，导致全表扫描次数剧增。

• 无排序规则引发的全局排序开销：原查询返回所有行不需要强制排序，但TOP 1如果没有指定ORDER BY，RedShift必须确定返回哪一行，此时会默认对整个子查询的结果做全局排序。在MPP架构下，全局排序需要把所有节点上的子查询结果 shuffle 到 leader 节点，再进行排序筛选，这个过程的开销远大于直接返回所有659行——哪怕数据量不大，跨节点的数据传输和排序操作也会耗时数小时。

• 子查询物化后的跨节点汇总开销：虽然执行计划显示子查询已完成，但子查询的结果是分散在RedShift各个计算节点上的。父查询的TOP 1需要把所有节点的结果汇总到 leader 节点，这个过程涉及数据的序列化、网络传输和重新聚合，若子查询返回的字段较多或者数据分布不合理，会产生额外的性能损耗。

• 统计信息不准确导致的计划误判：如果涉及的表统计信息过时，优化器会错误估计子查询的结果行数。比如它可能认为子查询结果只有几行，于是选择了适合小数据集的嵌套循环连接，但实际有659行，嵌套循环的开销会远大于原查询使用的哈希连接，直接拖慢整体速度。

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