PostgreSQL 大数据量空间查询耗时过长如何优化

从提供的执行计划可以明确几个核心耗时点：

• 4.9万次房屋自连接循环中，每次都要和预先合并的整体大障碍物Geometry做相交计算，大对象相交运算本身开销极高
• 全量结果排序用了外部磁盘合并，临时磁盘占用达到1.9GB，IO开销被大幅放大
• 窗口函数在2170万行的大结果集上重复执行排序逻辑，冗余计算开销高
• 自连接生成了(a,b)和(b,a)的重复配对，平白多了一倍的计算量

1. 改写障碍物相交判断逻辑，利用空间索引降低计算开销 #

不要提前用ST_Union把所有障碍物合并成一个大对象，改为直接用planet_osm_line的空间索引判断线段是否和任意障碍物相交，相交判断速度可提升数倍。

2. 临时调高work_mem避免磁盘排序 #

查询前临时调高工作内存，消除外部磁盘排序的IO开销：

SET work_mem = '4GB'; -- 足够容纳2170万行排序数据即可，查询结束后可恢复原值

3. 去重自连接配对，直接减少一半中间结果 #

用a.house_id < b.house_id替代原有的a.house_id != b.house_id，直接消除重复配对的冗余计算。

4. 预计算房屋序号映射，避免大结果集上跑重复窗口函数 #

提前给房屋ID生成序号映射，不需要在全量连接结果上重复执行dense_rank。

WITH house_idx AS (
    -- 预生成房屋ID和序号的映射，小表计算开销极低
    SELECT 
        house_id,
        row_number() over (order by house_id) - 1 as hid_idx
    FROM filtered_houses_29a
),
obstacles AS (
    -- 预查询当前簇所有障碍物，不需要合并
    SELECT l.way
    FROM cluster c
    JOIN planet_osm_line l ON st_dwithin(c.concave_hull, l.way, 500)
    WHERE c.cluster_id = 29
      AND tunnel IS NULL
      AND (
        highway IN ('motorway', 'motorway_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'tertiary_link')
        OR waterway IS NOT NULL
        OR railway IS NOT NULL
    )
)
SELECT 
    a.house_id,
    ai.hid_idx as a,
    bi.hid_idx as b,
    st_distance(a.geog, b.geog) as distance,
    rank() over (order by a.house_id) - 1 as indxptr,
    a.household_count
FROM filtered_houses_29a a
JOIN house_idx ai ON a.house_id = ai.house_id
JOIN filtered_houses_29a b 
    ON ST_DWithin(a.geog, b.geog, 150) 
    AND a.house_id < b.house_id -- 若业务需要保留双向配对，改回a.house_id != b.house_id即可
JOIN house_idx bi ON b.house_id = bi.house_id
WHERE NOT EXISTS (
    -- 利用障碍物表的空间索引做相交判断，开销远低于和大Union对象相交
    SELECT 1 FROM obstacles o
    WHERE ST_Intersects(ST_MakeLine(a.geom, b.geom), o.way)
)
ORDER BY a.house_id, distance;

• 可以给filtered_houses_29a的geom字段补充创建GIST空间索引，进一步加快线段的相交判断速度
• 若该查询是高频查询，可以把障碍物集合、房屋ID映射缓存为物化视图，避免每次查询重复计算

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