PostgreSQL DBA(53) - Index(BRIN)

本節簡單介紹了PostgreSQL中的BRIN索引,包括BRIN索引的基礎知識和結構等.

簡介
BRIN是塊範圍索引Block Range Index的簡稱,其設計思想是避免在絕對不匹配的Page中查詢,而不是快速找到匹配的行,利用相對於Btree極少的空間來獲取可相對較好的查詢效率,BRIN能夠在TB級別上面的表上建立索引,而索引和維護開銷則很小。
BRIN在列值與其在表中的物理位置相關性較大時工作得很好,比如序列值,但在相關性不大的列值上效能較差比如隨機值上.
BRIN索引的工作原理是:首先把資料表劃分為多個範圍每個範圍有多個pages,索引儲存range的概要資訊,如最大最小值等.如查詢條件包含該列,而條件值不在範圍中,那麼整個範圍都會跳過,反之,需要掃描所有pages中的所有行來進行匹配.
BRIN可以視為分割槽&順序掃描的加速器而不是Index.

結構
第一(準確來說是第0)頁儲存的是後設資料,通過後設資料定位帶有摘要資訊的pages.在這些頁面上的每一索引行包含每一個範圍的摘要資訊.
在meta page和摘要資訊之間的pages稱為反向範圍對映revmap(reverse range map),revmap實際上是指向索引行的TIDs指標陣列.

對於某些範圍,revmap中的指標沒有指向索引行,這意味著仍沒有摘要資訊.

索引掃描
與其他AM指向資料行不同,BRIN不會通過TID逐個返回行而是通過構建bitmap來訪問.有兩種型別的bitmap pages,一種是精確的指向行,另外一種是不精確的執行page,這裡使用的是不精確的page.
掃描演算法不復雜,順序訪問ranges map,通過指標確定包含range摘要資訊的索引行,所有range的pages都會新增到bitmap中,結果bitmap的使用方法與普通的bitmap的一樣.

索引更新
在page中新增新版本行,需要確定包含該行的range並使用ranges對映找到含有摘要資訊的索引行,這些操作都是簡單的算術運算.舉個例子,range大小為4在page 13上新增一行其值為42,range的編號(從0開始)為13/4 = 3,因此在revmap中使用偏移為3的指標.
該range最小值為31,最大值為40,由於新值42超出範圍,更新最大值為42,但如果新值在範圍之內,索引不需要更新.

在索引建立後,所有可用範圍的摘要資訊都會計算,但在表擴充套件後,新pages可能會超出限制,這時候有兩種可用的方法:
1.不在馬上更新索引.在vacuum的時候才更新,或者通過函式brin_summarize_new_values更新;
2.以autosummarize選項建立索引,則馬上更新索引,該引數預設為off.
在新範圍出現時,revmap的大小會增長.在通過其他page增長時,現存的行版本會移到其他pages上.

在刪除行時,不需要做任何操作.最大和最小值可能已經不存在了,但為了檢測這一點需要遍歷range中的所有值,這樣不太值得.索引的正確性不會影響掃描的正確性,只是對效能有所影響而已:需要掃描比實際更多的pages.PG提供了brin_desummarize_range和brin_summarize_new_values函式手工重新計算摘要資訊,但如何檢測到這樣的需求?起碼傳統方法是做不到的.

更新行,只需要刪除過期版本新增一個新的即可.

使用
測試資料:

testdb=# create table t_brin(id int,randomnum int);
CREATE TABLE
testdb=# 
testdb=# truncate table t_brin;
TRUNCATE TABLE
testdb=# insert into t_brin select x,random()*10000000 from generate_series(1,10000000) as x;
INSERT 0 10000000
testdb=# 
testdb=# create index idx_t_brin_id on t_brin using brin(id);
CREATE INDEX
testdb=# create index idx_t_brin_randomnum on t_brin using brin(randomnum);
CREATE INDEX
testdb=# create index idx_t_brin_btreeid on t_brin using btree(id);
CREATE INDEX
testdb=# 
testdb=# select pg_size_pretty(pg_table_size('t_brin'));
 pg_size_pretty 
----------------
 346 MB
(1 row)
testdb=# select pg_size_pretty(pg_table_size('idx_t_brin_id'));
 pg_size_pretty 
----------------
 48 kB
(1 row)
testdb=# select pg_size_pretty(pg_table_size('idx_t_brin_btreeid'));
 pg_size_pretty 
----------------
 214 MB
(1 row)

brin索引只有48KB,而Btree索引是214M,是BRIN索引大小的4565倍!

在順序列上執行查詢:

testdb=# analyze t_brin;
ANALYZE
testdb=# explain analyze verbose select * from t_brin where id = 102345;
                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Scan using idx_t_brin_btreeid on public.t_brin  (cost=0.43..8.45 rows=1 width=8) (actual time=0.221..0.222 rows=1 loop
s=1)
   Output: id, randomnum
   Index Cond: (t_brin.id = 102345)
 Planning Time: 0.521 ms
 Execution Time: 0.266 ms
(5 rows)
testdb=# drop index idx_t_brin_btreeid;
DROP INDEX
testdb=# explain analyze verbose select * from t_brin where id = 102345;
                                                          QUERY PLAN 
-------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.t_brin  (cost=12.03..41657.45 rows=1 width=8) (actual time=4.049..7.630 rows=1 loops=1)
   Output: id, randomnum
   Recheck Cond: (t_brin.id = 102345)
   Rows Removed by Index Recheck: 28927
   Heap Blocks: lossy=128
   ->  Bitmap Index Scan on idx_t_brin_id  (cost=0.00..12.03 rows=28902 width=0) (actual time=0.137..0.137 rows=1280 loops=1)
         Index Cond: (t_brin.id = 102345)
 Planning Time: 0.344 ms
 Execution Time: 7.666 ms
(9 rows)

等值查詢,PG選擇Btree索引,cost為8.45,執行時間0.266 ms;
刪除Btree索引,使用BRIN索引,cost為41657.45,執行時間為7.666 ms,是Btree的28倍.雖然慢了1個數量級,但絕對時間並不長,耗費的空間卻少了3個數量級.

在隨機值列上執行查詢:

testdb=# explain verbose select * from t_brin where randomnum = 102345;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Gather  (cost=1000.00..97331.41 rows=2 width=8)
   Output: id, randomnum
   Workers Planned: 2
   ->  Parallel Seq Scan on public.t_brin  (cost=0.00..96331.21 rows=1 width=8)
         Output: id, randomnum
         Filter: (t_brin.randomnum = 102345)
(6 rows)
testdb=# set enable_seqscan=off;
SET
testdb=# explain verbose select * from t_brin where randomnum = 102345;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Gather  (cost=1023.07..97354.49 rows=2 width=8)
   Output: id, randomnum
   Workers Planned: 2
   ->  Parallel Bitmap Heap Scan on public.t_brin  (cost=23.07..96354.29 rows=1 width=8)
         Output: id, randomnum
         Recheck Cond: (t_brin.randomnum = 102345)
         ->  Bitmap Index Scan on idx_t_brin_randomnum  (cost=0.00..23.07 rows=9999977 width=0)
               Index Cond: (t_brin.randomnum = 102345)
(8 rows)

PG不會使用該列上的BRIN索引而是選擇了全表掃描,禁用順序掃描後,使用brin索引,成本與全表掃描相差無幾,說明在隨機值列上的BRIN索引基本沒有效果.

testdb=# select attname, correlation from pg_stats where tablename='t_brin' order by correlation desc nulls last;
  attname  | correlation  
-----------+--------------
 id        |            1
 randomnum | 0.0016428155
(2 rows)

查詢統計資訊,id列相關性是1,而隨機值列相關性是0.0016428155,相差巨大.

參考資料
Indexes in PostgreSQL — 9 (BRIN)