PostgreSQL BRIN索引详解

pg9.5增加的brin索引，存储了被索引字段在块级别的边界值（最大值、最小值）以及其他统计信息。
BRIN索引的扫描原理很简单，扫描BRIN的元数据，根据元数据和用户输入的条件进行比较，过滤不符合条件的HEAP PAGE，只扫描需要扫描的HEAP PAGE。
所以brin索引仅仅适合存储与值线性相关性很好的列。

例子：
我们创建两张表，一张表插入线性相关很好的数据，另一张表插入离散的数据来测试brin索引的性能。

–建表插入数据：

bill=# create table t_brin1(id int);
CREATE TABLE
bill=# create table t_brin2(id int); 
CREATE TABLE
bill=# insert into t_brin1 select random()*1000000 from generate_series(1,10000000);
INSERT 0 10000000
bill=# insert into t_brin2 select generate_series(1,10000000);
INSERT 0 10000000

–在两张表上都创建brin索引

bill=# create index idx_t_brin1 on t_brin1 using brin(id);
CREATE INDEX
bill=# create index idx_t_brin2 on t_brin2 using brin(id);  
CREATE INDEX

–通过pg_stats表查看两张表索引列的线性相关性
可以发现t_brin1表中数据比较离散，而t_brin2中数据线性相关性很好。

bill=# select correlation from pg_stats where tablename='t_brin1';
  correlation  
---------------
 -0.0061385944
(1 row)

bill=# select correlation from pg_stats where tablename='t_brin2';
 correlation 
-------------
           1
(1 row)

–测试性能

t_brin1表：
bill=# explain (analyze ,verbose,timing,costs,buffers) select * from t_brin1 where id between 1 and 100000;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on bill.t_brin1  (cost=262.18..194509.84 rows