为何Postgres优化器对st_equals连接采用嵌套循环而非哈希连接？

问题描述 #

在使用PostgreSQL结合PostGIS时，发现用ST_Equals做空间连接速度极慢，换成=运算符后速度大幅提升。通过EXPLAIN分析看到，ST_Equals连接采用嵌套循环，=连接采用哈希连接。两张表的geom字段均已创建GIST索引，且ST_Equals可利用索引，每张表约22万条数据。

查询语句与执行计划 #

查询A（ST_Equals连接） #

explain
 select p1.geom from schema1.parcel p1
   join schema2.parcel p2
    on st_equals(p2.geom, p1.geom)
  where
    p2.pid is null

执行计划：

Gather  (cost=1000.42..2819605.27 rows=808 width=296)
  Workers Planned: 2
  ->  Nested Loop  (cost=0.42..2818524.47 rows=337 width=296)
        Join Filter: st_equals(p2.geom, p1.geom)
        ->  Parallel Seq Scan on parcel p1  (cost=0.00..9390.93 rows=95393 width=296)
        ->  Index Scan using parcel_pkey on parcel p2  (cost=0.42..4.44 rows=1 width=300)
              Index Cond: (pid IS NULL)

查询B（=运算符连接） #

explain
 select p1.geom from schema1.parcel p1
   join schema2.parcel p2
     on p2.geom = p1.geom
  where
    p2.pid is null

执行计划：

Gather  (cost=1004.45..10776.96 rows=229 width=296)
  Workers Planned: 2
  ->  Hash Join  (cost=4.45..9754.06 rows=95 width=296)
        Hash Cond: (p1.geom = p2.geom)
        ->  Parallel Seq Scan on parcel p1  (cost=0.00..9390.93 rows=95393 width=296)
        ->  Hash  (cost=4.44..4.44 rows=1 width=300)
              ->  Index Scan using parcel_pkey on parcel p2  (cost=0.42..4.44 rows=1 width=300)
                    Index Cond: (pid IS NULL)

原因分析 #

1. 运算符底层逻辑差异：=运算符针对PostGIS几何类型是直接比较对象的二进制表示，属于严格的等值比较，PostgreSQL优化器能识别这种关系，因此选择更高效的哈希连接（哈希连接适合大表间的等值匹配，尤其是当其中一张表数据量较小时）。
2. ST_Equals的函数特性：ST_Equals是空间判断函数，用于判断两个几何对象的拓扑等价性（比如顶点顺序不同但形状相同的多边形也会被判定为相等）。虽然它能利用GIST索引，但优化器不会将其视为严格等值条件，默认倾向于选择嵌套循环；加上优化器对ST_Equals的结果行数估计偏差，导致实际执行时大量行需要做空间计算，性能骤降。
3. 执行计划预估偏差：从执行计划看，查询A的预估行数（808行）与查询B（229行）差异较大，说明优化器对ST_Equals的结果分布判断不准确，进而选错了连接策略。

优化方案（让ST_Equals连接提速） #

• 强制切换连接策略：临时关闭嵌套循环，让优化器选择哈希连接（仅当前会话生效）：

  SET enable_nestloop = off;
  -- 执行你的ST_Equals连接查询
  SET enable_nestloop = on;
  

• 子查询过滤小数据集：先过滤出p2.pid IS NULL的极小数据集，再做连接，即使是嵌套循环也能大幅提升性能：

  SELECT p1.geom
  FROM schema1.parcel p1
  JOIN (
      SELECT geom FROM schema2.parcel WHERE pid IS NULL
  ) p2 ON ST_Equals(p2.geom, p1.geom);
  

• 更新统计信息：让优化器更准确判断执行计划：

  ANALYZE schema1.parcel;
  ANALYZE schema2.parcel;
  

• 预计算几何哈希值：通过预存几何对象的哈希值，先做哈希等值连接，再用ST_Equals验证，兼顾效率与准确性：

  -- 给表添加哈希字段并建B-tree索引
  ALTER TABLE schema1.parcel ADD COLUMN geom_hash bigint GENERATED ALWAYS AS (ST_Hash(geom)) STORED;
  CREATE INDEX idx_parcel_geom_hash ON schema1.parcel USING btree(geom_hash);
  
  ALTER TABLE schema2.parcel ADD COLUMN geom_hash bigint GENERATED ALWAYS AS (ST_Hash(geom)) STORED;
  CREATE INDEX idx_parcel2_geom_hash ON schema2.parcel USING btree(geom_hash);
  
  -- 优化后的查询
  SELECT p1.geom
  FROM schema1.parcel p1
  JOIN schema2.parcel p2 
    ON p1.geom_hash = p2.geom_hash AND ST_Equals(p1.geom, p2.geom)
  WHERE p2.pid IS NULL;
  

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