官术网_书友最值得收藏!

BRIN indexes

Block range indexes (BRIN) are of great practical use. All indexes discussed until now need quite a lot of disk space. Although a lot of work has gone into shrinking GIN indexes and the like, they still need quite a lot because an index pointer is needed for each entry. So if there are 10 million entries, there will be 10 million index pointers. Space is the main concern addressed by BRIN indexes. A BRIN index does not keep an index entry for each tuple but will store the minimum and the maximum value of 128 (default) blocks of data (1 MB). The index is therefore very small but lossy. Scanning the index will return more data than we asked for. PostgreSQL has to filter out those additional rows in a later step.

The following example demonstrates how small a BRIN index really is:

test=# CREATE INDEX idx_brin ON t_test USING brin(id); 
CREATE INDEX
test=# \di+ idx_brin
List of relations
Schema | Name | Type | Owner | Table | Size
--------+----------+-------+-------+--------+-------+-------------
public | idx_brin | index | hs | t_test | 48 KB
(1 row)

In my example, the BRIN index is 2,000 times smaller than a standard B-tree. The question naturally arising now is: why don't we always use BRIN indexes? To answer this kind of question, it is important to reflect on the layout of BRIN; the minimum and maximum value for 1 MB are stored. If the data is sorted (high correlation), BRIN is pretty efficient because we can fetch 1 MB of data, scan it, and we are done. However, what if data is shuffled? In this case, BRIN won't be able to exclude chunks of data anymore because it is very likely that something close to the overall high and the overall low is within 1 MB of data. Therefore, BRIN is mostly made for highly correlated data. In reality, correlated data is quite likely in data warehousing applications. Often data is loaded every day and therefore dates can be highly correlated.

主站蜘蛛池模板: 芒康县| 象山县| 喀喇| 呼伦贝尔市| 三穗县| 扎囊县| 广平县| 黄浦区| 台南县| 商河县| 乌鲁木齐市| 江北区| 铁力市| 潢川县| 灵川县| 陆丰市| 大余县| 嘉祥县| 金川县| 剑河县| 永宁县| 咸阳市| 梁平县| 陈巴尔虎旗| 固阳县| 社会| 若羌县| 白银市| 濮阳市| 科技| 江永县| 白城市| 绥棱县| 黄龙县| 翼城县| 玛曲县| 三河市| 大港区| 滦南县| 沂源县| 剑川县|