微信号:PostgresChina

介绍:致力于PostgreSQL数据库的技术推广和中文社区用户会活动发布.

PostgreSQL中BRIN和BTREE索引的比较 - Part II

2016-07-22 16:34 汪洋
 
本文为BRIN和BTREE索引讨论系列之二。在前面的文章中根据表中不同的数据分布比较了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在0到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产生浓厚兴趣,一直致力于相关的研究和推广工作。
更多内容可 关注公众号:
平安科技数据库技术部

本文整理自平安科技数据库技术部总监汪洋先生的投稿。如需转载,请联系本公众号获取授权!

只为一场完美的技术盛宴!
SACC2016
作为国内最受欢迎的架构师盛会, 2016第八届中国系统架构师大会(SACC)将于2016年10月27日-29日在北京万达索菲特大酒店撼世来袭!

大会以"架构创新之路"为主题,共设置两个主场分享时段,24个技术交流专场时段;邀请来自互联网、电子商务、金融、电信、政府、行业协会等20多个领域,150多位技术专家及行业领袖来分享他们的经验;并将吸引4000多名系统运维、架构师、及各种企业的IT决策人士参会,为他们提供最具价值的交流平台。
最优折扣来袭!
7月28日前,订购SACC2016门票可立享6.8折优惠!团购更有折上折!
点击 “阅读原文”立即购票!

 
postgres用户会 更多文章 Pivotal 招聘Greenplum\/HAWQ 数据库内核开发工程师 基于PostgreSQL的内存计算引擎 LeMCS设计开发经验之谈 系列文章:PostgreSQL for Oracle DBA 2017.4.8 PostgreSQL象行中国 [杭州站] 【​2017象行中国】 杭州站-- 4.8
猜您喜欢 Airbnb支付平台如何进行异常检测 大数据时代,我为什么说统计学依然是数据分析灵魂 一个开发眼中的运维 【职场】谷歌工程师:做正确的事情,等着被开除 CS183C创业课: Blitzscaling 课程笔记(九)