1、背景

PostgreSQL中扫描的方式有：

  • Sequence Scan：顺序扫描(全表扫描)。按顺序扫描所有页面
  • Index Scan：根据索引列创建的索引进行扫描，速度快
  • Index only Scan：SELECT 表的目标列都在索引键中，为了减少 I/O，仅索引扫描会直接使用索引中的键值

索引扫描在读取表的非索引键的数据时，按照以下顺序：从索引页面获取TID->从heap中取数据->从索引页面获取TID->从heap中取数据 …，索引列是有序的，但不能保证是按照存储的块位置排序，这就有可能导致随机页面访问，从而造成随机I/O。

PostgreSQL设计了Bitmap index Scan(位图索引扫描)，大致思路是：首先根据索引查找，将取出所有符合条件的block id在内存中进行排序，一次性的顺序的读取page。这样既使用了索引查找快捷的特点，又避免了随机I/O。


  • PostgreSQL（PG）：
  • 在PG中，Bitmap Index Scan是对索引扫描的一种优化方式，通过建立位图的方式将原来的随机堆表访问转换成顺序堆表访问。它首先通过索引扫描获取满足条件的TID（Tuple Identifier，元组标识符），然后构建位图，最后根据位图顺序访问堆表中的数据块，并在必要时进行过滤（recheck）以剔除不满足条件的记录。
  • Bitmap Index Scan在PG中主要用于处理复杂查询，特别是涉及多个条件或大量数据的查询，能够有效减少I/O消耗和提高查询效率。
  • Oracle：
  • Oracle数据库同样支持Bitmap Index，但Oracle的Bitmap Index和Bitmap Index Scan的具体实现可能与PG有所不同。Oracle的Bitmap Index允许在索引中直接存储位图，这些位图表示了表中满足索引条件的行的存在情况。
  • Oracle的Bitmap Index Scan利用这些位图来快速定位满足查询条件的行，并减少访问数据表时所需读取的数据量。然而，需要注意的是，Oracle的Bitmap Index和Bitmap Index Scan的具体行为和性能特性可能会随着Oracle数据库版本的更新而发生变化。


2、原理

在观察执行计划的时候。如果使用的是位图索引扫描，我们通常会发现Bitmap Index Scan嵌套在内层，Bitmap Heap Scan在外层，如下：

PostgreSQL Bitmap Scan_位图

在PostgreSQL中，位图扫描分为两个阶段：

  • ==Bitmap Index Scan 第一阶段==：执行 index scan并创建位图。根据where条件的索引列，在索引中找出符合条件的行所在的page，并在内存中进行排序并创建bitmap。当找到与搜索条件匹配的索引条目时，将该位设置为 1(true)，否则设置为0(false)
  • ==Bitmap Heap Scan 第二阶段==：按照第一阶段创建的位图，从左到右顺序的读取标记为1的heap page，0的跳过


1）Row - Level

  • 前提：work_mem足够大
  • 步骤：根据匹配索引的row id构建位图，在顺序读取位图时，获取到匹配的行

2）Page - Level

  • 前提：work_mem不足以放下所有行
  • 思路：取得符合条件的所有行号, 获得对应的block id，将block id在内存中排序并构建位图。根据位图顺序从heap 表搜索数据

⚠️注意：由于构建的是block的位图，在扫描heap page时，需要进行recheck操作，从堆表页面中筛选出符合条件的row


3、案例

构造测试数据

create table bms_test(a int,b int,c text) ;

insert into bms_test select i,ceil(random()*50),'test_'||i from generate_series(1,1000000) i; 

create index idx_bms_b on bms_test(b) ;

create index idx_bms_c on bms_test(c) ;

analyze ;

1）Row - Level

设置 work_mem = 64MB

  • Bitmap Index Scan阶段根据b = 44进行索引扫描，并构建位图
  • Bitmap Heap Scan阶段根据构建的位图进行顺序读取，有6114个block

如果是Row -Level的位图扫描，Recheck阶段可以省去

testdb=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

testdb=# set work_mem = '64MB';
SET

testdb=# show work_mem;
 work_mem 
----------
 64MB
(1 row)

testdb=# explain (analyze) select * from bms_test where b = 44 ;

                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on bms_test  (cost=380.46..7002.12 rows=20133 width=19) (actual time=2.404..11.359 rows=19978 loops=1)
   Recheck Cond: (b = 44)
   Heap Blocks: exact=6114
   ->  Bitmap Index Scan on idx_bms_b  (cost=0.00..375.42 rows=20133 width=0) (actual time=1.585..1.585 rows=19978 loops=1)
         Index Cond: (b = 44)
 Planning Time: 0.065 ms
 Execution Time: 11.985 ms
(7 rows)

2）Page - Level

将work_mem设置值较小

  • Bitmap Index Scan阶段根据b = 44进行索引扫描并构建位图（这里work_mem不足以放下构建的位图）
  • Bitmap Heap Scan阶段根据构建的位图进行顺序读取。

有 343 个block是精准的记录匹配的元组，而由于work_mem太小，有2055个block标记成"lossy"，

只记录哪些页面包含匹配的元组，而不是单独记住每个元组。在执行recheck阶段后，必须检查页面上的每个元组并重新检查扫描条件以查看要返回哪些元组，可以看到Rows Removed by Index Recheck: 264077

PG 12.2 版本

testdb=# set work_mem = '64kB';      
SET
testdb=# explain (analyze) select * from bms_test where b = 44 ;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1380.46..14561.89 rows=20133 width=19) (actual time=2.994..56.617 rows=19978 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Bitmap Heap Scan on bms_test  (cost=380.46..11548.59 rows=8389 width=19) (actual time=0.822..41.164 rows=6659 loops=3)
         Recheck Cond: (b = 44)
         Rows Removed by Index Recheck: 264077
         Heap Blocks: exact=343 lossy=2055
         ->  Bitmap Index Scan on idx_bms_b  (cost=0.00..375.42 rows=20133 width=0) (actual time=2.204..2.204 rows=19978 loops=1)
               Index Cond: (b = 44)
 Planning Time: 0.068 ms
 Execution Time: 57.470 ms
(11 rows)

MOGDB 5.0.1 

QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on bms_test  (cost=381.06..18667.38 rows=20233 width=19) (actual time=3.436..120.575 rows=19913 loops=1)
   Recheck Cond: (b = 44)
   Rows Removed by Index Recheck: 882748
   Heap Blocks: exact=355 lossy=5779
   ->  Bitmap Index Scan on idx_bms_b  (cost=0.00..376.00 rows=20233 width=0) (actual time=3.352..3.352 rows=19913 loops=1)
         Index Cond: (b = 44)
 Total runtime: 122.018 ms
(7 rows)

4、小结

  • bitmap scan目的是通过建立位图的方式，避免index scan带来的随机I/O，从而减少查询过程中的I/O消耗
  • Bitmap index Scan是在内存中完成，借助B-Tree索引实现，不占用空间，随用随构建，用完即删。
  • Bitmap Index Scan阶段是要从index中得到满足条件的tuple的heap block id。并给block id排序，构建位图；然后进入Bitmap Heap Scan阶段按照构建的位图顺序扫描heap page。
  • 位图会记录row id（row - level），但如果bitmap太大，会将位图转换成“lossy”样式（page - level）：只记录block id，所以需要==recheck==，读取heap page时需重新检查扫描条件找出匹配的元组
  • 在数据量比较大的情况下，走bitmap scan虽然可以减少I/O代价，但不能忽视recheck阶段所带来的损耗


参考

https://blog.51cto.com/u_15162069/2772460

https://www.postgresql.org/message-id/12553.1135634231@sss.pgh.pa.us