同机复制PostgreSQL库后地理位置查询性能下降5倍问题排查

架构老林

2026-8-27

问题背景

在基于位置服务的应用中，存在一条对执行性能要求较高的特定查询：

SELECT count(*) FROM users
  WHERE earth_box(ll_to_earth(40.71427000, -74.00597000), 50000) @> ll_to_earth(latitude, longitude)

使用PostgreSQL官方工具完成数据库复制操作后：

pg_dump dummy_users > dummy_users.dump
createdb slow_db
psql slow_db < dummy_users.dump

新库slow_db上的该查询耗时从原库的0.5秒上升至2.5秒，性能下降5倍。
慢库中查询优化器选择了不同的执行路径，slow_db上的EXPLAIN ANALYZE执行结果如下：

"Aggregate  (cost=10825.18..10825.19 rows=1 width=8) (actual time=2164.396..2164.396 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on users  (cost=205.45..10818.39 rows=2714 width=0) (actual time=26.188..2155.680 rows=122836 loops=1)"
"        Recheck Cond: ('(1281995.9045467733, -4697354.822067326, 4110397.4955141144),(1381995.648489849, -4597355.078124251, 4210397.23945719)'::cube @> (ll_to_earth(latitude, longitude))::cube)"
"        Rows Removed by Index Recheck: 364502"
"        Heap Blocks: exact=57514 lossy=33728"
"        ->  Bitmap Index Scan on distance_index  (cost=0.00..204.77 rows=2714 width=0) (actual time=20.068..20.068 rows=122836 loops=1)"
"              Index Cond: ((ll_to_earth(latitude, longitude))::cube <@ '(1281995.9045467733, -4697354.822067326, 4110397.4955141144),(1381995.648489849, -4597355.078124251, 4210397.23945719)'::cube)"
"Planning Time: 1.002 ms"
"Execution Time: 2164.807 ms"

原数据库上的EXPLAIN ANALYZE执行结果如下：

"Aggregate  (cost=8807.01..8807.02 rows=1 width=8) (actual time=239.524..239.525 rows=1 loops=1)"
"  ->  Index Scan using distance_index on users  (cost=0.41..8801.69 rows=2130 width=0) (actual time=0.156..233.760 rows=122836 loops=1)"
"        Index Cond: ((ll_to_earth(latitude, longitude))::cube <@ '(1281995.9045467733, -4697354.822067326, 4110397.4955141144),(1381995.648489849, -4597355.078124251, 4210397.23945719)'::cube)"
"Planning Time: 3.928 ms"
"Execution Time: 239.546 ms"

两个库的表都使用完全相同的语句创建了地理位置索引：

CREATE INDEX 
   distance_index ON users USING gist (ll_to_earth(latitude, longitude))

已尝试在查询执行前后运行ANALYZE、VACUUM等维护命令，也测试过删除后重建索引，均未解决问题。
两个数据库运行在完全相同的物理机上，PostgreSQL服务实例、发行版本、配置参数完全一致；两个库的数据完全相同（仅单张users表），且数据无写入变更，使用的PostgreSQL版本为12.8。
psql中\l命令查询两个数据库的属性输出如下：

List of databases
    Name     |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-------------+----------+----------+---------+-------+----------------------- 
 dummy_users | yoni     | UTF8     | en_IL   | en_IL | 
 slow_db     | yoni     | UTF8     | en_IL   | en_IL |

在慢库上执行SET enable_bitmapscan = off;和SET enable_seqscan = off;后再次运行查询，得到的EXPLAIN (ANALYZE, BUFFERS)输出如下：

"Aggregate  (cost=11018.63..11018.64 rows=1 width=8) (actual time=213.544..213.545 rows=1 loops=1)"
"  Buffers: shared hit=11667 read=110537"
"  ->  Index Scan using distance_index on users  (cost=0.41..11011.86 rows=2711 width=0) (actual time=0.262..207.164 rows=122836 loops=1)"
"        Index Cond: ((ll_to_earth(latitude, longitude))::cube <@ '(1282077.0159892815, -4697331.573647572, 4110397.4955141144),(1382076.7599323571, -4597331.829704497, 4210397.23945719)'::cube)"
"        Buffers: shared hit=11667 read=110537"
"Planning Time: 0.940 ms"
"Execution Time: 213.591 ms"

根本原因

性能异常的核心是导入后表数据的物理存储顺序与GiST索引逻辑顺序的相关性大幅下降，触发优化器选择了低效的位图扫描路径，且位图扫描因内存不足产生大量有损块导致额外开销暴增：

原库的表数据物理存储顺序和地理位置索引的排序近似聚集，走普通索引扫描时，按索引条目顺序读取堆块的随机IO开销极低，不需要额外的块内重校验，因此执行速度快。
通过pg_dump导出再导入数据时，数据按导出时的批量顺序写入新库，堆上的物理分布完全打乱，和地理索引顺序的相关性极低。优化器基于统计信息估算，普通索引扫描会产生大量随机IO，成本高于位图扫描，因此主动选择了位图扫描路径。
本次查询匹配的结果集超过12万行，默认work_mem配置下，位图结构没有足够内存存储所有匹配行的精确行指针，退化为只存储块号的有损（lossy）位图：读取对应堆块后，必须对块内所有行逐一做匹配条件重校验，仅重校验这一步就过滤掉了36万行非匹配数据，消耗了90%以上的执行时间，最终导致性能下降5倍。

验证依据：强制关闭位图扫描后，慢库查询耗时直接降到213ms，和原库性能基本持平，证明索引本身没有损坏，问题完全来自执行路径选择错误。

可选修复方式

对齐数据物理顺序：在慢库执行CLUSTER users USING distance_index;，让表数据的物理存储顺序和地理索引顺序对齐，后续优化器会自动选择普通索引扫描路径，性能恢复正常。CLUSTER操作会持有表级排他锁，建议在业务低峰期执行。
调整内存配置：适当调大work_mem参数，保证位图扫描有足够内存存储精确的行指针，避免生成有损位图，消除块内重校验的额外开销。
语句级强制路径：如果不适合调整数据分布或全局参数，可以在执行该查询前临时设置SET enable_bitmapscan = off;，强制优化器选择索引扫描。

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