BRIN Index

Availability

This feature is available since MogDB 3.0.0.

Introduction

MogDB 3.0.0 adds BRIN index feature.

A BRIN index is a block range index. Unlike other indexes, the BRIN index allows you to quickly exclude rows that do not satisfy the query criteria.

Benefits

• Sequential scans will be fast, then statistical SQL performance against large tables will be dramatically improved.
• Creating indexes is very fast.
• Indexes take up very little space.

Description

Block Range INdex is short for BRIN index. Unlike other indexes, the idea of a BRIN index is to quickly exclude rows that do not meet the query criteria, rather than quickly find matching rows.

The way a BRIN index works: The block of a table is divided into some intervals, and the index stores summary information (usually min and max information, and some other information for spatial data) for each interval. If the value of the column to be queried does not fall into the summary information of this interval, then the interval can be skipped. Otherwise, all rows of this interval need to be scanned.

The BRIN index is suitable for columns where the data is correlated with the physical location. The optimizer uses this value to make a choice between an index scan and a bitmap scan. We can also use it to estimate how well the BRIN index fits. The closer the column position correlation is to 1, the more correlated the column data is and the more suitable for building a BRIN index. Also BRIN indexes are mainly designed for large tables. Compared with indexes like btree, BRIN indexes have less data volume.

Scenarios

Create a BRIN Index

The syntax is the same as that for creating B-trees, hash, GiST, SP-GiST, and GINs.

The parameters pages_per_range and autosummarize can be specified when creating a BRIN index.

pages_per_range: specifies how many pages are contained in each range in the BRIN index. The range is 1 ~ 131072. if it is not specified, the default value is 128.

autosummarize: specifies whether to automatically create indexes for unindexed data pages in the table. The default value is off.

CREATE INDEX brinidx ON tbbrin USING brin(i1,i2,i3) WITH (pages_per_range=64, autosummarize=off);
--Online Create
CREATE INDEX CONCURRENTLY brinidx ON tbbrin USING brin(i1,i2,i3) WITH (pages_per_range=64);

Recreate a BRIN Index

The syntax is the same as that for recreating B-tree, hash, GiST, SP-GiST, and GINs.

REINDEX INDEX brinidx ;
--Online reindex
REINDEX INDEX CONCURRENTLY brinidx ;

Alter a BRIN Index

BRIN supports modifying the pages_per_range and autosummarize parameters. After the alter command is executed, only the metadata is updated, and the parameters set by the reindex command need to be executed to take effect.

Example:

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

View the Execution Plan of the BRIN Index

Example:

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) 

Manually Update a BRIN Index

It may happen that some data pages do not appear in the index during the use of the BRIN index. You can update the BRIN index manually by the following two ways.

• Perform a vacuum operation on a table.

• Execute the brin_summarize_new_values(oid) function. The input is the ID of the BRIN index. If the return value is 0, then the index is not updated. If it returns 1, then the index has been updated.

Example:

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

The index can be checked for updates by the following actions.

• View the total block size of the table from pg_class.

• View the page numbers of the indexes that have been created based on brin_revmap_data.

• Calculate if the difference between the previous 2 items is greater than pages_per_range. If it is, the index needs to be updated.

autosummarize for BRIN index

autosummarize is a switch for whether to automatically create indexes for data pages in the table that do not appear in the index.

You can test if autosummarize is invalid by following these steps.

1. Update the table data.

2. Query the table for updating the last_autovacuum field via pg_stat_user_table, e.g.

   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)           | 

   The following two commands can be executed to speed up the autovacuum frequency of the table.

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

3. After the pg_stat_user_table table is updated, you can observe the automatic update of index data.

   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)

View BRIN Index Via pageinspect

For the BRIN index of the table, you can query the page data by pageinspect.

The pageinspect module provides functions that allow viewing the contents of database data pages or index pages, which can be useful for debugging or locating problems. pageinspect tool is installed in the same way, by executing the following command during installation.

create extension pageinspect;

pageinspect only supports non-partitioned tables for now.

The BRIN index provides three functions for querying data in the meta page, revmap_page and regular page respectively.

• brin_metapage_info(page bytea) returns record

  Returns information about the classification of the BRIN index metapage, where the second parameter is fixed to 0. e.g.

  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

  Returns the list of tuple identifiers in the BRIN index range mapping page. e.g.

  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

  Returns the data stored in the BRIN data page. e.g.

  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}

Examples

# Create a test table
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
# Insert test data
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
# If you look at the execution plan of a query statement without creating an index, you can see that the execution plan uses 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)
# Create a brin index on the table
MogDB=# create index testtab_date_brin_idx on testtab using brin (date);
CREATE INDEX
# Looking at the index information of the brin index, you can see that the size of the brin index is about 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)
# Looking at the execution plan of the same query statement, you can see that the query uses the brin index
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)
# Create a btree index on the date column of the table
MogDB=# create index testtab_date_idx  on testtab(date);
CREATE INDEX
# Looking at the size of the btree index, we can see that the size of the btree index is about 172 MB, much larger than the 64 KB of the brin index
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)

Related Pages

CREATE INDEX, DROP INDEX, ALTER INDEX, VACUUM