Snowflake嵌套查询中OR谓词引发全表扫描的原因问询

核心原因 #

Snowflake的查询优化器无法将OR连接的空值匹配条件与前面的等值条件结合以高效利用索引；而内置的EQUAL_NULL函数被优化器识别为原子性的空值安全比较操作，能和其他等值条件协同触发索引查找，避免全表扫描。

具体细节 #

• 索引利用限制：当使用(a.column3 = a_cdc.column3 OR (a.column3 IS NULL AND a_cdc.column3 IS NULL))时，这个复合条件对优化器来说是两个独立的判断逻辑，无法合并成一个可被索引支持的单一比较规则。即便tableA和tableA_CDC上存在(column1, column2, column3)的组合索引，优化器也无法将该OR条件与前面的column1、column2等值条件结合执行索引查找，只能选择全表扫描逐一校验行数据。
• EQUAL_NULL的专属优化：EQUAL_NULL是Snowflake专为空值相等场景设计的内置函数，优化器对其有特殊的识别与优化逻辑。它会把这个函数调用视为一个单一的、可被索引支持的比较操作，因此column1 = a_cdc.column1、column2 = a_cdc.column2加上EQUAL_NULL(a.column3, a_cdc.column3)的组合条件，能被优化器识别为适合组合索引的查询模式，从而触发高效的索引查找。
• 非SARG谓词的影响：单独的a.column3 = a_cdc.column3或(a.column3 IS NULL AND a_cdc.column3 IS NULL)都是符合SARG（可搜索参数）标准的简单谓词，优化器能将它们与前面的等值条件结合利用索引；但用OR连接后，整个条件就变成了非SARG谓词，破坏了索引利用的前提，迫使优化器选择全表扫描。

原始查询（触发全表扫描） #

SELECT a.*
  FROM tableA a
 WHERE EXISTS (
               SELECT 1
                 FROM tableA_CDC a_cdc
                WHERE a.column1 = a_cdc.column1
                  AND a.column2 = a_cdc.column2
                  AND (a.column3 = a_cdc.column3 OR (a.column3 IS NULL AND a_cdc.column3 IS NULL))
              )

优化后查询（无全表扫描） #

SELECT a.*
  FROM tableA a
 WHERE EXISTS (
               SELECT 1
                 FROM tableA_CDC a_cdc
                WHERE a.column1 = a_cdc.column1
                  AND a.column2 = a_cdc.column2
                  AND EQUAL_NULL(a.column3, a_cdc.column3)
              )

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