PostgreSQL中字符串匹配更新与空间Join(ST_Intersects)哪个更快？

在处理15亿行的大规模数据时，基于多列字符串匹配的UPDATE操作速度会显著快于基于ST_Intersects的空间Join UPDATE操作，核心原因如下：

• 索引效率差异：字符串等值匹配的条件可依托成熟的B-tree索引，PostgreSQL对B-tree的查询优化极为完善，海量数据下的等值匹配能快速定位关联行，索引扫描的IO开销极低。而空间查询依赖的GIST空间索引，其构建和扫描的计算量远大于B-tree，且相交判断需要额外的几何计算逻辑，索引层面的性能差距明显。
• 单条记录处理开销：字符串等值匹配是简单的字符比对，CPU负载极低；而ST_Intersects需要对两个几何对象进行空间关系校验，涉及坐标运算、边界检测等复杂逻辑，单条记录的处理耗时是字符串匹配的数倍甚至数十倍，15亿行的规模会将这个差距无限放大。
• 关联结果集规模：多列字符串匹配的关联关系通常是一对一或少量一对多，过滤后的中间结果集更小；而空间相交可能出现一个网格匹配多个行政对象（或反之）的情况，Join阶段需要处理更多中间数据，大幅增加内存和IO压力，拖慢整体更新速度。

空间Join更新语句 #

UPDATE table_grid AS gd
SET col_grid = col_adm
FROM table_adm de
WHERE ST_Intersects(de.geom, gd.geom);

字符串匹配更新语句 #

UPDATE table_grid AS gd
SET col_grid = col_adm
FROM table_adm de
WHERE gd.col_match1 = de.col_match1
AND gd.col_match2 = de.col_match2
AND gd.col_match3 = de.col_match3;

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