PostgreSQL 9.5 BRIN 索引

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 以下内容转载自:PostgreSQL 9.5 new feature - BRIN (block range index) indexPostgreSQL 9.5引入的一个全新的索引访问方法BRIN(block range index),这个索引存储了表的连续数据块区间以及对应的数据取值范围。
PostgreSQL 9.5引入的一个全新的索引访问方法BRIN(block range index),这个索引存储了表的连续数据块区间以及对应的数据取值范围。
比如一张表有1000个数据块,我们建议一个BRIN在ID(假设这个表有ID字段)上的索引。
BRIN默认是每128个连续数据块区间存储一个字段取值的区间,所以这个索引的信息量是将1000个数据块划分为几个连续的128个块的区间,然后存储每个区间ID值的取值范围。
很显然,BRIN索引时lossy索引(即有损索引),那么我们并不能直接从索引中精确匹配要查询的记录,但是通过索引我们可以将查询范围缩小到最小128个连续的数据块(假设我们要找的值落在这个区间)。
以上是BRIN大概的原理,那么BRIN可以用在什么场景呢?
一个非常好的场景是流式日志数据,比如用户行为,大批量的数据按时间顺序不停的插入数据表。
我们如果要按照时间来访问这样的数据,以往我们需要创建BTREE索引,可以范围查询或者精确匹配。但是BTREE索引需要存储的信息量较大,如果数据量很大,索引也很庞大。
BRIN的话,索引可以变得很小,而且因为数据是按照时间顺序插入的,所以BRIN的信息量也很大,因为每个连续的数据块区间存储的时间范围和其他连续的数据块区间独立性很好,即不会出现大量数据交叉,如果有大量较差,那么使用BRIN检索还不如全表扫描。
BRIN可认为是全表扫描的切片,如果数据值分布和物理值分布的相关性很好,那么BRIN无疑是非常好的选择。
这里说到的相关性,大家可以参考统计学的知识,或者参考我之前写过的一篇文章。
接下来我们测试一下BRIN对于相关性好和相关性差的数据,以及他们的性能。
 
  

postgres=# create table t1(id int,info text);
CREATE TABLE
postgres=# create table t2(id int,info text);
CREATE TABLE
postgres=# insert into t1 select generate_series(1,10000000),md5(random()::text);
INSERT 0 10000000

以下数据ID和物理存储相关性非常差。
 
   

postgres=# insert into t2 select id,md5(random()::text) from generate_series(1,10000000) as t(id) order by random();
INSERT 0 10000000
postgres=# analyze t1;
ANALYZE
postgres=# analyze t2;
ANALYZE

查询他们的相关性。显然T2表的物理存储和实际值顺序相关性很差。
 
  

postgres=# select correlation from pg_stats where tablename='t1' and attname='id';
 correlation 
-------------
           1
(1 row)
postgres=# select correlation from pg_stats where tablename='t2' and attname='id';
 correlation 
-------------
  0.00805771
(1 row)

创建索引,创建索引的速度明显比BTREE索引快,因为BRIN只需要存储值区间,瘦得很。
 
  

postgres=# create index idx_t1_id on t1 using brin (id);
CREATE INDEX
postgres=# create index idx_t2_id on t2 using brin (id);
CREATE INDEX

我们看看索引的大小和表的大小,从BRIN的原理我们可以想象索引肯定很小,表650MB,索引才192K。
 
  

postgres=# \di+
                          List of relations
 Schema |   Name    | Type  |  Owner   | Table |  Size  | Description 
--------+-----------+-------+----------+-------+--------+-------------
 public | idx_t1_id | index | postgres | t1    | 192 kB | 
 public | idx_t2_id | index | postgres | t2    | 192 kB | 
(2 rows)
postgres=# \dt+ t1
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t1   | table | postgres | 650 MB | 
(1 row)
postgres=# \dt+ t2
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t2   | table | postgres | 650 MB | 
(1 row)


来看看实际的查询差别就知道,BRIN有多么适合流式数据了。
 
   

postgres=# explain analyze select * from t1 where id>=1000 and id<=5000;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=50.98..9767.60 rows=3803 width=37) (actual time=0.351..13.732 rows=4001 loops=1)
   Recheck Cond: ((id >= 1000) AND (id <= 5000))
   Rows Removed by Index Recheck: 57567
   Heap Blocks: lossy=128
   ->  Bitmap Index Scan on idx_t1_id  (cost=0.00..50.03 rows=3803 width=0) (actual time=0.104..0.104 rows=1280 loops=1)
         Index Cond: ((id >= 1000) AND (id <= 5000))
 Planning time: 0.111 ms
 Execution time: 14.019 ms
(8 rows)

对于相关性差的,还不如全表扫描。
 
   

postgres=# explain analyze select * from t2 where id>=1000 and id<=5000;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t2  (cost=49.78..9549.73 rows=3686 width=37) (actual time=2.806..2268.044 rows=4001 loops=1)
   Recheck Cond: ((id >= 1000) AND (id <= 5000))
   Rows Removed by Index Recheck: 9995999
   Heap Blocks: lossy=20791
   ->  Bitmap Index Scan on idx_t2_id  (cost=0.00..48.86 rows=3686 width=0) (actual time=2.019..2.019 rows=208640 loops=1)
         Index Cond: ((id >= 1000) AND (id <= 5000))
 Planning time: 0.195 ms
 Execution time: 2268.590 ms
(8 rows)

t2全表扫描
 
  

postgres=# set enable_bitmapscan=off;
SET
postgres=# explain analyze select * from t2 where id>=1000 and id<=5000;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Seq Scan on t2  (cost=0.00..170791.00 rows=3686 width=37) (actual time=0.593..1881.929 rows=4001 loops=1)
   Filter: ((id >= 1000) AND (id <= 5000))
   Rows Removed by Filter: 9995999
 Planning time: 0.109 ms
 Execution time: 1882.397 ms
(5 rows)

接下来BRIN和BTREE索引对比一下。
 
  

postgres=# create index idx_t1_id_bt on t1 using btree (id);
CREATE INDEX
postgres=# create index idx_t2_id_bt on t2 using btree (id);
CREATE INDEX
postgres=# set enable_bitmapscan=on;
SET
postgres=# drop index idx_t1_id;
DROP INDEX
postgres=# drop index idx_t2_id;
DROP INDEX
postgres=# explain analyze select * from t1 where id>=1000 and id<=5000;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t1_id_bt on t1  (cost=0.43..102.04 rows=3880 width=37) (actual time=0.023..1.048 rows=4001 loops=1)
   Index Cond: ((id >= 1000) AND (id <= 5000))
 Planning time: 0.412 ms
 Execution time: 1.318 ms
(4 rows)
postgres=# explain analyze select * from t2 where id>=1000 and id<=5000;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t2  (cost=53.05..10056.68 rows=3962 width=37) (actual time=1.932..8.304 rows=4001 loops=1)
   Recheck Cond: ((id >= 1000) AND (id <= 5000))
   Heap Blocks: exact=3642
   ->  Bitmap Index Scan on idx_t2_id_bt  (cost=0.00..52.05 rows=3962 width=0) (actual time=1.143..1.143 rows=4001 loops=1)
         Index Cond: ((id >= 1000) AND (id <= 5000))
 Planning time: 0.379 ms
 Execution time: 8.621 ms
(7 rows)


我们看到btree索引查询性能是提高了,但是索引大小你看看有多大?
 
  

postgres=# \di+
                            List of relations
 Schema |     Name     | Type  |  Owner   | Table |  Size  | Description 
--------+--------------+-------+----------+-------+--------+-------------
 public | idx_t1_id_bt | index | postgres | t1    | 213 MB | 
 public | idx_t2_id_bt | index | postgres | t2    | 213 MB | 
(2 rows)

接下调整brin索引的精度提高查询效率,我们了解到默认的brin是存储128个连续的数据块区间的,这个值越小,精度越高。
 
  

postgres=# create index idx_t1_id on t1 using brin (id) with (pages_per_range=1);
CREATE INDEX
postgres=# create index idx_t2_id on t2 using brin (id) with (pages_per_range=1);
CREATE INDEX
postgres=# \di+
                            List of relations
 Schema |     Name     | Type  |  Owner   | Table |  Size  | Description 
--------+--------------+-------+----------+-------+--------+-------------
 public | idx_t1_id    | index | postgres | t1    | 672 kB | 
 public | idx_t1_id_bt | index | postgres | t1    | 213 MB | 
 public | idx_t2_id    | index | postgres | t2    | 672 kB | 
 public | idx_t2_id_bt | index | postgres | t2    | 213 MB | 
(4 rows)
postgres=# drop index idx_t1_id_bt;
DROP INDEX
postgres=# drop index idx_t2_id_bt;
DROP INDEX
postgres=# explain analyze select * from t1 where id>=1000 and id<=5000;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=110.98..9827.60 rows=3803 width=37) (actual time=9.487..10.571 rows=4001 loops=1)
   Recheck Cond: ((id >= 1000) AND (id <= 5000))
   Rows Removed by Index Recheck: 328
   Heap Blocks: lossy=9
   ->  Bitmap Index Scan on idx_t1_id  (cost=0.00..110.03 rows=3803 width=0) (actual time=9.449..9.449 rows=90 loops=1)
         Index Cond: ((id >= 1000) AND (id <= 5000))
 Planning time: 0.141 ms
 Execution time: 10.853 ms
(8 rows)
postgres=# explain analyze select * from t2 where id>=1000 and id<=5000;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t2  (cost=109.78..9609.73 rows=3686 width=37) (actual time=10.407..481.673 rows=4001 loops=1)
   Recheck Cond: ((id >= 1000) AND (id <= 5000))
   Rows Removed by Index Recheck: 2125867  # 看看精度不高的后果,取4001条数据却额外扫描了2125867条无用数据
   Heap Blocks: lossy=4428
   ->  Bitmap Index Scan on idx_t2_id  (cost=0.00..108.86 rows=3686 width=0) (actual time=10.364..10.364 rows=44280 loops=1)
         Index Cond: ((id >= 1000) AND (id <= 5000))
 Planning time: 0.106 ms
 Execution time: 482.077 ms
(8 rows)

精度提高后,扫描效率有一定的提升。(对于相关度不高的就不要用BRIN了,精度提高到1都于事无补的,无用功太多)当然相比btree还有差距,不过对于大数据场景,我们还要考虑数据的插入性能,对于btree插入性能好还是brin的插入性能好呢?
我这里简单的测试了一下,并未涉及并发处理,已经可以明显的了解到btree索引对数据插入带来的开销更大。
 
   

postgres=# \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 info   | text    | 
Indexes:
    "idx_t1_id" brin (id) WITH (pages_per_range=1)
postgres=# \timing
Timing is on.
postgres=# insert into t1 select generate_series(1,1000000);
INSERT 0 1000000
Time: 2152.527 ms
postgres=# drop index idx_t1_id;
DROP INDEX
Time: 9.527 ms
postgres=# create index idx_t1_id_bt on t1 using btree (id);
CREATE INDEX
Time: 29659.752 ms
postgres=# insert into t1 select generate_series(1,1000000);
INSERT 0 1000000
Time: 5407.971 ms

最后,我们同样可以使用pageinspect来观测brin索引的内容。
 
  

postgres=# create extension pageinspect;
CREATE EXTENSION
postgres=# select * from brin_page_items(get_raw_page('idx_t1_id',10),'idx_t1_id');
 itemoffset | blknum | attnum | allnulls | hasnulls | placeholder |        value         
------------+--------+--------+----------+----------+-------------+----------------------
          1 |   2176 |      1 | f        | f        | f           | {1046657 .. 1047137}
          2 |   2177 |      1 | f        | f        | f           | {1047138 .. 1047618}
          3 |   2178 |      1 | f        | f        | f           | {1047619 .. 1048099}
          4 |   2179 |      1 | f        | f        | f           | {1048100 .. 1048580}
          5 |   2180 |      1 | f        | f        | f           | {1048581 .. 1049061}
          6 |   2181 |      1 | f        | f        | f           | {1049062 .. 1049542}
          7 |   2182 |      1 | f        | f        | f           | {1049543 .. 1050023}
。。。

例如我们看到 2176  这个数据块的ID取值区间是 {1046657 .. 1047137},我们使用ctid来验证一下.
 
  

postgres=# select min(id),max(id) from t1 where ctid::text ~ E'^\\(2176,';
   min   |   max   
---------+---------
 1046657 | 1047137
(1 row)
# 完全正确

其他还有几个pageinspect的函数:
 
  

postgres=# SELECT brin_page_type(get_raw_page('idx_t1_id', id)) from generate_series(0,10) t(id);
 brin_page_type 
----------------
 meta
 revmap
 revmap
 revmap
 revmap
 revmap
 regular
 regular
 regular
 regular
 regular
(11 rows)
postgres=# SELECT * FROM brin_metapage_info(get_raw_page('idx_t1_id', 0));
   magic    | version | pagesperrange | lastrevmappage 
------------+---------+---------------+----------------
 0xA8109CFA |       1 |             1 |              5
(1 row)
postgres=# SELECT * FROM brin_revmap_data(get_raw_page('idx_t1_id', 1)) limit 5;
   pages   
-----------
 (18,1105)
 (18,1106)
 (18,1107)
 (18,1108)
 (18,1109)
(5 rows)

截止目前,PostgreSQL可以支持btree,hash,gin,gist,spgist,brin共6种索引访问方法。用户可以根据实际应用场景选择合适的索引。

[参考]
 
  

BRIN indexes accept a different parameter:
pages_per_range
Defines the number of table blocks that make up one block range for each entry of a BRIN index (see Section 60.1 for more details). The default is 128.
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
103 1
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
445 0
|
4月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
313 0
|
存储 关系型数据库 数据库
PostgreSQL技术大讲堂 - 第28讲:索引内部结构
从零开始学PostgreSQL技术大讲堂 - 第28讲:索引内部结构
761 2
|
关系型数据库 Go 数据库
《提高查询速度:PostgreSQL索引实用指南》
《提高查询速度:PostgreSQL索引实用指南》
595 0
|
关系型数据库 分布式数据库 数据库
PolarDB for PostgreSQL 14:全局索引
PolarDB for PostgreSQL 14 相较于 PostgreSQL 14,提供了更多企业级数据库的特性。本实验将体验其中的全局索引功能。
|
弹性计算 关系型数据库 OLAP
AnalyticDB PostgreSQL版向量索引查询
本案例对比了传统查询和使用向量索引执行查询的执行时间,助您体验使用向量索引查询带来的高效和快捷。
|
存储 SQL 关系型数据库
PostgreSQL插件HypoPG:支持虚拟索引
PostgreSQL插件HypoPG:支持虚拟索引
400 0
|
存储 缓存 关系型数据库
PostgreSQL 14新特性--减少索引膨胀
PostgreSQL 14新特性--减少索引膨胀
487 0
|
7月前
|
SQL 关系型数据库 数据库
RDS PostgreSQL索引推荐原理及最佳实践
前言很多开发人员都知道索引对于数据库的查询性能至关重要,一个好的索引能使数据库的性能提升成千上万倍。但给数据库加索引是一项相对专业的工作,需要对数据库的运行原理有一定了解。同时,加了索引有没有性能提升、性能提升了多少,这些都是加索引前就想知道的。这项繁杂的工作有没有更好的方案呢?有!就是今天重磅推出...
120 1
RDS PostgreSQL索引推荐原理及最佳实践