PostgreSQL几何列相交左连接求和错误及性能优化问询

我有两个数据量较大的表，结构如下：

CREATE TABLE lines
(
    "Id" text COLLATE pg_catalog."default" NOT NULL,
    "ProjectId" text COLLATE pg_catalog."default" NOT NULL,
    "Geometry" geography(LineString,4326) NOT NULL,
    "Type" text COLLATE pg_catalog."default" NOT NULL,
    "Length" numeric,
    "Depth" numeric,
    "Source" text COLLATE pg_catalog."default"
)

CREATE TABLE corners
(
    "Id" integer NOT NULL,
    "CornerNumber" integer NOT NULL DEFAULT 1,
    "Polygon" geography(Polygon,4326) NOT NULL
)

已创建索引：

CREATE INDEX IF NOT EXISTS idx_lines_geometry ON lines USING GIST ("Geometry");
CREATE INDEX IF NOT EXISTS idx_corners_polygon ON corners USING GIST ("Polygon");

需求是按corners."CornerNumber"、lines."Type"、lines."Depth"、lines."Source"分组，对lines."Length"列求和，基于地理列进行连接。

现有问题与尝试 #

1. 初始左连接查询（求和错误）
   左连接会在lines记录与多个corners记录相交时返回重复行，导致求和结果偏大：

   select 
       c."CornerNumber", 
       l."Type",
       l."Depth", 
       l."Source", 
       sum(l."Length") as Length, 
   from lines l 
   left join corners c on ST_Intersects(l."Geometry", c."Polygon") 
   where l."ProjectId" = '12345'
   group by 
       c."CornerNumber", 
       l."Type",
       l."Depth", 
       l."Source" 
   ;
   

2. LATERAL子查询（正确但性能差）
   修正了求和问题，但耗时翻倍：

   select 
       c."CornerNumber", 
       l."Type",
       l."Depth", 
       l."Source", 
       sum(l."Length") as Length, 
   from lines l 
   left join lateral (
       select cj."CornerNumber" 
       from corners cj 
       where ST_Intersects(l."Geometry", cj."Polygon") 
       group by cj."CornerNumber"
   ) c on true 
   where l."ProjectId" = '12345'
   group by 
       c."CornerNumber", 
       l."Type",
       l."Depth", 
       l."Source" 
   ;
   

3. 分区查询（正确且性能优于LATERAL）
   用ROW_NUMBER()去重，性能介于错误查询和LATERAL之间：

   WITH filtered_lines AS (
       SELECT 
           "Id", "Type", "Depth", "Source", "Length", "Geometry"
       FROM 
           lines 
       WHERE 
           "ProjectId" = '12345'
   ),
   intersected_lines AS (
       SELECT 
           fl."Type", 
           fl."Depth", 
           fl."Source", 
           fl."Length", 
           cj."CornerNumber",
           ROW_NUMBER() OVER (PARTITION BY fl."Id", fl."Type", fl."Depth", fl."Source" ORDER BY cj."CornerNumber") as row_num
       FROM 
           filtered_lines fl
       LEFT JOIN 
           corners cj 
       ON 
           ST_Intersects(fl."Geometry", cj."Polygon")
   )
   SELECT 
       "CornerNumber", 
       "Type", 
       "Depth", 
       "Source", 
       SUM("Length") as "TotalLength"
   FROM 
       intersected_lines
   WHERE 
       row_num = 1
   GROUP BY 
       "CornerNumber", 
       "Type", 
       "Depth", 
       "Source"
   ;
   

各方法耗时对比（平均数据量项目）：

• 错误查询：3.6 - 4.1秒
• LATERAL子查询：7.2 - 7.6秒
• 分区查询：3.8 - 4.8秒

需要找到更高效的连接方式，既能正确求和，又比分区查询更快。

方案1：先聚合lines再关联地理查询 #

核心思路是先对lines按分组维度（Type、Depth、Source）聚合求和，再关联corners做地理相交匹配，避免重复计算长度，大幅减少后续地理连接的数据量。

SELECT 
    c."CornerNumber",
    l_agg."Type",
    l_agg."Depth",
    l_agg."Source",
    l_agg."TotalLength"
FROM (
    SELECT 
        "Type",
        "Depth",
        "Source",
        SUM("Length") AS "TotalLength",
        -- 聚合该分组下的所有几何，用于后续相交判断
        ST_Collect("Geometry") AS "GroupGeometry"
    FROM lines
    WHERE "ProjectId" = '12345'
    GROUP BY "Type", "Depth", "Source"
) l_agg
LEFT JOIN corners c 
    ON ST_Intersects(l_agg."GroupGeometry", c."Polygon")

如果同一分组的lines几何可能和多个corner相交，且需要每个corner都对应分组的求和值，这个方法效率极高——因为先将lines的行数压缩到分组级别，再执行地理连接操作。

方案2：使用数组关联+unnest（减少连接次数） #

先为每条line找到所有相交的CornerNumber并存储为数组，再通过unnest展开数组并聚合，避免笛卡尔积导致的重复计算。

SELECT 
    unnest(corners_arr) AS "CornerNumber",
    "Type",
    "Depth",
    "Source",
    SUM("Length") AS "TotalLength"
FROM (
    SELECT 
        l."Type",
        l."Depth",
        l."Source",
        l."Length",
        -- 获取所有相交的CornerNumber数组
        ARRAY(
            SELECT cj."CornerNumber" 
            FROM corners cj 
            WHERE ST_Intersects(l."Geometry", cj."Polygon")
        ) AS corners_arr
    FROM lines l
    WHERE l."ProjectId" = '12345'
) sub
GROUP BY unnest(corners_arr), "Type", "Depth", "Source"
-- 补充处理没有相交corner的情况，保留NULL值
UNION ALL
SELECT 
    NULL AS "CornerNumber",
    "Type",
    "Depth",
    "Source",
    SUM("Length") AS "TotalLength"
FROM lines l
WHERE 
    l."ProjectId" = '12345'
    AND NOT EXISTS (
        SELECT 1 FROM corners cj WHERE ST_Intersects(l."Geometry", cj."Polygon")
    )
GROUP BY "Type", "Depth", "Source"

这个方法每条line只执行一次地理查询，然后通过数组展开得到对应的corner分组，性能通常优于LATERAL和分区查询。

方案3：优化分区查询的索引与逻辑 #

如果坚持使用分区查询，可以通过简化分区字段、添加辅助索引来降低开销：

-- 先创建辅助索引，让地理查询直接获取CornerNumber，无需回表
CREATE INDEX idx_corners_polygon_corner ON corners USING GIST ("Polygon") INCLUDE ("CornerNumber");

-- 优化后的查询
WITH filtered_lines AS (
    SELECT 
        "Id", "Type", "Depth", "Source", "Length", "Geometry"
    FROM lines 
    WHERE "ProjectId" = '12345'
),
intersected_lines AS (
    SELECT 
        fl."Type", 
        fl."Depth", 
        fl."Source", 
        fl."Length", 
        cj."CornerNumber",
        -- 简化分区字段（Id唯一，已能标识单条line），减少分区计算量
        ROW_NUMBER() OVER (PARTITION BY fl."Id" ORDER BY cj."CornerNumber") as row_num
    FROM filtered_lines fl
    LEFT JOIN corners cj 
        ON ST_Intersects(fl."Geometry", cj."Polygon")
)
SELECT 
    "CornerNumber", 
    "Type", 
    "Depth", 
    "Source", 
    SUM("Length") as "TotalLength"
FROM intersected_lines
WHERE row_num = 1
GROUP BY "CornerNumber", "Type", "Depth", "Source"

通用性能建议 #

1. 给lines表的ProjectId字段加索引：CREATE INDEX idx_lines_projectid ON lines ("ProjectId");，加速初始数据过滤。
2. 执行表统计分析：ANALYZE lines; ANALYZE corners;，让PostgreSQL优化器获取最新的表统计信息，生成更优的执行计划。

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