BRIN (Block Range INdex) , 從 PostgreSQL 9.5版開始.
從這篇2013年討論開始研究發展.
https://www.postgresql.org/message-id/20130614222805.GZ5491@eldon.alvh.no-ip.org

在處理資料時,會有可排序線性的形態,也就是說可以使用 {min, max} 這樣的方式來表示一個範圍.
隨著資料庫資訊的普及,以及各資料庫產品的發展,也有不少人在使用如 Partitioning Table 的技術.
但是 Partitioning Table 也有其限制,在此我不花時間去討論.
我們先來看 BRIN 與 一般常用 Btree 的比較.

建立一個典型的時序資料

create table it0923a (
  id int not null primary key
, num int not null
, ts timestamp not null
);

輸入一千萬筆測試資料

insert into it0923a
select n
     , round(random() * 1000)
     , timestamp '2000-01-01 00:00:00' + (n || ' second')::interval
  from generate_series(1, 1e7) as n;
  
沒對 ts 建立 index 的情況下,做範圍查詢.

explain analyze
select *
  from it0923a
 where ts between timestamp '2000-02-01 00:00:00'
   and timestamp '2000-03-01 00:00:00';
+---------------------------------------------------------------------------------------------------------------------------------------+
|                                                              QUERY PLAN                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------+
| Seq Scan on it0923a  (cost=0.00..204057.62 rows=2488082 width=16) (actual time=449.734..1790.706 rows=2505601 loops=1)                |
|   Filter: ((ts >= '2000-02-01 00:00:00'::timestamp without time zone) AND (ts <= '2000-03-01 00:00:00'::timestamp without time zone)) |
|   Rows Removed by Filter: 7494399                                                                                                     |
| Planning Time: 7.967 ms                                                                                                               |
| Execution Time: 1974.434 ms                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------+
(5 rows)

對 ts 建立 index

create index it0923a_btree on it0923a(ts);

Time: 9549.938 ms (00:09.550)

analyze it0923a;

explain analyze
select *
  from it0923a
 where ts between timestamp '2000-02-01 00:00:00'
   and timestamp '2000-03-01 00:00:00';
   
+--------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                 QUERY PLAN                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Index Scan using it0923a_btree on it0923a  (cost=0.43..91922.05 rows=2526931 width=16) (actual time=40.380..1400.256 rows=2505601 loops=1) |
|   Index Cond: ((ts >= '2000-02-01 00:00:00'::timestamp without time zone) AND (ts <= '2000-03-01 00:00:00'::timestamp without time zone))  |
| Planning Time: 1.009 ms                                                                                                                    |
| Execution Time: 1653.522 ms                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------+

使用了 index , 但是速度是由 1974.434 ms  -> 1653.522 ms 

1653.522 / 1974.434 = 0.837

並沒有很顯著的效果.

看一下 btree index 的 size

select pg_size_pretty(pg_relation_size('it0923a_btree')) as btrSiz;
+--------+
| btrsiz |
+--------+
| 214 MB |
+--------+

接著將 btree index 刪除,建立 brin index

drop index it0923a_btree;

create index it0923a_btin on it0923a using brin(ts);

Time: 2470.548 ms (00:02.471)

建立時間比較短.

analyze it0923a;

explain analyze
select *
  from it0923a
 where ts between timestamp '2000-02-01 00:00:00'
   and timestamp '2000-03-01 00:00:00';
   
+-------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                   QUERY PLAN                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Bitmap Heap Scan on it0923a  (cost=663.60..93370.54 rows=2576457 width=16) (actual time=5.988..441.199 rows=2505601 loops=1)                    |
|   Recheck Cond: ((ts >= '2000-02-01 00:00:00'::timestamp without time zone) AND (ts <= '2000-03-01 00:00:00'::timestamp without time zone))     |
|   Rows Removed by Index Recheck: 4479                                                                                                           |
|   Heap Blocks: lossy=13568                                                                                                                      |
|   ->  Bitmap Index Scan on it0923a_btin  (cost=0.00..19.49 rows=2576796 width=0) (actual time=0.837..0.837 rows=135680 loops=1)                 |
|         Index Cond: ((ts >= '2000-02-01 00:00:00'::timestamp without time zone) AND (ts <= '2000-03-01 00:00:00'::timestamp without time zone)) |
| Planning Time: 0.428 ms                                                                                                                         |
| Execution Time: 615.363 ms                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------+

Execution Time: 615.363 ms  

對比 btree index 的 1653.522 ms 

select 615.363 / 1653.522 as "brin / btree"
     , 1653.522 / 615.363 as "btree / brin";

+------------------------+--------------------+
|      brin / btree      |    btree / brin    |
+------------------------+--------------------+
| 0.37215289545588144579 | 2.6870676332506179 |
+------------------------+--------------------+

接著來看 brin index 的 size


select pg_size_pretty(pg_relation_size('it0923a_btin')) as brinSiz;

+---------+
| brinsiz |
+---------+
| 32 kB   |
+---------+

嗯,剛才打錯字了....brin 打成 btin 了. 
但是 index size 驚人的小! 32K

與 btree index 214MB 的 size 相比. 十分的精實.

初步的介紹,在此告一段落.