PostgreSQL BRIN索引内核代码优化思考

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

PostgreSQL , BRIN 块级索引 , 扫描方法 , 数据结构 , pages_per_range算法


背景

BRIN是PostgreSQL 9.5新增的块级索引接口,存储了被索引字段在块级别的边界值(最大值、最小值)以及其他统计信息。

当需要对某个字段进行检索时,需要扫描整个BRIN索引(这个是BRIN索引内核层面将来值得优化的点)。然后跳过不符合条件的HEAP PAGE,扫描复合条件的HEAP PAGE。实现数据过滤的目的。

原理所致,对于建立BRIN索引的字段,相关性越好,BRIN索引的过滤性就越好。

BRIN同时还支持多种类型、多列字段等。

1、多列

2、单列

3、空间数据类型

如果你还对BRIN不了解,可以阅读我写过的一些案例文章。

《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践》

《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》

《PostgreSQL 并行写入堆表,如何保证时序线性存储 - BRIN索引优化》

《PostgreSQL 10.0 preview 功能增强 - BRIN 索引更新smooth化》

《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》

《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》

《PostgreSQL 9.5 new feature - lets BRIN be used with R-Tree-like indexing strategies For "inclusion" opclasses》

《PostgreSQL 9.5 new feature - BRIN (block range index) index》

BRIN索引支持的参数pages_per_range的作用是多少个块统计一次边界值。

本文将以下面这个场景中的案例为例,讲解一下BRIN索引的pages_per_range参数的设置算法,以及BRIN索引列的优化,BRIN索引的内核优化思路等。

《万亿(100TB)级电商广告 - PostgreSQL单机如何实现毫秒级圈人》

brin扫描原理

BRIN索引的扫描原理很简单,扫描BRIN的元数据,根据元数据和用户输入的条件进行比较,过滤不符合条件的HEAP PAGE,只扫描需要扫描的HEAP PAGE。

BRIN索引列的相关性优化

由于BRIN是块级索引,如果块的边界范围很大,或者说块与块之间的重叠度很高,那么BRIN索引的过滤性就很差。

因此BRIN仅仅适合存储与值线性相关性很好的列。

pg_stats.correlation可以观察列的线性相关性。

当然我们也可以人为的修改它的存储,改变它的线性相关性(排序存储是最简单的方法),甚至可以改变局部的线性相关性。你想知道更深层次原理的话,请参考如下文章。

《解密上帝之手 - 阿里云HDB for PostgreSQL数据库metascan特性(存储级、块级、batch级过滤与数据编排)》

多个条件扫描可以优化的点

当我们的查询条件是多个查询条件时,PostgreSQL会将多个索引的扫描合并成一个,跳过不符合条件的。这既是bitmapAnd, bitmapOr。

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

但是由于目前PostgreSQL BRIN索引的扫描需要扫描整个BRIN索引,因此每个条件都需要扫描一次,那么当BRIN本身比较大时,条件一多时间就会成倍增加。

BRIN索引的扫描方式,是PostgreSQL未来内核层面可以优化的点,比如将BRIN的边界再按树组织一下,不需要每次都全扫(太过暴力)。目前PostgreSQL没有做,也许是没有人有在几百亿的单表上建单块粒度(pages_per_range=1)的BRIN索引。

实际上我后面会来给大家展示这个问题。大伙就知道我为什么要优化pages_per_range参数了。

BRIN索引参数pages_per_range选择推荐算法

pages_per_range是粒度,默认为128(表示每128个数据块统计一次边界),决定了两件事情。

1、BRIN索引的精确度。pages_per_range=1,说明边界精确到1个数据块。pages_per_range越小,精度越高,过滤性就越好(注意过滤性越好取决于列的线性相关性很好的情况下,否则就是白瞎)。

2、BRIN索引本身的大小。pages_per_range越小,BRIN索引本身就越大。BRIN越大,单次走BRIN索引扫描BRIN块的成本就越高。

那么pages_per_range到底设置为多大合适呢?

根据我的经验,311GB的表,设置为512是不错的选择。越小的表,pages_per_range设置可以越小。

311GB的表,如果pages_per_range=1,BRIN索引本身就有1.6GB这么大了。扫一下很费劲。当设置为512时,大概只有几MB。扫一下很快(虽然过滤性可能差了,但是BRIN是每个条件都要扫一次的)。

DEMO

1、pages_per_range=1

postgres=# \d bi_user_tmall_vis1  
    Unlogged table "public.bi_user_tmall_vis1"  
 Column |  Type   | Collation | Nullable | Default   
--------+---------+-----------+----------+---------  
 uid    | bigint  |           |          |   
 bid    | bigint  |           |          |   
 cnt    | integer |           |          |   
Indexes:  
    "idx_bi_user_tmall_vis1" brin (bid, cnt) WITH (pages_per_range='1')  
  
  
 public | idx_bi_user_tmall_vis1 | index | postgres | bi_user_tmall_vis1 | 1644 MB    |   
  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where bid=1 and cnt between 1 and 100;  
                                                                  QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=264463.65..274155.70 rows=7351 width=20) (actual time=8213.046..8213.057 rows=4 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
   Rows Removed by Index Recheck: 153  
   Heap Blocks: lossy=1  
   Buffers: shared hit=269675  
   ->  Bitmap Index Scan on idx_bi_user_tmall_vis1  (cost=0.00..264461.81 rows=7379 width=0) (actual time=8213.023..8213.023 rows=10 loops=1)  
         Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
         Buffers: shared hit=269674  
 Planning time: 0.046 ms  
 Execution time: 8213.080 ms  
(11 rows)  

2、pages_per_range=128

postgres=# \d bi_user_tmall_vis1  
    Unlogged table "public.bi_user_tmall_vis1"  
 Column |  Type   | Collation | Nullable | Default   
--------+---------+-----------+----------+---------  
 uid    | bigint  |           |          |   
 bid    | bigint  |           |          |   
 cnt    | integer |           |          |   
Indexes:  
    "idx_bi_user_tmall_vis1" brin (bid, cnt) WITH (pages_per_range='128')  
    
 public | idx_bi1        | index | postgres | bi_user_tmall_vis1 | 13 MB      |   
  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100);  
                                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=2071.47..28408.93 rows=7351 width=20) (actual time=61.110..62.974 rows=4 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
   Rows Removed by Index Recheck: 20092  
   Heap Blocks: lossy=128  
   Buffers: shared hit=2236  
   ->  Bitmap Index Scan on idx_bi1  (cost=0.00..2069.63 rows=20096 width=0) (actual time=61.100..61.100 rows=1280 loops=1)  
         Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
         Buffers: shared hit=2108  
 Planning time: 0.072 ms  
 Execution time: 62.994 ms  
(11 rows)  
  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100) or (bid=2000 and cnt <10000) or (bid=12000 and cnt <10000);  
                                                                                                                                 QUERY PLAN                                                                                                    
                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
--------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=6324.38..242299.15 rows=153721 width=20) (actual time=184.909..191.652 rows=138 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: (((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100)) OR ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000)) OR ((bi_user_tmall_vis1.bid = 12000) AND (bi  
_user_tmall_vis1.cnt < 10000)))  
   Rows Removed by Index Recheck: 60150  
   Heap Blocks: lossy=384  
   Buffers: shared hit=6708  
   ->  BitmapOr  (cost=6324.38..6324.38 rows=180864 width=0) (actual time=184.896..184.896 rows=0 loops=1)  
         Buffers: shared hit=6324  
         ->  Bitmap Index Scan on idx_bi1  (cost=0.00..2069.63 rows=20096 width=0) (actual time=61.600..61.600 rows=1280 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
               Buffers: shared hit=2108  
         ->  Bitmap Index Scan on idx_bi1  (cost=0.00..2069.73 rows=80384 width=0) (actual time=61.522..61.522 rows=1280 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=2108  
         ->  Bitmap Index Scan on idx_bi1  (cost=0.00..2069.73 rows=80384 width=0) (actual time=61.773..61.773 rows=1280 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 12000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=2108  
 Planning time: 0.091 ms  
 Execution time: 191.684 ms  
(19 rows)  

3、 pages_per_range=256

public | idx_bi         | index | postgres | bi_user_tmall_vis1 | 6624 kB    |   
  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where bid=1 and cnt between 1 and 100;  
                                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=1038.00..53587.92 rows=7351 width=20) (actual time=30.259..33.742 rows=4 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
   Rows Removed by Index Recheck: 40188  
   Heap Blocks: lossy=256  
   Buffers: shared hit=1310  
   ->  Bitmap Index Scan on idx_bi  (cost=0.00..1036.16 rows=40192 width=0) (actual time=30.251..30.251 rows=2560 loops=1)  
         Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
         Buffers: shared hit=1054  
 Planning time: 0.061 ms  
 Execution time: 33.759 ms  
(11 rows)  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100) or (bid=2000 and cnt <10000) or (bid=12000 and cnt <10000);  
                                                                                                                                 QUERY PLAN                                                                                                    
                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
--------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=3223.91..265138.74 rows=153721 width=20) (actual time=90.760..105.509 rows=138 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: (((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100)) OR ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000)) OR ((bi_user_tmall_vis1.bid = 12000) AND (bi  
_user_tmall_vis1.cnt < 10000)))  
   Rows Removed by Index Recheck: 120438  
   Heap Blocks: lossy=768  
   Buffers: shared hit=3930  
   ->  BitmapOr  (cost=3223.91..3223.91 rows=200960 width=0) (actual time=90.746..90.746 rows=0 loops=1)  
         Buffers: shared hit=3162  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..1036.16 rows=40192 width=0) (actual time=30.838..30.838 rows=2560 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
               Buffers: shared hit=1054  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..1036.23 rows=80384 width=0) (actual time=29.966..29.966 rows=2560 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=1054  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..1036.23 rows=80384 width=0) (actual time=29.940..29.940 rows=2560 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 12000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=1054  
 Planning time: 0.131 ms  
 Execution time: 105.555 ms  
(19 rows)  

4、pages_per_range=512

 public | idx_bi                 | index | postgres | bi_user_tmall_vis1 | 3336 kB    |   
  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where bid=1 and cnt between 1 and 100;  
                                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=521.47..105255.40 rows=7351 width=20) (actual time=16.024..25.791 rows=4 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
   Rows Removed by Index Recheck: 80380  
   Heap Blocks: lossy=512  
   Buffers: shared hit=529 read=511  
   ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=16.010..16.010 rows=5120 loops=1)  
         Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
         Buffers: shared hit=528  
 Planning time: 0.238 ms  
 Execution time: 25.822 ms  
(11 rows)  
  
  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100) or (bid=2000 and cnt <10000) or (bid=12000 and cnt <10000);  
                                                                                                                                 QUERY PLAN                                                                                                    
                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
--------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=1674.17..315338.06 rows=153721 width=20) (actual time=47.115..78.014 rows=138 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: (((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100)) OR ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000)) OR ((bi_user_tmall_vis1.bid = 12000) AND (bi  
_user_tmall_vis1.cnt < 10000)))  
   Rows Removed by Index Recheck: 241014  
   Heap Blocks: lossy=1536  
   Buffers: shared hit=2608 read=512  
   ->  BitmapOr  (cost=1674.17..1674.17 rows=241151 width=0) (actual time=47.099..47.099 rows=0 loops=1)  
         Buffers: shared hit=1584  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=16.167..16.167 rows=5120 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
               Buffers: shared hit=528  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=15.494..15.494 rows=5120 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=528  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=15.437..15.437 rows=5120 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 12000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=528  
 Planning time: 0.145 ms  
 Execution time: 78.062 ms  
(19 rows)  

5、pages_per_range=sqrt(pg_class.relpages)=6384

 public | idx_bi         | index | postgres | bi_user_tmall_vis1 | 312 kB     |   
  
postgres=# create index idx_bi on bi_user_tmall_vis1 using brin (bid,cnt) WITH (pages_per_range='6384');  
CREATE INDEX  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100) or (bid=2000 and cnt <10000) or (bid=12000 and cnt <10000);  
                                                                                                                                 QUERY PLAN                                                                                                    
                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
--------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=252.98..3620468.00 rows=153721 width=20) (actual time=4.027..138.993 rows=138 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: (((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100)) OR ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000)) OR ((bi_user_tmall_vis1.bid = 12000) AND (bi  
_user_tmall_vis1.cnt < 10000)))  
   Rows Removed by Index Recheck: 1002150  
   Heap Blocks: lossy=6384  
   Buffers: shared hit=1662 read=4848  
   ->  BitmapOr  (cost=252.98..252.98 rows=3006577 width=0) (actual time=4.010..4.010 rows=0 loops=1)  
         Buffers: shared hit=126  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..45.90 rows=1002192 width=0) (actual time=1.373..1.373 rows=63840 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
               Buffers: shared hit=42  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..45.90 rows=1002192 width=0) (actual time=1.325..1.325 rows=63840 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=42  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..45.90 rows=1002192 width=0) (actual time=1.310..1.310 rows=63840 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 12000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=42  
 Planning time: 0.307 ms  
 Execution time: 139.046 ms  
(19 rows)  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100) or (bid=2000 and cnt <10000) ;  
                                                                                            QUERY PLAN                                                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=132.06..2459840.30 rows=80537 width=20) (actual time=2.735..112.409 rows=65 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: (((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100)) OR ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000)))  
   Rows Removed by Index Recheck: 1002223  
   Heap Blocks: lossy=6384  
   Buffers: shared hit=6468  
   ->  BitmapOr  (cost=132.06..132.06 rows=2004385 width=0) (actual time=2.720..2.720 rows=0 loops=1)  
         Buffers: shared hit=84  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..45.90 rows=1002192 width=0) (actual time=1.401..1.401 rows=63840 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
               Buffers: shared hit=42  
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..45.90 rows=1002192 width=0) (actual time=1.318..1.318 rows=63840 loops=1)  
               Index Cond: ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000))  
               Buffers: shared hit=42  
 Planning time: 0.126 ms  
 Execution time: 112.449 ms  
(16 rows)  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100);  
                                                              QUERY PLAN                                                                 
---------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=47.73..1258330.06 rows=7351 width=20) (actual time=1.381..97.717 rows=4 loops=1)  
   Output: uid, bid, cnt  
   Recheck Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
   Rows Removed by Index Recheck: 1002284  
   Heap Blocks: lossy=6384  
   Buffers: shared hit=6426  
   ->  Bitmap Index Scan on idx_bi  (cost=0.00..45.90 rows=1002192 width=0) (actual time=1.368..1.368 rows=63840 loops=1)  
         Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))  
         Buffers: shared hit=42  
 Planning time: 0.109 ms  
 Execution time: 97.744 ms  
(11 rows)  

不同pages_per_range的对比

索引精度 单表数据量 单表大小 索引大小 1个条件 2个条件 3个条件
pages_per_range=1 64亿 311GB 1.6GB 8.2秒 - -
pages_per_range=128 64亿 311GB 13MB 62毫秒 - 191毫秒
pages_per_range=256 64亿 311GB 6MB 33毫秒 - 105毫秒
pages_per_range=512 64亿 311GB 3MB 25毫秒 - 78毫秒
pages_per_range=sqrt(pg_class.relpages)=6384 64亿 311GB 300KB 97毫秒 112毫秒 139毫秒

虽然精度高,但是由于目前PG BRIN索引扫描方式是全扫的,所以索引本身越大,扫描索引本身的成本占比就越高,8.2秒就是这样来的。

当精度调成512时,单个条件变成了25毫秒,而索引大小只有3MB。

开不开心,意不意外。

BRIN内核优化思考

为了降低BRIN索引本身的扫描开销,我们可以把BRIN索引的边界,再生成一颗树,通过树来扫描,提高速率,而不是全扫的方式。

那么以后我们就只需要考虑精度=1的就可以了。因为这样过滤性是最好的,同时BRIN索引本身的扫描成本又是很低的。从而使BRIN索引的效率在海量数据的情况下,大幅度提升。

小结

本文主要讲了BRIN索引的原理,扫描的原理,精度参数的原理,以及如何选择精度参数,还讲了如何通过调整内核优化BRIN索引扫描的方法来降低BRIN索引本身的成本。

好了,祝大家玩得开心。你懂PostgreSQL多少,她就能给你多少,PG是一个可玩性很强的企业级开源数据库,加油。

BRIN索引的特性,可以用来支撑万亿级别甚至更大体量的海量数据筛选,同时索引的存储、对写入造成的影响等几乎为0。绝对属于黑科技级别的特性。

参考

《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践》

《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》

《PostgreSQL 并行写入堆表,如何保证时序线性存储 - BRIN索引优化》

《PostgreSQL 10.0 preview 功能增强 - BRIN 索引更新smooth化》

《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》

《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》

《PostgreSQL 9.5 new feature - lets BRIN be used with R-Tree-like indexing strategies For "inclusion" opclasses》

《PostgreSQL 9.5 new feature - BRIN (block range index) index》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
109 1
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
455 0
|
4月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
313 0
|
关系型数据库 Go 数据库
《提高查询速度:PostgreSQL索引实用指南》
《提高查询速度:PostgreSQL索引实用指南》
595 0
|
关系型数据库 分布式数据库 数据库
PolarDB for PostgreSQL 14:全局索引
PolarDB for PostgreSQL 14 相较于 PostgreSQL 14,提供了更多企业级数据库的特性。本实验将体验其中的全局索引功能。
|
弹性计算 关系型数据库 OLAP
AnalyticDB PostgreSQL版向量索引查询
本案例对比了传统查询和使用向量索引执行查询的执行时间,助您体验使用向量索引查询带来的高效和快捷。
|
7月前
|
SQL 关系型数据库 数据库
RDS PostgreSQL索引推荐原理及最佳实践
前言很多开发人员都知道索引对于数据库的查询性能至关重要,一个好的索引能使数据库的性能提升成千上万倍。但给数据库加索引是一项相对专业的工作,需要对数据库的运行原理有一定了解。同时,加了索引有没有性能提升、性能提升了多少,这些都是加索引前就想知道的。这项繁杂的工作有没有更好的方案呢?有!就是今天重磅推出...
121 1
RDS PostgreSQL索引推荐原理及最佳实践
|
3天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
48 15
|
4天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
8天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版