如何优化包含WKT的Snowflake SQL查询性能？

原查询语句 #

WITH CustomArea AS (
    SELECT 
        "PPPPPP" AS GC,
        ROUND(SUM(HP),5) AS HP,
        ROUND(SUM(PP),5) AS PP  
    FROM 
        "DB1"."SCHEMA1"."TABLE1"
    WHERE 
        ST_WITHIN (CE, TO_GEOGRAPHY('POLYGON ((...))'))
    GROUP BY 
        GC
),
FilteredData AS (
    SELECT
        tbl.VC,
        CASE
            WHEN tbl.WU = 'PP' THEN a.PP * tbl.VV
            WHEN tbl.WU = 'HH' THEN a.HP * tbl.VV
            ELSE tbl.VV
        END AS Count
    FROM 
        "DB2"."SCHEMA2"."TABLE2" tbl
    INNER JOIN 
        CustomArea a ON tbl.CC = a.GC 
    WHERE 
        tbl.GG = 'PPPPPP'
    AND 
        tbl.VC IN ('ABC', 'DEF', 'GHI')
)
SELECT
    VC,
    SUM(Count) AS Count
FROM 
    FilteredData
GROUP BY 
    VC;

涉及表结构信息 #

DB1.SCHEMA1.TABLE1 #

• Cluster by: 无

DB2.SCHEMA2.TABLE2 #

• Cluster by: LINEAR(GG, YY, SC)

查询执行计划 #

{
    "GlobalStats": {
        "partitionsTotal": 1085,
        "partitionsAssigned": 183,
        "bytesAssigned": 2916814336
    },
    "Operations": [
        [
            {
                "id": 0,
                "operation": "Result",
                "expressions": [
                    "tbl.VC",
                    "SUM(IFF(tbl.WU = 'PP', (TO_DOUBLE(ROUND(SUM(TABLE1.PP), 5))) * tbl.VV, IFF(tbl.WU = 'HH', (TO_DOUBLE(ROUND(SUM(TABLE1.HP), 5))) * tbl.VV, tbl.VV)))
                ]
            },
            {
                "id": 1,
                "operation": "Aggregate",
                "expressions": [
                    "aggExprs: [SUM(IFF(tbl.WU = 'PP', (TO_DOUBLE(ROUND(SUM(TABLE1.PP), 5))) * tbl.VV, IFF(tbl.WU = 'HH', (TO_DOUBLE(ROUND(SUM(TABLE1.HP), 5))) * tbl.VV, tbl.VV)))]",
                    "groupKeys: [tbl.VC]"
                ],
                "parentOperators": [
                    0
                ]
            },
            {
                "id": 2,
                "operation": "InnerJoin",
                "expressions": [
                    "joinKey: (TABLE1.PPPPPP = tbl.CC)"
                ],
                "parentOperators": [
                    1
                ]
            },
            {
                "id": 3,
                "operation": "Aggregate",
                "expressions": [
                    "aggExprs: [SUM(TABLE1.HP), SUM(TABLE1.PP)]",
                    "groupKeys: [TABLE1.PPPPPP]"
                ],
                "parentOperators": [
                    2
                ]
            },
            {
                "id": 4,
                "operation": "Filter",
                "expressions": [
                    "ST_CONTAINS_LNGLAT_ROUND(TO_BINARY(GET(IFF(PARSE_GEO('POLYGON ((...))') IS NULL, null, OBJECT_CONSTRUCT('_shape', PARSE_GEO('POLYGON ((...))'), 'version', 1, 'has_internal', TRUE, 'internal', GEOGRAPHY_COMPUTE_INTERNAL(PARSE_GEO('POLYGON ((...))')))), 'internal')), TO_BINARY(GET_PATH(TABLE1.CE, 'internal')))"
                ],
                "parentOperators": [
                    3
                ]
            },
            {
                "id": 5,
                "operation": "TableScan",
                "objects": [
                    "DB1.SCHEMA1.TABLE1"
                ],
                "expressions": [
                    "HP",
                    "PP",
                    "PPPPPP"
                ],
                "partitionsAssigned": 3,
                "partitionsTotal": 3,
                "bytesAssigned": 48081920,
                "parentOperators": [
                    4
                ]
            },
            {
                "id": 6,
                "operation": "Filter",
                "expressions": [
                    "(tbl.VC IN 'ABC' IN 'DEF' IN 'GHI') AND (tbl.GG = 'PPPPPP')"
                ],
                "parentOperators": [
                    2
                ]
            },
            {
                "id": 7,
                "operation": "JoinFilter",
                "expressions": [
                    "joinKey: (TABLE1.PPPPPP = tbl.CC)"
                ],
                "parentOperators": [
                    6
                ]
            },
            {
                "id": 8,
                "operation": "TableScan",
                "objects": [
                    "DB2.SCHEMA2.TABLE2"
                ],
                "expressions": [
                    "CC",
                    "GG",
                    "VC",
                    "VV",
                    "WU"
                ],
                "alias": "tbl",
                "partitionsAssigned": 180,
                "partitionsTotal": 1082,
                "bytesAssigned": 2868732416,
                "parentOperators": [
                    7
                ]
            }
        ]
    ]
}

性能优化建议 #

1. 优化地理空间查询（TABLE1） #

• 为TABLE1的CE列创建地理空间索引：Snowflake的GEOGRAPHY列索引能大幅加速ST_WITHIN这类空间过滤操作，执行语句：

  CREATE INDEX idx_table1_ce ON "DB1"."SCHEMA1"."TABLE1"(CE);
  

• 预解析WKT多边形：将TO_GEOGRAPHY('POLYGON ((...))')的结果存入会话变量，避免查询执行时重复解析：

  SET target_polygon = TO_GEOGRAPHY('POLYGON ((...))');
  
  WITH CustomArea AS (
      SELECT 
          "PPPPPP" AS GC,
          ROUND(SUM(HP),5) AS HP,
          ROUND(SUM(PP),5) AS PP  
      FROM 
          "DB1"."SCHEMA1"."TABLE1"
      WHERE 
          ST_WITHIN (CE, $target_polygon)
      GROUP BY 
          GC
  )
  -- 后续查询逻辑不变
  

2. 优化TABLE2的扫描效率 #

• 调整聚类键：当前TABLE2的聚类键是LINEAR(GG, YY, SC)，但查询过滤条件是GG = 'PPPPPP'和VC IN ('ABC', 'DEF', 'GHI')，关联键是CC。可将聚类键调整为LINEAR(GG, VC, CC)，让符合过滤条件的数据物理上更集中，减少扫描的分区和字节数。
• 创建组合覆盖索引：针对查询中用到的过滤、关联和计算列，创建索引避免回表查询：

  CREATE INDEX idx_table2_gg_vc_cc ON "DB2"."SCHEMA2"."TABLE2"(GG, VC, CC) INCLUDE(VV, WU);
  

3. 优化关联与聚合逻辑 #

• 统一关联列数据类型：TABLE1的PPPPPP是VARCHAR(8)，TABLE2的CC是VARCHAR(1000)，若CC的前8位与PPPPPP对应，可显式转换类型避免隐式转换导致的索引失效：

  INNER JOIN CustomArea a ON LEFT(tbl.CC, 8) = a.GC
  

• 用临时表存储聚合结果：CustomArea的聚合结果行数通常较少，存入临时表后再关联TABLE2，减少关联时的数据传输量：

  CREATE OR REPLACE TEMPORARY TABLE temp_custom_area AS
  SELECT 
      "PPPPPP" AS GC,
      ROUND(SUM(HP),5) AS HP,
      ROUND(SUM(PP),5) AS PP  
  FROM 
      "DB1"."SCHEMA1"."TABLE1"
  WHERE 
      ST_WITHIN (CE, $target_polygon)
  GROUP BY 
      GC;
  
  -- 后续查询使用temp_custom_area关联TABLE2
  

4. 简化计算逻辑 #

• 推迟ROUND操作：当前在CustomArea中对SUM结果做ROUND，后续关联时又转换为DOUBLE，可推迟到最终聚合后再执行ROUND，减少中间计算的性能开销：

  WITH CustomArea AS (
      SELECT 
          "PPPPPP" AS GC,
          SUM(HP) AS HP,
          SUM(PP) AS PP  
      FROM 
          "DB1"."SCHEMA1"."TABLE1"
      WHERE 
          ST_WITHIN (CE, $target_polygon)
      GROUP BY 
          GC
  ),
  FilteredData AS (
      SELECT
          tbl.VC,
          CASE
              WHEN tbl.WU = 'PP' THEN a.PP * tbl.VV
              WHEN tbl.WU = 'HH' THEN a.HP * tbl.VV
              ELSE tbl.VV
          END AS Count
      FROM 
          "DB2"."SCHEMA2"."TABLE2" tbl
      INNER JOIN 
          CustomArea a ON tbl.CC = a.GC 
      WHERE 
          tbl.GG = 'PPPPPP'
      AND 
          tbl.VC IN ('ABC', 'DEF', 'GHI')
  )
  SELECT
      VC,
      ROUND(SUM(Count),5) AS Count
  FROM 
      FilteredData
  GROUP BY 
      VC;
  

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