diff options
author | Pavan Deolasee | 2017-07-24 05:42:57 +0000 |
---|---|---|
committer | Pavan Deolasee | 2017-07-24 05:42:57 +0000 |
commit | 5794778b31e6abe6bf0ce6869778b80d9b29fc59 (patch) | |
tree | d7e15bb9f6657daae97035632f8a080d1b5da8b2 | |
parent | 5144b0d5c438f50916f4469095c3789ffa59664b (diff) |
Accept some obvious regression differences in the 'join' test case
These are only placements of Remote FQS or Remote Subplan nodes in the newly
added explain plans in the test case. There are some remaining failures in the
test case which will need more scrutiny.
-rw-r--r-- | src/test/regress/expected/join.out | 238 |
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; |