PostgreSQLGIN單列聚集索引應用

標籤

PostgreSQL , gin , 聚集索引 , 單列

---

背景

聚集儲存比較好理解，資料按照聚集KEY儲存在一個或相鄰的資料塊中，對聚集KEY的軌跡、行為資料檢索可以大幅減少IO。

那麼聚集索引呢？我們知道通常BTREE索引中儲存的是KEY以及對應資料的堆錶行號，每條記錄一個索引條目。

而GIN索引也是樹結構，只不過它對於單個KEY只儲存一個條目，所有行號會儲存到一個posting list或者posting tree中。

因此這樣的場景能體現GIN的幾個優勢

1. 對於帶重複KEY的軌跡、行為資料，建立GIN索引的話，可以節約一些空間。

2. 在使用GIN索引掃描單個KEY時，如果要輸出所有的軌跡資料，也能獲得非常好的查詢效率。

我們使用這裡同樣的例子，來測試一下就知道了

《PostgreSQL 聚集儲存 與 BRIN索引 – 高併發行為、軌跡類大吞吐資料查詢場景解說》

正文

1. 構建離散儲存測試資料，建立GIN索引

create unlogged table test(id int, info text, crt_time timestamp);    

insert into test select generate_series(1,10000), md5(id::text), clock_timestamp() from generate_series(1,10000) t(id);    

postgres=# dt+    
                           List of relations    
 Schema |        Name        | Type  |  Owner   |  Size   | Description     
--------+--------------------+-------+----------+---------+-------------    
 public | test               | table | postgres | 7303 MB |     

set maintenance_work_mem=`32GB`;  

create index idx_test_id on test using gin (id);  

di+ idx_test_id  

 public | idx_test_id              | index | postgres | test               | 391 MB     |   

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where id=1;  
                                                         QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.test  (cost=84.79..12541.74 rows=9767 width=45) (actual time=3.808..17.915 rows=10000 loops=1)  
   Output: id, info, crt_time  
   Recheck Cond: (test.id = 1)  
   Heap Blocks: exact=10000  
   Buffers: shared hit=10008  
   ->  Bitmap Index Scan on idx_test_id  (cost=0.00..82.35 rows=9767 width=0) (actual time=1.962..1.962 rows=10000 loops=1)  
         Index Cond: (test.id = 1)  
         Buffers: shared hit=8  
 Planning time: 0.092 ms  
 Execution time: 18.480 ms  
(10 rows)  

測試

$ vi test.sql    

set id random(1,10000)    
select * from test where id=:id;    

$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100000    

2. 構建聚集儲存測試資料，建立GIN索引

create unlogged table cluster_test_gin (like test);    

insert into cluster_test_gin select * from test order by id;    

set maintenance_work_mem =`32GB`;    

create index idx_cluster_test_gin_id on cluster_test_gin using gin (id);    

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from cluster_test_gin where id=1;  
                                                               QUERY PLAN                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.cluster_test_gin  (cost=90.83..13732.45 rows=10714 width=45) (actual time=1.037..2.236 rows=10000 loops=1)  
   Output: id, info, crt_time  
   Recheck Cond: (cluster_test_gin.id = 1)  
   Heap Blocks: exact=94  
   Buffers: shared hit=100  
   ->  Bitmap Index Scan on idx_cluster_test_gin_id  (cost=0.00..88.16 rows=10714 width=0) (actual time=1.010..1.010 rows=10000 loops=1)  
         Index Cond: (cluster_test_gin.id = 1)  
         Buffers: shared hit=6  
 Planning time: 0.092 ms  
 Execution time: 2.791 ms  
(10 rows)  

測試

$ vi test.sql    

set id random(1,10000)    
select * from cluster_test_gin where id=:id;    

$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100000    

測試結果

儲存格式	按KEY查詢軌跡 TPS	輸出吞吐	CPU利用率	索引大小	表大小
離散儲存 BTREE索引	2184	2184 萬行/s	99.8%	2.1 GB	7.3 GB
離散儲存 GIN索引	1620	1620 萬行/s	99.8%	391 MB	7.3 GB
聚集儲存 BTREE索引	4000	4000 萬行/s	99.8%	2.1 GB	7.3 GB
聚集儲存 GIN索引	3770	3770 萬行/s	99.8%	391 MB	7.3 GB
聚集儲存 BRIN索引	2255	2255 萬行/s	99.8%	232 KB	7.3 GB
行列變換 array	850	850 行/s	99.8%	248 KB	4.5 GB
行列變換 jsonb	1650	1650 行/s	99.8%	248 KB	4.5 GB

參考

《寶劍贈英雄 – 任意組合欄位等效查詢, 探探PostgreSQL多列展開式B樹》

《PostgreSQL GIN索引實現原理》

《PostgreSQL GIN multi-key search 優化》

《PostgreSQL 聚集儲存 與 BRIN索引 – 高併發行為、軌跡類大吞吐資料查詢場景解說》