Snowflake中ST_POLYGON(TO_GEOGRAPHY())静态多边形调用效率异常问题咨询

核心观测特征 #

• 带地理空间过滤条件的查询耗时远超预期，将逻辑简化为仅对固定静态字符串执行ST_POLYGON(TO_GEOGRAPHY(...))计算时，慢查询问题依然复现
• 性能表现不符合静态函数结果复用的预期：按照通用SQL引擎优化逻辑，无外部字段依赖、无动态输入的纯静态函数，应当在查询初始化阶段仅计算一次，将结果复用给所有输出行；但实际100万行的生成器查询（仅输出上述地理空间计算结果）在x-small仓库上耗时超1分钟，而同等长度的字符串直接输出的300万行查询，仅需2秒即可完成，证明瓶颈并非网络传输、结果序列化的固定开销，也和输出的数据体积大小无关
• 耗时和返回行数呈严格的线性正相关，和逐行重复执行地理空间函数计算的性能特征完全匹配

复现测试用例 #

慢查询（逐行执行地理空间计算） #

SELECT 
    ST_POLYGON(TO_GEOGRAPHY('LINESTRING(-95.75122850074004 28.793166796020444,-95.68622920563344 30.207416499279063,-94.5162418937178 32.56537633083211,-90.94128066286225 34.24734047810797,-88.17881062083825 36.812423897251634,-86.13133282498448 38.15341651409619,-85.28634198860107 38.66275098353796,-84.37635185711038 38.789523129087826,-82.84886842210855 38.4848923369382,-82.32887406125734 37.820427257446994,-82.26387476615074 36.96838022284757,-82.03637723327772 36.00158943485101,-80.99638851157454 35.34155096040939,-78.52641529752944 34.62260477275565,-77.51892622337955 34.005211031324734,-78.26641811710381 31.1020568651834,-80.24889661785029 29.926151366059756,-83.59636031583283 28.793166796020444,-95.75122850074004 28.793166796020444)'))
FROM TABLE(GENERATOR(ROWCOUNT=>1000000))

对照快查询（仅输出等长字符串） #

SELECT 
    'LINESTRING(-95.75122850074004 28.793166796020444,-95.68622920563344 30.207416499279063,-94.5162418937178 32.56537633083211,-90.94128066286225 34.24734047810797,-88.17881062083825 36.812423897251634,-86.13133282498448 38.15341651409619,-85.28634198860107 38.66275098353796,-84.37635185711038 38.789523129087826,-82.84886842210855 38.4848923369382,-82.32887406125734 37.820427257446994,-82.26387476615074 36.96838022284757,-82.03637723327772 36.00158943485101,-80.99638851157454 35.34155096040939,-78.52641529752944 34.62260477275565,-77.51892622337955 34.005211031324734,-78.26641811710381 31.1020568651834,-80.24889661785029 29.926151366059756,-83.59636031583283 28.793166796020444,-95.75122850074004 28.793166796020444)'
FROM TABLE(GENERATOR(ROWCOUNT=>3000000))

已尝试的优化手段 #

• 使用CTE（WITH子句）封装静态多边形的计算逻辑，意图让优化器识别到计算无外部依赖、仅需执行一次，实际被优化器判定为冗余计算自动消除，依然走逐行执行的执行路径
• 尝试通过会话变量存储预计算的地理空间对象，发现Snowflake原生会话变量不支持承载GEOGRAPHY/GEOMETRY类型的复杂地理空间值，无法实现预计算结果的复用

问题判定 #

现有Snowflake执行逻辑中，无外部输入依赖的地理空间函数计算没有被归类为可全局复用的常量表达式，引擎会强制逐行重复执行计算，和优化器对普通字符串函数的常量折叠、结果复用逻辑不一致，属于不符合预期的产品缺陷。

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