可获得性
本特性自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)
