PostgreSQL , PostGIS , 空间数据 , 多边形 , bound box , R-Tree , GiST , SP-GiST
在PostgreSQL中,目前对于空间对象的索引,采用的是GiST索引方法,空间树结构如下,每个ENTRY都是一个BOX:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuEDMw81YpB3XxAzX1ATOwcTMwIzLclDM3EDMy8CXyVGdzFWbvw1ZvxmYvwFbh92ZpR2Lc12bj5CduVGdu92YyV2c1JWdoRXan5ydhJ3Lc9CX6MHc0RHaiojIsJye.gif)
如果对象是多边形,那么在索引结构中,会存储这个多边形的bound box。
那么对于非box类型,一定是会出现空间放大的。
另一方面,如果输入条件是个多边形,那么同样会将这个多边形的BOUND BOX作为输入条件,根据查询OP(操作符)到索引结构中找到这个输入BOUND BOX的branch。
这样,如果无效面积过多,就出现了索引扫描的IO放大和CPU放大。
例子如下:
优化方法是切割多边形,减少无效空间,使用union all合并结果。
<a href="https://github.com/digoal/blog/blob/master/201710/20171004_01.md">《PostgreSQL 空间st_contains,st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》</a>
对于multi polygon对象,BOUND BOX就可能很大。
比如这个图上的三个多边形组成的multi polygon,实际上bound box是很大的。
以上SQL,在使用GiST搜索时,实际上会返回这个bound box包含的所有空间对象,而不是这几个小的多边形包含的空间对象。然后在通过check filter来过滤。这样就导致了IO和CPU放大。
优化方法如上所述,SPLIT空间对象,多个空间搜索结果UNION ALL得到最终结果。
1、创建测试表
2、写入1000万个空间点
3、创建空间索引
4、使用多个polygon构造成一个multi polygon
5、使用multi polygon搜索
6、使用多个polgon搜索,使用union all合并结果
7、写UDF,简化写多个UNION ALL
使用UDF后,大大简化了SQL写法,同时性能得到了质的飞跃。
尽量的减少搜索条件,或者数据本身的无效面积,可以降低IO和CPU,大幅提升性能。
<a href="https://github.com/digoal/blog/blob/master/201710/20171005_01.md">《PostgreSQL 空间切割(st_split)功能扩展 - 空间对象网格化 (多边形GiST优化)》</a>
<a href="https://github.com/digoal/blog/blob/master/201709/20170905_01.md">《PostgreSQL 黑科技 - 空间聚集存储, 内窥GIN, GiST, SP-GiST索引》</a>