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

来源：互联网发布：如何对待网络语言编辑：程序博客网时间：2024/06/07 01:27

create table t5(a int, b int);
create table t6(a int, b int);
insert into t5 values(generate_series(1,10000,1), generate_series(2,20000,2));
insert into t6 values(generate_series(1,10000,1), generate_series(2,20000,2));
create unique index I_t5 on t5 (a);
create unique index I_t6 on t6 (b);

test=# explain select V1.a, V2.b from t5 V1, t6 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 t5 v1 (cost=0.00..145.00 rows=10000 width=8)
   -> Bitmap Heap Scan on t6 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_t6 (cost=0.00..0.27 rows=1 width=0)
                     Index Cond: (v1.a = b)
               -> Bitmap Index Scan on i_t6 (cost=0.00..0.27 rows=1 width=0)
                     Index Cond: (b = 5000)
(10 rows)

test=# explain select V1.a, V2.b from t5 V1, t6 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.00..2250315.00 rows=4 width=8)
   Join Filter: (((v1.a = v2.b) OR (v1.b = v2.a)) AND ((v1.a = 5000) OR (v2.b =5000)))
   -> Seq Scan on t5 v1 (cost=0.00..145.00 rows=10000 width=8)
   -> Materialize (cost=0.00..195.00 rows=10000 width=8)
         -> Seq Scan on t6 v2 (cost=0.00..145.00 rows=10000 width=8)
(5 rows)

0 0