v5.0

BRIN索引

可获得性

本特性自MogDB 3.0.0版本开始引入。

特性简介

MogDB 3.0.0版本新增BRIN索引特性。

BRIN索引即block range index块范围索引。和其他索引不同，BRIN索引可以实现快速排除不满足查询条件的行。

客户价值

顺序扫描会很快，则针对大表的统计型SQL性能会大幅提升。
创建索引的速度非常快。
索引占用的空间很小。

特性描述

BRIN索引即block range index块范围索引。和其他索引不同，BRIN索引的思想是快速排除不满足查询条件的行，而不是快速找到匹配的行。

BRIN索引的工作方式：表的块被划分成一些区间，索引存储了每个区间内的摘要信息（一般是min和max信息，对于空间数据还有一些其他信息）。如果要查询的列的值，不会落入这个区间的摘要信息中，那么就可以跳过这个区间，如果不能跳过，就需要扫描这个区间的所有行。

BRIN索引适合数据与物理位置有相关性的列。优化器使用这个值来从索引扫描和位图扫描中做选择。我们也可以使用它估计BRIN索引的适合程度。列的位置相关性越接近1，则列数据的相关性越强，越适合建BRIN索引。另外BRIN索引主要是为大表而设计的，相比于btree等索引，BRIN索引的数据量较小。

特性约束

B兼容模式不支持BRIN索引。

使用场景

创建BRIN索引

语法与创建B-树、哈希、GiST、SP-GiST、GIN的方式一致。

创建BRIN索引时可以指定参数pages_per_range 和 autosummarize。

pages_per_range：指定BRIN索引中每个range中包含多少个page，取值范围是 1 ~ 131072，如果不指定默认值是128。

autosummarize：指定是否对表中未建索引的数据页自动创建索引，默认值是 off。

CREATE INDEX brinidx ON tbbrin USING brin(i1,i2,i3) WITH (pages_per_range=64, autosummarize=off);
--在线创建
CREATE INDEX CONCURRENTLY brinidx ON tbbrin USING brin(i1,i2,i3) WITH (pages_per_range=64);

重建BRIN索引

语法和重建创建B-树、哈希、GiST、SP-GiST、GIN的方式一致。

REINDEX INDEX brinidx ;
--在线重建索引
REINDEX INDEX CONCURRENTLY brinidx ;

修改BRIN索引

BRIN支持修改pages_per_range和autosummarize参数，alter命令执行后，只是更新了元数据，需要执行reindex命令设置的参数才会生效。

示例：

alter index idx set(pages_per_range=64);
reindex index idx;

查看BRIN索引的执行计划

示例：

MogDB=# explain select * from example where id = 100;
             QUERY PLAN
---------------------------------------------------------
 Bitmap Heap Scan on example (cost=15.88..486.21 rows=500 width=4)
  Recheck Cond:(id = 100)
  -> Bitmap Index Scan on idx (cost=0.00..15.75 rows=500 width=0)
      Index Cond:(id =100)
(4 rows)

BRIN索引手动更新

BRIN索引使用过程中可能出现部分数据页没有出现在索引中的情况，可以通过以下两种方式来手动更新BRIN索引。

对表执行vaccum操作；
执行brin_summarize_new_values(oid)函数。入参为BRIN索引的oid，如果返回值为0则没有更新索引，返回1则索引已被更新。

示例：

SELECT brin_summarize_new_values((select oid from pg_class where relname='brinidx')::oid);

可以通过以下操作来检查索引是否更新：

从pg_class查看表的总块大小；
根据brin_revmap_data查看已经建立索引的页号；
计算前面2项的差值是否大于pages_per_range，若大于则说明索引需要更新。

BRIN索引的autosummarize

autosummarize 是否对表中未出现在索引中的数据页自动创建索引的开关。

可以通过以下步骤测试 autosummarize 是否失效：

更新表数据；

通过pg_stat_user_table查询表的last_autovacuum字段更新，例如：

MogDB=# select relname,last_vacuum,last_autovacuum from pg_stat_user_tables where relname = 'person';
 relname |          last_vacuum          | last_autovacuum 
---------+-------------------------------+-----------------
 person  | 2022-06-20 19:21:58.201214+08 | 
(1 row)           |

可以执行以下两条命令，来加快表的autovacuum频率：

ALTER TABLE example SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE example SET (autovacuum_vacuum_threshold = 100);

pg_stat_user_table表更新后，可以观察到索引数据的自动更新。

MogDB=# select relname,last_vacuum,last_autovacuum from pg_stat_user_tables where relname = 'person';
 relname |          last_vacuum          | last_autovacuum 
---------+-------------------------------+-----------------
 person  | 2022-06-20 19:23:58.201214+08 | 2022-06-20 19:24:59.201214+08
(1 row)

通过pageinspect查看BRIN索引

对于表的BRIN索引，可以通过pageinspect查询页面数据。

pageinspect模块提供了允许查看数据库数据页面或索引页面内容的函数，这功能调试或问题定位很有帮助。pageinspect工具安装方式不变，安装时执行以下命令：

create extension pageinspect;

pageinspect暂时只支持非分区表。

BRIN索引提供三个函数分别用于查询meta page、revmap_page和regular page中的数据：

brin_metapage_info(page bytea) returns record

返回有关BRIN索引元页的分类信息，其中第二个参数固定为0。例如：

MogDB=# SELECT * FROM brin_metapage_info(get_raw_page('brinidx', 0));
   magic    | version | pagesperrange | lastrevmappage 
------------+---------+---------------+----------------
 0xA8109CFA |       1 |             4 |              2

brin_revmap_data(page bytea) returns setof tid

返回BRIN索引范围映射页面中的元组标识符列表。例如：

MogDB=# SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 2)) limit 5;
  pages  
---------
 (6,137)
 (6,138)
 (6,139)
 (6,140)
 (6,141)

brin_page_items(page bytea, index oid) returns setof record

返回存储在BRIN数据页面中的数据。例如：

MogDB=# SELECT * FROM brin_page_items(get_raw_page('brinidx', 5),
                                     'brinidx')
       ORDER BY blknum, attnum LIMIT 6;
 itemoffset | blknum | attnum | allnulls | hasnulls | placeholder |    value     
------------+--------+--------+----------+----------+-------------+--------------
        137 |      0 |      1 | t        | f        | f           | 
        137 |      0 |      2 | f        | f        | f           | {1 .. 88}
        138 |      4 |      1 | t        | f        | f           | 
        138 |      4 |      2 | f        | f        | f           | {89 .. 176}
        139 |      8 |      1 | t        | f        | f           | 
        139 |      8 |      2 | f        | f        | f           | {177 .. 264}

使用示例

#创建一张测试表
MogDB=# CREATE TABLE testtab (id int NOT NULL PRIMARY KEY,date TIMESTAMP NOT NULL, level INTEGER, msg TEXT);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "testtab_pkey" for table "testtab"
CREATE TABLE
#插入测试数据
MogDB=# INSERT INTO testtab (id, date, level, msg) SELECT g, CURRENT_TIMESTAMP + ( g || 'minute' ) :: interval, random() * 6, md5(g::text) FROM generate_series(1,8000000) as g;
INSERT 0 8000000
#不创建索引的情况下查看一条查询语句的执行计划，可以看到执行计划用的是 seq scan
MogDB=# explain analyze select * from public.testtab where date between '2019-08-08 14:40:47.974791' and '2019-08-08 14:50:47.974791';
                                                                         QUERY PLAN                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on testtab  (cost=0.00..212024.20 rows=43183 width=48) (actual time=46620.314..46620.314 rows=0 loops=1)
   Filter: (("date" >= '2019-08-08 14:40:47.974791'::timestamp without time zone) AND ("date" <= '2019-08-08 14:50:47.974791'::timestamp without time zone))
   Rows Removed by Filter: 8000000
 Total runtime: 46620.580 ms
(4 rows)
#在表上创建 brin 索引
MogDB=# create index testtab_date_brin_idx on testtab using brin (date);
CREATE INDEX
#查看 brin 索引的索引信息，可以看到 brin 索引的大小大约为 64 kB
MogDB=# \di+ testtab_date_brin_idx 
                                     List of relations
 Schema |          Name          | Type  | Owner  |  Table  | Size  | Storage | Description 
--------+------------------------+-------+--------+---------+-------+---------+-------------
 public | testtab_date_brin_idx  | index | wusong | testtab | 64 kB |         | 
(1 row)
#查看相同查询语句的执行计划，可以看到查询使用 brin 索引
MogDB=# explain analyze select * from public.testtab where date between '2019-08-08 14:40:47.974791' and '2019-08-08 14:50:47.974791';
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on testtab  (cost=20.00..24.02 rows=1 width=49) (actual time=1.121..1.121 rows=0 loops=1)
   Recheck Cond: (("date" >= '2019-08-08 14:40:47.974791'::timestamp without time zone) AND ("date" <= '2019-08-08 14:50:47.974791'::timestamp without time zone))
   ->  Bitmap Index Scan on "testtab_date_brin_idx "  (cost=0.00..20.00 rows=1 width=0) (actual time=1.119..1.119 rows=0 loops=1)
         Index Cond: (("date" >= '2019-08-08 14:40:47.974791'::timestamp without time zone) AND ("date" <= '2019-08-08 14:50:47.974791'::timestamp without time zone))
 Total runtime: 1.281 ms
(5 rows)
#在表的 date 列上创建 btree 索引
MogDB=# create index testtab_date_idx  on testtab(date);
CREATE INDEX
#查看 btree 索引的大小，可以看到 btree 索引大小约为 172 MB，远大于 brin 索引的 64 KB
MogDB=# \di+ testtab_date_idx 
                                   List of relations
 Schema |       Name        | Type  | Owner  |  Table  |  Size  | Storage | Description 
--------+-------------------+-------+--------+---------+--------+---------+-------------
 public | testtab_date_idx  | index | wusong | testtab | 172 MB |         | 
(1 row)