PostgreSQL中BRIN和BTREE索引的比较(三) 原作者:平安科技汪洋 创作时间:2016-07-22 12:16:43+08 |
doudou586 发布于2016-07-22 12:16:43 评论: 0 浏览: 5620 顶: 849 踩: 795 |
汪洋,中国平安集团旗下平安科技数据库技术部总监。从1994年开始接触Oracle 6数据库,于1998年获取Oracle 7.3 OCP证书,迄今已从事Oracle相关开发运维工作20年。在加入平安之前,就职于Oracle香港高级客户服务部门,为中国、香港以及澳门客户提供数据库架构设计,数据库性能优化等高级服务。加入平安后,负责数据库技术引入,数据库产品选型,数据库架构设计,数据库规范制定,开发、测试、生产环境运维等工作。近年,对开源数据库技术以及DBaaS产生浓厚兴趣,一直致力于相关的研究和引入工作。
本文是针对PostgreSQL 9.5推出的新索引类型BRIN如何在实际中运用的系列之三。前面两篇一篇是分析在不同数据分布下BTREE和BRIN索引在空间和查询性能上的不同表现;第二篇是介绍在某些数据分布不平均的场景下,如何同时使用BTREE和BRIN索引来获得查询性能的提升和存储空间的有效利用。本文将集中在对BRIN索引自身的使用上。
在一篇分析BRIN索引存储结构的文章中提到,一个BRIN索引主要分为三大部分:metadata page,revmap page和regular page。而regular page是BRIN索引的核心,正是通过查询在regular page中的BRIN索引条目,能够在进行大量数据访问的时候提升查询性能。那么BRIN的索引条目是如何提升查询性能的呢?每一个条目除了给出所涵盖的heap page范围中最大值和最小值之外,还包含了两个关键的信息。一个是allnulls,还有一个是hasnulls。拿缺省的pages_per_range=128为例:
今天我们就来看一下查询时执行器是如何利用BRIN索引上的这两个信息过滤掉不需要访问的heap page,从而提升查询性能。
先来构造我们的案例,创建一张表,并且插入2,000,001行记录。其中id字段是顺序递增的;id1字段的值绝大部分和id相同,存在少许NULL值,用于创建BRIN索引。具体如下图:
id1字段上的BRIN索引按缺省的pages_per_range=128创建,这点可以从下面的BRIN索引metapage中看到。
再来看一下BRIN索引regular page中的索引条目信息,请留意allnulls和hasnulls对应的值。在我们构造的案例中:
在这种情况下,当我们想要找出所有id1字段为非空的行的个数会发生什么?PG会扫描所有的heap page吗?来看下面的查询:
从执行结果可以看出,PG的执行器利用了BRIN索引条目上的allnulls和hasnulls信息,过滤掉了大部分无需扫描的heap page。
来看一下正好相反的例子。在本例中,我们将id1完全为空的值更新为非空;而将原先为非空的值更新为空。
可以想象到,更新后,id1字段绝大部分都为非空值,而只有少数为空值。来看一下更新后的BRIN索引条目。
索引条目数从70增加到了139,这是因为PG的MVCC特性导致的,更新后最初的数据行还在,heap page也没有释放。即使手工做了表的vacuum操作也是一样,只是更新了visibility map。由于PG的索引中并不包含数据行的visibility信息,所以对应heap page的索引条目也都存在。更新后的数据会占用新的heap page,并会产生新的索引条目。
也因为索引条目的新增,旧的条目以及对应的heap page还在,BRIN索引对于本次查询性能的提升并不十分明显。我们来具体分析一下:
我们再来看一下对表进行vacuum full操作以及reindex之后的查询效果。
vacuum full操作相当于重建t4表,之前的dead tuple全部删除,并且占用的heap page也得以全部释放。重建后的idx_t4_id1_br索引也只包含了更新后数据对应的索引条目,看上去和案例一是相似的。
再来执行一遍统计id1字段为空的行数的查询: