为何PostGIS空间数据查询性能低于MySQL？实测结果咨询

我了解PostGIS针对geometry、polygon等地理数据查询做了优化，因此开展测试对比其与MySQL的性能差异。

MySQL查询情况 #

以下是MySQL中用于根据经纬度搜索周边地点的查询语句：

select * from place where mbrcontains(ST_LINESTRINGFROMTEXT('LINESTRING(127.0214 37.4777, 126.9444 37.4166)'), coord)

该语句平均耗时30毫秒。

PostGIS查询情况 #

同时，我在带有PostGIS扩展的PostgreSQL中执行了以下同用途查询：coord列为geography类型，coord2列为geometry类型，两列均已创建GIST索引。

1. select * from place
where coord2 && st_setsrid(st_makebox2d(ST_point(127.0214, 37.4777), ST_Point(126.9444, 37.4166)), 4326);

2. select * from place
where st_dwithin('SRID=4326;POINT(126.9829371 37.4472168)'::geography, coord, 1000);

3. select * from place
where 'LINESTRING(127.0214 37.4777, 126.9444 37.4166)'::geometry ~ coord2

这些语句平均耗时至少150至250毫秒，结果显示MySQL的查询速度至少是PostGIS的5倍。

测试环境 #

• 数据库服务器配置相同
• 测试数据约40000行
• MySQL版本：5.7.33
• PostgreSQL版本：14.5
• PostgreSQL环境细节：PostgreSQL 14.5 on x86_64-pc-linux-gnu，gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12)编译，64位
• PostGIS版本：3.1.7 aafe1ff，依赖组件：PGSQL=140、GEOS=3.9.1-CAPI-1.14.2、PROJ=8.0.1、LIBXML=2.9.1、LIBJSON=0.15、LIBPROTOBUF=1.3.2、WAGYU=0.5.0 (Internal)

各查询执行计划 #

1. "Bitmap Heap Scan on place  (cost=10.32..622.30 rows=264 width=176) (actual time=0.083..0.117 rows=84 loops=1)"
"  Recheck Cond: (coord2 && '0103000020E61000000100000005000000EA95B20C71BC5F40BEC1172653B54240EA95B20C71BC5F404CA60A4625BD42409A081B9E5EC15F404CA60A4625BD42409A081B9E5EC15F40BEC1172653B54240EA95B20C71BC5F40BEC1172653B54240'::geometry)"
"  Heap Blocks: exact=12"
"  Buffers: shared hit=20"
"  ->  Bitmap Index Scan on coord2_index  (cost=0.00..10.26 rows=264 width=0) (actual time=0.076..0.077 rows=84 loops=1)"
"        Index Cond: (coord2 && '0103000020E61000000100000005000000EA95B20C71BC5F40BEC1172653B54240EA95B20C71BC5F404CA60A4625BD42409A081B9E5EC15F404CA60A4625BD42409A081B9E5EC15F40BEC1172653B54240EA95B20C71BC5F40BEC1172653B54240'::geometry)"
"        Buffers: shared hit=8"
"Planning Time: 0.134 ms"
"Execution Time: 0.147 ms"

2. "Bitmap Heap Scan on place  (cost=4.53..491.25 rows=4 width=176) (actual time=0.067..0.130 rows=11 loops=1)"
"  Filter: st_dwithin('0101000020E61000009BA10271E8BE5F40631C6D663EB94240'::geography, coord, '2000'::double precision, true)"
"  Rows Removed by Filter: 16"
"  Heap Blocks: exact=5"
"  Buffers: shared hit=12"
"  ->  Bitmap Index Scan on coord_index  (cost=0.00..4.53 rows=17 width=0) (actual time=0.055..0.056 rows=27 loops=1)"
"        Index Cond: (coord && _st_expand('0101000020E61000009BA10271E8BE5F40631C6D663EB94240'::geography, '2000'::double precision))"
"        Buffers: shared hit=7"
"Planning Time: 0.116 ms"
"Execution Time: 0.150 ms"

3. "Bitmap Heap Scan on place  (cost=4.59..141.82 rows=40 width=176) (actual time=0.133..0.167 rows=84 loops=1)"
"  Recheck Cond: ('0102000000020000009A081B9E5EC15F404CA60A4625BD4240EA95B20C71BC5F40BEC1172653B54240'::geometry ~ coord2)"
"  Heap Blocks: exact=12"
"  Buffers: shared hit=20"
"  ->  Bitmap Index Scan on coord2_index  (cost=0.00..4.58 rows=40 width=0) (actual time=0.126..0.126 rows=84 loops=1)"
"        Index Cond: (coord2 @ '0102000000020000009A081B9E5EC15F404CA60A4625BD4240EA95B20C71BC5F40BEC1172653B54240'::geometry)"
"        Buffers: shared hit=8"
"Planning Time: 0.060 ms"
"Execution Time: 0.199 ms"

疑问 #

请问该结果是否正常？

分析与解答 #

这个结果不正常，从提供的PostgreSQL执行计划来看，实际执行时间都在0.1~0.2毫秒之间，远低于你所说的150-250毫秒，说明统计的耗时数据可能存在误差，或者测试方法有问题。

可能的问题点包括：

1. 测试方法不严谨：比如没有预热缓存就直接测试，或者单次测试样本量太少，把规划时间、网络延迟等额外开销算进了执行时间。PostgreSQL的执行计划显示实际执行效率极高，正常情况下不会出现150毫秒以上的耗时。
2. 查询逻辑不完全等价：MySQL中用的是mbrcontains基于线串的MBR范围查询，而PostGIS的三个查询里，第一个是矩形框索引过滤，第二个是地理坐标距离查询，第三个是线串包含关系查询，逻辑和MySQL的查询并非完全一致，但从执行计划看，PostGIS的实际执行效率是很高的。
3. 额外开销干扰：统计耗时的时候可能包含了客户端连接、结果集传输等外部开销，而非单纯的数据库执行时间。

建议重新优化测试方法：

• 先执行几次查询预热缓存，避免首次加载数据的IO开销影响结果
• 使用EXPLAIN ANALYZE多次执行取平均，只统计数据库内部的实际执行时间，排除外部干扰
• 调整PostGIS的查询语句，使其和MySQL的查询逻辑完全等价，再进行性能对比

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