PostgreSQL 10升13后空间查询耗时暴增：求原因与优化方案

核心问题拆解 #

原查询需匹配45000个多边形与95327个点，判断点是否在多边形内部或与其接触，升级后耗时从1.5小时暴增至7小时，核心问题大概率出在优化器执行计划选择偏差或空间函数/索引的兼容性问题。

可能的耗时激增原因 #

• 统计信息失效：PostgreSQL 12+对统计信息收集逻辑做了调整，升级后若未重新收集统计信息，优化器无法获取准确的数据分布（比如多边形的面积分布、点的聚集程度），可能选择低效的连接策略（如嵌套循环替代哈希连接），导致4.5万×9.5万级别的笛卡尔积空间计算。
• 空间条件的优化器处理变化：原查询用ST_Touches OR ST_Within的OR条件，PostgreSQL 13或对应升级的PostGIS版本可能无法为该OR条件有效利用空间GIST索引，转而执行全表扫描后逐行判断，大幅增加计算量。
• 连接顺序/策略变更：PG13优化器的代价估算模型有调整，可能将原本的「先过滤小结果集再空间连接」的计划，改为「先做全量空间匹配再关联属性表」，或者选择了不适合当前数据量的连接方式（如嵌套循环而非哈希连接）。
• PostGIS版本兼容性问题：若升级PG时同步升级了PostGIS，旧的空间索引可能存在兼容性问题，或新PostGIS对空间函数的实现效率下降。

优化方案 #

1. 刷新统计信息 #

首先确保优化器拥有准确的数据分布，执行以下命令：

ANALYZE polyssqlspatial;
ANALYZE pointssqlspatial;
ANALYZE ax11001;
ANALYZE AX14004;

2. 重构空间判断条件，简化OR逻辑 #

ST_Touches(point, poly) OR ST_Within(point, poly)等价于ST_Covers(poly, point)（多边形覆盖点，包含内部和边界接触的情况），用单一函数替代OR条件，更容易被优化器识别并利用空间索引：

SELECT 
    p.objid, pt.objid
FROM 
    polyssqlspatial p
JOIN ax11001 a1 ON p.objid = a1.ax11001_objid
JOIN pointssqlspatial pt ON ST_Covers(p.shape, pt.shape)
JOIN AX14004 a4 ON pt.objid = a4.AX14004_objid;

3. 验证并重建空间索引 #

确保两张空间表的shape字段存在有效的GIST索引：

-- 检查索引是否存在
SELECT indexname FROM pg_indexes WHERE tablename IN ('polyssqlspatial', 'pointssqlspatial') AND indexdef LIKE '%GIST%shape%';

-- 若不存在则创建，若存在则重建（修复可能的兼容性问题）
CREATE INDEX IF NOT EXISTS idx_polys_shape ON polyssqlspatial USING GIST(shape);
CREATE INDEX IF NOT EXISTS idx_points_shape ON pointssqlspatial USING GIST(shape);

-- 重建索引（若升级后索引失效）
REINDEX INDEX idx_polys_shape;
REINDEX INDEX idx_points_shape;

4. 调整执行计划策略 #

如果优化器仍选择低效计划，可尝试强制连接方式或顺序：

• 强制使用哈希连接（需先安装pg_hint_plan扩展）：

/*+ HashJoin(p pt) */
SELECT 
    p.objid, pt.objid
FROM 
    polyssqlspatial p
JOIN ax11001 a1 ON p.objid = a1.ax11001_objid
JOIN pointssqlspatial pt ON ST_Covers(p.shape, pt.shape)
JOIN AX14004 a4 ON pt.objid = a4.AX14004_objid;

• 临时调整优化器参数（仅测试用）：

SET enable_nestloop = off; -- 关闭嵌套循环，迫使优化器选择哈希或合并连接
-- 执行查询后再改回默认
SET enable_nestloop = on;

5. 提前过滤结果集 #

如果ax11001和AX14004表存在过滤条件（用户原查询未体现），需将过滤条件前置，减少后续空间连接的数据量：

SELECT 
    p.objid, pt.objid
FROM 
    (SELECT * FROM polyssqlspatial p JOIN ax11001 a1 ON p.objid = a1.ax11001_objid WHERE a1.some_condition = 'xxx') p
JOIN 
    (SELECT * FROM pointssqlspatial pt JOIN AX14004 a4 ON pt.objid = a4.AX14004_objid WHERE a4.some_condition = 'xxx') pt
ON ST_Covers(p.shape, pt.shape);

验证步骤 #

1. 执行EXPLAIN ANALYZE对比优化前后的执行计划，重点查看是否使用了空间索引、连接方式是否合理、扫描行数是否大幅减少。
2. 逐步应用优化方案，每一步后测试查询耗时，定位最有效的优化点。

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