時間、空間、對象海量極速多維檢索 - 阿裏雲RDS PostgreSQL最佳實踐

標簽

PostgreSQL , 時間 , 空間 , 對象屬性 , 多維度檢索 , 海量 , 空間索引 , 數據分區 , 塊級索引BRIN , 多級索引 , GIN倒排索引 , JSON索引 , 多列索引 , 多索引掃描合並 , bitmapAnd , bitmapOr , 物理掃描 , ctid掃描 , intersect , partial index , partition index

背景

人類或者其他對象的活動產生了海量的時間、空間數據，如果有科技能實現回到過去，過去的世界狀態會是什麼樣的？

實際上這個需求在數據庫中也存在。

對象數據分類

一類為靜止數據（相對靜止，比如建築物），一類為動態數據（比如人類活動，物聯網傳感器的活動）。

搜索需求分類

1、時空快照數據搜索

我們可以這樣來理解，有一些對象產生數據的頻率較低，例如建築物，道路等相對較為惰性的對象，可能幾年都不會變化一次，那麼這類對象產生的數據寫入到數據庫中，我們按時間範圍（例如2017-07-01到2017-07-02）去查詢，你可能查不到這類對象的數據。因為它們在這個時間段就沒有數據寫入到數據庫中。

如果我們要查詢過去某個時間點所有對象的最終狀態，可以用以下方法，毫秒級構建出所有對象的最終狀態：

《PostgreSQL 海量時序數據(任意滑動窗口實時統計分析) - 傳感器、人群、物體等對象跟蹤》

2、時空行為數據搜索

時空行為數據，指運動對象產生的FEED數據，例如人類的活動。

比如我們要分析某個時間段，在某個區域活動的人群特征。每逢周末的大學附近，是不是經常有皮條客出沒。

時空快照不在本文討論範疇，有需要可以參考我前麵寫的文章。我們接下來說說時空行為數據搜索。

數據結構

包含時間、空間、對象描述三種屬性的數據。

非結構化數據結構：

create table test(  
  id int8,      
  crt_time timestamp,   -- 時間  
  pos geometry,   -- 位置  
  obj jsonb       -- 對象描述  
);

對象描述除了使用JSON，也可以使用結構化的數據(例如)：

create table test(  
  id int8,      
  crt_time timestamp,   -- 時間  
  pos geometry,         -- 位置  
  c1 int,               -- 一些屬性的例子  
  c2 int,  
  c3 text,  
  c4 float8,  
  c5 int,  
  c6 date,  
  c7 text,  
  c8 int,  
  c9 int,  
  c10 int  
);

時空行為查詢SQL例子如下

select * from test   
  where   
  pos <-> ? < ?   
  and crt_time between ? and ?  
  and ( (c1 = ? and c2 between ? and ?)  or  c10=?)  
  ...  
  ;

優化思路

首先是一些散的知識點，如下：

1、時序塊級索引

crt_time字段表示數據生成的時間，是一個時序字段，在PostgreSQL堆存儲中，存儲與這個字段的值線性相關性特別好。

所以使用塊級索引是特別適合的。

我在一個TPC-H的測試中，使用BRIN塊級索引代替分區表，在大範圍搜索時，性能甚至超越了分區表的性能。

create index idx_test_1 on test using brin(crt_time);

應用案例

《PostgreSQL 物聯網黑科技 - 瘦身幾百倍的索引(BRIN index)》

2、空間索引

空間檢索，自然要用上空間索引，在PostgreSQL中有3種方法可以實現空間搜索。

1、GIST索引，針對geometry類型的索引。

create index idx_test_2 on test using gist(pos);

這個索引支持空間KNN搜索，空間位置判斷等。

2、SPGIST索引，針對geometry類型的索引。

create index idx_test_2 on test using spgist(pos);

這個索引支持空間KNN搜索，空間位置判斷等。

3、GEOHASH和BTREE索引，將經緯度轉換為GEOHASH，對HASH VALUE創建BTREE索引。使用表達式索引即可。

create index idx_test_3 on test using btree( ST_GeoHash(pos,15) );

這個索引支持prefix搜索（從而實現編碼後的地理位置信息網格包含的關係）。屬於LOSSY索引，需要二次過濾。

GiST、SPGiST 空間索引可以獲得最精確的位置信息，比GEOHASH要好，但是查詢時需要注意，下麵是優化方法，性能可以提升幾個數量級。

《GIS附近查找性能優化 - PostGIS long lat geometry distance search tuning using gist knn function》

3、GIN 倒排索引

對於對象屬性字段JSONB，或者是結構化的對象屬性多個字段。使用GIN倒排即可。

例如

create extension btree_gin;

非結構化索引：

create index idx_test_4 on test using gin( obj );

結構化索引

create index idx_test_4 on test using gin( c1,c2,c3,c4,c5,c6,c7,c8,c9 );

4、bitmapAnd bitmapOr

前麵對所有查詢維度，根據數據的類型不同以及查詢需求的差異，選擇了不同的索引接口。

但是這麼多索引，能同時使用嗎？PostgreSQL為多個索引提供了bitmapAnd, bitmapOr接口，可以將多個索引搜索合並起來，減少掃描的數據庫數量。

原理如下：

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

例如：

select * from test where   
  c1 ...    
  and crt_time between ? and ?   
  and test->>'c1' in (?, ? ...);

會根據統計信息，自動使用對應的索引，如果有必要，會使用多個索引進行bitmapAnd 或 bitmapOr的合並掃描，SKIP不需要掃描的PAGE，對於命中的PAGE進行RECHECK。

5、堆表存儲分級、分區

存儲分級，可以分為一級或者多級：

1、一級分區：

例如按時間進行分區。

create table test(  
  id int8,      
  crt_time timestamp,   -- 時間  
  pos geometry,   -- 位置  
  obj jsonb       -- 對象描述  
)  
PARTITION BY range (crt_time)  
;  
  
create table test_201701 PARTITION OF test for values FROM ('2017-01-01') TO ('2017-02-01');  
......

2、多級分區

例如按時間，再按GEOHASH進行範圍分區。

create table test_201701 PARTITION OF test for values FROM ('2017-01-01') TO ('2017-02-01') partition by range(st_geohash(pos,15));  
...  
create table test_201701_prefix1 PARTITION OF test for values FROM ('xxxx1') TO ('xxxx2');  -- 在地圖上生成BOX（GRID），找到對應的邊界，用邊界作為分區條件

使用分區後，查詢條件帶有分區鍵（例如時間、空間範圍）時可以落到對應分區，從而減少數據掃描。

再針對對象屬性建立GIN索引，可以實現極端高效的查詢。

6、索引分級、分區

與數據類似，在不使用分區表的情況下，索引也是支持分區邏輯的，例如

《分區索引的應用和實踐 - 阿裏雲RDS PostgreSQL最佳實踐》

例子

空間索引 + 時間分區

create index idx_20170101 on tbl using gist (pos) where crt_time between '2017-01-01' and '2017-01-02';  
...  
create index idx_20170102 on tbl using gist (pos) where crt_time between '2017-01-02' and '2017-01-03';  
...

使用以上分區索引，在輸入時間段進行空間搜索時，可以快速定位數據。

select * from tbl   
  where crt_time between '2017-01-01' and '2017-01-02'  -- 時間條件  
  and (pos <-> ?) < ?   -- 與某被搜索點的距離條件  
  and ?                 -- 其他條件  
  order by pos <-> ?    -- 按距離遠近排序  
  limit ?;              -- 輸出若幹條

甚至可以加入更多層級的索引分區，比如某個維度（對象屬性）是常用搜索條件，例如店鋪類別（假設可枚舉，或是一個較小範圍的數量）。

create index idx_20170101_mod0 on tbl using gist (pos) where crt_time between '2017-01-01' and '2017-01-02' and dtype=0;  
...  
create index idx_20170101_mod1 on tbl using gist (pos) where crt_time between '2017-01-01' and '2017-01-02' and dtype=1;  
...

使用以上分區索引，在輸入時間段以及某些特定條件進行空間搜索時，可以快速定位數據。

select * from tbl   
  where crt_time between '2017-01-01' and '2017-01-02'  -- 時間條件  
  and (pos <-> ?) < ?   -- 與某被搜索點的距離條件  
  and dtype=0           -- 對象條件  
  and ?                 -- 其他條件  
  order by pos <-> ?    -- 按距離遠近排序  
  limit ?;              -- 輸出若幹條

注意，以上SQL可以有極端性能優化的方法，參見：

《GIS附近查找性能優化 - PostGIS long lat geometry distance search tuning using gist knn function》

索引本身的組織形式，或者說索引結構，可以按邏輯分區進行重構，類似以上創建索引的方法，覆蓋所有的條件。

7、CTID intersect array JOIN SCAN

前麵說了多個索引，或者GIN索引的內部會自動進行BitmapAnd，BitmapOr合並掃描，實際上我們在SQL中，也可以明確進行這類掃描。

每個條件篩選出對應的CTID

使用intersect，UNION生成最終複合條件的CTID。（intersect對應and條件, union對應or條件。）

生成ctid的array, 使用ctid掃描用法如下

《在PostgreSQL中實現update | delete limit》

例子

1、創建對象FEED數據表

postgres=# create table tbl (id int, info text, crt_time timestamp, pos point, c1 int , c2 int, c3 int );  
CREATE TABLE

2、寫入5000萬測試數據

postgres=# insert into tbl select generate_series(1,50000000), md5(random()::text), clock_timestamp(), point(180-random()*180, 90-random()*90), random()*10000, random()*5000, random()*1000;   
INSERT 0 50000000

3、創建對象索引

postgres=# create index idx_tbl_1 on tbl using gin (info, c1, c2, c3);  
CREATE INDEX

4、創建時間索引

postgres=# create index idx_tbl_2 on tbl using btree (crt_time);  
CREATE INDEX

5、創建空間索引

postgres=# create index idx_tbl_3 on tbl using gist (pos);  
CREATE INDEX

6、生成數據layout，方便後麵的查詢

postgres=# select min(crt_time),max(crt_time),count(*) from tbl;  
            min             |            max             |  count     
----------------------------+----------------------------+----------  
 2017-07-22 17:59:34.136497 | 2017-07-22 18:01:27.233688 | 50000000  
(1 row)

7、創建KNN極端查詢函數

create or replace function ff(point, float8, int) returns setof tid as $$                                                          
declare  
  v_rec record;  
  v_limit int := $3;  
begin  
  set local enable_seqscan=off;   -- 強製索引, 掃描行數夠就退出.  
  for v_rec in   
    select *,  
    (pos <-> $1) as dist,  
    ctid  
    from tbl   
    order by pos <-> $1  
  loop  
    if v_limit <=0 then  
      -- raise notice '已經取足數據';  
      return;  
    end if;  
    if v_rec.dist > $2 then  
      -- raise notice '滿足條件的點已輸出完畢';  
      return;  
    else  
      return next v_rec.ctid;  
    end if;  
    v_limit := v_limit -1;  
  end loop;  
end;  
$$ language plpgsql strict volatile;  
    
postgres=# select * from ff(point '(100,100)',100,100) ;  
     ff        
-------------  
 (407383,11)  
 (640740,9)  
 (26073,51)  
 (642750,34)  
...  
(100 rows)  
Time: 1.061 ms

8、ctid合並檢索

輸出滿足以下條件的記錄

(  
c1 in (1,2,3,4,100,200,99,88,77,66,55)  
  or  
c2 < 10  
)  
  and  
pos <-> point '(0,0)' < 5  
  and  
crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40';

首先進行條件分解，了解一下每個條件有多少記錄，以及使用索引掃描的時間開銷。

1、54907條。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where c1 in (1,2,3,4,100,200,99,88,77,66,55);  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on postgres.tbl  (cost=820.07..65393.94 rows=54151 width=73) (actual time=23.842..91.911 rows=54907 loops=1)  
   Output: id, info, crt_time, pos, c1, c2, c3  
   Recheck Cond: (tbl.c1 = ANY ('{1,2,3,4,100,200,99,88,77,66,55}'::integer[]))  
   Heap Blocks: exact=52778  
   Buffers: shared hit=52866  
   ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..806.54 rows=54151 width=0) (actual time=14.264..14.264 rows=54907 loops=1)  
         Index Cond: (tbl.c1 = ANY ('{1,2,3,4,100,200,99,88,77,66,55}'::integer[]))  
         Buffers: shared hit=88  
 Planning time: 0.105 ms  
 Execution time: 94.606 ms  
(10 rows)

2、95147條。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where c2<10;  
                                                           QUERY PLAN                                                              
---------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on postgres.tbl  (cost=835.73..112379.10 rows=99785 width=73) (actual time=69.243..179.388 rows=95147 loops=1)  
   Output: id, info, crt_time, pos, c1, c2, c3  
   Recheck Cond: (tbl.c2 < 10)  
   Heap Blocks: exact=88681  
   Buffers: shared hit=88734  
   ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..810.79 rows=99785 width=0) (actual time=53.612..53.612 rows=95147 loops=1)  
         Index Cond: (tbl.c2 < 10)  
         Buffers: shared hit=53  
 Planning time: 0.094 ms  
 Execution time: 186.201 ms  
(10 rows)

3、149930條。（PostgreSQL使用了bitmapOr進行合並掃描，快速的得到結果）

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where c1 in (1,2,3,4,100,200,99,88,77,66,55) or c2 <10;  
                                                             QUERY PLAN                                                               
------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on postgres.tbl  (cost=1694.23..166303.58 rows=153828 width=73) (actual time=98.988..266.852 rows=149930 loops=1)  
   Output: id, info, crt_time, pos, c1, c2, c3  
   Recheck Cond: ((tbl.c1 = ANY ('{1,2,3,4,100,200,99,88,77,66,55}'::integer[])) OR (tbl.c2 < 10))  
   Heap Blocks: exact=134424  
   Buffers: shared hit=134565  
   ->  BitmapOr  (cost=1694.23..1694.23 rows=153936 width=0) (actual time=73.763..73.763 rows=0 loops=1)  
         Buffers: shared hit=141  
         ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..806.54 rows=54151 width=0) (actual time=16.733..16.733 rows=54907 loops=1)  
               Index Cond: (tbl.c1 = ANY ('{1,2,3,4,100,200,99,88,77,66,55}'::integer[]))  
               Buffers: shared hit=88  
         ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..810.79 rows=99785 width=0) (actual time=57.029..57.029 rows=95147 loops=1)  
               Index Cond: (tbl.c2 < 10)  
               Buffers: shared hit=53  
 Planning time: 0.149 ms  
 Execution time: 274.548 ms  
(15 rows)

4、60687條。（我們使用了KNN的變態性能優化方法，依舊需要195毫秒）

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from ff(point '(0,0)',5,1000000);  
                                                      QUERY PLAN                                                        
----------------------------------------------------------------------------------------------------------------------  
 Function Scan on postgres.ff  (cost=0.25..10.25 rows=1000 width=6) (actual time=188.563..192.114 rows=60687 loops=1)  
   Output: ff  
   Function Call: ff('(0,0)'::point, '5'::double precision, 1000000)  
   Buffers: shared hit=61296  
 Planning time: 0.029 ms  
 Execution time: 195.097 ms  
(6 rows)

如果不使用KNN優化，看看需要多久。

驚不驚喜、意不意外，極端優化性能提升了1個數量級。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where pos<-> point '(0,0)' < 5 ;  
                                                        QUERY PLAN                                                           
---------------------------------------------------------------------------------------------------------------------------  
 Seq Scan on postgres.tbl  (cost=0.00..1416667.00 rows=16666667 width=73) (actual time=0.016..6393.542 rows=60687 loops=1)  
   Output: id, info, crt_time, pos, c1, c2, c3  
   Filter: ((tbl.pos <-> '(0,0)'::point) < '5'::double precision)  
   Rows Removed by Filter: 49939313  
   Buffers: shared hit=666667  
 Planning time: 0.090 ms  
 Execution time: 6397.087 ms  
(7 rows)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where pos<-> point '(0,0)' < 5 order by pos<-> point '(0,0)';  
                                                                  QUERY PLAN                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_tbl_3 on postgres.tbl  (cost=0.42..2623952.79 rows=16666667 width=81) (actual time=0.088..83076.718 rows=60687 loops=1)  
   Output: id, info, crt_time, pos, c1, c2, c3, (pos <-> '(0,0)'::point)  
   Order By: (tbl.pos <-> '(0,0)'::point)  
   Filter: ((tbl.pos <-> '(0,0)'::point) < '5'::double precision)  
   Rows Removed by Filter: 49939313  
   Buffers: shared hit=50454244  
 Planning time: 0.097 ms  
 Execution time: 83080.970 ms  
(8 rows)

5、2640751條。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40';  
                                                                          QUERY PLAN                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_tbl_2 on postgres.tbl  (cost=0.56..90860.33 rows=2462443 width=73) (actual time=0.017..444.194 rows=2640751 loops=1)  
   Output: id, info, crt_time, pos, c1, c2, c3  
   Index Cond: ((tbl.crt_time >= '2017-07-22 17:59:34'::timestamp without time zone) AND (tbl.crt_time <= '2017-07-22 17:59:40'::timestamp without time zone))  
   Buffers: shared hit=42430  
 Planning time: 0.140 ms  
 Execution time: 567.451 ms  
(6 rows)

使用所有的索引，逐個條件掃描，並得到ctid，然後進行CTID掃描，我們可以拆解來看：

首先我們看一下時間、對象屬性的合並查詢，哇，COOL!!!，由於時使用了bitmapAnd, bitmapOr，使得SKIP了大多數的數據塊，因此掃描時間比單一索引掃描要短。

注意到這一步操作，記錄數直接降到了7847條。

postgres=# explain (analyze,verbose,timing,costs,buffers) select ctid from tbl   
  where crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40'   
  and (   
  c1 in (1,2,3,4,100,200,99,88,77,66,55)  
    or  
  c2 < 10  
  );  
                                                                                                                      QUERY PLAN     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on postgres.tbl  (cost=35025.85..44822.94 rows=7576 width=6) (actual time=205.577..214.821 rows=7847 loops=1)  
   Output: ctid  
   Recheck Cond: (((tbl.c1 = ANY ('{1,2,3,4,100,200,99,88,77,66,55}'::integer[])) OR (tbl.c2 < 10)) AND (tbl.crt_time >= '2017-07-22 17:59:34'::timestamp without time zone) AND (tbl.crt_time <= '2017-07-22 17:59:40'::timestamp without time zone))  
   Heap Blocks: exact=6983  
   Buffers: shared hit=14343  
   ->  BitmapAnd  (cost=35025.85..35025.85 rows=7581 width=0) (actual time=204.048..204.048 rows=0 loops=1)  
         Buffers: shared hit=7360  
         ->  BitmapOr  (cost=1621.11..1621.11 rows=153936 width=0) (actual time=70.279..70.279 rows=0 loops=1)  
               Buffers: shared hit=141  
               ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..806.54 rows=54151 width=0) (actual time=15.860..15.860 rows=54907 loops=1)  
                     Index Cond: (tbl.c1 = ANY ('{1,2,3,4,100,200,99,88,77,66,55}'::integer[]))  
                     Buffers: shared hit=88  
               ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..810.79 rows=99785 width=0) (actual time=54.418..54.418 rows=95147 loops=1)  
                     Index Cond: (tbl.c2 < 10)  
                     Buffers: shared hit=53  
         ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..33402.60 rows=2462443 width=0) (actual time=127.101..127.101 rows=2640751 loops=1)  
               Index Cond: ((tbl.crt_time >= '2017-07-22 17:59:34'::timestamp without time zone) AND (tbl.crt_time <= '2017-07-22 17:59:40'::timestamp without time zone))  
               Buffers: shared hit=7219  
 Planning time: 0.203 ms  
 Execution time: 216.697 ms  
(20 rows)

然後我們看看KNN的掃描時長：

注意到符合KNN距離條件的數據有60687條，所以我會引出CTID合並掃描與原始掃描方法性能對比問題的解釋。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from ff(point '(0,0)',5,1000000);  
                                                      QUERY PLAN                                                        
----------------------------------------------------------------------------------------------------------------------  
 Function Scan on postgres.ff  (cost=0.25..10.25 rows=1000 width=6) (actual time=188.563..192.114 rows=60687 loops=1)  
   Output: ff  
   Function Call: ff('(0,0)'::point, '5'::double precision, 1000000)  
   Buffers: shared hit=61296  
 Planning time: 0.029 ms  
 Execution time: 195.097 ms  
(6 rows)

最後，我們將這幾個合並成CTID

select * from ff(point '(0,0)',5,1000000)   
  intersect   
select ctid from tbl   
  where crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40'   
  and (   
  c1 in (1,2,3,4,100,200,99,88,77,66,55)  
    or  
  c2 < 10  
  );  
     ff       
------------  
 (1394,8)  
 (3892,50)  
 (6124,45)  
 (7235,8)  
 (7607,45)  
 (11540,8)  
 (13397,31)  
 (14266,36)  
 (18149,7)  
 (19256,44)  
 (24671,62)  
 (26525,64)  
 (30235,48)  
(13 rows)  
  
Time: 463.012 ms

最終章，得到最終記錄。

select * from tbl where ctid = any   
(   
array( -- array start  
select * from ff(point '(0,0)',5,1000000) intersect select ctid from tbl   
  where crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40'   
  and (   
  c1 in (1,2,3,4,100,200,99,88,77,66,55)  
    or  
  c2 < 10  
  )  
)  -- array end  
);  
  
   id    |               info               |          crt_time          |                  pos                   |  c1  |  c2  | c3    
---------+----------------------------------+----------------------------+----------------------------------------+------+------+-----  
  104558 | c4699c933d4e2d2a10d828c4ff0b3362 | 2017-07-22 17:59:34.362508 | (4.20534582808614,2.43749532848597)    |   99 | 4858 | 543  
  291950 | 1c2901689ab1eb7653d8ad972f7aa376 | 2017-07-22 17:59:34.776808 | (2.5384977646172,1.09820357523859)     |    3 | 2131 | 360  
  459345 | 9e46548f29d914019ce53a589be8ebac | 2017-07-22 17:59:35.148699 | (0.715781506150961,3.1486327573657)    |    1 | 1276 |   8  
  542633 | c422d6137f9111d5c2dc723b40c7023f | 2017-07-22 17:59:35.334278 | (0.0631888210773468,2.2334903664887)   | 4968 |    3 | 245  
  570570 | fc57bfc6b7781d89b17c90417bd306f7 | 2017-07-22 17:59:35.39653  | (3.14926156774163,1.04107855819166)    |   88 | 2560 | 561  
  865508 | 34509c7f7640afaf288a5e1d38199701 | 2017-07-22 17:59:36.052573 | (3.12869547866285,2.34822122845799)    |    2 |   65 | 875  
 1004806 | afe9f88cbebf615a7ae5f41180c4b33f | 2017-07-22 17:59:36.362027 | (1.13972157239914,3.28763140831143)    |    3 | 1639 | 208  
 1069986 | 6b9f27bfde993fb0bae3336ac010af7a | 2017-07-22 17:59:36.507775 | (4.51995821669698,2.08761331625283)    |    2 |  200 | 355  
 1361182 | 7c4c1c208c2b2b21f00772c43955d238 | 2017-07-22 17:59:37.155127 | (1.7334086727351,2.18367457855493)     | 9742 |    0 | 232  
 1444244 | 41bf6f8e4b89458c13fb408a7db05284 | 2017-07-22 17:59:37.339594 | (0.52773853763938,2.16670122463256)    |    1 | 2470 | 820  
 1850387 | 6e0011c6db76075edd2aa7f81ec94129 | 2017-07-22 17:59:38.243091 | (0.0168232340365648,0.420973123982549) |  100 | 4395 | 321  
 1989439 | 6211907ac254a4a3ca54f90822a2095e | 2017-07-22 17:59:38.551637 | (0.0274275150150061,0.490507003851235) | 1850 |    5 |  74  
 2267673 | 898fdd54dcc5b14c27cf1c8b9afe2471 | 2017-07-22 17:59:39.170035 | (0.394239127635956,2.86229319870472)   | 2892 |    6 | 917  
(13 rows)  
  
Time: 462.715 ms

最終耗時462毫秒。

9、采用原始SQL，性能如何呢？ - PostgreSQL 多索引bitmapAnd bitmapOr skip scan

直寫SQL，不使用CTID合並掃描。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl   
  where   
  crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40'   
  and (   
    c1 in (1,2,3,4,100,200,99,88,77,66,55)  
      or  
    c2 < 10  
    )  
  and  
  pos <-> point '(0,0)' < 5;  
  
  
 Bitmap Heap Scan on postgres.tbl  (cost=35022.06..44857.06 rows=2525 width=73) (actual time=205.542..214.547 rows=13 loops=1)  
   Output: id, info, crt_time, pos, c1, c2, c3  
   Recheck Cond: (((tbl.c1 = ANY ('{1,2,3,4,100,200,99,88,77,66,55}'::integer[])) OR (tbl.c2 < 10)) AND (tbl.crt_time >= '2017-07-22 17:59:34'::timestamp without time zone) AND (tbl.crt_time <= '2017-07-22 17:59:40'::timestamp without time zone))  
   Filter: ((tbl.pos <-> '(0,0)'::point) < '5'::double precision)  
   Rows Removed by Filter: 7834  
   Heap Blocks: exact=6983  
   Buffers: shared hit=14343  
   ->  BitmapAnd  (cost=35022.06..35022.06 rows=7581 width=0) (actual time=203.620..203.620 rows=0 loops=1)  
         Buffers: shared hit=7360  
         ->  BitmapOr  (cost=1618.58..1618.58 rows=153936 width=0) (actual time=71.660..71.660 rows=0 loops=1)  
               Buffers: shared hit=141  
               ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..806.54 rows=54151 width=0) (actual time=14.861..14.861 rows=54907 loops=1)  
                     Index Cond: (tbl.c1 = ANY ('{1,2,3,4,100,200,99,88,77,66,55}'::integer[]))  
                     Buffers: shared hit=88  
               ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..810.79 rows=99785 width=0) (actual time=56.797..56.797 rows=95147 loops=1)  
                     Index Cond: (tbl.c2 < 10)  
                     Buffers: shared hit=53  
         ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..33402.60 rows=2462443 width=0) (actual time=125.255..125.255 rows=2640751 loops=1)  
               Index Cond: ((tbl.crt_time >= '2017-07-22 17:59:34'::timestamp without time zone) AND (tbl.crt_time <= '2017-07-22 17:59:40'::timestamp without time zone))  
               Buffers: shared hit=7219  
 Planning time: 0.160 ms  
 Execution time: 216.797 ms  
(22 rows)

COOL!!!，也是意料之中的，因為我們前麵已經解釋了，出去KNN條件，其他條件已經將結果收斂到7000多條了，所以完全沒有必要使用KNN的索引（符合KNN條件的記錄數有60687條，所以使用KNN索引掃描都花了195毫秒）。

結果驗證：

select * from tbl   
  where   
  crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40'   
  and (   
    c1 in (1,2,3,4,100,200,99,88,77,66,55)  
      or  
    c2 < 10  
    )    
  and    
  pos <-> point '(0,0)' < 5;    
  
   id    |               info               |          crt_time          |                  pos                   |  c1  |  c2  | c3    
---------+----------------------------------+----------------------------+----------------------------------------+------+------+-----  
  104558 | c4699c933d4e2d2a10d828c4ff0b3362 | 2017-07-22 17:59:34.362508 | (4.20534582808614,2.43749532848597)    |   99 | 4858 | 543  
  291950 | 1c2901689ab1eb7653d8ad972f7aa376 | 2017-07-22 17:59:34.776808 | (2.5384977646172,1.09820357523859)     |    3 | 2131 | 360  
  459345 | 9e46548f29d914019ce53a589be8ebac | 2017-07-22 17:59:35.148699 | (0.715781506150961,3.1486327573657)    |    1 | 1276 |   8  
  542633 | c422d6137f9111d5c2dc723b40c7023f | 2017-07-22 17:59:35.334278 | (0.0631888210773468,2.2334903664887)   | 4968 |    3 | 245  
  570570 | fc57bfc6b7781d89b17c90417bd306f7 | 2017-07-22 17:59:35.39653  | (3.14926156774163,1.04107855819166)    |   88 | 2560 | 561  
  865508 | 34509c7f7640afaf288a5e1d38199701 | 2017-07-22 17:59:36.052573 | (3.12869547866285,2.34822122845799)    |    2 |   65 | 875  
 1004806 | afe9f88cbebf615a7ae5f41180c4b33f | 2017-07-22 17:59:36.362027 | (1.13972157239914,3.28763140831143)    |    3 | 1639 | 208  
 1069986 | 6b9f27bfde993fb0bae3336ac010af7a | 2017-07-22 17:59:36.507775 | (4.51995821669698,2.08761331625283)    |    2 |  200 | 355  
 1361182 | 7c4c1c208c2b2b21f00772c43955d238 | 2017-07-22 17:59:37.155127 | (1.7334086727351,2.18367457855493)     | 9742 |    0 | 232  
 1444244 | 41bf6f8e4b89458c13fb408a7db05284 | 2017-07-22 17:59:37.339594 | (0.52773853763938,2.16670122463256)    |    1 | 2470 | 820  
 1850387 | 6e0011c6db76075edd2aa7f81ec94129 | 2017-07-22 17:59:38.243091 | (0.0168232340365648,0.420973123982549) |  100 | 4395 | 321  
 1989439 | 6211907ac254a4a3ca54f90822a2095e | 2017-07-22 17:59:38.551637 | (0.0274275150150061,0.490507003851235) | 1850 |    5 |  74  
 2267673 | 898fdd54dcc5b14c27cf1c8b9afe2471 | 2017-07-22 17:59:39.170035 | (0.394239127635956,2.86229319870472)   | 2892 |    6 | 917  
(13 rows)

分區索引例子

假設我們還是以上查詢條件，同時我們使用了分區索引，那麼能達到什麼樣的效果呢？

（這裏為了演示分區索引帶來的極端效果，所以這麼幹的，實際上現實情況可能收斂得沒有這麼嚴重，例如按天、按ID HASH等進行收斂。隻要能收斂，就一樣能達到很好的效果。）

postgres=# create index idx_tbl_4 on tbl using gist (pos) where crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40'   
  and (   
    c1 in (1,2,3,4,100,200,99,88,77,66,55)  
      or  
    c2 < 10  
    )  ;  
  
CREATE INDEX  
Time: 8359.330 ms (00:08.359)

重構極端KNN優化函數

create or replace function ff(point, float8, int) returns setof record as $$                                                          
declare  
  v_rec record;  
  v_limit int := $3;  
begin  
  set local enable_seqscan=off;   -- 強製索引, 掃描行數夠就退出.  
  for v_rec in   
    select *,  
    (pos <-> $1) as dist  
    from tbl   
    where   
    crt_time between '2017-07-22 17:59:34' and '2017-07-22 17:59:40'   
    and (   
      c1 in (1,2,3,4,100,200,99,88,77,66,55)  
        or  
      c2 < 10  
    )  
    order by pos <-> $1  
  loop  
    if v_limit <=0 then  
      -- raise notice '已經取足數據';  
      return;  
    end if;  
    if v_rec.dist > $2 then  
      -- raise notice '滿足條件的點已輸出完畢';  
      return;  
    else  
      return next v_rec;  
    end if;  
    v_limit := v_limit -1;  
  end loop;  
end;  
$$ language plpgsql strict volatile;

查詢性能：

postgres=# select * from ff(point '(0,0)', 5, 10000000) as t(id int, info text, crt_time timestamp, pos point, c1 int, c2 int, c3 int, dist float8);   
   id    |               info               |          crt_time          |                  pos                   |  c1  |  c2  | c3  |       dist          
---------+----------------------------------+----------------------------+----------------------------------------+------+------+-----+-------------------  
 1850387 | 6e0011c6db76075edd2aa7f81ec94129 | 2017-07-22 17:59:38.243091 | (0.0168232340365648,0.420973123982549) |  100 | 4395 | 321 | 0.421309141034319  
 1989439 | 6211907ac254a4a3ca54f90822a2095e | 2017-07-22 17:59:38.551637 | (0.0274275150150061,0.490507003851235) | 1850 |    5 |  74 |  0.49127323294376  
 1444244 | 41bf6f8e4b89458c13fb408a7db05284 | 2017-07-22 17:59:37.339594 | (0.52773853763938,2.16670122463256)    |    1 | 2470 | 820 |  2.23004532710301  
  542633 | c422d6137f9111d5c2dc723b40c7023f | 2017-07-22 17:59:35.334278 | (0.0631888210773468,2.2334903664887)   | 4968 |    3 | 245 |  2.23438404136508  
  291950 | 1c2901689ab1eb7653d8ad972f7aa376 | 2017-07-22 17:59:34.776808 | (2.5384977646172,1.09820357523859)     |    3 | 2131 | 360 |  2.76586731309247  
 1361182 | 7c4c1c208c2b2b21f00772c43955d238 | 2017-07-22 17:59:37.155127 | (1.7334086727351,2.18367457855493)     | 9742 |    0 | 232 |  2.78803520274409  
 2267673 | 898fdd54dcc5b14c27cf1c8b9afe2471 | 2017-07-22 17:59:39.170035 | (0.394239127635956,2.86229319870472)   | 2892 |    6 | 917 |  2.88931598221975  
  459345 | 9e46548f29d914019ce53a589be8ebac | 2017-07-22 17:59:35.148699 | (0.715781506150961,3.1486327573657)    |    1 | 1276 |   8 |  3.22896754478952  
  570570 | fc57bfc6b7781d89b17c90417bd306f7 | 2017-07-22 17:59:35.39653  | (3.14926156774163,1.04107855819166)    |   88 | 2560 | 561 |  3.31688000783581  
 1004806 | afe9f88cbebf615a7ae5f41180c4b33f | 2017-07-22 17:59:36.362027 | (1.13972157239914,3.28763140831143)    |    3 | 1639 | 208 |  3.47958123047986  
  865508 | 34509c7f7640afaf288a5e1d38199701 | 2017-07-22 17:59:36.052573 | (3.12869547866285,2.34822122845799)    |    2 |   65 | 875 |  3.91188935630676  
  104558 | c4699c933d4e2d2a10d828c4ff0b3362 | 2017-07-22 17:59:34.362508 | (4.20534582808614,2.43749532848597)    |   99 | 4858 | 543 |  4.86069100130757  
 1069986 | 6b9f27bfde993fb0bae3336ac010af7a | 2017-07-22 17:59:36.507775 | (4.51995821669698,2.08761331625283)    |    2 |  200 | 355 |  4.97877009299311  
(13 rows)  
  
Time: 0.592 ms

SO COOL!!!，從200多毫秒，優化到了0.幾個毫秒。

優化思路小結

回顧優化方法，

1、對不同的數據類型，構建不同的索引。

例如空間gist或spgist索引、時間btree或brin索引、對象多種屬性（倒排）gin索引。

索引的目的是降低數據掃描的範圍。

2、方法5，提到了數據分區，數據分區的目的是讓數據有意識的組織，這裏指的意識是根據搜索的需求進行有意識的組織。例如時間是必要查詢條件，或者常用查詢條件，那麼可以對數據按時間切分（分區），從而降低掃描的量。

3、方法6，提到了索引分區，目的和方法5類似，隻是在索引層麵進行了這樣的分布，從而在索引掃描時，直接提高數據的命中率。

4、方法7，CTID合並掃描，與PostgreSQL 多索引的bitmapAnd, bitmapOr掃描的思想類似，bitmapAnd/bitmapOr是跳過不需要掃描的BLOCK，而方法7的ctid合並掃描則是跳過不需要掃描的行。

將多個索引掃描得到的CTID進行合並。跳過不需要掃描的行號。

如果某個過濾條件可以將CTID（記錄數）降到很低（其他條件為AND條件）的情況下，則沒有必要使用CTID合並掃描，其他條件使用FILTER即可（增加一點點CPU開銷）。

5、以無法為有法，以無限為有限，此乃武術最高境界。

在PostgreSQL中，實現了多索引的bitmapAnd, bitmapOr掃描，極大的提高了多個條件（索引）帶來的數據命中率。

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

並且PostgreSQL有很好的CBO估算機製，使得PG不會要一味的使用上所有的索引進行BITMAP合並掃描。這也是為什麼章節“采用原始SQL，性能如何呢？ - PostgreSQL 多索引bitmapAnd bitmapOr skip scan”性能更優秀的原因。

6、極端的優化是什麼樣的？

采樣方法5或在方法6，以可以固定的條件作為分區鍵，對數據或索引進行分區。

對於其他條件，可以使用PostgreSQL中多索引的bitmapAnd, bitmapOr掃描，提高多個輸入條件（索引）帶來的數據命中率。

我們可以看到，在5000萬數據中，按時間、空間、對象屬性進行多維檢索，性能提升到了0.592毫秒。

參考

《多字段，任意組合條件查詢(無需建模) - 毫秒級實時圈人最佳實踐》

最後更新：2017-07-23 22:02:37

時間、空間、對象海量極速多維檢索 - 阿裏雲RDS PostgreSQL最佳實踐

標簽

背景

對象數據分類

搜索需求分類

數據結構

優化思路

1、時序塊級索引

2、空間索引

3、GIN 倒排索引

4、bitmapAnd bitmapOr

5、堆表存儲分級、分區

6、索引分級、分區

7、CTID intersect array JOIN SCAN

例子

1、創建對象FEED數據表

2、寫入5000萬測試數據

3、創建對象索引

4、創建時間索引

5、創建空間索引

6、生成數據layout，方便後麵的查詢

7、創建KNN極端查詢函數

8、ctid合並檢索

9、采用原始SQL，性能如何呢？ - PostgreSQL 多索引bitmapAnd bitmapOr skip scan

分區索引例子

優化思路小結

參考

上一篇：我的考駕照之路

下一篇：我的考駕照之路

相關內容

熱門內容

最新內容

時間、空間、對象 海量極速多維檢索 - 阿裏雲RDS PostgreSQL最佳實踐

標簽

背景

對象數據分類

搜索需求分類

數據結構

優化思路

1、時序塊級索引

2、空間索引

3、GIN 倒排索引

4、bitmapAnd bitmapOr

5、堆表存儲分級、分區

6、索引分級、分區

7、CTID intersect array JOIN SCAN

例子

1、創建對象FEED數據表

2、寫入5000萬測試數據

3、創建對象索引

4、創建時間索引

5、創建空間索引

6、生成數據layout，方便後麵的查詢

7、創建KNN極端查詢函數

8、ctid合並檢索

9、采用原始SQL，性能如何呢？ - PostgreSQL 多索引bitmapAnd bitmapOr skip scan

分區索引例子

優化思路小結

參考

上一篇： 我的考駕照之路

下一篇： 我的考駕照之路

相關內容

熱門內容

最新內容

時間、空間、對象海量極速多維檢索 - 阿裏雲RDS PostgreSQL最佳實踐

上一篇：我的考駕照之路

下一篇：我的考駕照之路