升级Postgres/PostGIS后ST_DistanceSphere查询性能骤降问题排查

核心原因 #

1. 统计信息过时：PostgreSQL 14的查询优化器对表统计信息依赖度更高，迁移后未及时更新统计信息，导致优化器误判表数据分布，选择了效率更低的并行顺序扫描（Parallel Seq Scan）而非索引扫描。
2. 旧索引兼容性问题：虽然执行了postgis_extensions_upgrade()，但迁移自PostGIS 2.4.1的GIST索引内部格式未完全适配PostGIS 3.1.4，无法被新环境优化器高效利用。
3. 并行查询反向优化：针对1.3万行的小表，并行扫描的调度开销超过了并行收益，优化器错误评估了并行执行的成本。
4. ST_DistanceSphere索引逻辑变化：PostGIS 3.x对ST_DistanceSphere的索引支持逻辑有调整，原查询写法在新版本中无法触发GIST索引。

解决步骤 #

1. 更新表统计信息 #

执行命令强制更新my_table的统计数据，让优化器获取准确的表数据分布：

VACUUM ANALYZE my_table;

2. 重建GIST索引 #

重建索引确保格式适配PostGIS 3.1.4：

-- 方式1：删除后重建
DROP INDEX geofence_index;
CREATE INDEX geofence_index ON my_table USING GIST (geofence);

-- 方式2：直接重索引
REINDEX INDEX geofence_index;

3. 禁用小表并行扫描 #

针对1.3万行的小表，并行调度成本高于收益，可临时禁用该表的并行执行：

ALTER TABLE my_table SET (parallel_workers = 0);

4. 改写查询触发索引优化 #

将WHERE子句的ST_DistanceSphere替换为ST_DWithin并结合geography类型，PostGIS对地理类型的距离查询索引支持更高效：

SELECT ST_DistanceSphere('SRID=4326;POINT(13.154672331767976 55.673222697684935)'::geometry, mt.geofence) AS distance
FROM my_table mt 
WHERE ST_DWithin(mt.geofence::geography, 'SRID=4326;POINT(13.543852374474474 55.93984692695315)'::geography, 2000)
ORDER BY distance;

若频繁执行此类查询，建议创建地理类型专属索引：

CREATE INDEX geofence_geog_index ON my_table USING GIST (geofence::geography);

5. 调整PostgreSQL配置参数 #

检查Google Cloud SQL实例的effective_cache_size参数，设置为实例内存的70%-80%，让优化器更倾向于选择索引扫描。

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