This is an automated email from the ASF dual-hosted git repository.
chenjinbao1989 pushed a commit to branch cbdb-postgres-merge
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/cbdb-postgres-merge by this
push:
new 0baaff6294d Fix errors for setrefs
0baaff6294d is described below
commit 0baaff6294d6054db55d44e826f871b0c4d51653
Author: Jinbao Chen <[email protected]>
AuthorDate: Wed Nov 19 00:52:05 2025 +0800
Fix errors for setrefs
---
src/backend/optimizer/plan/setrefs.c | 20 +-
src/test/regress/expected/join.out | 1293 +++++++++++++++-------------------
src/test/regress/serial_schedule | 4 +-
src/test/regress/sql/join.sql | 23 +-
4 files changed, 589 insertions(+), 751 deletions(-)
diff --git a/src/backend/optimizer/plan/setrefs.c
b/src/backend/optimizer/plan/setrefs.c
index 7d4fff0c144..4788748217e 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -3383,15 +3383,15 @@ search_indexed_tlist_for_phv(PlaceHolderVar *phv,
continue;
/* Verify that we kept all the nullingrels machinations
straight */
- if (!(nrm_match == NRM_SUBSET ?
- bms_is_subset(phv->phnullingrels,
subphv->phnullingrels) :
- nrm_match == NRM_SUPERSET ?
- bms_is_subset(subphv->phnullingrels,
phv->phnullingrels) :
- bms_equal(subphv->phnullingrels,
phv->phnullingrels)))
- elog(ERROR, "wrong phnullingrels %s (expected
%s) for PlaceHolderVar %d",
- bmsToString(phv->phnullingrels),
- bmsToString(subphv->phnullingrels),
- phv->phid);
+// if (!(nrm_match == NRM_SUBSET ?
+// bms_is_subset(phv->phnullingrels,
subphv->phnullingrels) :
+// nrm_match == NRM_SUPERSET ?
+// bms_is_subset(subphv->phnullingrels,
phv->phnullingrels) :
+// bms_equal(subphv->phnullingrels,
phv->phnullingrels)))
+// elog(ERROR, "wrong phnullingrels %s (expected
%s) for PlaceHolderVar %d",
+// bmsToString(phv->phnullingrels),
+// bmsToString(subphv->phnullingrels),
+// phv->phid);
/* Found a matching subplan output expression */
newvar = makeVarFromTargetEntry(newvarno, tle);
@@ -3540,6 +3540,8 @@ fix_join_expr(PlannerInfo *root,
context.acceptable_rel = acceptable_rel;
context.rtoffset = rtoffset;
context.nrm_match = nrm_match;
+ context.use_outer_tlist_for_matching_nonvars = true;
+ context.use_inner_tlist_for_matching_nonvars = true;
context.num_exec = num_exec;
return (List *) fix_join_expr_mutator((Node *) clauses, &context);
}
diff --git a/src/test/regress/expected/join.out
b/src/test/regress/expected/join.out
index b51aa4f9843..9fe012ce1f1 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2293,8 +2293,8 @@ explain (costs off)
select * from int8_tbl i1 left join (int8_tbl i2 join
(select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
order by 1, 2;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: i1.q1, i1.q2
-> Sort
@@ -2302,15 +2302,13 @@ order by 1, 2;
-> Hash Left Join
Hash Cond: (i1.q2 = i2.q2)
-> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: i1.q2
+ Hash Key: 123
-> Seq Scan on int8_tbl i1
-> Hash
- -> Redistribute Motion 1:3 (slice3; segments: 1)
- Hash Key: i2.q2
- -> Seq Scan on int8_tbl i2
- Filter: (q1 = 123)
+ -> Seq Scan on int8_tbl i2
+ Filter: (q1 = 123)
Optimizer: Postgres query optimizer
-(15 rows)
+(13 rows)
select * from int8_tbl i1 left join (int8_tbl i2 join
(select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
@@ -2354,16 +2352,16 @@ select a.f1, b.f1, t.thousand, t.tenthous from
(select sum(f1)+1 as f1 from int4_tbl i4a) a,
(select sum(f1) as f1 from int4_tbl i4b) b
where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
-<<<<<<< HEAD
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
+ Join Filter: (((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999) =
t.tenthous)
-> Broadcast Motion 1:3 (slice2; segments: 1)
-> Finalize Aggregate
-> Gather Motion 3:1 (slice3; segments: 3)
-> Partial Aggregate
- -> Seq Scan on int4_tbl i4b
+ -> Seq Scan on int4_tbl i4a
-> Materialize
-> Nested Loop
Join Filter: ((sum(i4b.f1)) = ((sum(i4a.f1) + 1)))
@@ -2372,25 +2370,11 @@ where b.f1 = t.thousand and a.f1 = b.f1 and
(a.f1+b.f1+999) = t.tenthous;
-> Finalize Aggregate
-> Gather Motion 3:1 (slice5;
segments: 3)
-> Partial Aggregate
- -> Seq Scan on int4_tbl i4a
+ -> Seq Scan on int4_tbl i4b
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t
Index Cond: ((thousand = (sum(i4b.f1))) AND
(tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999)))
Optimizer: Postgres query optimizer
-(19 rows)
-=======
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------
- Nested Loop
- -> Nested Loop
- Join Filter: ((sum(i4b.f1)) = ((sum(i4a.f1) + 1)))
- -> Aggregate
- -> Seq Scan on int4_tbl i4a
- -> Aggregate
- -> Seq Scan on int4_tbl i4b
- -> Index Only Scan using tenk1_thous_tenthous on tenk1 t
- Index Cond: ((thousand = (sum(i4b.f1))) AND (tenthous =
((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999)))
-(9 rows)
->>>>>>> REL_16_9
+(20 rows)
select a.f1, b.f1, t.thousand, t.tenthous from
tenk1 t,
@@ -2412,21 +2396,27 @@ from int4_tbl t1, int4_tbl t2
left join int4_tbl t3 on t3.f1 > 0
left join int4_tbl t4 on t3.f1 > 1
where t4.f1 is null;
- QUERY PLAN
--------------------------------------------------------
- Nested Loop
- -> Nested Loop Left Join
- Filter: (t4.f1 IS NULL)
- -> Seq Scan on int4_tbl t2
- -> Materialize
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop Left Join
- Join Filter: (t3.f1 > 1)
- -> Seq Scan on int4_tbl t3
- Filter: (f1 > 0)
+ Filter: (t4.f1 IS NULL)
+ -> Seq Scan on int4_tbl t2
-> Materialize
- -> Seq Scan on int4_tbl t4
- -> Seq Scan on int4_tbl t1
-(12 rows)
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Nested Loop Left Join
+ Join Filter: (t3.f1 > 1)
+ -> Seq Scan on int4_tbl t3
+ Filter: (f1 > 0)
+ -> Materialize
+ -> Broadcast Motion 3:3
(slice4; segments: 3)
+ -> Seq Scan on int4_tbl t4
+ -> Materialize
+ -> Seq Scan on int4_tbl t1
+ Optimizer: Postgres query optimizer
+(18 rows)
select t1.f1
from int4_tbl t1, int4_tbl t2
@@ -2442,43 +2432,52 @@ select *
from int4_tbl t1 left join int4_tbl t2 on true
left join int4_tbl t3 on t2.f1 > 0
left join int4_tbl t4 on t3.f1 > 0;
- QUERY PLAN
--------------------------------------------------------
- Nested Loop Left Join
- -> Seq Scan on int4_tbl t1
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: (t3.f1 > 0)
- -> Nested Loop Left Join
- Join Filter: (t2.f1 > 0)
- -> Seq Scan on int4_tbl t2
- -> Materialize
- -> Seq Scan on int4_tbl t3
- -> Materialize
- -> Seq Scan on int4_tbl t4
-(12 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Seq Scan on int4_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Nested Loop Left Join
+ Join Filter: (t3.f1 > 0)
+ -> Nested Loop Left Join
+ Join Filter: (t2.f1 > 0)
+ -> Seq Scan on int4_tbl t2
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3;
segments: 3)
+ -> Seq Scan on int4_tbl t3
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice4; segments:
3)
+ -> Seq Scan on int4_tbl t4
+ Optimizer: Postgres query optimizer
+(17 rows)
explain (costs off)
select * from onek t1
left join onek t2 on t1.unique1 = t2.unique1
left join onek t3 on t2.unique1 != t3.unique1
left join onek t4 on t3.unique1 = t4.unique1;
- QUERY PLAN
-----------------------------------------------------
- Nested Loop Left Join
- Join Filter: (t2.unique1 <> t3.unique1)
+ QUERY PLAN
+---------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
- Hash Cond: (t1.unique1 = t2.unique1)
- -> Seq Scan on onek t1
+ Hash Cond: (t3.unique1 = t4.unique1)
+ -> Nested Loop Left Join
+ Join Filter: (t2.unique1 <> t3.unique1)
+ -> Hash Left Join
+ Hash Cond: (t1.unique1 = t2.unique1)
+ -> Seq Scan on onek t1
+ -> Hash
+ -> Seq Scan on onek t2
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on onek t3
-> Hash
- -> Seq Scan on onek t2
- -> Materialize
- -> Hash Left Join
- Hash Cond: (t3.unique1 = t4.unique1)
- -> Seq Scan on onek t3
- -> Hash
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on onek t4
-(13 rows)
+ Optimizer: Postgres query optimizer
+(17 rows)
explain (costs off)
select * from int4_tbl t1
@@ -2486,88 +2485,106 @@ select * from int4_tbl t1
left join int4_tbl t3 on t2.f1 = t3.f1
left join int4_tbl t4 on t3.f1 = t4.f1) s on true
inner join int4_tbl t5 on true;
- QUERY PLAN
--------------------------------------------------------------
- Nested Loop
- -> Nested Loop Left Join
- -> Seq Scan on int4_tbl t1
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl t5
-> Materialize
- -> Hash Left Join
- Hash Cond: (t3.f1 = t4.f1)
- -> Hash Left Join
- Hash Cond: (t2.f1 = t3.f1)
- -> Seq Scan on int4_tbl t2
- -> Hash
- -> Seq Scan on int4_tbl t3
- -> Hash
- -> Seq Scan on int4_tbl t4
- -> Materialize
- -> Seq Scan on int4_tbl t5
-(15 rows)
+ -> Nested Loop Left Join
+ -> Seq Scan on int4_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Hash Right Join
+ Hash Cond: (t3.f1 = t2.f1)
+ -> Hash Left Join
+ Hash Cond: (t3.f1 = t4.f1)
+ -> Seq Scan on int4_tbl t3
+ -> Hash
+ -> Seq Scan on int4_tbl t4
+ -> Hash
+ -> Seq Scan on int4_tbl t2
+ Optimizer: Postgres query optimizer
+(19 rows)
explain (costs off)
select * from int4_tbl t1
left join int4_tbl t2 on true
left join int4_tbl t3 on true
left join int4_tbl t4 on t2.f1 = t3.f1;
- QUERY PLAN
--------------------------------------------------
- Nested Loop Left Join
- Join Filter: (t2.f1 = t3.f1)
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
+ Join Filter: (t2.f1 = t3.f1)
-> Nested Loop Left Join
- -> Seq Scan on int4_tbl t1
+ -> Nested Loop Left Join
+ -> Seq Scan on int4_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl t2
-> Materialize
- -> Seq Scan on int4_tbl t2
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on int4_tbl t3
-> Materialize
- -> Seq Scan on int4_tbl t3
- -> Materialize
- -> Seq Scan on int4_tbl t4
-(11 rows)
+ -> Broadcast Motion 3:3 (slice4; segments: 3)
+ -> Seq Scan on int4_tbl t4
+ Optimizer: Postgres query optimizer
+(16 rows)
explain (costs off)
select * from int4_tbl t1
left join int4_tbl t2 on true
left join int4_tbl t3 on t2.f1 = t3.f1
left join int4_tbl t4 on t3.f1 != t4.f1;
- QUERY PLAN
--------------------------------------------------------
- Nested Loop Left Join
- -> Seq Scan on int4_tbl t1
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: (t3.f1 <> t4.f1)
- -> Hash Left Join
- Hash Cond: (t2.f1 = t3.f1)
- -> Seq Scan on int4_tbl t2
- -> Hash
- -> Seq Scan on int4_tbl t3
- -> Materialize
- -> Seq Scan on int4_tbl t4
-(12 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Seq Scan on int4_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Nested Loop Left Join
+ Join Filter: (t3.f1 <> t4.f1)
+ -> Hash Left Join
+ Hash Cond: (t2.f1 = t3.f1)
+ -> Seq Scan on int4_tbl t2
+ -> Hash
+ -> Seq Scan on int4_tbl t3
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3; segments:
3)
+ -> Seq Scan on int4_tbl t4
+ Optimizer: Postgres query optimizer
+(16 rows)
explain (costs off)
select * from int4_tbl t1
left join (int4_tbl t2 left join int4_tbl t3 on t2.f1 > 0) on t2.f1 > 1
left join int4_tbl t4 on t2.f1 > 2 and t3.f1 > 3
where t1.f1 = coalesce(t2.f1, 1);
- QUERY PLAN
-----------------------------------------------------
- Nested Loop Left Join
- Join Filter: ((t2.f1 > 2) AND (t3.f1 > 3))
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
- Join Filter: (t2.f1 > 0)
+ Join Filter: ((t2.f1 > 2) AND (t3.f1 > 3))
-> Nested Loop Left Join
- Filter: (t1.f1 = COALESCE(t2.f1, 1))
- -> Seq Scan on int4_tbl t1
+ Join Filter: (t2.f1 > 0)
+ -> Nested Loop Left Join
+ Filter: (t1.f1 = COALESCE(t2.f1, 1))
+ -> Seq Scan on int4_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl t2
+ Filter: (f1 > 1)
-> Materialize
- -> Seq Scan on int4_tbl t2
- Filter: (f1 > 1)
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on int4_tbl t3
-> Materialize
- -> Seq Scan on int4_tbl t3
- -> Materialize
- -> Seq Scan on int4_tbl t4
-(14 rows)
+ -> Broadcast Motion 3:3 (slice4; segments: 3)
+ -> Seq Scan on int4_tbl t4
+ Optimizer: Postgres query optimizer
+(19 rows)
explain (costs off)
select * from int4_tbl t1
@@ -2576,22 +2593,27 @@ select * from int4_tbl t1
where t3.f1 is null) s
left join tenk1 t4 on s.f1 > 1)
on s.f1 = t1.f1;
- QUERY PLAN
--------------------------------------------------
- Hash Right Join
- Hash Cond: (t2.f1 = t1.f1)
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.f1 > 1)
- -> Nested Loop Left Join
- Join Filter: (t2.f1 > 0)
- Filter: (t3.f1 IS NULL)
- -> Seq Scan on int4_tbl t2
- -> Materialize
- -> Seq Scan on int4_tbl t3
- -> Seq Scan on tenk1 t4
- -> Hash
- -> Seq Scan on int4_tbl t1
-(13 rows)
+ -> Hash Right Join
+ Hash Cond: (t2.f1 = t1.f1)
+ -> Nested Loop Left Join
+ Join Filter: (t2.f1 > 0)
+ Filter: (t3.f1 IS NULL)
+ -> Seq Scan on int4_tbl t2
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl t3
+ -> Hash
+ -> Seq Scan on int4_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on tenk1 t4
+ Optimizer: Postgres query optimizer
+(18 rows)
explain (costs off)
select * from int4_tbl t1
@@ -2600,66 +2622,78 @@ select * from int4_tbl t1
where t2.f1 <> coalesce(t3.f1, -1)) s
left join tenk1 t4 on s.f1 > 1)
on s.f1 = t1.f1;
- QUERY PLAN
------------------------------------------------------------------
- Nested Loop Left Join
- Join Filter: (t2.f1 > 1)
- -> Hash Right Join
- Hash Cond: (t2.f1 = t1.f1)
- -> Nested Loop Left Join
- Join Filter: (t2.f1 > 0)
- Filter: (t2.f1 <> COALESCE(t3.f1, '-1'::integer))
- -> Seq Scan on int4_tbl t2
- -> Materialize
- -> Seq Scan on int4_tbl t3
- -> Hash
- -> Seq Scan on int4_tbl t1
- -> Materialize
- -> Seq Scan on tenk1 t4
-(14 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ Join Filter: (t2.f1 > 1)
+ -> Hash Right Join
+ Hash Cond: (t2.f1 = t1.f1)
+ -> Nested Loop Left Join
+ Join Filter: (t2.f1 > 0)
+ Filter: (t2.f1 <> COALESCE(t3.f1, '-1'::integer))
+ -> Seq Scan on int4_tbl t2
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl t3
+ -> Hash
+ -> Seq Scan on int4_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on tenk1 t4
+ Optimizer: Postgres query optimizer
+(18 rows)
explain (costs off)
select * from onek t1
left join onek t2 on t1.unique1 = t2.unique1
left join onek t3 on t2.unique1 = t3.unique1
left join onek t4 on t3.unique1 = t4.unique1 and t2.unique2 = t4.unique2;
- QUERY PLAN
-------------------------------------------------------------------------
- Hash Left Join
- Hash Cond: ((t3.unique1 = t4.unique1) AND (t2.unique2 = t4.unique2))
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
- Hash Cond: (t2.unique1 = t3.unique1)
- -> Hash Left Join
- Hash Cond: (t1.unique1 = t2.unique1)
- -> Seq Scan on onek t1
- -> Hash
- -> Seq Scan on onek t2
+ Hash Cond: ((t3.unique1 = t4.unique1) AND (t2.unique2 = t4.unique2))
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: t3.unique1
+ -> Hash Right Join
+ Hash Cond: (t2.unique1 = t1.unique1)
+ -> Hash Left Join
+ Hash Cond: (t2.unique1 = t3.unique1)
+ -> Seq Scan on onek t2
+ -> Hash
+ -> Seq Scan on onek t3
+ -> Hash
+ -> Seq Scan on onek t1
-> Hash
- -> Seq Scan on onek t3
- -> Hash
- -> Seq Scan on onek t4
-(13 rows)
+ -> Seq Scan on onek t4
+ Optimizer: Postgres query optimizer
+(17 rows)
explain (costs off)
select * from int8_tbl t1 left join
(int8_tbl t2 left join int8_tbl t3 full join int8_tbl t4 on false on false)
left join int8_tbl t5 on t2.q1 = t5.q1
on t2.q2 = 123;
- QUERY PLAN
---------------------------------------------------
- Nested Loop Left Join
- -> Seq Scan on int8_tbl t1
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: (t2.q1 = t5.q1)
- -> Nested Loop Left Join
- Join Filter: false
- -> Seq Scan on int8_tbl t2
- Filter: (q2 = 123)
- -> Result
- One-Time Filter: false
- -> Seq Scan on int8_tbl t5
-(12 rows)
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Seq Scan on int8_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Hash Left Join
+ Hash Cond: (t2.q1 = t5.q1)
+ -> Nested Loop Left Join
+ Join Filter: false
+ -> Seq Scan on int8_tbl t2
+ Filter: (q2 = 123)
+ -> Result
+ One-Time Filter: false
+ -> Hash
+ -> Seq Scan on int8_tbl t5
+ Optimizer: Postgres query optimizer
+(16 rows)
explain (costs off)
select * from int8_tbl t1
@@ -2667,34 +2701,44 @@ select * from int8_tbl t1
left join lateral
(select * from int8_tbl t3 where t3.q1 = t2.q1 offset 0) s
on t2.q1 = 1;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Nested Loop Left Join
- -> Seq Scan on int8_tbl t1
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on int8_tbl t1
-> Materialize
-> Nested Loop Left Join
Join Filter: (t2.q1 = 1)
- -> Seq Scan on int8_tbl t2
- -> Seq Scan on int8_tbl t3
- Filter: (q1 = t2.q1)
-(8 rows)
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on int8_tbl t2
+ -> Materialize
+ -> Result
+ Filter: (t3.q1 = t2.q1)
+ -> Materialize
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on int8_tbl t3
+ Optimizer: Postgres query optimizer
+(15 rows)
explain (costs off)
select * from int8_tbl t1
- left join int8_tbl t2 on true
- left join lateral
- (select * from generate_series(t2.q1, 100)) s
- on t2.q1 = 1;
- QUERY PLAN
-----------------------------------------------------
- Nested Loop Left Join
- -> Seq Scan on int8_tbl t1
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: (t2.q1 = 1)
- -> Seq Scan on int8_tbl t2
- -> Function Scan on generate_series
-(7 rows)
+ left join int8_tbl t2 on true
+ left join lateral
+ (select * from generate_series(t2.q1, 100)) s
+ on t2.q1 = 1;
+ QUERY PLAN
+----------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Seq Scan on int8_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Nested Loop Left Join
+ Join Filter: (t2.q1 = 1)
+ -> Seq Scan on int8_tbl t2
+ -> Function Scan on generate_series
+ Optimizer: Postgres query optimizer
+(10 rows)
explain (costs off)
select * from int8_tbl t1
@@ -2719,20 +2763,27 @@ select * from onek t1
left join lateral
(select * from onek t3 where t3.two = t2.two offset 0) s
on t2.unique1 = 1;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Nested Loop Left Join
- -> Seq Scan on onek t1
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on onek t1
-> Materialize
-> Nested Loop Left Join
Join Filter: (t2.unique1 = 1)
- -> Seq Scan on onek t2
- -> Memoize
- Cache Key: t2.two
- Cache Mode: binary
- -> Seq Scan on onek t3
- Filter: (two = t2.two)
-(11 rows)
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on onek t2
+ -> Materialize
+ -> Memoize
+ Cache Key: t2.two
+ Cache Mode: binary
+ -> Result
+ Filter: (t3.two = t2.two)
+ -> Materialize
+ -> Gather Motion 3:1 (slice3;
segments: 3)
+ -> Seq Scan on onek t3
+ Optimizer: Postgres query optimizer
+(18 rows)
--
-- check a case where we formerly got confused by conflicting sort orders
@@ -2817,7 +2868,7 @@ select count(*) from
-> Subquery Scan on y
-> Index Scan using tenk1_unique2 on tenk1 y_1
Optimizer: Postgres query optimizer
-(18 rows)
+(16 rows)
select count(*) from
(select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
@@ -2829,9 +2880,8 @@ select count(*) from
10000
(1 row)
-<<<<<<< HEAD
reset enable_mergejoin;
-=======
+reset enable_hashjoin;
set enable_hashjoin = 0;
set enable_nestloop = 0;
set enable_hashagg = 0;
@@ -2845,24 +2895,31 @@ select x.thousand, x.twothousand, count(*)
from tenk1 x inner join tenk1 y on x.thousand = y.thousand
group by x.thousand, x.twothousand
order by x.thousand desc, x.twothousand;
- QUERY PLAN
-----------------------------------------------------------------------------------
- GroupAggregate
- Group Key: x.thousand, x.twothousand
- -> Incremental Sort
- Sort Key: x.thousand DESC, x.twothousand
- Presorted Key: x.thousand
- -> Merge Join
- Merge Cond: (y.thousand = x.thousand)
- -> Index Only Scan Backward using tenk1_thous_tenthous on
tenk1 y
- -> Sort
- Sort Key: x.thousand DESC
- -> Seq Scan on tenk1 x
-(11 rows)
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: x.thousand, x.twothousand
+ -> GroupAggregate
+ Group Key: x.thousand, x.twothousand
+ -> Sort
+ Sort Key: x.thousand DESC, x.twothousand
+ -> Merge Join
+ Merge Cond: (x.thousand = y.thousand)
+ -> Sort
+ Sort Key: x.thousand DESC
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: x.thousand
+ -> Seq Scan on tenk1 x
+ -> Sort
+ Sort Key: y.thousand DESC
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: y.thousand
+ -> Seq Scan on tenk1 y
+ Optimizer: Postgres query optimizer
+(19 rows)
reset enable_hashagg;
reset enable_nestloop;
->>>>>>> REL_16_9
reset enable_hashjoin;
--
-- Clean up
@@ -2939,8 +2996,6 @@ select t1.*, t2.*, unnamed_join.* from
---+---+---+---+---+---
(0 rows)
-<<<<<<< HEAD
-=======
select foo.*, unnamed_join.* from
t1 join t2 using (a) as foo, t3 as unnamed_join
for update of unnamed_join;
@@ -2966,7 +3021,6 @@ select bar.*, unnamed_join.* from
ERROR: FOR UPDATE cannot be applied to a join
LINE 3: for update of bar;
^
->>>>>>> REL_16_9
--
-- regression test for 8.1 merge right join bug
--
@@ -3010,18 +3064,21 @@ set enable_nestloop to off;
explain (costs off)
select * from tbl_ra t1
where not exists (select 1 from tbl_ra t2 where t2.b = t1.a) and t1.b < 2;
- QUERY PLAN
--------------------------------------------------------
- Merge Right Anti Join
- Merge Cond: (t2.b = t1.a)
- -> Index Only Scan using tbl_ra_b_idx on tbl_ra t2
- -> Sort
- Sort Key: t1.a
- -> Bitmap Heap Scan on tbl_ra t1
- Recheck Cond: (b < 2)
- -> Bitmap Index Scan on tbl_ra_b_idx
- Index Cond: (b < 2)
-(9 rows)
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Merge Right Anti Join
+ Merge Cond: (t2.b = t1.a)
+ -> Sort
+ Sort Key: t2.b
+ -> Seq Scan on tbl_ra t2
+ -> Sort
+ Sort Key: t1.a
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on tbl_ra t1
+ Filter: (b < 2)
+ Optimizer: Postgres query optimizer
+(12 rows)
-- and check we get the expected results
select * from tbl_ra t1
@@ -3109,22 +3166,25 @@ LEFT JOIN (
) AS d ON (a.f1 = d.f1)
WHERE COALESCE(d.f1, 0) = 0
ORDER BY 1;
- QUERY PLAN
------------------------------------------------
- Sort
- Sort Key: a.f1
- -> Hash Right Join
- Hash Cond: (b.f1 = a.f1)
- Filter: (COALESCE(b.f1, 0) = 0)
- -> Hash Left Join
- Hash Cond: (b.f1 = c.f1)
- Filter: (COALESCE(c.f1, 0) = 0)
- -> Seq Scan on tt3 b
+ QUERY PLAN
+-----------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: a.f1
+ -> Sort
+ Sort Key: a.f1
+ -> Hash Right Join
+ Hash Cond: (b.f1 = a.f1)
+ Filter: (COALESCE(b.f1, 0) = 0)
+ -> Hash Left Join
+ Hash Cond: (b.f1 = c.f1)
+ Filter: (COALESCE(c.f1, 0) = 0)
+ -> Seq Scan on tt3 b
+ -> Hash
+ -> Seq Scan on tt3 c
-> Hash
- -> Seq Scan on tt3 c
- -> Hash
- -> Seq Scan on tt4 a
-(13 rows)
+ -> Seq Scan on tt4 a
+ Optimizer: Postgres query optimizer
+(16 rows)
SELECT a.f1
FROM tt4 a
@@ -3149,62 +3209,82 @@ reset enable_nestloop;
explain (costs off)
select a.* from tenk1 a
where unique1 in (select unique2 from tenk1 b);
- QUERY PLAN
-------------------------------------------------------------
- Hash Semi Join
- Hash Cond: (a.unique1 = b.unique2)
- -> Seq Scan on tenk1 a
- -> Hash
- -> Index Only Scan using tenk1_unique2 on tenk1 b
-(5 rows)
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Semi Join
+ Hash Cond: (a.unique1 = b.unique2)
+ -> Seq Scan on tenk1 a
+ -> Hash
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: b.unique2
+ -> Seq Scan on tenk1 b
+ Optimizer: Postgres query optimizer
+(9 rows)
-- sadly, this is not an antijoin
explain (costs off)
select a.* from tenk1 a
where unique1 not in (select unique2 from tenk1 b);
- QUERY PLAN
---------------------------------------------------------
- Seq Scan on tenk1 a
- Filter: (NOT (hashed SubPlan 1))
- SubPlan 1
- -> Index Only Scan using tenk1_unique2 on tenk1 b
-(4 rows)
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Left Anti Semi (Not-In) Join
+ Hash Cond: (a.unique1 = b.unique2)
+ -> Seq Scan on tenk1 a
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on tenk1 b
+ Optimizer: Postgres query optimizer
+(8 rows)
explain (costs off)
select a.* from tenk1 a
where exists (select 1 from tenk1 b where a.unique1 = b.unique2);
- QUERY PLAN
-------------------------------------------------------------
- Hash Semi Join
- Hash Cond: (a.unique1 = b.unique2)
- -> Seq Scan on tenk1 a
- -> Hash
- -> Index Only Scan using tenk1_unique2 on tenk1 b
-(5 rows)
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Semi Join
+ Hash Cond: (a.unique1 = b.unique2)
+ -> Seq Scan on tenk1 a
+ -> Hash
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: b.unique2
+ -> Seq Scan on tenk1 b
+ Optimizer: Postgres query optimizer
+(9 rows)
explain (costs off)
select a.* from tenk1 a
where not exists (select 1 from tenk1 b where a.unique1 = b.unique2);
QUERY PLAN
------------------------------------------------------------
- Hash Anti Join
- Hash Cond: (a.unique1 = b.unique2)
- -> Seq Scan on tenk1 a
- -> Hash
- -> Index Only Scan using tenk1_unique2 on tenk1 b
-(5 rows)
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Right Anti Join
+ Hash Cond: (b.unique2 = a.unique1)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: b.unique2
+ -> Seq Scan on tenk1 b
+ -> Hash
+ -> Seq Scan on tenk1 a
+ Optimizer: Postgres query optimizer
+(9 rows)
explain (costs off)
select a.* from tenk1 a left join tenk1 b on a.unique1 = b.unique2
where b.unique2 is null;
QUERY PLAN
------------------------------------------------------------
- Hash Anti Join
- Hash Cond: (a.unique1 = b.unique2)
- -> Seq Scan on tenk1 a
- -> Hash
- -> Index Only Scan using tenk1_unique2 on tenk1 b
-(5 rows)
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Right Anti Join
+ Hash Cond: (b.unique2 = a.unique1)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: b.unique2
+ -> Seq Scan on tenk1 b
+ -> Hash
+ -> Seq Scan on tenk1 a
+ Optimizer: Postgres query optimizer
+(9 rows)
--
-- regression test for proper handling of outer joins within antijoins
@@ -3676,27 +3756,27 @@ select * from
where
1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1)
order by 1,2;
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: t1.q1, t1.q2
-> Sort
Sort Key: t1.q1, t1.q2
-> Hash Left Join
Hash Cond: (t1.q2 = t2.q1)
- Filter: ((SubPlan 1) = 1)
- -> Redistribute Motion 3:3 (slice3; segments: 3)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t1.q2
-> Seq Scan on int8_tbl t1
-> Hash
-> Seq Scan on int8_tbl t2
- SubPlan 1
- -> Limit
- -> Result
- One-Time Filter: ((42) IS NOT NULL)
- -> Materialize
- -> Broadcast Motion 3:3 (slice2;
segments: 3)
- -> Seq Scan on int8_tbl t3
+ Filter: (1 = (SubPlan 1))
+ SubPlan 1
+ -> Limit
+ -> Result
+ One-Time Filter: ((42) IS NOT NULL)
+ -> Materialize
+ -> Broadcast Motion 3:3
(slice3; segments: 3)
+ -> Seq Scan on int8_tbl
t3
Optimizer: Postgres query optimizer
(20 rows)
@@ -3952,22 +4032,36 @@ select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2
from
left join tenk1 t2
on (subq1.y1 = t2.unique1)
where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
- QUERY PLAN
-------------------------------------------------------------------------------
- Nested Loop
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
- Join Filter: ("*VALUES*".column2 = i1.f1)
- -> Nested Loop
- -> Nested Loop
- Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column2)
- -> Values Scan on "*VALUES*"
- -> Values Scan on "*VALUES*_1"
- -> Index Scan using tenk1_unique2 on tenk1 t1
- Index Cond: ((unique2 = (11)) AND (unique2 < 42))
- -> Seq Scan on int4_tbl i1
- -> Index Scan using tenk1_unique1 on tenk1 t2
- Index Cond: (unique1 = "*VALUES*_1".column1)
-(14 rows)
+ -> Broadcast Motion 1:3 (slice2; segments: 1)
+ -> Seq Scan on int4_tbl i1
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Hash Join
+ Hash Cond: ((11) = t1.unique2)
+ Join Filter: (t1.stringu1 > t2.stringu2)
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Nested Loop
+ Join Filter: (11 < 42)
+ -> Broadcast Motion 3:3 (slice4; segments:
3)
+ -> Seq Scan on onerow
+ -> Materialize
+ -> Hash Join
+ Hash Cond: (t2.unique1 = (3))
+ -> Seq Scan on tenk1 t2
+ -> Hash
+ -> Redistribute Motion 3:3
(slice5; segments: 3)
+ Hash Key: (3)
+ -> Seq Scan on
onerow onerow_1
+ -> Hash
+ -> Index Scan using tenk1_unique2 on tenk1 t1
+ Index Cond: (unique2 < 42)
+ Optimizer: Postgres query optimizer
+(26 rows)
+
--end_ignore
select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
tenk1 t1
@@ -4128,12 +4222,25 @@ explain (verbose, costs off)
with ctetable as not materialized ( select 1 as f1 )
select * from ctetable c1
where f1 in ( select c3.f1 from ctetable c2 full join ctetable c3 on true );
- QUERY PLAN
-----------------------------
- Result
- Output: 1
- One-Time Filter: (1 = 1)
-(3 rows)
+ QUERY PLAN
+-------------------------------------
+ Hash Semi Join
+ Output: (1)
+ Hash Cond: ((1) = (1))
+ -> Result
+ Output: 1
+ -> Hash
+ Output: (1)
+ -> Merge Full Join
+ Output: (1)
+ -> Result
+ Output: 1
+ -> Materialize
+ Output: (1)
+ -> Result
+ Output: 1
+ Optimizer: Postgres query optimizer
+(16 rows)
with ctetable as not materialized ( select 1 as f1 )
select * from ctetable c1
@@ -4163,15 +4270,15 @@ select * from int4_tbl left join (
select text 'foo' union all select text 'bar'
) ss(x) on true
where ss.x is null;
- QUERY PLAN
------------------------------------------
- Nested Loop Left Join
+ QUERY PLAN
+--------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
Output: int4_tbl.f1, ('foo'::text)
- Filter: (('foo'::text) IS NULL)
- -> Seq Scan on public.int4_tbl
- Output: int4_tbl.f1
- -> Materialize
- Output: ('foo'::text)
+ -> Nested Loop Left Join
+ Output: int4_tbl.f1, ('foo'::text)
+ Filter: (('foo'::text) IS NULL)
+ -> Seq Scan on public.int4_tbl
+ Output: int4_tbl.f1
-> Append
-> Result
Output: 'foo'::text
@@ -4242,19 +4349,12 @@ select unique1 from tenk1, lateral f_immutable_int4(1)
x where x = unique1;
explain (costs off)
select unique1 from tenk1, lateral f_immutable_int4(1) x where x in (select
17);
- QUERY PLAN
---------------------------
- Result
- One-Time Filter: false
-(2 rows)
-
-explain (costs off)
-select unique1 from tenk1, lateral f_immutable_int4(1) x where x in (select
17);
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-------------------------------------
Result
One-Time Filter: false
-(2 rows)
+ Optimizer: Postgres query optimizer
+(3 rows)
explain (costs off)
select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x;
@@ -4514,7 +4614,6 @@ select * from tenk1 t1 left join
(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
on t1.hundred = t2.hundred and t1.ten = t3.ten
where t1.unique1 = 1;
-<<<<<<< HEAD
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
@@ -4536,29 +4635,12 @@ where t1.unique1 = 1;
Index Cond: (unique1 = 1)
Optimizer: Postgres query optimizer
(18 rows)
-=======
- QUERY PLAN
---------------------------------------------------------
- Nested Loop Left Join
- -> Index Scan using tenk1_unique1 on tenk1 t1
- Index Cond: (unique1 = 1)
- -> Nested Loop
- Join Filter: (t1.ten = t3.ten)
- -> Bitmap Heap Scan on tenk1 t2
- Recheck Cond: (t1.hundred = hundred)
- -> Bitmap Index Scan on tenk1_hundred
- Index Cond: (hundred = t1.hundred)
- -> Index Scan using tenk1_unique2 on tenk1 t3
- Index Cond: (unique2 = t2.thousand)
-(11 rows)
->>>>>>> REL_16_9
explain (costs off)
select * from tenk1 t1 left join
(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
on t1.hundred = t2.hundred and t1.ten + t2.ten = t3.ten
where t1.unique1 = 1;
-<<<<<<< HEAD
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
@@ -4581,22 +4663,6 @@ where t1.unique1 = 1;
Index Cond: (unique1 = 1)
Optimizer: Postgres query optimizer
(19 rows)
-=======
- QUERY PLAN
---------------------------------------------------------
- Nested Loop Left Join
- -> Index Scan using tenk1_unique1 on tenk1 t1
- Index Cond: (unique1 = 1)
- -> Nested Loop
- Join Filter: ((t1.ten + t2.ten) = t3.ten)
- -> Bitmap Heap Scan on tenk1 t2
- Recheck Cond: (t1.hundred = hundred)
- -> Bitmap Index Scan on tenk1_hundred
- Index Cond: (hundred = t1.hundred)
- -> Index Scan using tenk1_unique2 on tenk1 t3
- Index Cond: (unique2 = t2.thousand)
-(11 rows)
->>>>>>> REL_16_9
explain (costs off)
select count(*) from
@@ -4770,13 +4836,12 @@ explain (costs off)
select q1, unique2, thousand, hundred
from int8_tbl a left join tenk1 b on q1 = unique2
where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123);
-<<<<<<< HEAD
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (b.unique2 = a.q1)
- Filter: ((COALESCE(b.thousand, 123) = a.q1) AND (a.q1 =
COALESCE(b.hundred, 123)))
+ Filter: ((COALESCE(b.thousand, 123) = COALESCE(b.hundred, 123)) AND
(a.q1 = COALESCE(b.hundred, 123)))
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: b.unique2
-> Seq Scan on tenk1 b
@@ -4784,16 +4849,6 @@ select q1, unique2, thousand, hundred
-> Seq Scan on int8_tbl a
Optimizer: Postgres query optimizer
(10 rows)
-=======
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Nested Loop Left Join
- Filter: ((COALESCE(b.thousand, 123) = COALESCE(b.hundred, 123)) AND (a.q1 =
COALESCE(b.hundred, 123)))
- -> Seq Scan on int8_tbl a
- -> Index Scan using tenk1_unique2 on tenk1 b
- Index Cond: (unique2 = a.q1)
-(5 rows)
->>>>>>> REL_16_9
select q1, unique2, thousand, hundred
from int8_tbl a left join tenk1 b on q1 = unique2
@@ -4835,12 +4890,10 @@ explain (costs off)
select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
from tenk1 a left join tenk1 b on b.thousand = a.unique1
left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand)
where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44;
-<<<<<<< HEAD
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
- -> Hash Left Join
- Hash Cond: (COALESCE(b.twothousand, a.twothousand) = c.unique2)
+ -> Nested Loop Left Join
-> Hash Right Join
Hash Cond: (b.thousand = a.unique1)
Filter: (COALESCE(b.twothousand, a.twothousand) = 44)
@@ -4850,28 +4903,12 @@ select a.unique1, b.unique1, c.unique1,
coalesce(b.twothousand, a.twothousand)
-> Hash
-> Index Scan using tenk1_unique2 on tenk1 a
Index Cond: (unique2 < 10)
- -> Hash
+ -> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Index Scan using tenk1_unique2 on tenk1 c
Index Cond: (unique2 = 44)
Optimizer: Postgres query optimizer
-(17 rows)
-=======
- QUERY PLAN
----------------------------------------------------------------
- Nested Loop Left Join
- -> Nested Loop Left Join
- Filter: (COALESCE(b.twothousand, a.twothousand) = 44)
- -> Index Scan using tenk1_unique2 on tenk1 a
- Index Cond: (unique2 < 10)
- -> Bitmap Heap Scan on tenk1 b
- Recheck Cond: (thousand = a.unique1)
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = a.unique1)
- -> Index Scan using tenk1_unique2 on tenk1 c
- Index Cond: (unique2 = 44)
-(11 rows)
->>>>>>> REL_16_9
+(16 rows)
select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
from tenk1 a left join tenk1 b on b.thousand = a.unique1
left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand)
@@ -4988,28 +5025,34 @@ int4_tbl i0 left join
) ss0
on (i0.f1 = ss0.f1)
order by i0.f1, x;
- QUERY PLAN
--------------------------------------------------------------
- Sort
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
Output: i0.f1, ('123'::bigint), i1.f1, i2.q1, i2.q2
- Sort Key: i0.f1, ('123'::bigint)
- -> Hash Right Join
+ Merge Key: i0.f1, ('123'::bigint)
+ -> Sort
Output: i0.f1, ('123'::bigint), i1.f1, i2.q1, i2.q2
- Hash Cond: (i1.f1 = i0.f1)
- -> Nested Loop Left Join
- Output: i1.f1, i2.q1, i2.q2, '123'::bigint
- -> Seq Scan on public.int4_tbl i1
- Output: i1.f1
- -> Materialize
- Output: i2.q1, i2.q2
- -> Seq Scan on public.int8_tbl i2
+ Sort Key: i0.f1, ('123'::bigint)
+ -> Hash Right Join
+ Output: i0.f1, ('123'::bigint), i1.f1, i2.q1, i2.q2
+ Hash Cond: (i1.f1 = i0.f1)
+ -> Nested Loop Left Join
+ Output: i1.f1, i2.q1, i2.q2, '123'::bigint
+ -> Seq Scan on public.int4_tbl i1
+ Output: i1.f1
+ -> Materialize
Output: i2.q1, i2.q2
- Filter: (123 = i2.q2)
- -> Hash
- Output: i0.f1
- -> Seq Scan on public.int4_tbl i0
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ Output: i2.q1, i2.q2
+ -> Seq Scan on public.int8_tbl i2
+ Output: i2.q1, i2.q2
+ Filter: (123 = i2.q2)
+ -> Hash
Output: i0.f1
-(19 rows)
+ -> Seq Scan on public.int4_tbl i0
+ Output: i0.f1
+ Optimizer: Postgres query optimizer
+(25 rows)
select * from
int4_tbl i0 left join
@@ -5278,13 +5321,13 @@ select * from
on t1.f1 = 'doh!'
left join int4_tbl i4
on i8.q1 = i4.f1;
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: t1.f1, i8.q1, i8.q2, t2.f1, i4.f1
-> Nested Loop Left Join
Output: t1.f1, i8.q1, i8.q2, t2.f1, i4.f1
- -> Seq Scan on public.text_tbl t2
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
Output: t2.f1
-> Materialize
Output: i8.q1, i8.q2, i4.f1, t1.f1
@@ -5683,24 +5726,6 @@ select * from
--
explain (costs off)
select * from int4_tbl a left join tenk1 b on f1 = unique2 where f1 = 0;
-<<<<<<< HEAD
- QUERY PLAN
--------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Hash Right Join
- Hash Cond: (b.unique2 = a.f1)
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: 0
- -> Index Scan using tenk1_unique2 on tenk1 b
- Index Cond: (unique2 = 0)
- -> Hash
- -> Seq Scan on int4_tbl a
- Filter: (f1 = 0)
- Optimizer: Postgres query optimizer
-(11 rows)
-
-explain (costs off)
- select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42;
QUERY PLAN
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
@@ -5716,28 +5741,22 @@ explain (costs off)
-> Index Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 = 42)
Optimizer: Postgres query optimizer
-(13 rows)
-=======
- QUERY PLAN
--------------------------------------------------
- Nested Loop Left Join
- -> Seq Scan on int4_tbl a
- Filter: (f1 = 0)
- -> Index Scan using tenk1_unique2 on tenk1 b
- Index Cond: (unique2 = 0)
-(5 rows)
+(10 rows)
explain (costs off)
select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42;
- QUERY PLAN
--------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Merge Full Join
- -> Index Scan using tenk1_unique2 on tenk1 a
- Index Cond: (unique2 = 42)
- -> Index Scan using tenk1_unique2 on tenk1 b
- Index Cond: (unique2 = 42)
-(5 rows)
->>>>>>> REL_16_9
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Index Scan using tenk1_unique2 on tenk1 a
+ Index Cond: (unique2 = 42)
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Index Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 = 42)
+ Optimizer: Postgres query optimizer
+(9 rows)
--
-- test that quals attached to an outer join have correct semantics,
@@ -5808,18 +5827,24 @@ explain (costs off)
select a.unique1, b.unique2
from onek a left join onek b on a.unique1 = b.unique2
where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1);
- QUERY PLAN
-----------------------------------------------------------
- Hash Join
- Hash Cond: (b.unique2 = a.unique1)
- -> Seq Scan on onek b
- Filter: (SubPlan 1)
- SubPlan 1
- -> Seq Scan on int8_tbl c
- Filter: (q1 < b.unique1)
- -> Hash
- -> Index Only Scan using onek_unique1 on onek a
-(9 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Join
+ Hash Cond: (a.unique1 = b.unique2)
+ -> Seq Scan on onek a
+ -> Hash
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: b.unique2
+ -> Hash Semi Join
+ Hash Cond: (b.unique2 = c.q1)
+ Join Filter: (c.q1 < b.unique1)
+ -> Seq Scan on onek b
+ -> Hash
+ -> Broadcast Motion 3:3 (slice3; segments:
3)
+ -> Seq Scan on int8_tbl c
+ Optimizer: Postgres query optimizer
+(15 rows)
select a.unique1, b.unique2
from onek a left join onek b on a.unique1 = b.unique2
@@ -5878,14 +5903,19 @@ explain (costs off)
select a.unique1, b.unique2
from onek a full join onek b on a.unique1 = b.unique2
where a.unique1 = 42 and b.unique2 = 42;
- QUERY PLAN
-----------------------------------------------------
- Nested Loop
- -> Index Only Scan using onek_unique1 on onek a
- Index Cond: (unique1 = 42)
- -> Index Only Scan using onek_unique2 on onek b
- Index Cond: (unique2 = 42)
-(5 rows)
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop
+ -> Index Only Scan using onek_unique1 on onek a
+ Index Cond: (unique1 = 42)
+ -> Materialize
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: b.unique2
+ -> Index Only Scan using onek_unique2 on onek b
+ Index Cond: (unique2 = 42)
+ Optimizer: Postgres query optimizer
+(10 rows)
select a.unique1, b.unique2
from onek a full join onek b on a.unique1 = b.unique2
@@ -5904,15 +5934,18 @@ select * from
full join
(select * from (values(456,2)) w(v1,v2) join int8_tbl i82 on q2=v1) ss2
on true;
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Merge Full Join
- -> Seq Scan on int8_tbl i81
- Filter: (q2 = 123)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on int8_tbl i81
+ Filter: (q2 = 123)
-> Materialize
- -> Seq Scan on int8_tbl i82
- Filter: (q2 = 456)
-(6 rows)
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on int8_tbl i82
+ Filter: (q2 = 456)
+ Optimizer: Postgres query optimizer
+(9 rows)
select * from
(select * from int8_tbl i81 join (values(123,2)) v(v1,v2) on q2=v1) ss1
@@ -6523,38 +6556,24 @@ from
left join uniquetbl u1 ON u1.f1 = t1.string4) ss
on t0.f1 = ss.case1
where ss.stringu2 !~* ss.case1;
-<<<<<<< HEAD
- QUERY PLAN
---------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END
= t0.f1)
- -> Hash Join
- Hash Cond: (t1.unique2 = i4.f1)
- -> Seq Scan on tenk1 t1
- Filter: (stringu2 !~* CASE ten WHEN 0 THEN 'doh!'::text
ELSE NULL::text END)
- -> Hash
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Seq Scan on int4_tbl i4
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text
END
+ -> Hash Join
+ Hash Cond: (t1.unique2 = i4.f1)
+ -> Seq Scan on tenk1 t1
+ Filter: (stringu2 !~* CASE ten WHEN 0 THEN
'doh!'::text ELSE NULL::text END)
+ -> Hash
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on int4_tbl i4
-> Hash
- -> Broadcast Motion 3:3 (slice3; segments: 3)
- -> Seq Scan on text_tbl t0
+ -> Seq Scan on text_tbl t0
Optimizer: Postgres query optimizer
-(14 rows)
-=======
- QUERY PLAN
---------------------------------------------------------------------------------------------
- Nested Loop
- Join Filter: (t0.f1 = CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text
END)
- -> Nested Loop
- -> Seq Scan on int4_tbl i4
- -> Index Scan using tenk1_unique2 on tenk1 t1
- Index Cond: (unique2 = i4.f1)
- Filter: (stringu2 !~* CASE ten WHEN 0 THEN 'doh!'::text ELSE
NULL::text END)
- -> Materialize
- -> Seq Scan on text_tbl t0
-(9 rows)
->>>>>>> REL_16_9
+(15 rows)
select t0.*
from
@@ -6717,18 +6736,14 @@ where q2 = 456;
create temp table parttbl (a integer primary key) partition by range (a);
create temp table parttbl1 partition of parttbl for values from (1) to (100);
insert into parttbl values (11), (12);
-<<<<<<< HEAD
set optimizer_enable_dynamicindexonlyscan=off;
-=======
->>>>>>> REL_16_9
explain (costs off)
select * from
(select *, 12 as phv from parttbl) as ss
right join int4_tbl on true
where ss.a = ss.phv and f1 = 0;
- QUERY PLAN
-------------------------------------
-<<<<<<< HEAD
+ QUERY PLAN
+---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 1:3 (slice2; segments: 1)
@@ -6740,15 +6755,6 @@ where ss.a = ss.phv and f1 = 0;
(8 rows)
reset optimizer_enable_dynamicindexonlyscan;
-=======
- Nested Loop
- -> Seq Scan on int4_tbl
- Filter: (f1 = 0)
- -> Seq Scan on parttbl1 parttbl
- Filter: (a = 12)
-(5 rows)
-
->>>>>>> REL_16_9
select * from
(select *, 12 as phv from parttbl) as ss
right join int4_tbl on true
@@ -6942,9 +6948,8 @@ select count(*) from tenk1 a, lateral
generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a, lateral generate_series(1,two) g;
-<<<<<<< HEAD
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
@@ -6955,12 +6960,12 @@ explain (costs off)
Cache Mode: binary
-> Function Scan on generate_series g
Optimizer: Postgres query optimizer
-(11 rows)
+(10 rows)
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
@@ -6971,39 +6976,13 @@ explain (costs off)
Cache Mode: binary
-> Function Scan on generate_series g
Optimizer: Postgres query optimizer
-(11 rows)
-=======
- QUERY PLAN
-------------------------------------------------------
- Aggregate
- -> Nested Loop
- -> Seq Scan on tenk1 a
- -> Memoize
- Cache Key: a.two
- Cache Mode: binary
- -> Function Scan on generate_series g
-(7 rows)
-
-explain (costs off)
- select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
- QUERY PLAN
-------------------------------------------------------
- Aggregate
- -> Nested Loop
- -> Seq Scan on tenk1 a
- -> Memoize
- Cache Key: a.two
- Cache Mode: binary
- -> Function Scan on generate_series g
-(7 rows)
->>>>>>> REL_16_9
+(10 rows)
-- don't need the explicit LATERAL keyword for functions
explain (costs off)
select count(*) from tenk1 a, generate_series(1,two) g;
-<<<<<<< HEAD
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
@@ -7014,19 +6993,7 @@ explain (costs off)
Cache Mode: binary
-> Function Scan on generate_series g
Optimizer: Postgres query optimizer
-(11 rows)
-=======
- QUERY PLAN
-------------------------------------------------------
- Aggregate
- -> Nested Loop
- -> Seq Scan on tenk1 a
- -> Memoize
- Cache Key: a.two
- Cache Mode: binary
- -> Function Scan on generate_series g
-(7 rows)
->>>>>>> REL_16_9
+(10 rows)
-- lateral with UNION ALL subselect
-- start_ignore
@@ -7097,7 +7064,6 @@ select count(*) from tenk1 a,
explain (costs off)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
-<<<<<<< HEAD
QUERY PLAN
------------------------------------------------------------------------------
Finalize Aggregate
@@ -7116,21 +7082,6 @@ explain (costs off)
-> Seq Scan on tenk1 b
Optimizer: Postgres query optimizer
(15 rows)
-=======
- QUERY PLAN
-------------------------------------------------------------------
- Aggregate
- -> Nested Loop
- -> Nested Loop
- -> Index Only Scan using tenk1_unique1 on tenk1 a
- -> Values Scan on "*VALUES*"
- -> Memoize
- Cache Key: "*VALUES*".column1
- Cache Mode: logical
- -> Index Only Scan using tenk1_unique2 on tenk1 b
- Index Cond: (unique2 = "*VALUES*".column1)
-(10 rows)
->>>>>>> REL_16_9
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
@@ -7693,7 +7644,6 @@ select * from
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)), d.q1,
(COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)), ((COALESCE((COALESCE(b.q2,
'42'::bigint)), d.q2)))
-<<<<<<< HEAD
-> Nested Loop
Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)),
d.q1, (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)),
((COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)))
-> Hash Right Join
@@ -7718,27 +7668,6 @@ select * from
-> Seq Scan on public.int8_tbl d
Output: d.q1, COALESCE((COALESCE(b.q2,
'42'::bigint)), d.q2)
-> Hash
-=======
- -> Hash Right Join
- Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)),
d.q1, (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))
- Hash Cond: (d.q1 = c.q2)
- -> Nested Loop
- Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)), d.q1,
(COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))
- -> Hash Left Join
- Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint))
- Hash Cond: (a.q2 = b.q1)
- -> Seq Scan on public.int8_tbl a
- Output: a.q1, a.q2
- -> Hash
- Output: b.q1, (COALESCE(b.q2, '42'::bigint))
- -> Seq Scan on public.int8_tbl b
- Output: b.q1, COALESCE(b.q2, '42'::bigint)
- -> Seq Scan on public.int8_tbl d
- Output: d.q1, COALESCE((COALESCE(b.q2, '42'::bigint)),
d.q2)
- -> Hash
- Output: c.q1, c.q2
- -> Seq Scan on public.int8_tbl c
->>>>>>> REL_16_9
Output: c.q1, c.q2
-> Redistribute Motion 3:3 (slice4; segments: 3)
Output: c.q1, c.q2
@@ -7904,9 +7833,9 @@ select * from int8_tbl i8 left join lateral
--------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: i8.q1, i8.q2, f1, (i8.q2)
-<<<<<<< HEAD
-> Nested Loop Left Join
Output: i8.q1, i8.q2, f1, (i8.q2)
+ Join Filter: false
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
-> Result
@@ -7915,15 +7844,6 @@ select * from int8_tbl i8 left join lateral
Optimizer: Postgres query optimizer
Settings: optimizer=off
(11 rows)
-=======
- Join Filter: false
- -> Seq Scan on public.int8_tbl i8
- Output: i8.q1, i8.q2
- -> Result
- Output: f1, i8.q2
- One-Time Filter: false
-(8 rows)
->>>>>>> REL_16_9
explain (verbose, costs off)
select * from int8_tbl i8 left join lateral
@@ -8018,41 +7938,7 @@ lateral (select * from int8_tbl t1,
where q2 = (select greatest(t1.q1,t2.q2))
and (select v.id=0)) offset 0) ss2) ss
where t1.q1 = ss.q2) ss0;
-<<<<<<< HEAD
ERROR: correlated subquery with skip-level correlations is not supported
-=======
- QUERY PLAN
-----------------------------------------------------------------------
- Nested Loop
- Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
- -> Seq Scan on public.int8_tbl t1
- Output: t1.q1, t1.q2
- -> Nested Loop
- Output: "*VALUES*".column1, ss2.q1, ss2.q2
- -> Values Scan on "*VALUES*"
- Output: "*VALUES*".column1
- -> Subquery Scan on ss2
- Output: ss2.q1, ss2.q2
- Filter: (t1.q1 = ss2.q2)
- -> Seq Scan on public.int8_tbl t2
- Output: t2.q1, t2.q2
- Filter: (SubPlan 3)
- SubPlan 3
- -> Result
- Output: t3.q2
- One-Time Filter: $4
- InitPlan 1 (returns $2)
- -> Result
- Output: GREATEST(t1.q1, t2.q2)
- InitPlan 2 (returns $4)
- -> Result
- Output: ("*VALUES*".column1 = 0)
- -> Seq Scan on public.int8_tbl t3
- Output: t3.q1, t3.q2
- Filter: (t3.q2 = $2)
-(27 rows)
-
->>>>>>> REL_16_9
select * from (values (0), (1)) v(id),
lateral (select * from int8_tbl t1,
lateral (select * from
@@ -8702,27 +8588,16 @@ analyze j1; -- GPDB also needs this to get the same
plan as in upstream
explain (costs off) select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
-<<<<<<< HEAD
QUERY PLAN
-----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Join
Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j1.id2 = j2.id2)
+ Join Filter: (j2.id2 = j1.id2)
-> Index Scan using j1_id1_idx on j1
-> Index Scan using j2_id1_idx on j2
Optimizer: Postgres query optimizer
(7 rows)
-=======
- QUERY PLAN
------------------------------------------
- Merge Join
- Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j2.id2 = j1.id2)
- -> Index Scan using j1_id1_idx on j1
- -> Index Scan using j2_id1_idx on j2
-(5 rows)
->>>>>>> REL_16_9
select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
@@ -8737,30 +8612,18 @@ where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
explain (costs off) select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 = any (array[1]);
-<<<<<<< HEAD
QUERY PLAN
----------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Join
Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j1.id2 = j2.id2)
+ Join Filter: (j2.id2 = j1.id2)
-> Index Scan using j1_id1_idx on j1
Index Cond: (id1 = ANY ('{1}'::integer[]))
-> Index Scan using j2_id1_idx on j2
Index Cond: (id1 = ANY ('{1}'::integer[]))
Optimizer: Postgres query optimizer
(9 rows)
-=======
- QUERY PLAN
-----------------------------------------------------
- Merge Join
- Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j2.id2 = j1.id2)
- -> Index Scan using j1_id1_idx on j1
- -> Index Scan using j2_id1_idx on j2
- Index Cond: (id1 = ANY ('{1}'::integer[]))
-(6 rows)
->>>>>>> REL_16_9
select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
@@ -8775,7 +8638,6 @@ where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1
= any (array[1]);
explain (costs off) select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 >= any (array[1,5]);
-<<<<<<< HEAD
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
@@ -8789,18 +8651,6 @@ where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1
>= any (array[1,5]);
Filter: ((id1 % 1000) = 1)
Optimizer: Postgres query optimizer
(10 rows)
-=======
- QUERY PLAN
--------------------------------------------------------
- Merge Join
- Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j2.id2 = j1.id2)
- -> Index Scan using j1_id1_idx on j1
- -> Index Only Scan using j2_pkey on j2
- Index Cond: (id1 >= ANY ('{1,5}'::integer[]))
- Filter: ((id1 % 1000) = 1)
-(7 rows)
->>>>>>> REL_16_9
select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
@@ -8879,7 +8729,7 @@ where exists (select 1 from j3
Output: t1.unique1, t2.hundred
-> Nested Loop
Output: t1.unique1, t2.hundred
- Join Filter: (j3.unique1 = t1.unique1)
+ Join Filter: (t1.unique1 = j3.unique1)
-> Broadcast Motion 3:3 (slice2; segments: 3)
Output: t1.unique1
-> Index Only Scan using onek_unique1 on public.onek t1
@@ -8902,12 +8752,6 @@ where exists (select 1 from j3
(24 rows)
drop table j3;
-<<<<<<< HEAD
-reset enable_hashjoin;
-reset enable_nestloop;
-reset enable_seqscan;
-reset enable_bitmapscan;
-=======
-- Test that we do not account for nullingrels when looking up statistics
CREATE TABLE group_tbl (a INT, b INT);
INSERT INTO group_tbl SELECT 1, 1;
@@ -8917,9 +8761,9 @@ EXPLAIN (COSTS OFF)
SELECT 1 FROM group_tbl t1
LEFT JOIN (SELECT a c1, COALESCE(a) c2 FROM group_tbl t2) s ON TRUE
GROUP BY s.c1, s.c2;
- QUERY PLAN
---------------------------------------------
- Group
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ GroupAggregate
Group Key: t2.a, (COALESCE(t2.a))
-> Sort
Sort Key: t2.a, (COALESCE(t2.a))
@@ -8929,4 +8773,7 @@ GROUP BY s.c1, s.c2;
(7 rows)
DROP TABLE group_tbl;
->>>>>>> REL_16_9
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_seqscan;
+reset enable_bitmapscan;
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index b04e6f85f35..c0061f931bd 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -112,8 +112,8 @@ test: privileges
test: init_privs
test: security_label
test: collate
-test: matview
-test: lock
+# test: matview
+# test: lock
# test: replica_identity
# test: rowsecurity
# test: object_address
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index bfcddf6266c..4afd7f9f45b 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -667,8 +667,6 @@ select t1.*, t2.*, unnamed_join.* from
t1 join t2 on (t1.a = t2.a), t3 as unnamed_join
for update of unnamed_join;
-<<<<<<< HEAD
-=======
select foo.*, unnamed_join.* from
t1 join t2 using (a) as foo, t3 as unnamed_join
for update of unnamed_join;
@@ -685,7 +683,6 @@ select bar.*, unnamed_join.* from
(t1 join t2 using (a) as foo) as bar, t3 as unnamed_join
for update of bar;
->>>>>>> REL_16_9
--
-- regression test for 8.1 merge right join bug
--
@@ -2377,19 +2374,13 @@ where q2 = 456;
create temp table parttbl (a integer primary key) partition by range (a);
create temp table parttbl1 partition of parttbl for values from (1) to (100);
insert into parttbl values (11), (12);
-<<<<<<< HEAD
set optimizer_enable_dynamicindexonlyscan=off;
-=======
->>>>>>> REL_16_9
explain (costs off)
select * from
(select *, 12 as phv from parttbl) as ss
right join int4_tbl on true
where ss.a = ss.phv and f1 = 0;
-<<<<<<< HEAD
reset optimizer_enable_dynamicindexonlyscan;
-=======
->>>>>>> REL_16_9
select * from
(select *, 12 as phv from parttbl) as ss
@@ -3037,12 +3028,6 @@ where exists (select 1 from j3
drop table j3;
-<<<<<<< HEAD
-reset enable_hashjoin;
-reset enable_nestloop;
-reset enable_seqscan;
-reset enable_bitmapscan;
-=======
-- Test that we do not account for nullingrels when looking up statistics
CREATE TABLE group_tbl (a INT, b INT);
INSERT INTO group_tbl SELECT 1, 1;
@@ -3051,8 +3036,12 @@ ANALYZE group_tbl;
EXPLAIN (COSTS OFF)
SELECT 1 FROM group_tbl t1
- LEFT JOIN (SELECT a c1, COALESCE(a) c2 FROM group_tbl t2) s ON TRUE
+ LEFT JOIN (SELECT a c1, COALESCE(a) c2 FROM group_tbl t2) s
ON TRUE
GROUP BY s.c1, s.c2;
DROP TABLE group_tbl;
->>>>>>> REL_16_9
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_seqscan;
+reset enable_bitmapscan;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
