如何将Snowflake SQL查询性能优化至5秒以内？

核心问题概述 #

当前查询的性能瓶颈集中在DB2.SCHEMA2.TABLE2的表扫描操作：该表包含234亿行数据（324.4GB），已按LINEAR(GG,SC)设置聚类键，优化后查询耗时从75秒降至25秒，但目标是将耗时控制在5秒以内。完整查询最终仅返回23行数据，但中间阶段（CTE2）会生成144万行的结果集。

现有查询与表结构信息 #

性能瓶颈查询片段 #

WITH CTE AS
(
  SELECT DISTINCT
      AC
  FROM "DB1"."SCHEMA1"."TABLE1"
  WHERE
      AG   = 'PPPPPP'
      AND MG = 'CCC'
      AND MC IN ('11')
)
SELECT
    c.AC
    ,VC
    ,VV
FROM "DB2"."SCHEMA2"."TABLE2" v
INNER JOIN CTE c ON v.AC = c.AC
WHERE
   SC = 70
   AND VC IN ( 'V1', 'V2' , 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20', 'V21', 'V22', 'V23', 'PP', 'HH' )
   AND GG = 'PPPPPP'

表详情 #

TABLE1（DB1.SCHEMA1.TABLE1）

• 数据量：22,852,551行（239.7MB）
• 无聚类键
• 表结构：

• CTE子查询返回57,936条去重后的AC值，执行速度快。

TABLE2（DB2.SCHEMA2.TABLE2）

• 数据量：23,449,693,351行（324.4GB）
• 聚类键：LINEAR(GG, SC)
• 表结构：

• 与CTE关联后返回1,448,400行，是查询性能瓶颈所在。

完整查询代码 #

WITH

CTE AS (
  SELECT DISTINCT
    AC
  FROM "DB1"."SCHEMA1"."TABLE1"
  WHERE
    AG   = 'PPPPPP'
    AND MG = 'CCC'
    AND MC IN ('11')
),
 
CTE2 AS (
  SELECT
    c.AC
    ,VC
    ,VV
  FROM "DB2"."SCHEMA2"."TABLE2" v
INNER JOIN CTE c ON v.AC = c.AC
WHERE
    SC = 70
    AND VC IN ( 'V1', 'V2' , 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20', 'V21', 'V22', 'V23', 'PP', 'HH' )
    AND GG = 'PPPPPP'
),
 
CTE3 AS (
    SELECT
      AC        
      ,VC
      ,CASE              
        WHEN "VC" IN ('V1', 'V2' , 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17') THEN 'PP'
        WHEN "VC" IN ('V18', 'V19', 'V20', 'V21', 'V22', 'V23') THEN 'HH'
      END AS BVC
      ,VV
    FROM CTE2
    WHERE VC IN ('V1', 'V2' , 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20', 'V21', 'V22', 'V23')
),
 
CTE4 AS (
    SELECT 
      AC, 
      VC, 
      VV AS BV
    FROM CTE2
    WHERE VC IN ('PP','HH')
),
 
CTE5 AS (
    SELECT  
      v.AC
      ,v.VC
      ,v.VV
      ,v.BVC 
      ,b.BV
    FROM CTE3 v       
    INNER JOIN CTE4 b ON v.AC = b.AC AND v.BVC = b.VC 
),
 
CTE6 AS (
    SELECT
        "__PPPPPP" AS CG
        ,SUM("__C") AS CW
    FROM "DB3"."SCHEMA3"."TABLE3"
    WHERE "__ID" = TRUE
    GROUP BY CG    
),
 
CTE7 AS (
    SELECT 
        v.VC        
        ,SUM(v.VV * c.CW) AS TWV
        ,SUM(v.BV * c.CW) AS TWBV
        ,DIV0(TWV, TWBV) AS PC
    FROM CTE5 v
    INNER JOIN CTE6 c ON v.AC = c.CG
    GROUP BY v.VC
),
 
CTE8 AS (
    SELECT 
        v.VC        
        ,SUM(v.VV) AS TBV
        ,SUM(v.BV) AS TBBV
        ,DIV0(TBV, TBBV) AS BPC
    FROM CTE5 v
    GROUP BY v.VC
)
 
SELECT  
    b.VC
    ,w.PC
    ,b.TBV
    ,b.BPC
    ,((DIV0(w.PC, b.BPC)) * 100)::INT AS Index
FROM CTE8 b 
LEFT JOIN CTE7 w ON b.VC = w.VC;

优化建议 #

1. 调整TABLE2的聚类键，强化数据剪枝 #

当前聚类键仅覆盖GG和SC，但查询还用到AC和VC的过滤条件。建议将聚类键调整为LINEAR(GG, SC, AC, VC)，这样可以进一步缩小扫描的微分区范围，直接跳过无关数据，大幅减少扫描的数据量。

2. 创建覆盖索引，避免回表查询 #

针对查询中用到的列（AC, GG, SC, VC, VV）创建覆盖索引，让Snowflake直接从索引中获取所需数据，无需扫描主表：

CREATE INDEX idx_table2_coverage ON "DB2"."SCHEMA2"."TABLE2" (GG, SC, AC, VC) INCLUDE (VV);

3. 调整过滤与JOIN顺序，减少中间数据量 #

将CTE2中的过滤操作提前到JOIN之前，先通过聚类键过滤掉大部分无关数据，再与CTE的AC列表进行JOIN：

CTE2 AS (
  SELECT
    v.AC
    ,v.VC
    ,v.VV
  FROM "DB2"."SCHEMA2"."TABLE2" v
  WHERE
    GG = 'PPPPPP'
    AND SC = 70
    AND VC IN ( 'V1', 'V2' , 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20', 'V21', 'V22', 'V23', 'PP', 'HH' )
  INNER JOIN CTE c ON v.AC = c.AC
)

4. 优化CTE的AC列表匹配逻辑 #

将CTE生成的AC列表写入临时表并创建索引，加速与TABLE2的JOIN操作：

-- 创建临时表存储去重后的AC值
CREATE TEMPORARY TABLE temp_ac_list AS
SELECT DISTINCT AC FROM "DB1"."SCHEMA1"."TABLE1"
WHERE AG = 'PPPPPP' AND MG = 'CCC' AND MC IN ('11');

-- 为临时表创建索引
CREATE INDEX idx_temp_ac ON temp_ac_list (AC);

-- 调整CTE2使用临时表JOIN
CTE2 AS (
  SELECT
    v.AC
    ,v.VC
    ,v.VV
  FROM "DB2"."SCHEMA2"."TABLE2" v
  INNER JOIN temp_ac_list c ON v.AC = c.AC
  WHERE
    GG = 'PPPPPP'
    AND SC = 70
    AND VC IN (...)
)

5. 合并后续CTE逻辑，减少中间结果集 #

将CTE3、CTE4、CTE5合并为一个CTE，直接在TABLE2上进行自JOIN，避免多次中间数据转换：

CTE3_4_5 AS (
  SELECT
    v.AC
    ,v.VC
    ,v.VV
    ,CASE              
      WHEN v.VC IN ('V1', 'V2' , 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17') THEN 'PP'
      WHEN v.VC IN ('V18', 'V19', 'V20', 'V21', 'V22', 'V23') THEN 'HH'
    END AS BVC
    ,b.VV AS BV
  FROM "DB2"."SCHEMA2"."TABLE2" v
  INNER JOIN CTE c ON v.AC = c.AC
  INNER JOIN "DB2"."SCHEMA2"."TABLE2" b 
    ON v.AC = b.AC 
    AND b.VC = CASE              
      WHEN v.VC IN ('V1', 'V2' , 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17') THEN 'PP'
      WHEN v.VC IN ('V18', 'V19', 'V20', 'V21', 'V22', 'V23') THEN 'HH'
    END
  WHERE
    v.GG = 'PPPPPP'
    AND v.SC = 70
    AND v.VC IN ('V1', 'V2' , 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20', 'V21', 'V22', 'V23')
    AND b.GG = 'PPPPPP'
    AND b.SC = 70
    AND b.VC IN ('PP','HH')
)

6. 临时扩容Warehouse，提升并行处理能力 #

如果上述优化仍未达标，可临时增大查询使用的Warehouse规格（如从XS调整为L/XL），利用更多计算资源并行处理扫描和JOIN操作，完成后再调回原规格控制成本。

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