PostGIS查询优化之空间索引失效

Spatial Data 2021-05-06

2969

一前言

经常有朋友问，为什么我已经建立了空间索引，在空间查询和空间分析时候还是这么慢？是不是PostGIS不行？经常质疑xx不行的人以后应当谨慎点，因为以我个人的经验来看，通常情况都是是使用的人的问题。众所周知，sql是一门非常灵活的查询语言，同一个需求可以有很多种sql表达式实现，不同实现方式性能差异可能很大，在进行空间查询优化的时候首先要明确什么原因导致查询变慢的，本文总结Spatial SQL查询较慢主要原因如下：

建立空间索引的图形列通过表达式、函数、类型转换后导致索引失效。
部分空间函数根本不会走空间索引。
索引效率低，IO与CPU放大严重。

本文主要阐述索引失效的原因和解决办法。

二索引失效案例

2.1 表达式导致索引失效

索引失效是sql中新手每天都要问的问题，在关系表的查询时候，有很多sql教程要求我们应该这样，不该这样，如表达式不能放左边，假如test表的value字段建立了btree索引，这样的查询语句就是不走索引的：

select * from test where (value+2)<10;

修改如下就走索引了：

select * from test where value<(10-2);

很多空间索引查询的准则和关系表是完全一致的，因为对建立索引的字段进行表达式操作，得到的是一份新数据了，和基于原数据构造的索引已经风马牛不相及了（表达式索引除外）。

测试场景：已知250万POI点，查询“我的位置”附近一公里内全部的poi点。

create table poi(
  gid serial primary key,
  name text,
  geom geometry(Point,4326)
);
create index poi_geom_idx on poi using gist(geom);
--区域内构造测试数据
insert into poi(geom)
select (st_dump(
ST_GeneratePoints(
  ST_MakeEnvelope(113,24,125,35,4326),
  2500000)
)).geom;

附近的xx

方案一：对每个poi点缓冲1公里，判定缓冲区内是否包含“我的位置”：

explain select a.* from poi a where ST_Intersects(
  ST_Buffer(a.geom,0.01), 
  ST_SetSrid(ST_MakePoint(118.3749,32.0987),4326)
);                                                                    QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..52115609.00 rows=250 width=68)
   Workers Planned: 2
   ->  Parallel Seq Scan on poi a  (cost=0.00..52114584.00 rows=104 width=68)
         Filter: st_intersects(st_buffer(geom, '0.01'::double precision, ''::text), '0101000020E61000004ED1915CFE975D40BF0E9C33A20C4040'::geometry)
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(7 rows)


Time: 145.681 ms

查看以上语句执行规划，走的是顺序扫描（seq scan on poi），并没有走空间索引，原因是我们对poi的geom建立索引，但是查询时候，对geom进行了缓冲操作，违反表达式放右边的规则，方案一结果正确，实现错误。

方案二：根据“我的位置”缓冲1公里，判断缓冲区内有哪些poi点：

explain select a.* from poi a where ST_Intersects(
  a.geom, 
  st_buffer(ST_SetSrid(ST_MakePoint(118.3749,32.0987),4326),0.01)
);                            QUERY PLAN                                                                     
-----------------------------------------------------------------------------
 Index Scan using poi_geom_idx on poi a  (cost=0.41..149.50 rows=5 width=68)

方案二结果正确，实现正确。
方案三：使用ST_Distance实现，就是判定poi点与我的位置之间距离小于1公里即可，很明显是遍历poi点与我的位置的距离做判断。

explain select a.* from poi a where ST_Distance(a.geom,
ST_SetSrid(ST_MakePoint(118.3749,32.0987),4326))<0.01;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..26159854.80 rows=833333 width=68)
   Workers Planned: 2
   ->  Parallel Seq Scan on poi a  (cost=0.00..26075521.50 rows=347222 width=68)
         Filter: (st_distance(geom, '0101000020E61000004ED1915CFE975D40BF0E9C33A20C4040'::geometry) < '0.01'::double precision)
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(7 rows)


Time: 1.015 ms

方案三：结果正确，走的是顺序扫描（seq scan on poi）不走索引，实现错误。

方案四：使用ST_DWithin实现，走的空间索引。

explain select a.* from poi a where ST_DWithin(
  a.geom,ST_SetSrid(ST_MakePoint(118.3749,32.0987),4326),0.01);
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using poi_geom_idx on poi a  (cost=0.54..149.62 rows=250 width=68)
   Index Cond: (geom && st_expand('0101000020E61000004ED1915CFE975D40BF0E9C33A20C4040'::geometry, '0.01'::double precision))
   Filter: st_dwithin(geom, '0101000020E61000004ED1915CFE975D40BF0E9C33A20C4040'::geometry, '0.01'::double precision)
(3 rows)

方案四：结果正确，实现正确，性能最佳。

2.2 图形函数与图形类型转换导致索引失效

回到上文的案例，计算我的位置1公里内的poi，上文使用的是经纬度0.01近似表达（1度约111km），如果想用使用精确距离，可以使用geography类型去参与计算：

explain select a.* from poi a where ST_DWithin(a.geom::geography, 
ST_SetSrid(ST_MakePoint(118.3749,32.0987),4326)::geography,1000);
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..26076546.50 rows=250 width=68)
   Workers Planned: 2
   ->  Parallel Seq Scan on poi a  (cost=0.00..26075521.50 rows=104 width=68)
         Filter: st_dwithin((geom)::geography, '0101000020E61000004ED1915CFE975D40BF0E9C33A20C4040'::geography, '1000'::double precision, true)
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(7 rows)

为了计算的准确，一不小心将geom从geometry类型转换到geography类型，导致基于geometry类型建立的索引失效了，走的是顺序扫描（seq scan on poi）。

那么如何既要保证计算的精度又要保证计算的性能咧？比如上文不得不用geography类型，我们可以通过pg的表达式索引去优化：

--对必须要进行类型转换计算，可以预先建立表达式索引
create index poi_geogra_idx on poi using gist((geom::geography));
--查询计划
explain select a.* from poi a where ST_DWithin(
a.geom::geography, 
ST_SetSrid(ST_MakePoint(118.3749,32.0987),4326)::geography,1000);
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on poi a  (cost=14.47..7181.94 rows=250 width=68)
   Filter: st_dwithin((geom)::geography, '0101000020E61000004ED1915CFE975D40BF0E9C33A20C4040'::geography, '1000'::double precision, true)
   ->  Bitmap Index Scan on poi_geogra_idx  (cost=0.00..14.41 rows=250 width=0)
         Index Cond: ((geom)::geography && _st_expand('0101000020E61000004ED1915CFE975D40BF0E9C33A20C4040'::geography, '1000'::double precision))
(4 rows)


Time: 0.813 ms

好了这下开始根据空间索引扫描了，完美的既保证了查询的精确度又保证了查询的效率。

三优化总结

同一个空间查询业务，可以同时有很多种Spatial SQL可以达到同样的查询结果，一定要仔细分析判定哪些方案根本就不能用，能用的方案对比下哪个性能更好。
原则上不要对建立空间索引的图形列在业务应用时对其进行表达式计算或强制类型转换，这会导致索引失效。
特殊情况下，可以通过建立表达式索引兼顾业务功能和查询性能。

数据库

文章转载自Spatial Data，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostGIS查询优化之空间索引失效

评论