Redshift中结果相同的两个SQL查询执行耗时差异巨大？

这事儿我太熟了，核心原因在于Redshift处理窗口函数和limit的执行顺序差异，以及数据处理范围的不同！

先看第一个慢查询的问题所在 #

你的第一个查询：

select rank() over (order by id) id_number, id, sid1 ,sid2 from table limit 10000

Redshift执行这个语句时，窗口函数的优先级远高于limit。也就是说：

• 它会先扫描整个表的所有数据，然后对全表的id字段做排序操作（因为rank() over (order by id)需要全局的排序结果来计算排名）
• 等所有数据的rank值都计算完成后，才会执行limit 10000截取前10000条结果

如果你的表数据量很大，全表排序是非常消耗资源的操作——要占用大量CPU、内存，还要做大量IO读写，耗时自然会很长。

再看第二个快查询的优化逻辑 #

第二个查询用CTE先缩小了数据范围：

with A as( select id, sid1, sid2 from table limit 10000 ) 
select rank() over (order by id) id_number, id, sid1, sid2 from A

这里的执行顺序完全不同：

• 首先CTE A会先执行select ... limit 10000，从表中快速获取10000条数据（不需要扫描全表，Redshift可以直接返回满足limit的结果）
• 然后只对这10000条数据执行rank() over (order by id)——因为数据量只有原来的极小一部分，排序和计算rank的开销几乎可以忽略，所以整体速度会快很多

补充说明 #

需要注意的是，两个查询结果一致的前提是你的表数据在两次查询期间没有变化，且limit获取的是相同的10000条数据（如果表没有默认排序，理论上limit返回的结果可能不稳定，但你这里结果一致说明当时数据是稳定的）。另外，Redshift的CTE在多数场景下会被优化为等价的子查询，所以你写成select ... from (select ... limit 10000) A的效果是一样的。

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