如何拆分耗时的PostgreSQL空间SQL查询以处理百万级数据

针对110万条点数据的空间匹配更新任务，直接全量执行会因计算负载过高导致超时或崩溃，通过分批循环处理可以解决这个问题。以下是两种可靠的实现方式，以及优化建议：

方法1：基于循环的固定批量处理 #

通过PL/pgSQL循环每次处理指定数量的未更新记录，直到所有符合条件的数据都完成匹配：

DO $$
DECLARE
    batch_size INT := 200000; -- 每次处理的记录数，可根据服务器性能调整
    updated_rows INT := batch_size;
BEGIN
    -- 只要上一批处理了满额的记录，就继续循环
    WHILE updated_rows = batch_size LOOP
        UPDATE alerts
        SET ogc_fid = subquery.ogc_fid
        FROM (
            SELECT a.uuid, ct.ogc_fid
            FROM alerts a
            JOIN competences_territoriales ct 
                ON ST_Intersects(ST_SetSRID(a.location::geometry, 4326), ct.wkb_geometry)
            WHERE ct.competence = 'GN'
              AND a.ogc_fid IS NULL -- 只处理未匹配的记录
            LIMIT batch_size
        ) AS subquery
        WHERE alerts.uuid = subquery.uuid
          AND alerts.ogc_fid IS NULL;
        
        -- 获取本次更新的行数，判断是否还有剩余记录
        GET DIAGNOSTICS updated_rows = ROW_COUNT;
        COMMIT; -- 每批提交，释放锁与内存资源
    END LOOP;
END $$;

方法2：按UUID范围分段处理（适合有序主键） #

如果alerts.uuid是有序生成的，可以通过分段UUID范围来避免重复扫描全表，提升效率：

DO $$
DECLARE
    batch_size INT := 200000;
    min_uuid UUID;
    max_uuid UUID;
    current_uuid UUID;
    updated_rows INT;
BEGIN
    -- 获取未更新记录的UUID范围
    SELECT MIN(uuid), MAX(uuid) INTO min_uuid, max_uuid 
    FROM alerts WHERE ogc_fid IS NULL;
    
    current_uuid := min_uuid;
    
    WHILE current_uuid < max_uuid LOOP
        UPDATE alerts
        SET ogc_fid = subquery.ogc_fid
        FROM (
            SELECT a.uuid, ct.ogc_fid
            FROM alerts a
            JOIN competences_territoriales ct 
                ON ST_Intersects(ST_SetSRID(a.location::geometry, 4326), ct.wkb_geometry)
            WHERE ct.competence = 'GN'
              AND a.ogc_fid IS NULL
              AND a.uuid >= current_uuid
            LIMIT batch_size
        ) AS subquery
        WHERE alerts.uuid = subquery.uuid
          AND alerts.ogc_fid IS NULL;
        
        GET DIAGNOSTICS updated_rows = ROW_COUNT;
        IF updated_rows = 0 THEN
            EXIT; -- 没有更多记录需要处理，退出循环
        END IF;
        
        -- 更新当前UUID范围的起点
        SELECT MAX(uuid) INTO current_uuid 
        FROM alerts WHERE ogc_fid IS NULL AND uuid >= current_uuid;
        
        COMMIT;
    END LOOP;
END $$;

关键优化建议 #

1. 添加空间索引：空间JOIN是性能瓶颈，必须确保两张表的几何字段有GIST索引：

-- 给alerts表的location字段创建空间索引（转换为4326坐标系）
CREATE INDEX IF NOT EXISTS idx_alerts_location_geom 
ON alerts USING GIST (ST_SetSRID(location::geometry, 4326));

-- 给competences_territoriales表的多边形字段创建空间索引
CREATE INDEX IF NOT EXISTS idx_ct_wkb_geometry 
ON competences_territoriales USING GIST (wkb_geometry);

-- 给competence字段添加普通索引，加速筛选
CREATE INDEX IF NOT EXISTS idx_ct_competence 
ON competences_territoriales (competence);

2. 调整批量大小：根据服务器CPU、内存性能调整batch_size，比如10万或30万，找到既能高效处理又不会过载的数值。

3. 低峰时段执行：避免在业务高峰期运行批量更新，减少对线上服务的影响。

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