BRIN index (Block Range Index) is the physical format of the newly added index. Traditional BTREE index saves the location of accurate tuples for a column value. While this method is fast for the search by index, there was a disadvantage that the capacity of the index itself increased. BRIN index saves the maximum value and the minimum value of the column values in the block together. This achieved both high query speed and reduction of storage capacity. In particular, it can be expected to be effective as an index to the table that contains a large number of tuples.
Figure 3Structure of BRIN INDEX
PDF page 65
In the BRIN index, pages_per_range parameter as can be specified the additional option. In this parameter, the number of pages used by the index of the entry is specified. The default value is 128. The minimum value of this option is 1, and the maximum is 131,072.
Syntax
To create BRIN index, specify USING BRIN clause in the CREATE INDEX statement. Syntax CREATE INDEX index_name ON table_name USING BRIN (column_name, …) [ WITH (pages_per_range = value) ]
3.6.2 Examples
The following are examples of creating a BRIN index. First create the table and store the data.
PDF page 66
Example 62Create the table and store the data
postgres=> CREATE TABLE brin1 (c1 NUMERIC, c2 NUMERIC, c3 NUMERIC, c4 NUMERIC) ;
CREATE TABLE
postgres=> INSERT INTO brin1 VALUES (
postgres(> generate_series(1, 10000000),
postgres(> generate_series(1, 10000000),
postgres(> generate_series(1, 10000000),
postgres(> generate_series(1, 10000000)
postgres(> ) ;
INSERT 0 10000000
Next, create an index for each column. BTREE index is created for the c1 column as a comparison.
From c2 column to c4 column, three BRIN index with different parameter pages_per_range value are
created.
PDF page 67
Example 63Create index and check
postgres=> CREATE INDEX btree1 ON brin1 (c1) ;
CREATE INDEX
postgres=> CREATE INDEX brin1_def ON brin1 USING BRIN (c2) ;
CREATE INDEX
postgres=> CREATE INDEX brin1_64 ON brin1 USING BRIN (c3)
WITH (pages_per_range = 64) ;
CREATE INDEX
postgres=> CREATE INDEX brin1_512 ON brin1 USING BRIN (c4)
WITH (pages_per_range = 512) ;
CREATE INDEX
postgres=> ANALYZE VERBOSE brin1 ;
INFO: analyzing "public.brin1"
INFO: "brin1": scanned 30000 of 73520 pages, containing 4080483 live rows and 0 dead rows;
30000 rows in sample, 9999961 estimated total rows
ANALYZE
postgres=> \d+ brin1
Table "public.brin1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
c1 | numeric | | main | |
c2 | numeric | | main | |
c3 | numeric | | main | |
c4 | numeric | | main | |
Indexes:
"brin1_512" brin (c4) WITH (pages_per_range=512)
"brin1_64" brin (c3) WITH (pages_per_range=64)
"brin1_def" brin (c2)
"btree1" btree (c1)
Finally, checking the usage of storage area. In the case of the example below, BRIN index is found
to be constructed with a very small area.
PDF page 68
Example 64Check the size of files (storage)
postgres=> SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM
pg_class WHERE relname LIKE 'brin1%' OR relname = 'btree1' ;
relname | pg_size_pretty
-----------+----------------
brin1 | 574 MB
btree1 | 214 MB
brin1_def | 32 kB
brin1_64 | 48 kB
brin1_512 | 24 kB
(5 rows)
Now I validate the performance. Run the same SQL statements, which have the difference in their
column name, multiple times to verify the results of using the index.
Example 65Execution plan for BTREE index
postgres=> EXPLAIN ANALYZE SELECT * FROM brin1 WHERE c1 = 5000000 ;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using btree1 on brin1 (cost=0.43..8.45 rows=1 width=24)
(actual time=0.085..0.086 rows=1 loops=1)
Index Cond: (c1 = '5000000'::numeric)
Planning time: 0.389 ms
Execution time: 0.240 ms
(4 rows)
The following are examples of using the BRIN index.
PDF page 69
Example 66Execution plan for BRIN index (default parameter value)
postgres=> EXPLAIN ANALYZE SELECT * FROM brin1 WHERE c2 = 5000000 ;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on brin1 (cost=16.01..20.02 rows=1 width=24)
(actual time=1.955..9.026 rows=1 loops=1)
Recheck Cond: (c2 = '5000000'::numeric)
Rows Removed by Index Recheck: 17407
Heap Blocks: lossy=128
-> Bitmap Index Scan on brin1_def (cost=0.00..16.01 rows=1 width=0)
(actual time=0.747..0.747 rows=1280 loops=1)
Index Cond: (c2 = '5000000'::numeric)
Planning time: 0.144 ms
Execution time: 9.222 ms
(8 rows)
Example 67Execution plan for BRIN index (pages_per_range=64)
postgres=> EXPLAIN ANALYZE SELECT * FROM brin1 WHERE c3 = 5000000 ;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on brin1 (cost=24.01..28.02 rows=1 width=24)
(actual time=2.542..5.310 rows=1 loops=1)
Recheck Cond: (c3 = '5000000'::numeric)
Rows Removed by Index Recheck: 8703
Heap Blocks: lossy=64
-> Bitmap Index Scan on brin1_64 (cost=0.00..24.01 rows=1 width=0)
(actual time=1.420..1.420 rows=640 loops=1)
Index Cond: (c3 = '5000000'::numeric)
Planning time: 0.140 ms
Execution time: 5.360 ms
(8 rows)
PDF page 70
Example 68Execution plan for BRIN index (pages_per_range=512)
postgres=> EXPLAIN ANALYZE SELECT * FROM brin1 WHERE c4 = 5000000 ;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on brin1 (cost=12.01..16.02 rows=1 width=24)
(actual time=29.661..36.501 rows=1 loops=1)
Recheck Cond: (c4 = '5000000'::numeric)
Rows Removed by Index Recheck: 69631
Heap Blocks: lossy=512
-> Bitmap Index Scan on brin1_512 (cost=0.00..12.01 rows=1 width=0)
(actual time=0.258..0.258 rows=5120 loops=1)
Index Cond: (c4 = '5000000'::numeric)
Planning time: 0.146 ms
Execution time: 36.561 ms
(8 rows)
3.6.3 Information confirming
To the Contrib module pageinspect, following functions have been added to retrieve information of BRIN index.
Function name
Description
brin_page_type
Get the page type of index page
brin_metapage_info
Get a variety of information in the index metadata
brin_revmap_data
Get a list of tuples from the map page
brin_page_items
Get the stored data from the data page
Table 31Functions that have been added to the pageinspect module
PDF page 71
Example 69Example of brin_page_items function
postgres=# CREATE EXTENSION pageinspect ;
CREATE EXTENSION
postgres=# SELECT itemoffset, blknum, value
FROM brin_page_items(get_raw_page('brin1_def', 2), 'brin1_def') LIMIT 10 ;
itemoffset | blknum | value
------------+--------+--------------------
1 | 0 | {1 .. 18745}
2 | 128 | {18746 .. 36153}
3 | 256 | {36154 .. 53561}
4 | 384 | {53562 .. 70969}
5 | 512 | {70970 .. 88377}
6 | 640 | {88378 .. 105785}
7 | 768 | {105786 .. 123193}
8 | 896 | {123194 .. 140601}
9 | 1024 | {140602 .. 158009}
10 | 1152 | {158010 .. 175417}
(10 rows)
Option information in the BRIN index can also be found from reloptions column of pg_class catalog.
Example 70Obtain of options information
postgres=> SELECT relname,reloptions FROM pg_class where relname like 'brin1%' ;
relname | reloptions
-----------+-----------------------
brin1 |
brin1_512 | {pages_per_range=512}
brin1_64 | {pages_per_range=64}
brin1_def |
(4 rows)
