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

本文链接：https://blog.csdn.net/fly2nn/article/details/61924316

--------------------------------------------------------------------------------
//首先，创建表和索引（在一个表的两个列上都建立索引）
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)