Snowflake查询优化：如何避免两次扫描Category表？

核心思路：复用单次扫描结果 #

Snowflake的查询优化器会自动尝试消除重复扫描，但如果查询结构导致无法自动优化，可通过以下方式手动实现单次扫描：

• 用CTE预提取Category表数据
  将Category表的必要字段通过CTE提前查询出来，后续所有逻辑基于该CTE操作，确保原表只被扫描一次。示例：

  WITH category_cte AS (
      SELECT id, name, parent_id, created_date
      FROM Category
      WHERE created_date >= '2023-01-01' -- 提前加过滤条件减少数据量
  )
  SELECT c1.name AS parent_name, c2.name AS child_name
  FROM category_cte c1
  JOIN category_cte c2 ON c1.id = c2.parent_id
  UNION ALL
  SELECT name AS single_name, NULL
  FROM category_cte
  WHERE parent_id IS NULL;
  

• 用临时表缓存扫描结果
  若查询逻辑复杂（如多次聚合、多条件过滤），可先把Category表的扫描结果存入临时表，后续查询直接引用临时表。临时表仅在当前会话有效，不会占用持久存储：

  CREATE TEMPORARY TABLE temp_category AS
  SELECT id, name, parent_id, created_date, COUNT(*) OVER (PARTITION BY parent_id) AS child_count
  FROM Category;
  
  -- 后续查询统一使用temp_category
  SELECT name, child_count
  FROM temp_category
  WHERE parent_id IS NOT NULL
  UNION ALL
  SELECT name, 0
  FROM temp_category
  WHERE parent_id IS NULL;
  

• 合并重复查询逻辑
  如果原查询通过两个独立子查询分别引用Category表，尝试把逻辑合并到单次扫描中。比如用IN替代两次独立过滤，或用CASE WHEN在单次扫描中完成多维度计算：

  -- 原两次扫描的写法
  SELECT name FROM Category WHERE type = 'PARENT'
  UNION ALL
  SELECT name FROM Category WHERE type = 'CHILD';
  
  -- 优化为单次扫描
  SELECT name
  FROM Category
  WHERE type IN ('PARENT', 'CHILD');
  

验证方法 #

执行查询后查看Snowflake的Query Profile，在"Scan"节点下确认Category表的扫描次数是否变为1次。如果仍存在多次扫描，检查是否有以下情况：

• 对Category表使用了不同的过滤条件或投影字段，导致无法复用扫描结果
• 会话级设置（如RESULT_SCAN）影响了优化器的自动合并逻辑

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