Snowflake视图子查询过滤失效：如何实现筛选条件下推？

在Snowflake中定义包含子查询关联的视图后，执行外层带date和hour筛选的查询时，发现子查询未应用这些筛选条件，导致扫描全部分区。以下是优化视图定义的具体方案：

原视图与查询语句 #

原视图定义 #

CREATE OR REPLACE VIEW my_view AS (
    SELECT
        main_table.id,
        main_table.name,
        main_table.date,
        main_table.hour,
        subquery_table.additional_info
    FROM main_table
    LEFT JOIN (
        SELECT
            sub_id,
            date,
            hour,
            additional_info
        FROM subquery_table
    ) subquery_table 
ON main_table.id = subquery_table.sub_id
);

查询视图语句 #

SELECT * FROM my_view 
WHERE date = '2023-07-27'
and hour = 0

优化方案 #

方案1：移除嵌套子查询，直接关联表 #

将子查询展开，直接与主表JOIN，让Snowflake优化器自动将外层筛选条件下推到subquery_table：

CREATE OR REPLACE VIEW my_view AS (
    SELECT
        main_table.id,
        main_table.name,
        main_table.date,
        main_table.hour,
        subquery_table.additional_info
    FROM main_table
    LEFT JOIN subquery_table 
        ON main_table.id = subquery_table.sub_id
        AND main_table.date = subquery_table.date
        AND main_table.hour = subquery_table.hour
);

方案2：使用关联子查询传递筛选条件 #

如果需要保留子查询结构，可将主表的date和hour作为关联条件传入子查询，让子查询依赖主表的筛选结果：

CREATE OR REPLACE VIEW my_view AS (
    SELECT
        main_table.id,
        main_table.name,
        main_table.date,
        main_table.hour,
        (SELECT additional_info 
         FROM subquery_table 
         WHERE sub_id = main_table.id
           AND date = main_table.date
           AND hour = main_table.hour) AS additional_info
    FROM main_table
);

原理说明 #

原视图中的子查询是独立执行的，Snowflake优化器无法将外层的date/hour筛选条件下推到这个独立子查询，导致扫描全部分区。优化后的视图通过直接关联或关联子查询的方式，让subquery_table的查询逻辑与主表的筛选条件绑定，优化器会自动将筛选条件下推到subquery_table，仅扫描符合条件的分区。

内容的提问来源于stack exchange，提问作者working-yoghurt-1995