如何优化Snowflake中与中型Type II表的关联查询？

背景信息 #

咱们现在有两张核心业务表需要处理：

• lkp.session：共3300万行，存储会话基础信息

CREATE TABLE lkp.session (
 session_id BIGINT,
 visitor_id BIGINT,
 session_datetime TIMESTAMP
);

• lkp.visitor_customer_hist：共1700万行，存储访客在不同时间段对应的生效客户ID（已确认同一访客的时间区间无重叠）

CREATE TABLE lkp.visitor_customer_hist (
 visitor_id BIGINT,
 customer_id BIGINT,
 from_datetime TIMESTAMP,
 to_datetime TIMESTAMP
);

业务目标是通过visitor_id和session_datetime，为每个会话匹配对应的生效customer_id，当前执行的查询语句如下：

CREATE TABLE lkp.session_effective_customer AS
SELECT s.session_id, vch.customer_id AS effective_customer_id
FROM lkp.session s
JOIN lkp.visitor_customer_hist vch
 ON vch.visitor_id = s.visitor_id
 AND s.session_datetime >= vch.from_datetime
 AND s.session_datetime < vch.to_datetime;

当前问题 #

即使在Large规格仓库且无其他查询运行的情况下，这个查询耗时高达1小时15分钟，急需优化性能。

优化方案 #

一、表结构优化（聚类与索引） #

• 为lkp.visitor_customer_hist设置聚类键：
  因为关联逻辑是先按visitor_id匹配，再按时间范围过滤，把visitor_id设为主聚类键，辅助加上from_datetime，能让同一访客的所有历史记录物理聚集在一起，大幅减少关联时的数据扫描范围。
  示例语句：

  ALTER TABLE lkp.visitor_customer_hist CLUSTER BY (visitor_id, from_datetime);
  

• 为lkp.session设置聚类键：
  同样按visitor_id和session_datetime聚类，让同一访客的会话按时间顺序聚集，关联时能更快定位到对应时间范围的数据。
  示例语句：

  ALTER TABLE lkp.session CLUSTER BY (visitor_id, session_datetime);
  

• 开启搜索优化服务（Search Optimization Service）：
  对于visitor_customer_hist这种频繁按visitor_id+时间范围过滤的表，开启该服务可以加速点查询和范围查询的性能，尤其适合数据更新不频繁的场景。

二、查询改写优化 #

• 利用无重叠数据特性简化关联逻辑：
  既然同一访客的时间区间没有重叠，我们可以改用QUALIFY窗口函数来匹配生效客户ID，避免大表关联时的冗余计算。改写后的查询如下：

  CREATE TABLE lkp.session_effective_customer AS
  SELECT 
      s.session_id,
      vch.customer_id AS effective_customer_id
  FROM lkp.session s
  LEFT JOIN lkp.visitor_customer_hist vch
      ON s.visitor_id = vch.visitor_id
  QUALIFY 
      s.session_datetime >= vch.from_datetime 
      AND s.session_datetime < vch.to_datetime
      -- 无重叠特性保证每个会话只会匹配到一条有效记录
      AND ROW_NUMBER() OVER (PARTITION BY s.session_id ORDER BY vch.from_datetime DESC) = 1;
  

  这种写法先按visitor_id关联，再通过窗口函数筛选出符合时间条件的唯一记录，能有效减少不必要的计算开销。
• 临时升级仓库规格或启用多集群自动缩放：
  虽然当前用了Large规格，可以尝试临时升级到XLarge甚至更大规格，或者开启多集群仓库的自动缩放功能，让Snowflake根据查询负载自动调配更多计算资源。

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