以Postgresql为主,再聊聊资料库 PostgreSQL BRIN index 介绍一

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 it0923aselect n     , round(random() * 1000)     , timestamp '2000-01-01 00:00:00' + (n || ' second')::interval  from generate_series(1, 1e7) as n;  没对 ts 建立 index 的情况下,做範围查询.explain analyzeselect *  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 建立 indexcreate index it0923a_btree on it0923a(ts);Time: 9549.938 ms (00:09.550)analyze it0923a;explain analyzeselect *  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 的 sizeselect pg_size_pretty(pg_relation_size('it0923a_btree')) as btrSiz;+--------+| btrsiz |+--------+| 214 MB |+--------+接着将 btree index 删除,建立 brin indexdrop index it0923a_btree;create index it0923a_btin on it0923a using brin(ts);Time: 2470.548 ms (00:02.471)建立时间比较短.analyze it0923a;explain analyzeselect *  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 的 sizeselect pg_size_pretty(pg_relation_size('it0923a_btin')) as brinSiz;+---------+| brinsiz |+---------+| 32 kB   |+---------+嗯,刚才打错字了....brin 打成 btin 了. 但是 index size 惊人的小! 32K与 btree index 214MB 的 size 相比. 十分的精实.初步的介绍,在此告一段落.