PostgreSQL multipolygon 空间索引查询过滤精简优化 - IO,CPU放大优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , PostGIS , 空间数据 , 多边形 , bound box , R-Tree , GiST , SP-GiST


背景

在PostgreSQL中,目前对于空间对象的索引,采用的是GiST索引方法,空间树结构如下,每个ENTRY都是一个BOX:

pic

如果对象是多边形,那么在索引结构中,会存储这个多边形的bound box。

那么对于非box类型,一定是会出现空间放大的。

pic

另一方面,如果输入条件是个多边形,那么同样会将这个多边形的BOUND BOX作为输入条件,根据查询OP(操作符)到索引结构中找到这个输入BOUND BOX的branch。

这样,如果无效面积过多,就出现了索引扫描的IO放大和CPU放大。

例子如下:

优化方法是切割多边形,减少无效空间,使用union all合并结果。

《PostgreSQL 空间st_contains,st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》

对于multi polygon对象,BOUND BOX就可能很大。

比如这个图上的三个多边形组成的multi polygon,实际上bound box是很大的。

pic

select * from tbl where st_contains(multi_polygon, pos);  

以上SQL,在使用GiST搜索时,实际上会返回这个bound box包含的所有空间对象,而不是这几个小的多边形包含的空间对象。然后在通过check filter来过滤。这样就导致了IO和CPU放大。

优化方法如上所述,SPLIT空间对象,多个空间搜索结果UNION ALL得到最终结果。

例子

1、创建测试表

create table tbl (id int, pos geometry);  

2、写入1000万个空间点

insert into tbl select id,   
st_setsrid(  
  st_makepoint(  
    round((random()*(135.085831-73.406586)+73.406586)::numeric,6),   
    round((random()*(53.880950-3.408477)+3.408477)::numeric,6)  
  ),   
  4326  
) from generate_series(1,10000000) t(id);  

3、创建空间索引

create index idx_tbl_pos on tbl using gist(pos);  

4、使用多个polygon构造成一个multi polygon

select st_union(  
  array[  
    st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)')), 4326),   
    st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(125.15 29.53,125 29,125.6 29.5, 125.15 29.53)')), 4326)  
  ]  
);  

5、使用multi polygon搜索

explain (analyze,verbose,timing,costs,buffers) select * from tbl where st_contains(  
st_union(  
  array[  
    st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)')), 4326),   
    st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(125.15 29.53,125 29,125.6 29.5, 125.15 29.53)')), 4326)  
  ]  
),  
pos  
);  
 Bitmap Heap Scan on public.tbl  (cost=156.65..13467.61 rows=3333 width=36) (actual time=41.020..5062.317 rows=2445 loops=1)  
   Output: id, pos  
   Recheck Cond: ('0106000020E610000002000000010300000001000000040000009A99999999C9524048E17A14AE873D4000000000004053400000000000003D4066666666666653400000000000803D409A99999999C9524048E17A14AE873D40010300000001000000040000009A99999999495F4048E17A14AE873D400000000000405F400000000000003D406666666666665F400000000000803D409A99999999495F4048E17A14AE873D40'::geometry ~ tbl.pos)  
   Filter: _st_contains('0106000020E610000002000000010300000001000000040000009A99999999C9524048E17A14AE873D4000000000004053400000000000003D4066666666666653400000000000803D409A99999999C9524048E17A14AE873D40010300000001000000040000009A99999999495F4048E17A14AE873D400000000000405F400000000000003D406666666666665F400000000000803D409A99999999495F4048E17A14AE873D40'::geometry, tbl.pos)  
   Rows Removed by Filter: 83589  
   Heap Blocks: exact=53874  
   Buffers: shared hit=823 read=53873 written=7641  
   ->  Bitmap Index Scan on idx_tbl_pos  (cost=0.00..155.82 rows=10000 width=0) (actual time=22.305..22.305 rows=86034 loops=1)  
         Index Cond: ('0106000020E610000002000000010300000001000000040000009A99999999C9524048E17A14AE873D4000000000004053400000000000003D4066666666666653400000000000803D409A99999999C9524048E17A14AE873D40010300000001000000040000009A99999999495F4048E17A14AE873D400000000000405F400000000000003D406666666666665F400000000000803D409A99999999495F4048E17A14AE873D40'::geometry ~ tbl.pos)  
         Buffers: shared hit=822  
 Planning time: 0.268 ms  
 Execution time: 5062.947 ms  
(12 rows)  

6、使用多个polgon搜索,使用union all合并结果

explain (analyze,verbose,timing,costs,buffers)   
select * from tbl where st_contains(st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)')), 4326), pos)  
union all  
select * from tbl where st_contains(st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(125.15 29.53,125 29,125.6 29.5, 125.15 29.53)')), 4326), pos);  
 Append  (cost=156.65..27001.89 rows=6666 width=36) (actual time=1.594..11.143 rows=2445 loops=1)  
   Buffers: shared hit=5230  
   ->  Bitmap Heap Scan on public.tbl  (cost=156.65..13467.61 rows=3333 width=36) (actual time=1.594..8.473 rows=2016 loops=1)  
         Output: tbl.id, tbl.pos  
         Recheck Cond: ('0103000020E610000001000000040000009A99999999C9524048E17A14AE873D4000000000004053400000000000003D4066666666666653400000000000803D409A99999999C9524048E17A14AE873D40'::geometry ~ tbl.pos)  
         Filter: _st_contains('0103000020E610000001000000040000009A99999999C9524048E17A14AE873D4000000000004053400000000000003D4066666666666653400000000000803D409A99999999C9524048E17A14AE873D40'::geometry, tbl.pos)  
         Rows Removed by Filter: 2172  
         Heap Blocks: exact=4083  
         Buffers: shared hit=4133  
         ->  Bitmap Index Scan on idx_tbl_pos  (cost=0.00..155.82 rows=10000 width=0) (actual time=1.001..1.001 rows=4188 loops=1)  
               Index Cond: ('0103000020E610000001000000040000009A99999999C9524048E17A14AE873D4000000000004053400000000000003D4066666666666653400000000000803D409A99999999C9524048E17A14AE873D40'::geometry ~ tbl.pos)  
               Buffers: shared hit=50  
   ->  Bitmap Heap Scan on public.tbl tbl_1  (cost=156.65..13467.61 rows=3333 width=36) (actual time=0.429..2.227 rows=429 loops=1)  
         Output: tbl_1.id, tbl_1.pos  
         Recheck Cond: ('0103000020E610000001000000040000009A99999999495F4048E17A14AE873D400000000000405F400000000000003D406666666666665F400000000000803D409A99999999495F4048E17A14AE873D40'::geometry ~ tbl_1.pos)  
         Filter: _st_contains('0103000020E610000001000000040000009A99999999495F4048E17A14AE873D400000000000405F400000000000003D406666666666665F400000000000803D409A99999999495F4048E17A14AE873D40'::geometry, tbl_1.pos)  
         Rows Removed by Filter: 655  
         Heap Blocks: exact=1076  
         Buffers: shared hit=1097  
         ->  Bitmap Index Scan on idx_tbl_pos  (cost=0.00..155.82 rows=10000 width=0) (actual time=0.295..0.295 rows=1084 loops=1)  
               Index Cond: ('0103000020E610000001000000040000009A99999999495F4048E17A14AE873D400000000000405F400000000000003D406666666666665F400000000000803D409A99999999495F4048E17A14AE873D40'::geometry ~ tbl_1.pos)  
               Buffers: shared hit=21  
 Planning time: 0.247 ms  
 Execution time: 11.432 ms  
(24 rows)  

7、写UDF,简化写多个UNION ALL

create or replace function q_mp(VARIADIC arr geometry[]) returns setof record as $$  
declare  
  sql text := '';  
  var geometry;  
begin  
  foreach var in array arr loop  
    sql := sql || format(' select * from tbl where st_contains(''%s''::geometry, pos) union all', var);  
  end loop;  
  sql := rtrim(sql, 'union all');  
  return query execute sql;  
end;  
$$ language plpgsql strict;  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from q_mp  
(  
st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)')), 4326),   
st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(125.15 29.53,125 29,125.6 29.5, 125.15 29.53)')), 4326)  
) as t(id int, pos geometry);  
 Function Scan on public.q_mp t  (cost=0.25..10.25 rows=1000 width=36) (actual time=11.451..11.707 rows=2445 loops=1)  
   Output: id, pos  
   Function Call: q_mp(VARIADIC '{0103000020E610000001000000040000009A99999999C9524048E17A14AE873D4000000000004053400000000000003D4066666666666653400000000000803D409A99999999C9524048E17A14AE873D40:0103000020E610000001000000040000009A99999999495F4048E17A14AE873D400000000000405F400000000000003D406666666666665F400000000000803D409A99999999495F4048E17A14AE873D40}'::geometry[])  
   Buffers: shared hit=5230  
 Planning time: 0.095 ms  
 Execution time: 11.975 ms  
(6 rows)  

小结

使用UDF后,大大简化了SQL写法,同时性能得到了质的飞跃。

尽量的减少搜索条件,或者数据本身的无效面积,可以降低IO和CPU,大幅提升性能。

参考

《PostgreSQL 空间切割(st_split)功能扩展 - 空间对象网格化 (多边形GiST优化)》

《PostgreSQL 空间st_contains,st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》

《PostgreSQL 黑科技 - 空间聚集存储, 内窥GIN, GiST, SP-GiST索引》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
19天前
|
SQL 监控 关系型数据库
|
14天前
|
SQL 关系型数据库 数据库
Python执行PostgreSQL数据库查询语句,并打印查询结果
本文介绍了如何使用Python连接和查询PostgreSQL数据库。首先,确保安装了`psycopg2`库,然后创建数据库连接函数。接着,展示如何编写SQL查询并执行,例如从`employees`表中选取所有记录。此外,还讨论了处理查询结果、格式化输出和异常处理的方法。最后,提到了参数化查询和事务处理以增强安全性及确保数据一致性。
Python执行PostgreSQL数据库查询语句,并打印查询结果
|
14天前
|
SQL 关系型数据库 数据库
Python查询PostgreSQL数据库
木头左教你如何用Python连接PostgreSQL数据库:安装`psycopg2`库,使用提供的代码模板建立连接。执行SQL脚本,如创建表、插入和查询数据,同时掌握错误处理和事务管理,确保数据一致性。关注性能优化和高级特性,如索引、批量操作以及PostgreSQL的存储过程和触发器。别忘了点赞哦!
|
14天前
|
SQL 关系型数据库 数据库
Python查询PostgreSQL数据库
木头左教你如何用Python连接PostgreSQL数据库:安装`psycopg2`库,建立连接,执行SQL脚本如创建表、插入数据,同时掌握错误处理和事务管理。别忘了性能优化,利用索引、批量操作提升效率。下期更精彩!💡 csvfile
Python查询PostgreSQL数据库
|
20天前
|
监控 关系型数据库 运维
|
23天前
|
SQL 运维 监控
关系型数据库查询日志分析
【5月更文挑战第21天】
26 2
|
16天前
|
关系型数据库 分布式数据库 数据库
PolarDB产品使用合集之可以使用什么来查看查询的执行计划和性能信息
PolarDB是阿里云推出的一种云原生数据库服务,专为云设计,提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案,可以有效地管理和优化PolarDB实例,确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。
|
19天前
|
Java
io读两个文件,生成list 排重后写本地文件(Java)
io读两个文件,生成list 排重后写本地文件(Java)
|
23天前
|
存储 Java API
Java语言IO(输入/输出)编程技术深度解析
Java语言IO(输入/输出)编程技术深度解析
252 1
|
27天前
|
存储 Java API
Java IO流
Java IO流
28 2

相关产品

  • 云原生数据库 PolarDB