PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box)

最新推荐文章于 2025-05-29 17:46:21 发布

转载

最新推荐文章于 2025-05-29 17:46:21 发布 · 8.9k 阅读

11 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/228263

文章标签：

#数据库

本文探讨了在PostgreSQL中优化空间包含查询的方法，通过空间聚集和st_split减少IO和CPU开销。针对长条形多边形查询，结合st_contains和st_within操作符，提出利用GiST索引和BOUND BOX避免扫描大量无效数据，同时介绍了st_split的使用以进一步优化查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

点面判断、按面圈选点或其他对象，是GIS几何应用中非常典型的需求。

在PostgreSQL中通过建立GiST索引可以加速这类判断，然而，建立索引就够了吗？

很多时候建立索引是不够的，性能没有到达巅峰，如果要更低的延迟，更少的CPU开销，还有什么优化手段呢？

实际上我以前写过一篇类似的文章，讲的是BTree索引访问的优化，当数据存放与索引顺序的线性相关性很差时，引入了一个问题，访问时IO放大：

《索引顺序扫描引发的堆扫描IO放大背后的统计学原理与解决办法 - PostgreSQL index scan enlarge heap page scans when index and column correlation small.》

原理和解决办法上面的文档已经讲得很清楚了。对于空间索引也有类似的问题和优化方法。但是首先你需要了解空间索引的构造：

《通过空间思想理解GiST索引的构造》

然后你可以通过空间聚集，来降低空间扫描的IO。

《PostgreSQL 黑科技 - 空间聚集存储》

下面以一个搜索为例，讲解空间包含搜索的优化方法：

在表中有1000万空间对象数据，查询某个多边形覆盖到的空间对象。这个查询有一个特点，这个多边形是一个长条条的多边形，包含这个多边形的BOUND BOX是比较大的。

构建这个多边形的方法

postgres=# select st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(0 0,1 0,1 2.5,6 2.5,6 4,7 4,7 5,5 5,5 3,0 3,0 0)')), 4326);  
    st_setsrid             
----------------------------  
 0103000020E6100000010000000B00000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000044000000000000018400000000000000440000000000000184000000000000010400000000000001C4000000000000010400000000000001C40000000000000144000000000000014400000000000001440000000000000144000000000000008400000000000000000000000000000084000000000000000000000000000000000  
(1 row)

优化手段1 - 空间聚集

1、建表

postgres=# create table e(id int8, pos geometry);  
CREATE TABLE

2、写入空间测试数据（1000万个随机点，覆盖 +-50 的经纬度区间）

postgres=# insert into e select id, st_setsrid(st_makepoint(50-random()*100, 50-random()*100), 4326) from generate_series(1,10000000) t(id);  
INSERT 0 10000000

3、创建空间索引

postgres=# create index idx_e on e using gist(pos);  
CREATE INDEX

4、查询满足这个多边形的BOUND BOX覆盖的对象的BOUND BOX条件的对象。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from e where pos @ st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(0 0,1 0,1 2.5,6 2.5,6 4,7 4,7 5,5 5,5 3,0 3,0 0)')), 4326);  
   QUERY PLAN                                                                                                                                                                                  
-----------------------  
 Index Scan using idx_e on public.e  (cost=0.42..12526.72 rows=10000 width=40) (actual time=0.091..39.449 rows=35081 loops=1)  
   Output: id, pos  
   Index Cond: (e.pos @ '0103000020E6100000010000000B00000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000044000000000000018400000000000000440000000000000184000000000000010400000000000001C4000000000000010400000000000001C40000000000000144000000000000014400000000000001440000000000000144000000000000008400000000000000000000000000000084000000000000000000000000000000000'::geometry)  
   Buffers: shared hit=35323  
 Planning time: 0.108 ms  
 Execution time: 41.222 ms  
(6 rows)

搜索了35323个数据块，返回了35081条记录。

5、查询被这个多边形包含的对象。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from e where st_contains(st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(0 0,1 0,1 2.5,6 2.5,6 4,7 4,7 5,5 5,5 3,0 3,0 0)')), 4326), pos);    
   QUERY PLAN                                                                                                                                                                                  
-----------------------  
 Index Scan using idx_e on public.e  (cost=0.42..15026.72 rows=3333 width=40) (actual