BigQuery分区表查询成本估算错误，计费按全表计算问题求助

我创建了如下结构的分区表：

CREATE TABLE `main.viewings`
(
  event_time TIMESTAMP,
  other_columns INT64,
  more_columns STRING
)
PARTITION BY DATE(event_time)
OPTIONS(
  partition_expiration_days=365
);

随后我按日聚合新数据并追加到报表表中，使用的查询语句如下：

DECLARE from_event_time TIMESTAMP DEFAULT (SELECT TIMESTAMP(DATE_ADD(IFNULL(MAX(`date`), '2022-10-31'), INTERVAL 1 DAY)) FROM main.`reporting_table`);
DECLARE to_event_time TIMESTAMP DEFAULT TIMESTAMP(CURRENT_DATE());

SELECT DISTINCT DATE(event_time) AS `date`
FROM main.`viewings`
WHERE
    event_time >= from_event_time
    AND event_time < to_event_time;

但BigQuery错误地估算并按整个viewings表的大小计费。若硬编码from_event_time和to_event_time的值，就能正确估算出更小的成本。

更令人困惑的是，当WHERE条件仅保留event_time >= from_event_time时，估算也正常；仅当添加event_time < to_event_time条件后，估算就出现问题。有人遇到过类似情况吗？

• 问题根源：BigQuery的查询优化器在处理动态声明的变量作为分区过滤的上下边界时，尤其是to_event_time基于CURRENT_DATE()这种运行时才确定的值时，无法提前推断出有效的分区范围，导致优化器放弃分区剪枝的估算逻辑，默认按全表扫描计算成本。而仅保留>=条件时，优化器能基于from_event_time的子查询结果（来自报表表的MAX(date)）推断出起始分区，因此能正确估算。

• 解决方法：

  1. 用子查询替代DECLARE变量：把变量的逻辑直接嵌入WHERE条件，让优化器能解析到分区过滤逻辑：

     SELECT DISTINCT DATE(event_time) AS `date`
     FROM main.`viewings`
     WHERE
         event_time >= (SELECT TIMESTAMP(DATE_ADD(IFNULL(MAX(`date`), '2022-10-31'), INTERVAL 1 DAY)) FROM main.`reporting_table`)
         AND event_time < TIMESTAMP(CURRENT_DATE());
     

  2. 用动态SQL强制提前解析变量值：如果必须使用DECLARE，可通过EXECUTE IMMEDIATE生成动态查询，让优化器能获取具体的边界值：

     DECLARE from_event_time TIMESTAMP DEFAULT (SELECT TIMESTAMP(DATE_ADD(IFNULL(MAX(`date`), '2022-10-31'), INTERVAL 1 DAY)) FROM main.`reporting_table`);
     DECLARE to_event_time TIMESTAMP DEFAULT TIMESTAMP(CURRENT_DATE());
     DECLARE query STRING;
     
     SET query = '''
         SELECT DISTINCT DATE(event_time) AS `date`
         FROM main.`viewings`
         WHERE
             event_time >= ''' || QUOTE(from_event_time) || '''
             AND event_time < ''' || QUOTE(to_event_time) || ''';
     ''';
     
     EXECUTE IMMEDIATE query;
     

  3. 预计算边界值到临时表：先把from和to的时间值写入临时表，再在查询中引用临时表的值，让优化器能明确获取过滤范围。

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