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)
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)