前言
今天我们来探讨一个有意思的问题，为什么我在PostgreSQL中没有创建位图索引，但是总是会使用Bitmap Heap SCAN这样的执行计划？
谓词包含or的SQL，PG和Oracle的区别
我们先来看一个例子，我们创建一个表A，上面分别有id列和name列，然后插入一些数据，接下来在id和name列分别创建索引。
create table a(id numeric,name varchar(40));insert into a select i, 'aaa' from generate_series (1,1000000) i;insert into a select i, 'bbb' from generate_series (1000001,1000011) i;create unique index idx_a1 on a(id);create index idx_a2 on a(name);postgres=# alter table a alter column name set statistics 10000; analyze a;postgres=# explain analyze select * from a where id =1000001 or name='bbb'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on a (cost=8.95..55.43 rows=12 width=10) (actual time=0.023..0.026 rows=11 loops=1) Recheck Cond: ((id = '1000001'::numeric) OR ((name)::text = 'bbb'::text)) Heap Blocks: exact=1 -> BitmapOr (cost=8.95..8.95 rows=12 width=0) (actual time=0.019..0.020 rows=0 loops=1) -> Bitmap Index Scan on idx_a1 (cost=0.00..4.43 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1) Index Cond: (id = '1000001'::numeric) -> Bitmap Index Scan on idx_a2 (cost=0.00..4.51 rows=11 width=0) (actual time=0.007..0.007 rows=11 loops=1) Index Cond: ((name)::text = 'bbb'::text) Planning Time: 0.200 ms Execution Time: 0.044 ms(10 rows)
可以看到，where条件使用or的情况下，PostgreSQL分别使用了Bitmap Index Scan扫描了2个索引，然后进行了BitmapOr的操作，最后再选择Bitmap Heap Scan扫描堆页面。
而Oracle会怎么操作同样的SQL语句呢？我们在Oracle中执行同样的SQL语句。
SQL> select * from table(dbms_xplan.display_cursor('dpju0ntn95ghb',null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------------------------SQL_ID dpju0ntn95ghb, child number 0-------------------------------------select * from a where id =1000001 or name='bbb'Plan hash value: 141402839---------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 8 || 1 | CONCATENATION | | 1 | | 11 |00:00:00.01 | 8 || 2 | TABLE ACCESS BY INDEX ROWID| A | 1 | 1 | 1 |00:00:00.01 | 4 ||* 3 | INDEX UNIQUE SCAN | IDX_O_A1 | 1 | 1 | 1 |00:00:00.01 | 3 ||* 4 | TABLE ACCESS BY INDEX ROWID| A | 1 | 10 | 10 |00:00:00.01 | 4 ||* 5 | INDEX RANGE SCAN | IDX_O_A2 | 1 | 11 | 11 |00:00:00.01 | 3 |---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("ID"=1000001) 4 - filter(LNNVL("ID"=1000001)) 5 - access("NAME"='bbb')
同样的SQL在Oracle中，会分别扫描索引A1，然后回表，接着扫描索引A2，再一次回表。最后在做CONCATENATION
操作，该操作类似进行去重处理，和UNION ALL差不多。所以Oracle的操作上会回表两次，理论上它的执行方式没有PostgreSQL的方效。
为了让Oracle和PostgreSQL他们一致，简单的方法就是创建两个bitmap索引。于是现在你可以看到它的回表只有一次了。
SQL> select * from table(dbms_xplan.display_cursor('dpju0ntn95ghb',null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------------------------SQL_ID dpju0ntn95ghb, child number 0-------------------------------------select * from a where id =1000001 or name='bbb'Plan hash value: 3538979899---------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 7 || 1 | TABLE ACCESS BY INDEX ROWID | A | 1 | 11 | 11 |00:00:00.01 | 7 || 2 | BITMAP CONVERSION TO ROWIDS| | 1 | | 11 |00:00:00.01 | 5 || 3 | BITMAP OR | | 1 | | 1 |00:00:00.01 | 5 ||* 4 | BITMAP INDEX SINGLE VALUE| IDX_O_A2 | 1 | | 1 |00:00:00.01 | 2 ||* 5 | BITMAP INDEX SINGLE VALUE| IDX_O_A1 | 1 | | 1 |00:00:00.01 | 3 |---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("NAME"='bbb') 5 - access("ID"=1000001)
为什么用Bitmap？
这里你可能会有疑问？我一开始也是非常疑惑的，按照道理来说，PG我们没有创建位图索引，它的行为应该和Oracle类似。后面我查阅了一些资料，我才搞明白了这个问题。
首先这个问题，我们需要科普几个概念。
索引扫描，我们的B-Tree中只包含的是索引数据（键值），这个键值有一个指针来指向堆页面具体位置，在Oracle中叫rowid，在PostgreSQL中叫TID(由页号和其中的偏移量组成)。而对于其他数据，它是需要从堆页面来获取的。所以这种行为我们一般也称之为回表。
因为索引它的指针记录的是堆页面的准确位置（页面号和偏移量）。每次取数据都需要先读一个索引页面，然后根据TID读指向的页面号和偏移量的实际数据，再读下一个索引页面，再根据TID读该页面指向的页面号和偏移量的实际数据，这样一直到读完全部为止。因此，这就导致了一个问题，随机I/O。
那么PostgreSQL为了避免这样的随机I/O，就搞出了一个页面位图，这个页面位图是动态创建的。它直接检索全部的索引行，填充一个包含TID的位图结构，每一个bit位与堆页面一一对应。这样在接下来的扫描过程中，就可以使用Bitmap Heap Scan
读取位图，获取与存储的页号和偏移量相对应的堆上的数据行，最终将结果返回。
以下图来自于图宾根大学的数据库系统：
当有多个索引执行and或者or的操作时，他就会同时读取多个索引，形成位图，位图和位图再做and和or的操作。最后再通过Bitmap Heap Sacn获取堆页面。
后记
实话实说，在这一点上，PostgreSQL优化器是要优于Oracle的。今天这个问题只有搞Oracle的DBA才会有这种困扰吧。
参考链接
1.https://stackoverflow.com/questions/33100637/understanding-bitmap-indexes-in-postgresql
2.https://www.slideshare.net/EnterpriseDB/flexibleindexingwpostgres2014?from_action=save
3.https://rajeevrastogi.blogspot.com/2018/02/bitmap-scan-in-postgresql.html?showComment=1518410565792#c4647352762092142586
4.https://db.inf.uni-tuebingen.de/teaching/DB2SS2018.html
