HybridDBforPostgreSQL軌跡相似(伴隨分析)

德哥發表於2018-09-15
SQL

標籤

PostgreSQL , Greenplum , 伴隨分析 , 軌跡相似

背景

《阿里雲 PostgreSQL 產品生態；案例、開發實踐、管理實踐、學習資料、學習視訊 – 珍藏級》

以上有個CASE是講：如何找出在同一輛車內的人，實際上就是通過車輛、人物的軌跡點資料，進行多輪的求交集，將結果收斂到一個較小的範圍。

Greenplum伴隨分析測試

1、建立測試表

create table tbl_pos (uid int8, phonenum text, ts timestamp, pos geometry, ghash text);

2、寫入測試資料3億條左右

insert into tbl_pos  
select uid, md5(uid::text), ts, pos, st_geohash(pos,8) from  
(  
  select   
    (random()*10000000)::int8 as uid,   
    ts,   
    st_setsrid(st_makepoint(120.2+0.5-random(), 30.3+0.5-random()), 4326) as pos   
  from   
  generate_series(`2018-01-01`::timestamp, `2019-01-01`::timestamp, interval `0.1 sec`)   
t (ts)  
) t;

3、重複生成資料到100億

postgres=> insert into tbl_pos select * from tbl_pos;  
INSERT 0 315360001  
postgres=> 	iming  
Timing is on.  
postgres=> insert into tbl_pos select * from tbl_pos;  
INSERT 0 630720002  
Time: 8992.130 ms (00:08.992)  
postgres=> insert into tbl_pos select * from tbl_pos;  
INSERT 0 1261440004  
Time: 19517.465 ms (00:19.517)  
postgres=> insert into tbl_pos select * from tbl_pos;  
INSERT 0 2522880008  
Time: 37244.890 ms (00:37.245)  
postgres=> insert into tbl_pos select * from tbl_pos;  
INSERT 0 5045760016  
Time: 73391.309 ms (01:13.391)

4、新增索引

create index idx_tbl_pos_1 on tbl_pos(ghash);  
create index idx_tbl_pos_2 on tbl_pos using gist(pos);  
create index idx_tbl_pos_3 on tbl_pos (ts);

5、收集統計資訊

postgres=> vacuum analyze tbl_pos;

6、伴隨分析

postgres=> select count(*) from tbl_pos where ghash >= `wtmm1k` and ghash < `wtmm1`||chr(ascii(`k`)+1);  
 count    
--------  
 609632  
(1 row)  
  
Time: 60.275 ms  
  
postgres=> select count(*) from tbl_pos where ts between `2018-01-01 00:06:25.2` and `2018-01-01 01:06:25.2` and ghash >= `wtmmmm` and ghash < `wtmmm`||chr(ascii(`m`)+1);  
 count   
-------  
 69984  
(1 row)  
  
Time: 70.161 ms  
  
  
postgres=> explain select count(*) from tbl_pos where ts between `2018-01-01 00:06:25.2` and `2018-01-01 01:06:25.2` and ghash >= `wtmmmm` and ghash < `wtmmm`||chr(ascii(`m`)+1);  
                                                                                                     QUERY PLAN                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=10681940.85..10681940.86 rows=1 width=8)  
   ->  Gather Motion 1024:1  (slice1; segments: 1024)  (cost=10681930.57..10681940.83 rows=1 width=8)  
         ->  Aggregate  (cost=10681930.57..10681930.58 rows=1 width=8)  
               ->  Bitmap Heap Scan on tbl_pos  (cost=10520118.62..10681889.69 rows=16 width=0)  
                     Recheck Cond: ts >= `2018-01-01 00:06:25.2`::timestamp without time zone AND ts <= `2018-01-01 01:06:25.2`::timestamp without time zone AND ghash >= `wtmmmm`::text AND ghash < `wtmmmn`::text  
                     ->  BitmapAnd  (cost=10520118.62..10520118.62 rows=2 width=0)  
                           ->  Bitmap Index Scan on idx_tbl_pos_3  (cost=0.00..1751.33 rows=161 width=0)  
                                 Index Cond: ts >= `2018-01-01 00:06:25.2`::timestamp without time zone AND ts <= `2018-01-01 01:06:25.2`::timestamp without time zone  
                           ->  Bitmap Index Scan on idx_tbl_pos_1  (cost=0.00..10518358.87 rows=97466 width=0)  
                                 Index Cond: ghash >= `wtmmmm`::text AND ghash < `wtmmmn`::text  
 Settings:  effective_cache_size=8GB; enable_seqscan=off; gp_statistics_use_fkeys=on; optimizer=off; work_mem=64MB  
 Optimizer status: legacy query optimizer  
(12 rows)

在某5天出現在某個範圍的人的交集：

select uid from   
(select distinct uid from tbl_pos where ts between `2018-01-01 00:06:25.2` and `2018-01-01 01:06:25.2` and ghash >= `wtmmmm` and ghash < `wtmmm`||chr(ascii(`m`)+1)) t1  
join  
(select distinct uid from tbl_pos where ts between `2018-02-01 00:06:25.2` and `2018-02-01 01:06:25.2` and ghash >= `wtmmmm` and ghash < `wtmmm`||chr(ascii(`m`)+1)) t2  
using (uid)  
join  
(select distinct uid from tbl_pos where ts between `2018-03-01 00:06:25.2` and `2018-03-01 01:06:25.2` and ghash >= `wtmmmm` and ghash < `wtmmm`||chr(ascii(`m`)+1)) t3  
using (uid)  
join  
(select distinct uid from tbl_pos where ts between `2018-04-01 00:06:25.2` and `2018-04-01 01:06:25.2` and ghash >= `wtmmmm` and ghash < `wtmmm`||chr(ascii(`m`)+1)) t4  
using (uid)  
join  
(select distinct uid from tbl_pos where ts between `2018-05-01 00:06:25.2` and `2018-05-01 01:06:25.2` and ghash >= `wtmmmm` and ghash < `wtmmm`||chr(ascii(`m`)+1)) t5  
using (uid);  
    
  
 uid   
-----  
(0 rows)  
  
Time: 207.750 ms

小結

1、PostgreSQL bitmap scan支援將多個索引合併，在本例中體現在時間、空間、（其他）條件。

《PostgreSQL 資料庫多列複合索引的欄位順序選擇原理》

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

2、除了使用這種暴力求交集的方法，還有別的方法來計算伴隨麼？相似軌跡分析可能也是一個不錯的選擇

參考

《PostgreSQL + PostGIS 時態分析》

《阿里雲 PostgreSQL 產品生態；案例、開發實踐、管理實踐、學習資料、學習視訊 – 珍藏級》


相關文章