PostgreSQL查询计划中的路径-BitmapHeapPath-辨析

--------------------------------------------------------------------------------
//首先，创建表和索引（在一个表的两个列上都建立索引）
test=# create table t3(a int, b int);
CREATE TABLE
test=# insert into t3 values(generate_series(1,10000,1), generate_series(2,20000
,2));
INSERT 0 10000
test=# create unique index I_a on t3 (a);
CREATE INDEX
test=# create unique index I_b on t3 (b);
CREATE INDEX

//示例1
 //进行查询，观看查询计划，其中有“Bitmap Heap Scan”表示使用的是“BitmapHeapPath”
test=#  explain select V1.a, V2.b from t3 V1, t3 v2 where (V1.a=V2.b or V1.b=V2.
a) ;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop  (cost=0.54..46064.00 rows=19999 width=8)
   ->  Seq Scan on t3 v1  (cost=0.00..145.00 rows=10000 width=8)
   ->  Bitmap Heap Scan on t3 v2  (cost=0.54..4.56 rows=2 width=8)
         Recheck Cond: ((v1.a = b) OR (v1.b = a))
         ->  BitmapOr  (cost=0.54..0.54 rows=2 width=0)
               ->  Bitmap Index Scan on i_b  (cost=0.00..0.27 rows=1 width=0)
                     Index Cond: (v1.a = b)
               ->  Bitmap Index Scan on i_a  (cost=0.00..0.27 rows=1 width=0)
                     Index Cond: (v1.b = a)
(9 rows)

//示例2
//对比示例1，把条件中的“or”改为“and”，查询计划变为了索引扫描
test=# explain select V1.a, V2.b from t3 V1, t3 v2 where (V1.a=V2.b and V1.b=V2.
a);
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Merge Join  (cost=0.03..639.88 rows=1 width=8)
   Merge Cond: (v1.b = v2.a)
   Join Filter: (v1.a = v2.b)
   ->  Index Scan using i_b on t3 v1  (cost=0.00..318.25 rows=10000 width=8)
   ->  Index Scan using i_a on t3 v2  (cost=0.00..318.25 rows=10000 width=8)
(5 rows)

//示例3
//改变查询条件，但条件中依然使用“or”操作符
test=# explain select V1.a, V2.b from t3 V1, t3 v2 where (V1.a=5000 or V2.b=5000
);
                              QUERY PLAN
-----------------------------------------------------------------------
 Nested Loop  (cost=0.00..1750315.00 rows=19999 width=8)
   Join Filter: ((v1.a = 5000) OR (v2.b = 5000))
   ->  Seq Scan on t3 v1  (cost=0.00..145.00 rows=10000 width=4)
   ->  Materialize  (cost=0.00..195.00 rows=10000 width=4)
         ->  Seq Scan on t3 v2  (cost=0.00..145.00 rows=10000 width=4)
(5 rows)

//示例4
//对比示例3，把条件中的“or”改为“and”，查询计划变为了索引扫描
//问题1：为什么示例3使用的是序列扫描？ 对比示例3和4的cost值，相差很大
test=# explain select V1.a, V2.b from t3 V1, t3 v2 where (V1.a=5000 and V2.b=500
0);
                              QUERY PLAN
-----------------------------------------------------------------------
 Nested Loop  (cost=0.00..16.55 rows=1 width=8)
   ->  Index Scan using i_a on t3 v1  (cost=0.00..8.27 rows=1 width=4)
         Index Cond: (a = 5000)
   ->  Index Scan using i_b on t3 v2  (cost=0.00..8.27 rows=1 width=4)
         Index Cond: (b = 5000)
(5 rows)

//示例5
//对比示例1、2、3、4，增加条件既有“or”也有“and”，查询计划变为了索引扫描且使用了BitmapOr 
test=#  explain select V1.a, V2.b from t3 V1, t3 v2 where (V1.a=V2.b or V1.b=V2.
a) and (V1.a=5000 or V2.b=5000);
                                  QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop  (cost=0.54..46164.00 rows=4 width=8)
   Join Filter: ((v1.a = 5000) OR (v2.b = 5000))
   ->  Seq Scan on t3 v1  (cost=0.00..145.00 rows=10000 width=8)
   ->  Bitmap Heap Scan on t3 v2  (cost=0.54..4.56 rows=2 width=8)
         Recheck Cond: ((v1.a = b) OR (v1.b = a))
         ->  BitmapOr  (cost=0.54..0.54 rows=2 width=0)
               ->  Bitmap Index Scan on i_b  (cost=0.00..0.27 rows=1 width=0)
                     Index Cond: (v1.a = b)
               ->  Bitmap Index Scan on i_a  (cost=0.00..0.27 rows=1 width=0)
                     Index Cond: (v1.b = a)
(10 rows)

//示例6
//对比示例5，把条件中的“or”改为“and”、“and”改为“or”，查询计划变为了索引在“i_b”上的索引扫描，所以， 有多个索引可用时，多个索引组合构成Bitmap
test=# explain select V1.a, V2.b from t3 V1, t3 v2 where (V1.a=V2.b and V1.b=V2.
a) or (V1.a=5000 and V2.b=5000);
                                       QUERY PLAN

--------------------------------------------------------------------------------
---------
 Nested Loop  (cost=0.54..46164.00 rows=2 width=8)
   Join Filter: (((v1.a = v2.b) AND (v1.b = v2.a)) OR ((v1.a = 5000) AND (v2.b =
 5000)))
   ->  Seq Scan on t3 v1  (cost=0.00..145.00 rows=10000 width=8)
   ->  Bitmap Heap Scan on t3 v2  (cost=0.54..4.56 rows=2 width=8)
         Recheck Cond: ((v1.a = b) OR (b = 5000))
         ->  BitmapOr  (cost=0.54..0.54 rows=2 width=0)
               ->  Bitmap Index Scan on i_b  (cost=0.00..0.27 rows=1 width=0)
                     Index Cond: (v1.a = b)
               ->  Bitmap Index Scan on i_b  (cost=0.00..0.27 rows=1 width=0)
                     Index Cond: (b = 5000)
(10 rows)