结果一致的两个BigQuery查询性能相差近一倍的原因是什么？

待比对查询语句 #

查询1 #

WITH
  T AS(
  SELECT
    ROW_NUMBER() OVER() AS uid,
    position_geom
  FROM
    `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
  WHERE
    date > "2017-12-26" )
SELECT
  T1.uid,
  T2.uid,
  ST_DISTANCE(T1.position_geom, T2.position_geom) distance
FROM
  T AS T1,
  T AS T2
WHERE
  T1.uid <> T2.uid QUALIFY ROW_NUMBER() OVER (PARTITION BY T1.uid ORDER BY ST_DISTANCE(T1.position_geom, T2.position_geom)) < 101
ORDER BY
  T1.uid,
  ST_DISTANCE(T1.position_geom, T2.position_geom)

查询2 #

WITH
  T AS(
  SELECT
    ROW_NUMBER() OVER() AS uid,
    position_geom
  FROM
    `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
  WHERE
    date > "2017-12-26" ),
  T2 AS(
  SELECT
    T1.position_geom pointA,
    T1.uid uidA,
    T2.position_geom pointB,
    T2.uid uidB,
    ST_DISTANCE(T1.position_geom, T2.position_geom) distance
  FROM
    T AS T1,
    T AS T2 )
SELECT
  uidA,
  uidB,
  distance
FROM
  T2
WHERE
  uidA <> uidB QUALIFY ROW_NUMBER() OVER (PARTITION BY uidA ORDER BY distance) < 101
ORDER BY
  uidA,
  distance

性能差异核心原因 #

二者性能差距的本质是ST_DISTANCE地理空间函数的重复计算次数不同，BigQuery优化器暂未实现该场景下的自动公共表达式提取优化，具体差异如下：

• 查询1中，ST_DISTANCE(T1.position_geom, T2.position_geom)被重复调用了3次：SELECT子句输出1次、QUALIFY窗口排序1次、最终全局ORDER BY排序1次。ST_DISTANCE属于计算开销较高的空间计算函数，每一次调用都需要对两个坐标点做距离运算，重复计算直接带来了多倍的资源消耗。
• 查询2中，ST_DISTANCE仅在CTE T2的笛卡尔计算阶段被调用1次，结果被预存为distance字段，后续QUALIFY排序、全局ORDER BY、SELECT输出都直接读取预计算的数值结果，没有额外的重复计算开销，因此耗时和槽位消耗仅为查询1的一半左右。

补充说明 #

BigQuery优化器的自动去重优化仅覆盖部分确定性高、计算开销低的简单函数，对于计算成本较高的空间函数、自定义函数，目前不会自动识别重复调用并做结果复用，需要用户手动通过CTE或者子查询提前计算并保存结果实现性能优化。

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