PostgreSQL 9.5引入了Block Range Index，简称BRIN，用于字段值和在表中的物理位置具有一定关联关系的大数据量访问。但是BRIN对于不同数据分布带来的性能提升有多少，和传统的BTREE索引比较性能又有什么差别，恐怕大家还没有一个直观的印象。本文将就这些问题尝试给出一些解答，希望可以帮助大家在选择BTREE或者BRIN时不再那么纠结，有据可依。

为了能够在一致的环境中评估BRIN和BTREE对数据访问的差异，使用了两张完全相同的表。它们的结构相同，数据量相同，字段上的数据分布也完全一致，唯一不同的就是一张表上为每个字段创建的都是BRIN类型索引，而另一张表上都是BTREE类型索引。

详细信息如下：

两张表t1和t2的数据是用以下数据来插入，已获得在各个字段上不同的数据分布：

1、表数据量是1000万；

2、id字段是顺序增长；

3、id1是100取模，id2是除以100然后取整；

4、id3是1000取模，id4是除以1000然后取整；

5、id5是30000取模，id6是除以30000然后取整；

从pageinspect来看，数据在两张表中的存储也是完全相同的，每个page存放136条记录。

可以看到，虽然结构、数据量和数据分布完全相同，但vacuum analyze之后统计信息由于取样的原因还是稍有不同，属于正常现象。不是100%精确。

但BRIN和BTREE索引在大小上却有天壤之别，BRIN相比BTREE索引在空间占用上小很多。

表的大小一样，都是574MB。但每个字段上BRIN索引只有32KB，而相应的BTREE索引却占据214MB空间，是BRIN索引的6848倍！这里不讲BRIN索引的存储方式，留待以后专题讨论。本文只关注两种索引类型大小的巨大差异和表中各字段不同的数据分布。

我们先来看一下不同数据分布下等值查询的情况。需要指出的是，除了id字段外，其他字段值不是唯一的，所以等值查询也会出现范围扫描。

1、首先是在id字段上的等值查询

id字段是顺序增加，所以是唯一的。由于是唯一扫描，BTREE的性能应该是好的。而BRIN因为其的Lossy特性，需要扫描更多的数据页。BRIN缺省是128个数据页面做为一个范围，所以即算是等值查询，最少也需要扫描128个heap页面。从实际性能数据上看，通过BRIN索引运行查询访问了143个buffer，而BTREE只有4个buffer，效率很高。BTREE的执行时间也比BRIN要少很多。因此，对于等值的唯一查询，BTREE在性能上有显著优势。

2、在id1上的等值查询。前面讲过，由于值不唯一，实际上是范围扫描。而且由于id1字段对100取模的关系，几乎每个数据页面都包含9这个值。也就是说，无论BRIN还是BTREE，都要访问几乎所有的数据块。我们来看一下性能的实际表现。

可以看到，确实每个heap的数据块都被访问到了。而且，由于BTREE结构比较大的原因，比BRIN在索引上还多扫描了270个数据块。本文中，也不打算论述访问数据块个数的精确差异，只要知道大概就可以。但BTREE的访问效率依然较高，这是为什么？BRIN执行计划中多余的时间很有可能是消耗在CPU上，由于BRIN是lossy索引，还需要对访问的数据进行精确匹配，做过滤操作；而BTREE只需通过访问路径即可查找到匹配的数据，因此性能较高。

3、在id2上的等值查询。id2是除以100后取整，所以等值查询也是范围扫描。id2是以一定重复的值顺序递增。BRIN和BTREE都只需访问较少的数据页。

在这种情况下，BTREE仍然具有较明显的性能优势。重复值较少，所以BTREE只需访问很少的buffer，在本例中是5个buffer；而BRIN缺省就是128个heap page范围，所以最少也需要访问128个heap page，本例中访问了131个buffer。

4、在id3上的等值查询。id3是对1000取模，并不是每个数据块都包含9这个值。通过BTREE可以访问较少的buffer。由于BRIN是128个heap page做为一个范围单位，在一个范围单位里面还是会出现9这个值。也就是说，通过BRIN还是会访问到所有heap page。从这点来看，BTREE还是具有较大优势。我们来看实际情况是否如此。

可以看到，通过BRIN的执行SQL查询访问了73533个buffer，而通过BTREE的执行只访问了10031个buffer。从执行时间上，BTREE也是具备明显的优势，是BRIN性能的21倍。

5、再来看id4上的等值查询。id4是除以1000后取整，是以更多重复的值顺序增加。来看一下实际运行的数据。

虽然由于除以1000的关系，id4以更大的重复度顺序增加，但依然没有超过BRIN缺省128个heap page的范围，所以访问的buffer还是131个。而BTREE却因为重复值的增加而扫描了更多的buffer，达到14个。比之前的id2（除以100后取整）多了7个buffer的访问。但相比BRIN的性能优势还是比较明显。

我们可以想象，如果再增加值的重复度，例如除以2000、3000，两者性能差距会逐渐缩小。BRIN还是在一个范围之内，也就是128个heap page；而BTREE因为重复值的增加所需要访问的index page和heap page数量都会增加。

6、再来看id5上的等值查询。id5使用了一个比较大的数值30000来取模。一个BRIN范围是128，而一个heap page中有136个数据行，因此一个BRIN可以涵盖17408行字段上的值。换句话说，并不是每个BRIN单位都含有9这个值，BRIN可以减少buffer的访问量。而BTREE因为唯一值数量的增加，也会访问较少的buffer。来看看实际运行性能孰优孰劣。

可以看到，正如前面预测的一样。通过BRIN执行的语句本次不用再访问所有的heap page，访问的buffer数量相比之前取模的分布情况减少到42755。而BTREE也比之前id3通过1000取模的情况大幅减少，从10031降低到338。从执行时间的性能数据上来看，BTREE仍然具有明显的优势，是BRIN的265倍！

7、最后我们来看一下id6上的等值查询情况。id6通过除以30000后取整获得。按照第5点的分析，BRIN和BTREE的查询在此种数据分钟下性能差距会缩小，看看是否如此。

果然，二者的性能差距在本例中已经基本持平。BRIN由于值重复度的进一步增加，需要访问3个BRIN单位，最终需要访问387个buffer；而BTREE由于重复值的增加，buffer的访问数量也增加到了306个，已经非常接近BRIN。

总结：

1、BRIN索引和BTREE索引相比，在存储空间占用上具备巨大优势。本例中达到6848倍！对于数据仓库或者VLDB应用可以节省大量的存储成本。

2、本文给出并分析了7中不同的数据分布，在等值查询情况下，所有的场景BTREE的性能都优于BRIN索引。但在不同数据分布情况下，BTREE在性能上的领先优势差别非常大，从不到1倍到265倍。

3、在选择创建BRIN还是BTREE索引时，需要权衡性能和空间使用两方面的影响。根据数据量、数据分布和SQL选择最适合的索引类型。在性能相差不大的情况下，选择BRIN可能是更加经济的选择。总体来说，当实际匹配数据量较少时，BTREE索引更加适合；反之，BRIN更加适合。

4、由于BRIN索引的lossy特性，需要消耗较多的CPU时间用于精确匹配。

5、BRIN索引的引入无疑给数据访问开辟了一条新的途径，使得PG的功能更加丰富。期望Oracle也能够将功能类似的Storage Index早日引入到其非Exadata数据库版本中。

。在前面的文章中根据表中不同的数据分布比较了BRIN和BTREE索引在大小和查询性能上的差异，希望对大家在选择使用BRIN还是BTREE索引时有所帮助。

系列一的结论指出，BTREE相比BRIN索引的结构庞大，但在测试的各种数据分布情况下查询性能较好，虽然有时性能差异不是太大；反之BRIN虽然结构精简，但查询性能却不如BTREE，特别是所需访问数据量较小时性能和BTREE有巨大差异。那么有没有可能鱼和熊掌兼得呢？既可以获得BTREE在小数据量查询下的高性能，同时又可以使用BRIN的精简结构进行大数据量的查询，还能够节省巨大的存储空间，这该是多好的一件事情！Bring the best of both worlds! 有没有可能呢？答案是肯定的！

在PG中，有一个特性叫Partial Indexes。这也正是PG强大且灵活的地方。通过使用这个特性，在特定的数据分布下，我们就可以享受BRIN带来的空间节省以及BTREE带来的高查询性能。先简单解释一下什么是Partial Indexes，普通的索引需要对表的所有行对应的索引字段建立索引，而Partial顾名思义，只对表的一部分子集对应的索引字段建立索引，而且这个子集可以写过滤条件（Predicate）来自己定义。根据这个条件创建出来的索引，条目中也就只包含了满足过滤条件的字段值。什么时候需要用到Partial Index？是在字段中数据存在Data Skew，我们常说的分布不均情况下。大家都知道，如果一个字段上distinct值越多，density越小，我们说索引的selectivity就越高，使用BTREE类型的索引查询性能就越高；反之通过BTREE的查询性能会下降。当一个字段上出现高频值的时候，是没有必要对这个高频出现的值建立索引条目的，既浪费存储空间又起不到提升查询性能的效果。很多情况下，还会导致性能下降，引起并发竞争等问题。如果不对这些高频值建立索引，还能够提升数据库中DML语句的执行性能。一般来讲，一个表上的索引越多，虽然查询性能能够获得一定提升，但DML语句的执行性能会越差，因为需要维护对应的索引结构。如果连查询性能都提升不了，就更没有必要建立索引了。Partial Index正是针对这种情况而设计的。

回到本文的主题，我们构造了一个数据严重偏移的数据分布情况。表中有10,000,000的数据量，在id1这个字段上总共有100个唯一值，但1-99这些值只重复了10次；而id1=100却有9,999,010行，占了总数据量的99.9901%。很明显，对于id1=100这个值使用BTREE索引是完全没有必要的，不仅会消耗大量的存储空间，也无法带来查询性能的提升。这正是Partial Index适用的场景。

表和数据的构造语句如下图所示：

可以看到，构造完毕后。

1. 普通的BTREE索引idx_t2_id1_bt_in占用了214MB的空间；

2. 而排除了id1=100的Partial BTREE Index idx_t2_id1_bt只使用了40KB；

3. 普通的BRIN索引idx_t2_id1_br占用了24KB的空间；

4. 只包含了id1=100的Partial BRIN Index idx_t2_id1_br_ex也是使用了24KB，没有差别

到这里，大家可能已经知道了我们如何鱼和熊掌兼而得之！通过创建Partial BTREE Index，可以排除不必要的高频值，从而大幅减少存储空间的使用，同时获得对低频值的高性能查询；而通过建立BRIN索引，可以获得对大批量数据访问的性能提升。

我们来看一下实际的查询执行性能情况：

在id1=99时，由于是一个低频值，在Partial BTREE Index idx_t2_id1_bt中有对应的索引条目，PG优化器选择走索引，只需要访问11个buffer，执行时间也只有0.246ms。

而在id1=100时，优化器选择走了Partial BRIN Index idx_t2_id1_br_ex，访问了表t2所有的heap page，Lossy=44248。加上扫描BRIN的buffer，总buffer数量是44256。这是正常的，前面提到id1=100的行占据了总数据量的99.9901%。

再看一下使用普通BRIN索引的情况，由于id1在到99之间值很少，所以对最终结果影响不大。还是扫描了表t2所有的heap page，lossy=44248。扫描BRIN的buffer变成了2，最终总扫描buffer数量是44250。

总结：

1. 通过在特定的数据分布下使用PG的Partial BTREE Index特性和BRIN索引，既可以做到索引空间的大量节省，又可以实现对不同频率数据的高性能访问。在本例中，如果采用普通BTREE和BRIN索引，空间使用是560MB；而使用Partial BTREE Index和BRIN，空间使用则只有346MB，索引使用空间基本忽略不计。借助PG的灵活性，鱼和熊掌可以兼得！

2. 本例中为了说明Partial BTREE Index和BRIN的完美结合带来了空间和性能的双重收益，构造了很特别的数据分布，并不能代表真实应用场景。通过本文是想告诉大家，PG带给我们其他数据库产品所不具备的特性，在进行应用设计时，提供给了我们更大的想象空间。在特定的场景下，通过同时使用Partial Index和BRIN索引，能够有意想不到的收获。

作者简介：

汪洋，中国平安集团旗下平安科技数据库技术部总监。从1994年开始接触Oracle 6数据库，于1998年获取Oracle 7.3 OCP证书，迄今已从事Oracle相关开发运维工作20年。在加入平安之前，就职于Oracle香港高级客户服务部门，为中国、香港以及澳门客户提供数据库架构设计，数据库性能优化等高级服务。加入平安后，负责数据库技术引入，数据库产品选型，数据库架构设计，数据库规范制定，开发、测试、生产环境运维等工作。近年，对开源数据库技术以及DBaaS产生浓厚兴趣，一直致力于相关的研究和引入工作。

----------我是一条分割线------------

欢迎大家投稿到PostgreSQL微信公众号

投稿邮箱：press@postgres.cn

邮件主题：投稿+文章标题