PostGIS空间查询添加sum()聚合后性能骤降问题咨询

问题背景 #

• 业务逻辑：使用PostGIS计算落入指定国家范围内的多边形总面积，跨国家边界的多边形仅统计与边界相交部分的面积。查询涉及两张表：Country表存储国家边界数据，cpdata_gb表存储互不重叠的待统计多边形数据。
• 基准查询表现：执行逐行返回所有相交多边形/相交片段面积的查询时，仅需10秒即可返回120万条结果，对应SQL如下：

select a.area from (
    select ct."Code", CASE
          WHEN ST_CoveredBy(cp.geom, ct."Boundary") THEN ST_Area(cp.geom)
          ELSE ST_Area(ST_Multi(ST_Intersection(cp.geom, ct."Boundary")))
          END as area
    from cpdata_gb as cp
    inner join "Country" as ct on ST_Intersects(cp.geom, ct."Boundary")
where ct."Code" = 'GB-ENG') as a;

对应执行计划：

Nested Loop  (cost=0.41..88198.66 rows=1655 width=8)
  ->  Seq Scan on "Country" ct  (cost=0.00..1.06 rows=1 width=32)
        Filter: (("Code")::text = 'GB-ENG'::text)
  ->  Index Scan using cpdata_gb_geom_idx on cpdata_gb cp  (cost=0.41..4825.32 rows=166 width=329)
        Index Cond: (geom && ct."Boundary")
        Filter: st_intersects(geom, ct."Boundary")

• 异常表现：添加sum()聚合函数统计总面积时，查询耗时飙升至6.5小时，对应SQL如下：

select sum(a.area) from (
    select ct."Code", CASE
          WHEN ST_CoveredBy(cp.geom, ct."Boundary") THEN ST_Area(cp.geom)
          ELSE ST_Area(ST_Multi(ST_Intersection(cp.geom, ct."Boundary")))
          END as area
    from cpdata_gb as cp
    inner join "Country" as ct on ST_Intersects(cp.geom, ct."Boundary")
where ct."Code" = 'GB-ENG') as a;

对应explain (analyze, buffers)执行计划：

Finalize GroupAggregate  (cost=1000.00..52619030.70 rows=1 width=15) (actual time=23188484.154..23188490.791 rows=1 loops=1)
  Group Key: ct."Code"
  Buffers: shared hit=2575358 read=70144
  ->  Gather  (cost=1000.00..52619030.68 rows=2 width=15) (actual time=23187705.263..23188490.608 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=2575358 read=70144
        ->  Partial GroupAggregate  (cost=0.00..52618030.48 rows=1 width=15) (actual time=23188004.960..23188004.962 rows=1 loops=3)
              Group Key: ct."Code"
              Buffers: shared hit=2575358 read=70144
              ->  Nested Loop  (cost=0.00..18017348.75 rows=686794 width=7976568) (actual time=299.721..25284.973 rows=403475 loops=3)
                    Join Filter: st_intersects(cp.geom, ct."Boundary")
                    Rows Removed by Join Filter: 147302
                    Buffers: shared hit=1662545 read=70144
                    ->  Parallel Seq Scan on cpdata_gb cp  (cost=0.00..84605.03 rows=687803 width=326) (actual time=2.933..2189.890 rows=550777 loops=3)
                          Buffers: shared hit=7583 read=70144
                    ->  Seq Scan on "Country" ct  (cost=0.00..1.06 rows=1 width=7976242) (actual time=0.007..0.007 rows=1 loops=1652330)
                          Filter: (("Code")::text = 'GB-ENG'::text)
                          Rows Removed by Filter: 1
                          Buffers: shared hit=1652330
Planning:
  Buffers: shared hit=250
Planning Time: 7.362 ms
JIT:
  Functions: 36
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.525 ms, Inlining 149.719 ms, Optimization 294.058 ms, Emission 219.979 ms, Total 666.279 ms
Execution Time: 23188588.520 ms

性能劣化核心原因 #

• 执行计划选择完全错误是性能暴跌的根源。对比两个执行计划可以发现：
  • 无聚合的快速查询走了高效路径：先通过顺序扫描快速定位Country表中编码为GB-ENG的单条英格兰边界记录，再命中cpdata_gb表的空间索引cpdata_gb_geom_idx，通过边界框预筛快速找到和英格兰范围相交的多边形，之后再做精确相交判断、面积计算，全程无效计算极少。
  • 带sum()的慢查询放弃了空间索引：优化器错误选择了并行全表扫描路径，启动2个并行工作进程对cpdata_gb做全表扫描，3个进程合计扫描165万+条多边形记录；对每一条扫描到的多边形，都重复扫描Country表做匹配，仅国家表的扫描就执行了165万次，产生巨量无效IO；全表扫描中合计过滤掉44万+条和英格兰范围不相交的多边形，带来海量无意义的空间判断计算。
• 统计信息偏差误导了优化器决策。慢查询执行计划中，优化器错误估算Country表的边界单字段长度接近8MB，高估了索引扫描时传递国家边界几何对象的成本，同时低估了全表扫描+空间相交计算的CPU成本，最终判定全表并行扫描的总成本更低。
• 两类查询的优化器评估逻辑差异触发了坏计划选择。不带聚合的查询需要逐行返回结果给客户端，优化器会优先选择启动成本低、返回首批结果速度快的索引扫描路径；带sum()的聚合查询不需要逐行返回中间结果，优化器会以“总执行成本最低”为目标选计划，在统计信息不准的情况下就选中了极差的执行方案。
• 额外开销放大了性能差距：并行执行带来的进程上下文切换、JIT编译的额外开销，进一步拉长了查询时间。

快速修复方案 #

• 会话级关闭并行查询验证问题：执行set max_parallel_workers_per_gather = 0;后再运行带sum()的查询，执行计划会回退到和无聚合查询一致的索引扫描路径，查询速度会回到10秒级。
• 使用物化CTE固定执行路径，避免优化器误判，示例SQL如下：

with england_boundary as materialized (
    select "Boundary" as geom from "Country" where "Code" = 'GB-ENG'
)
select sum(
    CASE
        WHEN ST_CoveredBy(cp.geom, eb.geom) THEN ST_Area(cp.geom)
        ELSE ST_Area(ST_Multi(ST_Intersection(cp.geom, eb.geom)))
    END
) as total_area
from cpdata_gb cp, england_boundary eb
where ST_Intersects(cp.geom, eb.geom);

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