Prune before/after/during join三类SQL写法性能对比与最佳实践

首先先指出你给出的第一个SQL存在语法问题：你定义了pruned CTE但主查询仅关联了foo表，ON条件直接引用pruned.id会因为pruned未加入关联列表报错，我们默认你是想写INNER JOIN pruned ON bar.id = pruned.id来做三者对比，以下分析基于这个修正后的版本展开。

你给出的三个写法修正后逻辑等价，代码如下：

-- 写法1：CTE提前过滤
WITH pruned AS (
SELECT id FROM foo WHERE timestamp >= TIMESTAMP('some timestamp')
)
SELECT bar.* FROM bar
INNER JOIN pruned
ON bar.id = pruned.id

-- 写法2：WHERE子句加过滤
SELECT bar.* FROM bar
INNER JOIN foo
ON bar.id = foo.id
WHERE foo.timestamp >= TIMESTAMP('some timestamp')

-- 写法3：JOIN ON子句加过滤
SELECT bar.* FROM bar
INNER JOIN foo
ON foo.timestamp >= TIMESTAMP('some timestamp')
AND bar.id = foo.id

性能结论 #

你的两个猜测都不符合当前BigQuery的实际执行逻辑：

1. 三种写法在默认场景下性能完全一致，不存在第三种性能最高、第一种性能最差的情况
2. BigQuery的基于代价的优化器（CBO）会自动做谓词下推和CTE展开，你写的仅做简单过滤的CTE不会被临时存储，会被优化器完全合并到主查询的执行计划中，不会产生额外开销
3. 对于INNER JOIN来说，过滤条件写在JOIN的ON子句还是WHERE子句逻辑完全等价，优化器都会把foo表的timestamp过滤条件下推到扫描foo表的阶段，直接只读取符合时间条件的分区/数据，不会等到JOIN完成后再过滤。

仅以下场景会出现性能差异 #

只有当过滤逻辑复杂到优化器无法下推时，不同写法才会有性能区别：

• 如果你在CTE里做了聚合、窗口函数、子查询关联这类无法直接下推到基表扫描的操作，此时CTE会产生中间计算结果，可能带来额外开销
• 如果你给分区列的过滤条件包了函数，比如WHERE DATE(foo.timestamp) >= '2024-01-01'，不管你把条件写在什么位置，都会导致分区裁剪失效，需要扫描全表数据，这才是最大的性能损耗点，比过滤条件的摆放位置影响大得多。

最佳实践建议 #

• 优先选择可读性最高的写法即可，不用为了所谓的性能刻意把过滤条件堆在ON子句里，或者手动写提前过滤的子查询/CTE，优化器会帮你做最优的逻辑改写
• 分区表的过滤条件要避免包裹函数，保证优化器可以识别到做分区裁剪，这是性能优化的核心要点
• 如果实际执行后发现优化器没有做预期的下推优化，再考虑手动调整写法，或者加MATERIALIZE CTE的hint（这类场景非常少见）

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