PostgreSQL 里表访问方式有 Bitmap heap scan, 这个访问方式有些特别，要理解它首先得理解 Sequence Scan

Seq Scan

Sequence Scan 可以翻译成全表扫描，如果表上没有任何索引，如果查询这张表将会走 Seq Scan , Seq Scan 将从表的第一行开始顺序扫描，一直扫描到最后满足查询条件的记录。

创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

skytf=> create table test_43(id integer,name varchar(32));  
CREATE TABLE

skytf=> insert into test_43 select generate_series(1,100000),'a';  
INSERT 0 100000

skytf=> select * From test_43 limit 1;  
id | name  
----+------  
 1 | a  
(1 row)

skytf=> /d test_43  
 Table "skytf.test_43"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
name | character varying(32) |

Seq Scan 例子

1
2
3
4
5
6

skytf=> explain select * From test_43 where id=1;  
 QUERY PLAN  
------------------------------------------------------------  
Seq Scan on test_43 (cost=0.00..830.62 rows=155 width=86)  
 Filter: (id = 1)  
(2 rows)

Bitmap Heap Scan

当 PostgreSQL 需要合并索引访问的结果子集时 会用到这种方式 ，通常情况是在用到 “or”，“and” 时会出现“Bitmap heap scan”。

Bitmap heap scan 举例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

skytf=> create index concurrently idx_test_43_id on test_43 using btree ( id );  
CREATE INDEX

skytf=> analyze test_43;  
ANALYZE

skytf=> /d test_43  
 Table "skytf.test_43"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
name | character varying(32) |  
Indexes:  
 "idx_test_43_id" btree (id)  
 "idx_test_43_name" btree (name)
  
skytf=> explain select * from test_43 where id=1 or id=2;  
 QUERY PLAN  
-----------------------------------------------------------------------------------  
Bitmap Heap Scan on test_43 (cost=4.53..8.42 rows=2 width=6)  
 Recheck Cond: ((id = 1) OR (id = 2))  
 -> BitmapOr (cost=4.53..4.53 rows=2 width=0)  
 -> Bitmap Index Scan on idx_test_43_id (cost=0.00..2.26 rows=1 width=0)  
 Index Cond: (id = 1)  
 -> Bitmap Index Scan on idx_test_43_id (cost=0.00..2.26 rows=1 width=0)  
 Index Cond: (id = 2)

备注：以上只是列出 “Bitmap Heap Scan” 的简单情况，在生产环境中，出现“Bitmap Heap Scan” 的情况将会很多，而且更复杂，有些是因为统计信息没有及时生成, 需要具体分析。

“Bitmap Heap Scan” 解释

A plain indexscan fetches one tuple-pointer at a time from the index, and immediately visits that tuple in the table. A bitmap scan fetches all the tuple-pointers from the index in one go, sorts them using an in-memory “bitmap” data structure, and then visits the table tuples in physical tuple-location order. The bitmap scan improves locality of reference to the table at the cost of more bookkeeping overhead to manage the “bitmap” data structure — and at the cost that the data is no longer retrieved in index order, which doesn’t matter for your query but would matter if you said ORDER BY.

上面的意思是说，普通的索引扫描（ index scan） 一次只读一条索引项，那么一个 PAGE面有可能被多次访问；而 bitmap scan 一次性将满足条件的索引项全部取出，并在内存中进行排序, 然后根据取出的索引项访问表数据。