postgressql-9.5新特性BRIN (block range index)
PostgreSQL 9.5引入的一個全新的索引訪問方法BRIN（block range index），這個索引存儲了表的連續數據塊區間以及對應的數據取值范圍。
比如一張表有1000個數據塊，我們建議一個BRIN在ID（假設這個表有ID字段）上的索引。
BRIN默認是每128個連續數據塊區間存儲一個字段取值的區間，所以這個索引的信息量是將1000個數據塊劃分為幾個連續的128個塊的區間，然后存儲每個區間ID值的取值范圍。
那么我們并不能直接從索引中精確匹配要查詢的記錄，但是通過索引我們可以將查詢范圍縮小到最小128個連續的數據塊（假設我們要找的值落在這個區間）。
以上是BRIN大概的原理，那么BRIN可以用在什么場景呢？
一個非常好的場景是流式日志數據，比如用戶行為，大批量的數據按時間順序不停的插入數據表。
我們如果要按照時間來訪問這樣的數據，以往我們需要創建BTREE索引，可以范圍查詢或者精確匹配。但是BTREE索引需要存儲的信息量較大，如果數據量很大，索引也很龐大。
BRIN的話，索引可以變得很小，而且因為數據是按照時間順序插入的，所以BRIN的信息量也很大，因為每個連續的數據塊區間存儲的時間范圍和其他連續的數據塊區間獨立性很好，即不會出現大量數據交叉，如果有大量較差，那么使用BRIN檢索還不如全表掃描。BRIN可認為是全表掃描的切片，如果數據值分布和物理值分布的相關性很好，那么BRIN無疑是非常好的選擇。
1、做好兩張實驗表，每張表有1000萬數據量。第一張表ID字段大小順序和物理存儲順序相關性是1，第二張表ID字段大小順序和物理存儲順序幾乎沒有物理相關性。參考：http://www.postgresql.org/docs/9.5/static/view-pg-stats.html
postgres=# create table a(id int, msg text); CREATE TABLE postgres=# create table b(id int, msg text); CREATE TABLE postgres=# insert into a select generate_series(1, 10000000), md5(random()::text); INSERT 0 10000000 postgres=# insert into b select id, md5(random()::text) from generate_series(1, 10000000) as t(id) order by random(); INSERT 0 10000000 postgres=# analyze a; ANALYZE postgres=# analyze b; ANALYZE postgres=# select correlation from pg_stats where tablename = 'a' and attname = 'id'; correlation ------------- 1 (1 row) postgres=# select correlation from pg_stats where tablename = 'b' and attname = 'id'; correlation ------------- 0.00601343 (1 row) postgres=# create index idx_a_id on a using brin(id); CREATE INDEX postgres=# create index idx_b_id on b using brin(id); CREATE INDEX
2、實驗兩張表的訪問效率。說明brin索引適合字段大小順序和物理存儲順序相關性大的表。
postgres=# explain analyze select * from a where id >1000 and id <5000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on a (cost=62.53..14756.81 rows=4540 width=37) (actual time=0.662..2.952 rows=3999 loops=1) Recheck Cond: ((id > 1000) AND (id < 5000)) Rows Removed by Index Recheck: 11361 Heap Blocks: lossy=128 -> Bitmap Index Scan on idx_a_id (cost=0.00..61.40 rows=4540 width=0) (actual time=0.199..0.199 rows=1280 loops=1) Index Cond: ((id > 1000) AND (id < 5000)) Planning time: 0.050 ms Execution time: 3.581 ms (8 rows) postgres=# explain analyze select * from b where id >1000 and id <5000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- - Bitmap Heap Scan on b (cost=51.12..11509.18 rows=3426 width=37) (actual time=5.384..1147.752 rows=3999 loops=1) Recheck Cond: ((id > 1000) AND (id < 5000)) Rows Removed by Index Recheck: 9965281 Heap Blocks: lossy=83078 -> Bitmap Index Scan on idx_b_id (cost=0.00..50.26 rows=3426 width=0) (actual time=5.174..5.174 rows=832000 loops=1) Index Cond: ((id > 1000) AND (id < 5000)) Planning time: 0.044 ms Execution time: 1148.616 ms (8 rows)
3、實驗順序掃描和brin掃描，說明字段大小順序和物理存儲順序相關性小的表，不如順序掃描快。
postgres=# explain analyze select * from b where id >1000 and id <5000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- - Bitmap Heap Scan on b (cost=51.12..11509.18 rows=3426 width=37) (actual time=5.384..1147.752 rows=3999 loops=1) Recheck Cond: ((id > 1000) AND (id < 5000)) Rows Removed by Index Recheck: 9965281 Heap Blocks: lossy=83078 -> Bitmap Index Scan on idx_b_id (cost=0.00..50.26 rows=3426 width=0) (actual time=5.174..5.174 rows=832000 loops=1) Index Cond: ((id > 1000) AND (id < 5000)) Planning time: 0.044 ms Execution time: 1148.616 ms (8 rows) postgres=# set enable_bitmapscan = off; SET postgres=# explain analyze select * from b where id >1000 and id <5000; QUERY PLAN --------------------------------------------------------------------------------------------------------- Seq Scan on b (cost=0.00..233334.00 rows=3426 width=37) (actual time=0.196..961.569 rows=3999 loops=1) Filter: ((id > 1000) AND (id < 5000)) Rows Removed by Filter: 9996001 Planning time: 0.058 ms Execution time: 962.425 ms (5 rows)
4，比較btree索引和brin索引。brin索引不如btree索引效率高。優點是brin索引空間占用小。
postgres=# \d a Table "public.a" Column | Type | Modifiers --------+---------+----------- id | integer | msg | text | Indexes: "idx_a_id" brin (id) postgres=# explain analyze select * from a where id >1000 and id <5000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on a (cost=62.53..14756.81 rows=4540 width=37) (actual time=0.662..2.952 rows=3999 loops=1) Recheck Cond: ((id > 1000) AND (id < 5000)) Rows Removed by Index Recheck: 11361 Heap Blocks: lossy=128 -> Bitmap Index Scan on idx_a_id (cost=0.00..61.40 rows=4540 width=0) (actual time=0.199..0.199 rows=1280 loops=1) Index Cond: ((id > 1000) AND (id < 5000)) Planning time: 0.050 ms Execution time: 3.581 ms (8 rows) postgres=# drop index idx_a_id ; DROP INDEX postgres=# create index idx_a_id on a using btree(id); CREATE INDEX postgres=# explain analyze select * from a where id >1000 and id <5000; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Index Scan using idx_a_id on a (cost=0.43..186.45 rows=4601 width=37) (actual time=0.014..1.190 rows=3999 loops=1) Index Cond: ((id > 1000) AND (id < 5000)) Planning time: 0.174 ms Execution time: 1.742 ms (4 rows) postgres=# \di+ idx_a_id_b* List of relations Schema | Name | Type | Owner | Table | Size | Description --------+----------------+-------+----------+-------+--------+------------- public | idx_a_id_brin | index | postgres | a | 56 kB | public | idx_a_id_btree | index | postgres | a | 214 MB | (2 rows)
5、結論，brin索引適合字段大小順序和物理存儲順序相關性大的表，如流水表的時間字段，日志表的時間字段，自增長字段。
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！