开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL 空间聚合性能 - 行政区、电子围栏 空间聚合 - 时间、空间热力图

简介: 标签 PostgreSQL , 空间聚合 , 空间热力图 , 行政区 , 电子围栏 背景 某个时间区间(或其他条件),出现在某些围栏、行政区(多边形信息)中的对象(空间点信息)有多少,按颜色深浅渲染这些多边形。
+关注继续查看

标签

PostgreSQL , 空间聚合 , 空间热力图 , 行政区 , 电子围栏


背景

某个时间区间(或其他条件),出现在某些围栏、行政区(多边形信息)中的对象(空间点信息)有多少,按颜色深浅渲染这些多边形。

例如

pic

例子

1、面数据 - 围栏、行政区(多边形信息)

生成1万个面数据

create table t1 (  
  gid int,   -- 多边形ID(用户定义的围栏、行政区)  
  face box   -- 空间信息,实际使用时,可以使用PostGIS的geometry类型  
);  
  
insert into t1 select row_number() over(), box (point(x,y),point(x+1,y+1)) from generate_series(0,99) t1(x),generate_series(0,99) t2(y);  
  
create index idx_t1_face on t1 using gist(face);  

2、点数据,空间对象的位置信息

生成1000万点数据

create table t2 (  
  id int,   -- 对象ID  
  pos point,   -- 位置,实际使用时,可以使用PostGIS的geometry类型  
  att text   -- 其他属性,可以有更多其他属性  
);  
  
insert into t2 select id, point(random()*100, random()*100) from generate_series(1,10000000) t(id);  

3、创建函数,输入点的值,获得面的值。

支持并行SQL

create or replace function get_gid(point) returns int as $$  
  select gid from t1 where face @> box($1,$1) limit 1;  
$$ language sql strict immutable parallel safe;  

4、按GID空间聚合的SQL如下

select gid, count(*) from t1 join t2 on (t1.face @> box(t2.pos,t2.pos)) group by gid;  
  
或  
  
select get_gid(pos) as gid, count(*) from t2 group by 1;  

5、使用PG 并行聚合

postgres=# show max_worker_processes ;  
 max_worker_processes   
----------------------  
 128  
(1 row)  
  
  
set max_parallel_workers=128;  
set max_parallel_workers_per_gather =28;  
set enable_sort=off;  
set parallel_tuple_cost =0;  
set parallel_setup_cost =0;  
set min_parallel_table_scan_size =0;  
set min_parallel_index_scan_size =0;  
set work_mem ='2GB';  
alter table t1 set (parallel_workers =28);  
alter table t2 set (parallel_workers =28);  
analyze t1;  
analyze t2;  

1000万点,1万面,空间聚合性能

5.6 秒

postgres=# explain analyze select get_gid(pos) as gid, count(*) from t2 group by 1;  
                                                               QUERY PLAN                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------  
 Finalize HashAggregate  (cost=86550.79..86594.18 rows=200 width=12) (actual time=5592.898..5594.204 rows=10000 loops=1)  
   Group Key: (get_gid(pos))  
   ->  Gather  (cost=86483.13..86526.52 rows=5600 width=12) (actual time=5485.528..5536.356 rows=290000 loops=1)  
         Workers Planned: 28  
         Workers Launched: 28  
         ->  Partial HashAggregate  (cost=86483.13..86526.52 rows=200 width=12) (actual time=5443.795..5445.860 rows=10000 loops=29)  
               Group Key: get_gid(pos)  
               ->  Parallel Seq Scan on t2  (cost=0.00..84806.71 rows=386720 width=4) (actual time=1.014..5311.532 rows=344828 loops=29)  
 Planning Time: 0.118 ms  
 Execution Time: 5595.278 ms  
(10 rows)  

100万点,1万面,空间聚合性能

690 毫秒

truncate t2;  
insert into t2 select id, point(random()*100, random()*100) from generate_series(1,1000000) t(id);  
  
postgres=# explain analyze select get_gid(pos) as gid, count(*) from t2 group by 1;  
                                                             QUERY PLAN                                                                
-------------------------------------------------------------------------------------------------------------------------------------  
 Finalize HashAggregate  (cost=8061.46..8104.85 rows=200 width=12) (actual time=687.602..688.897 rows=10000 loops=1)  
   Group Key: (get_gid(pos))  
   ->  Gather  (cost=7993.80..8037.18 rows=5600 width=12) (actual time=582.986..632.877 rows=280419 loops=1)  
         Workers Planned: 28  
         Workers Launched: 28  
         ->  Partial HashAggregate  (cost=7993.80..8037.18 rows=200 width=12) (actual time=541.534..543.355 rows=9670 loops=29)  
               Group Key: get_gid(pos)  
               ->  Parallel Seq Scan on t2  (cost=0.00..7838.98 rows=35714 width=4) (actual time=1.010..527.932 rows=34483 loops=29)  
 Planning Time: 0.130 ms  
 Execution Time: 689.867 ms  
(10 rows)  

如果预先聚合 - 速度更快

比如数据写入时,就把GID算出来,写入T2表的GID字段里,直接按GID聚合。速度会飞起来。

1000万点,258毫秒。

postgres=# alter table t2 add column gid int;
ALTER TABLE
  
-- 假设GID已提前算好(比如insert时直接设置为get_gid(pos),这里只是为了测试,一次性全部更新掉)
postgres=# update t2 set gid = get_gid(pos);
UPDATE 10000000
postgres=# vacuum full t2;
VACUUM



postgres=# explain analyze select gid,count(*) from t2 group by gid;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Finalize HashAggregate  (cost=3882.87..3884.76 rows=9983 width=12) (actual time=255.763..257.094 rows=10000 loops=1)
   Group Key: gid
   ->  Gather  (cost=2669.26..2671.14 rows=279524 width=12) (actual time=135.953..200.398 rows=290000 loops=1)
         Workers Planned: 28
         Workers Launched: 28
         ->  Partial HashAggregate  (cost=2669.26..2671.14 rows=9983 width=12) (actual time=98.026..99.585 rows=10000 loops=29)
               Group Key: gid
               ->  Parallel Seq Scan on t2  (cost=0.00..1121.05 rows=357143 width=4) (actual time=0.005..30.248 rows=344828 loops=29)
 Planning Time: 0.078 ms
 Execution Time: 258.268 ms
(10 rows)

参考

PostGIS 比 内置geometry 操作符稍慢。

《PostgreSQL Oracle 兼容性之 - 自定义并行聚合函数 PARALLEL_ENABLE AGGREGATE》

《HTAP数据库 PostgreSQL 场景与性能测试之 23 - (OLAP) 并行计算》

https://www.postgresql.org/docs/11/functions-geometry.html

http://postgis.net/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云PolarDB、RDS获评信通院数据库Serverless认证最高“先进级”,AnalyticDB获“增强级”
随着云原生数据库的快速发展,Serverless能力正成为数据库产品的核心能力之一。
119 0
《阿里云企业级自治数据库 RDS 详解》电子版地址
阿里云企业级自治数据库 RDS 详解.ppt
26 0
《RDS数据库入门一本通》电子版地址
本书体系完整,理论充实,内容由浅入深,循序渐进,更配有详细的实例解说,为初学者提供一个完整、严密的思维框架,零基础的你也可轻松掌握RDS数据库的美妙节奏。
28 0
Redis的KEYS命令引起RDS数据库雪崩,RDS发生两次宕机,造成几百万的资金损失
Redis的KEYS命令引起RDS数据库雪崩,RDS发生两次宕机,造成几百万的资金损失
163 0
前沿分享|阿里云数据库高级技术专家 宋利兵:阿里云企业级自治数据库RDS详解
本篇内容为2021云栖大会-企业级云原生数据库最佳实践论坛中,阿里云数据库高级技术专家 宋利兵关于“阿里云企业级自治数据库RDS详解”的分享。
367 0
【RDS MySQL】将Excel的数据导入数据库
您可以将Excel的数据通过数据管理服务DMS(Data Management Service)导入到RDS MySQL数据库中。
343 0
PolarDB-X 1.0-常见问题-分库分表问题-删除数据库时,PolarDB-X各物理分库是否会被自动删除?是否会影响RDS上的数据库?
在控制台上删除PolarDB-X的数据库时,只会删除之前由PolarDB-X所创建的数据库,不会影响原本在RDS上创建的数据库。
92 0
阿里云RDS数据库助力孔网提升运维效率
业务/技术亮点:高效运维/弹性能力/跨机房容灾/数据迁移一致性
193 0
RDS数据库架构实践及解析|阿里云产品内容精选(十二)
本文内容来源于阿里云开发者社区,助力各位开发者了解阿里云数据库相关内容。
408 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
金融级 PostgreSQL监控及优化
立即下载
PostgresChina2018_刘东明_PostgreSQL并行查询
立即下载
国产化浪潮之上的PostgreSQL-删时间
立即下载