summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/test/regress/expected/join.out238
1 files changed, 142 insertions, 96 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 9d4ed14d85..cfb758ae19 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5703,19 +5703,23 @@ analyze j3;
-- ensure join is properly marked as unique
explain (verbose, costs off)
select * from j1 inner join j2 on j1.id = j2.id;
- QUERY PLAN
------------------------------------
- Hash Join
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Remote Fast Query Execution
Output: j1.id, j2.id
- Inner Unique: true
- Hash Cond: (j1.id = j2.id)
- -> Seq Scan on public.j1
- Output: j1.id
- -> Hash
- Output: j2.id
- -> Seq Scan on public.j2
+ Node/s: datanode_1, datanode_2
+ Remote query: SELECT j1.id, j2.id FROM (j1 JOIN j2 ON ((j1.id = j2.id)))
+ -> Nested Loop
+ Output: j1.id, j2.id
+ Inner Unique: true
+ Join Filter: (j1.id = j2.id)
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Materialize
Output: j2.id
-(10 rows)
+ -> Seq Scan on public.j2
+ Output: j2.id
+(14 rows)
-- ensure join is not unique when not an equi-join
explain (verbose, costs off)
@@ -5740,53 +5744,63 @@ select * from j1 inner join j2 on j1.id > j2.id;
-- ensure non-unique rel is not chosen as inner
explain (verbose, costs off)
select * from j1 inner join j3 on j1.id = j3.id;
- QUERY PLAN
------------------------------------
- Hash Join
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Remote Fast Query Execution
Output: j1.id, j3.id
- Inner Unique: true
- Hash Cond: (j3.id = j1.id)
- -> Seq Scan on public.j3
- Output: j3.id
- -> Hash
- Output: j1.id
- -> Seq Scan on public.j1
+ Node/s: datanode_1, datanode_2
+ Remote query: SELECT j1.id, j3.id FROM (j1 JOIN j3 ON ((j1.id = j3.id)))
+ -> Nested Loop
+ Output: j1.id, j3.id
+ Inner Unique: true
+ Join Filter: (j1.id = j3.id)
+ -> Seq Scan on public.j3
+ Output: j3.id
+ -> Materialize
Output: j1.id
-(10 rows)
+ -> Seq Scan on public.j1
+ Output: j1.id
+(14 rows)
-- ensure left join is marked as unique
explain (verbose, costs off)
select * from j1 left join j2 on j1.id = j2.id;
- QUERY PLAN
------------------------------------
- Hash Left Join
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Remote Fast Query Execution
Output: j1.id, j2.id
- Inner Unique: true
- Hash Cond: (j1.id = j2.id)
- -> Seq Scan on public.j1
- Output: j1.id
- -> Hash
- Output: j2.id
- -> Seq Scan on public.j2
+ Node/s: datanode_1, datanode_2
+ Remote query: SELECT j1.id, j2.id FROM (j1 LEFT JOIN j2 ON ((j1.id = j2.id)))
+ -> Nested Loop Left Join
+ Output: j1.id, j2.id
+ Inner Unique: true
+ Join Filter: (j1.id = j2.id)
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Materialize
Output: j2.id
-(10 rows)
+ -> Seq Scan on public.j2
+ Output: j2.id
+(14 rows)
-- ensure right join is marked as unique
explain (verbose, costs off)
select * from j1 right join j2 on j1.id = j2.id;
- QUERY PLAN
------------------------------------
- Hash Left Join
+ QUERY PLAN
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
Output: j1.id, j2.id
- Inner Unique: true
- Hash Cond: (j2.id = j1.id)
- -> Seq Scan on public.j2
- Output: j2.id
- -> Hash
- Output: j1.id
- -> Seq Scan on public.j1
+ -> Nested Loop Left Join
+ Output: j1.id, j2.id
+ Inner Unique: true
+ Join Filter: (j1.id = j2.id)
+ -> Seq Scan on public.j2
+ Output: j2.id
+ -> Materialize
Output: j1.id
-(10 rows)
+ -> Seq Scan on public.j1
+ Output: j1.id
+(12 rows)
-- ensure full join is marked as unique
explain (verbose, costs off)
@@ -5831,19 +5845,23 @@ select * from j1 cross join j2;
-- ensure a natural join is marked as unique
explain (verbose, costs off)
select * from j1 natural join j2;
- QUERY PLAN
------------------------------------
- Hash Join
+ QUERY PLAN
+-----------------------------------------------------------
+ Remote Fast Query Execution
Output: j1.id
- Inner Unique: true
- Hash Cond: (j1.id = j2.id)
- -> Seq Scan on public.j1
+ Node/s: datanode_1, datanode_2
+ Remote query: SELECT j1.id FROM (j1 JOIN j2 USING (id))
+ -> Nested Loop
Output: j1.id
- -> Hash
- Output: j2.id
- -> Seq Scan on public.j2
+ Inner Unique: true
+ Join Filter: (j1.id = j2.id)
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Materialize
Output: j2.id
-(10 rows)
+ -> Seq Scan on public.j2
+ Output: j2.id
+(14 rows)
-- ensure a distinct clause allows the inner to become unique
explain (verbose, costs off)
@@ -5997,14 +6015,17 @@ create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1;
explain (costs off) select * from j1 j1
inner join j1 j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
- QUERY PLAN
---------------------------------------------
- Merge Join
- Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j1.id2 = j2.id2)
- -> Index Scan using j1_id1_idx on j1
- -> Index Scan using j1_id1_idx on j1 j2
-(5 rows)
+ QUERY PLAN
+----------------------------------------------------------------
+ Remote Fast Query Execution
+ Node/s: datanode_1, datanode_2
+ -> Nested Loop
+ Join Filter: ((j1.id1 = j2.id1) AND (j1.id2 = j2.id2))
+ -> Seq Scan on j1
+ Filter: ((id1 % 1000) = 1)
+ -> Seq Scan on j1 j2
+ Filter: ((id1 % 1000) = 1)
+(8 rows)
select * from j1 j1
inner join j1 j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
@@ -6028,27 +6049,41 @@ from onek t1, tenk1 t2
where exists (select 1 from tenk1 t3
where t3.thousand = t1.unique1 and t3.tenthous = t2.hundred)
and t1.unique1 < 1;
- QUERY PLAN
----------------------------------------------------------------------------------
- Nested Loop
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
Output: t1.unique1, t2.hundred
- -> Hash Join
- Output: t1.unique1, t3.tenthous
- Hash Cond: (t3.thousand = t1.unique1)
- -> HashAggregate
- Output: t3.thousand, t3.tenthous
- Group Key: t3.thousand, t3.tenthous
- -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 t3
- Output: t3.thousand, t3.tenthous
- -> Hash
- Output: t1.unique1
- -> Index Only Scan using onek_unique1 on public.onek t1
- Output: t1.unique1
- Index Cond: (t1.unique1 < 1)
- -> Index Only Scan using tenk1_hundred on public.tenk1 t2
- Output: t2.hundred
- Index Cond: (t2.hundred = t3.tenthous)
-(18 rows)
+ -> Nested Loop
+ Output: t1.unique1, t2.hundred
+ -> Remote Subquery Scan on all (datanode_1,datanode_2)
+ Output: t1.unique1, t3.tenthous
+ Distribute results by H: tenthous
+ -> Hash Join
+ Output: t1.unique1, t3.tenthous
+ Hash Cond: (t3.thousand = t1.unique1)
+ -> Remote Subquery Scan on all (datanode_1,datanode_2)
+ Output: t3.thousand, t3.tenthous
+ Distribute results by H: thousand
+ -> HashAggregate
+ Output: t3.thousand, t3.tenthous
+ Group Key: t3.thousand, t3.tenthous
+ -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 t3
+ Output: t3.thousand, t3.tenthous
+ -> Hash
+ Output: t1.unique1
+ -> Index Only Scan using onek_unique1 on public.onek t1
+ Output: t1.unique1
+ Index Cond: (t1.unique1 < 1)
+ -> Materialize
+ Output: t2.hundred
+ -> Remote Subquery Scan on all (datanode_1,datanode_2)
+ Output: t2.hundred
+ Distribute results by H: hundred
+ Sort Key: t2.hundred
+ -> Index Only Scan using tenk1_hundred on public.tenk1 t2
+ Output: t2.hundred
+ Index Cond: (t2.hundred = t3.tenthous)
+(32 rows)
-- ... unless it actually is unique
create table j3 as select unique1, tenthous from onek;
@@ -6060,21 +6095,32 @@ from onek t1, tenk1 t2
where exists (select 1 from j3
where j3.unique1 = t1.unique1 and j3.tenthous = t2.hundred)
and t1.unique1 < 1;
- QUERY PLAN
-------------------------------------------------------------------------
- Nested Loop
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
Output: t1.unique1, t2.hundred
-> Nested Loop
- Output: t1.unique1, j3.tenthous
- -> Index Only Scan using onek_unique1 on public.onek t1
- Output: t1.unique1
- Index Cond: (t1.unique1 < 1)
- -> Index Only Scan using j3_unique1_tenthous_idx on public.j3
- Output: j3.unique1, j3.tenthous
- Index Cond: (j3.unique1 = t1.unique1)
- -> Index Only Scan using tenk1_hundred on public.tenk1 t2
- Output: t2.hundred
- Index Cond: (t2.hundred = j3.tenthous)
-(13 rows)
+ Output: t1.unique1, t2.hundred
+ -> Remote Subquery Scan on all (datanode_1,datanode_2)
+ Output: t1.unique1, j3.tenthous
+ Distribute results by H: tenthous
+ -> Nested Loop
+ Output: t1.unique1, j3.tenthous
+ -> Index Only Scan using onek_unique1 on public.onek t1
+ Output: t1.unique1
+ Index Cond: (t1.unique1 < 1)
+ -> Index Only Scan using j3_unique1_tenthous_idx on public.j3
+ Output: j3.unique1, j3.tenthous
+ Index Cond: (j3.unique1 = t1.unique1)
+ -> Materialize
+ Output: t2.hundred
+ -> Remote Subquery Scan on all (datanode_1,datanode_2)
+ Output: t2.hundred
+ Distribute results by H: hundred
+ Sort Key: t2.hundred
+ -> Index Only Scan using tenk1_hundred on public.tenk1 t2
+ Output: t2.hundred
+ Index Cond: (t2.hundred = j3.tenthous)
+(24 rows)
drop table j3;
generated by cgit v1.2.3 (git 2.39.1) at 2025-07-23 19:56:53 +0000