Understanding Bitmap heap scan

PostgreSQL 里表访问方式有 "Bitmap heap scan ", 这个访问方式有些特别，
要理解它首先得理解“Sequence Scan”
 
--1 Seq Scan
"Sequence Scan"  
     可以翻译成全表扫描，如果表上没有任何索引，如果查询这张表将会走 "Seq Scan" ,
     Seq Scan 将从表的第一行开始顺序扫描，一直扫描到最后满足查询条件的记录。
     
     
--2 创建测试表，并插入数据     
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) |


--3 "Seq Scan" 例子
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)


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

--5 "Bitmap heap scan" 举例
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”
             的情况将会很多，而且更复杂，有些是因为统计信息没有及时生成, 需要具体分析。
        

--6 "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 一次性将满足条件的索引项全部取
出，并在内存中进行排序, 然后根据取出的索引项访问表数据。