无聚类大表Stream查询耗时差异异常：64/63亿行表排查

核心现象对比 #

• Stream_1：查询数秒返回，仅处理约800万新增行（Stream捕获的增量变更）
• Stream_2：查询耗时超1小时，执行两次全表扫描后关联，最终结果约1200万行

关键原因分析 #

1. Stream_2进入「Stale（过期）」状态

Snowflake标准Stream依赖表的变更日志捕获增量数据，默认变更数据保留窗口为14天。如果Stream_2长时间未被消费（超过保留窗口），或者表的变更日志被清理，Stream会进入Stale状态：

• 此时查询无法直接读取已捕获的增量数据，只能通过扫描基线表+历史变更日志的方式重构变更记录，这就会触发两次全表扫描（对应你看到的查询剖面），63亿行的全表扫描自然耗时极久。
• 而Stream_1的变更数据仍在保留窗口内，查询直接读取预捕获的800万增量行，所以速度极快。

2. Table_2发生过DDL变更

若Stream_2创建后，Table_2执行过DDL操作（如修改列类型、添加/删除列、调整表属性等），会导致Stream的元数据与表结构不一致。此时查询Stream会触发全表扫描来重新同步变更数据，替代原本的增量读取逻辑。

3. 变更数据分布差异

即使Stream状态正常，如果Table_2的变更分散在大量微分区中，而Stream_1的变更集中在少数微分区，也会导致查询效率差异：

• Stream_1可以快速定位到包含变更的微分区，直接读取增量；
• Stream_2需要扫描更多微分区来聚合变更，甚至触发全表扫描。

验证步骤 #

1. 检查Stream状态
   执行以下命令查看Stream_2是否过期：

DESCRIBE STREAM db.schema.STREAM_2;

重点查看STALE列：若值为TRUE，说明Stream已过期；同时查看LAST_CONSUMED列，确认是否长时间未消费。

2. 检查表的DDL历史
   确认Table_2在Stream创建后是否有DDL变更：

SELECT 
  TABLE_NAME, 
  CREATE_TIME, 
  LAST_ALTERED 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'schema' AND TABLE_NAME = 'TABLE_2';

如果LAST_ALTERED晚于Stream的创建时间，说明表结构有变更导致Stream异常。

3. 对比Stream消费记录
   查看两个Stream的消费情况：

SELECT 
  STREAM_NAME, 
  STALE, 
  LAST_CONSUMED, 
  RETENTION_TIME 
FROM INFORMATION_SCHEMA.STREAMS 
WHERE STREAM_SCHEMA = 'schema' AND STREAM_NAME IN ('STREAM_1', 'STREAM_2');

解决方案 #

1. 重建过期的Stream
   如果Stream_2已Stale，直接重建Stream以重新捕获增量：

DROP STREAM IF EXISTS db.schema.STREAM_2;
CREATE STREAM IF NOT EXISTS db.schema.STREAM_2 ON TABLE db.schema.table_2;

重建后，新的查询会读取最新的增量变更（若有），避免全表扫描。

2. 定期消费Stream
   确保Stream被定期查询/消费，避免超过14天的默认保留窗口导致过期。若需要更长的保留时间，可在创建Stream时指定RETENTION_TIME参数（最大可设为90天）。

3. 统一日期处理逻辑
   将Stream_2的查询改为与Stream_1一致的日期转换方式，避免因时间戳精度影响分组效率（虽然结果行数差异不大，但可优化查询计划）：

select date_column::date, count(1) 
from db.schema.stream_2 
group by date_column::date 
order by date_column::date desc;

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