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 23f4eddc2d2 Fix answer file for join
23f4eddc2d2 is described below
commit 23f4eddc2d2e479f9c6bdf0dbe00d0dba18ddf30
Author: Jinbao Chen <[email protected]>
AuthorDate: Sun Nov 23 10:30:36 2025 +0800
Fix answer file for join
---
src/backend/executor/nodeAppend.c | 3 +
src/test/regress/expected/join.out | 964 +++++++++++++++++++------------------
2 files changed, 501 insertions(+), 466 deletions(-)
diff --git a/src/backend/executor/nodeAppend.c
b/src/backend/executor/nodeAppend.c
index 47f5773042f..d0d5f4f9f26 100644
--- a/src/backend/executor/nodeAppend.c
+++ b/src/backend/executor/nodeAppend.c
@@ -177,7 +177,10 @@ ExecInitAppend(Append *node, EState *estate, int eflags)
appendstate->as_prune_state = NULL;
if (node->join_prune_paramids)
+ {
appendstate->as_valid_subplans = NULL;
+ appendstate->as_valid_subplans_identified = false;
+ }
}
/*
diff --git a/src/test/regress/expected/join.out
b/src/test/regress/expected/join.out
index 9fe012ce1f1..2e74904fe89 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2746,17 +2746,7 @@ select * from int8_tbl t1
left join lateral
(select t2.q1 from int8_tbl t3) 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
- -> Seq Scan on int8_tbl t3
-(7 rows)
-
+ERROR: could not devise a query plan for the given query (pathnode.c:285)
explain (costs off)
select * from onek t1
left join onek t2 on true
@@ -3064,21 +3054,22 @@ 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
----------------------------------------------------------------
+ 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
+ -> Merge Anti Join
+ Merge Cond: (t1.a = t2.b)
-> Sort
Sort Key: t1.a
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Seq Scan on tbl_ra t1
- Filter: (b < 2)
+ -> Seq Scan on tbl_ra t1
+ Filter: (b < 2)
+ -> Sort
+ Sort Key: t2.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: t2.b
+ -> Seq Scan on tbl_ra t2
Optimizer: Postgres query optimizer
-(12 rows)
+(13 rows)
-- and check we get the expected results
select * from tbl_ra t1
@@ -3756,27 +3747,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)
- -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Filter: (1 = (SubPlan 1))
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
Hash Key: t1.q2
-> Seq Scan on int8_tbl t1
-> Hash
-> Seq Scan on int8_tbl t2
- 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
+ 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
Optimizer: Postgres query optimizer
(20 rows)
@@ -4257,10 +4248,10 @@ from int4_tbl t1
inner join (int8_tbl t2
left join information_schema.column_udt_usage on null)
on null;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Result
- Output: (current_database())::information_schema.sql_identifier,
(c.relname)::information_schema.sql_identifier
+ Output: 'regression'::information_schema.sql_identifier,
(c.relname)::information_schema.sql_identifier
One-Time Filter: false
(3 rows)
@@ -4381,16 +4372,17 @@ select unique1, x from tenk1 left join
f_immutable_int4(1) x on unique1 = x;
explain (costs off)
select unique1, x from tenk1 right join f_immutable_int4(1) x on unique1 = x;
- QUERY PLAN
-----------------------------------------------------------------
- Nested Loop Left Join
- -> Result
- -> Materialize
- -> Gather Motion 1:1 (slice1; segments: 1)
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = 1)
+ QUERY PLAN
+------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Redistribute Motion 1:3 (slice2; segments: 1)
+ Hash Key: 1
+ -> Result
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = 1)
Optimizer: Postgres query optimizer
-(7 rows)
+(8 rows)
explain (costs off)
select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x;
@@ -4505,22 +4497,26 @@ explain (verbose, costs off)
select (t2.*).unique1, f_field_select(t2) from tenk1 t1
left join onek t2 on t1.unique1 = t2.unique1
left join int8_tbl t3 on true;
- QUERY PLAN
---------------------------------------------------------------------
- Nested Loop Left Join
+
QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
Output: t2.unique1, t2.unique2
- -> Hash Left Join
+ -> Nested Loop Left Join
Output: t2.unique1, t2.unique2
- Hash Cond: (t1.unique1 = t2.unique1)
- -> Index Only Scan using tenk1_unique1 on public.tenk1 t1
- Output: t1.unique1
- -> Hash
+ -> Hash Left Join
Output: t2.unique1, t2.unique2
- -> Seq Scan on public.onek t2
+ Hash Cond: (t1.unique1 = t2.unique1)
+ -> Seq Scan on public.tenk1 t1
+ Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten,
t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous,
t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
+ -> Hash
Output: t2.unique1, t2.unique2
- -> Materialize
- -> Seq Scan on public.int8_tbl t3
-(13 rows)
+ -> Seq Scan on public.onek t2
+ Output: t2.unique1, t2.unique2
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on public.int8_tbl t3
+ Optimizer: Postgres query optimizer
+(17 rows)
drop function f_field_select(t onek);
--
@@ -4921,17 +4917,23 @@ select a.unique1, b.unique1, c.unique1,
coalesce(b.twothousand, a.twothousand)
explain (costs off)
select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
- QUERY PLAN
--------------------------------------------
- Nested Loop
- -> Hash Left Join
- Hash Cond: (t1.q2 = t2.q1)
- Filter: (t2.q1 = t2.q2)
- -> Seq Scan on int8_tbl t1
- -> Hash
- -> Seq Scan on int8_tbl t2
- -> Seq Scan on int8_tbl t3
-(8 rows)
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop
+ -> Hash Left Join
+ Hash Cond: (t1.q2 = t2.q1)
+ Filter: (t2.q1 = t2.q2)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: t1.q2
+ -> Seq Scan on int8_tbl t1
+ -> Hash
+ -> Seq Scan on int8_tbl t2
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on int8_tbl t3
+ Optimizer: Postgres query optimizer
+(14 rows)
select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
@@ -5329,12 +5331,16 @@ select * from
Output: t1.f1, i8.q1, i8.q2, t2.f1, i4.f1
-> Redistribute Motion 3:3 (slice2; segments: 3)
Output: t2.f1
+ Hash Key: 'doh!'::text
+ -> Seq Scan on public.text_tbl t2
+ Output: t2.f1
-> Materialize
Output: i8.q1, i8.q2, i4.f1, t1.f1
- -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Nested Loop
Output: i8.q1, i8.q2, i4.f1, t1.f1
- -> Nested Loop
- Output: i8.q1, i8.q2, i4.f1, t1.f1
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: i8.q1, i8.q2, i4.f1
+ Hash Key: 'doh!'::text
-> Hash Right Join
Output: i8.q1, i8.q2, i4.f1
Hash Cond: (i4.f1 = i8.q1)
@@ -5345,13 +5351,9 @@ select * from
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
Filter: (i8.q2 = 456)
- -> Materialize
- Output: t1.f1
- -> Broadcast Motion 1:3 (slice3; segments:
1)
- Output: t1.f1
- -> Seq Scan on public.text_tbl t1
- Output: t1.f1
- Filter: (t1.f1 = 'doh!'::text)
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ Filter: (t1.f1 = 'doh!'::text)
Optimizer: Postgres query optimizer
(30 rows)
@@ -5401,21 +5403,26 @@ select 1 from
-----------------------------------------------------------
Nested Loop Left Join
-> Result
- -> Nested Loop
- -> Nested Loop Left Join
- Join Filter: NULL::boolean
- Filter: (((1) IS NULL) OR (i8.q1 <> i8.q2))
- -> Nested Loop Left Join
- Join Filter: (i4.f1 IS NOT NULL)
- -> Seq Scan on int4_tbl i4
- Filter: (2 < f1)
+ -> Materialize
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop
+ -> Nested Loop Left Join
+ Join Filter: NULL::boolean
+ Filter: (((1) IS NULL) OR (i8.q1 <> i8.q2))
+ -> Nested Loop Left Join
+ Join Filter: (i4.f1 IS NOT NULL)
+ -> Seq Scan on int4_tbl i4
+ Filter: (2 < f1)
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2;
segments: 3)
+ -> Seq Scan on int8_tbl i8
+ -> Result
+ One-Time Filter: false
-> Materialize
- -> Seq Scan on int8_tbl i8
- -> Result
- One-Time Filter: false
- -> Materialize
- -> Seq Scan on int4_tbl i42
-(16 rows)
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on int4_tbl i42
+ Optimizer: Postgres query optimizer
+(21 rows)
--
-- test for appropriate join order in the presence of lateral references
@@ -5572,9 +5579,7 @@ where tt1.f1 = ss1.c0;
----------
(0 rows)
-<<<<<<< HEAD
--end_ignore
-=======
explain (verbose, costs off)
select 1 from
int4_tbl as i4
@@ -5624,7 +5629,6 @@ select 1 from t t1
(2 rows)
rollback;
->>>>>>> REL_16_9
--
-- check a case in which a PlaceHolderVar forces join order
--
@@ -5642,34 +5646,45 @@ select ss2.* from
on i41.f1 = ss1.c1,
lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
where ss1.c2 = 0;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Nested Loop
Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
- -> Hash Join
- Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42
- Hash Cond: (i41.f1 = i42.f1)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, (42)
-> Nested Loop
- Output: i8.q1, i8.q2, i43.f1, i41.f1
+ Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42
-> Nested Loop
- Output: i8.q1, i8.q2, i43.f1
+ Output: i41.f1, i42.f1, i8.q1, i8.q2
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: i41.f1, i42.f1
+ Hash Key: 0
+ -> Hash Join
+ Output: i41.f1, i42.f1
+ Hash Cond: (i41.f1 = i42.f1)
+ -> Seq Scan on public.int4_tbl i41
+ Output: i41.f1
+ -> Hash
+ Output: i42.f1
+ -> Seq Scan on public.int4_tbl i42
+ Output: i42.f1
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
Filter: (i8.q1 = 0)
- -> Seq Scan on public.int4_tbl i43
- Output: i43.f1
- Filter: (i43.f1 = 0)
- -> Seq Scan on public.int4_tbl i41
- Output: i41.f1
- -> Hash
- Output: i42.f1
- -> Seq Scan on public.int4_tbl i42
- Output: i42.f1
- -> Limit
+ -> Seq Scan on public.int4_tbl i43
+ Output: i43.f1
+ Filter: (i43.f1 = 0)
+ -> Materialize
Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
- -> Seq Scan on public.text_tbl
- Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42)
-(25 rows)
+ -> Limit
+ Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
+ -> Result
+ Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42)
+ -> Materialize
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on public.text_tbl
+ Optimizer: Postgres query optimizer
+(36 rows)
--end_ignore
select ss2.* from
@@ -5729,17 +5744,14 @@ explain (costs off)
QUERY PLAN
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
- -> Hash Full Join
- Hash Cond: (a.unique2 = b.unique2)
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: a.unique2
- -> Index Scan using tenk1_unique2 on tenk1 a
- Index Cond: (unique2 = 42)
- -> Hash
- -> Redistribute Motion 3:3 (slice3; segments: 3)
- Hash Key: b.unique2
+ -> Nested Loop Left Join
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: 0
-> Index Scan using tenk1_unique2 on tenk1 b
- Index Cond: (unique2 = 42)
+ Index Cond: (unique2 = 0)
Optimizer: Postgres query optimizer
(10 rows)
@@ -5861,14 +5873,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;
- QUERY PLAN
-----------------------------------------------------
- Nested Loop Left Join
- -> 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 Left Join
+ -> Index Only Scan using onek_unique1 on onek a
+ Index Cond: (unique1 = 42)
+ -> Materialize
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: 42
+ -> 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
@@ -5882,14 +5899,18 @@ explain (costs off)
select a.unique1, b.unique2
from onek a full join onek b on a.unique1 = b.unique2
where b.unique2 = 43;
- QUERY PLAN
-----------------------------------------------------
- Nested Loop Left Join
- -> Index Only Scan using onek_unique2 on onek b
- Index Cond: (unique2 = 43)
- -> Index Only Scan using onek_unique1 on onek a
- Index Cond: (unique1 = 43)
-(5 rows)
+ QUERY PLAN
+----------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: 43
+ -> Index Only Scan using onek_unique2 on onek b
+ Index Cond: (unique2 = 43)
+ -> Index Only Scan using onek_unique1 on onek a
+ Index Cond: (unique1 = 43)
+ Optimizer: Postgres query optimizer
+(9 rows)
select a.unique1, b.unique2
from onek a full join onek b on a.unique1 = b.unique2
@@ -6023,13 +6044,16 @@ select a1.id from
left join
(a a3 left join a a4 on a3.id = a4.id)
on a2.id = a3.id;
- QUERY PLAN
-------------------------------
- Nested Loop Left Join
- -> Seq Scan on a a1
- -> Materialize
- -> Seq Scan on a a2
-(4 rows)
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Seq Scan on a a1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on a a2
+ Optimizer: Postgres query optimizer
+(7 rows)
explain (costs off)
select a1.id from
@@ -6037,26 +6061,32 @@ select a1.id from
left join
(a a3 left join a a4 on a3.id = a4.id)
on a2.id = a3.id;
- QUERY PLAN
-------------------
- Seq Scan on a a1
-(1 row)
+ QUERY PLAN
+------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on a a1
+ Optimizer: Postgres query optimizer
+(3 rows)
explain (costs off)
select 1 from a t1
left join a t2 on true
inner join a t3 on true
left join a t4 on t2.id = t4.id and t2.id = t3.id;
- QUERY PLAN
-------------------------------------
- Nested Loop
- -> Nested Loop Left Join
- -> Seq Scan on a t1
+ QUERY PLAN
+---------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop
+ -> Nested Loop Left Join
+ -> Seq Scan on a t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on a t2
-> Materialize
- -> Seq Scan on a t2
- -> Materialize
- -> Seq Scan on a t3
-(7 rows)
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on a t3
+ Optimizer: Postgres query optimizer
+(11 rows)
-- another example (bug #17781)
explain (costs off)
@@ -6071,14 +6101,16 @@ from int4_tbl as t1
inner join int8_tbl as t7 on null)
on t5.q1 = t7.q2)
on false;
- QUERY PLAN
---------------------------------
- Nested Loop Left Join
- Join Filter: false
- -> Seq Scan on int4_tbl t1
- -> Result
- One-Time Filter: false
-(5 rows)
+ QUERY PLAN
+------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ Join Filter: false
+ -> Seq Scan on int4_tbl t1
+ -> Result
+ One-Time Filter: false
+ Optimizer: Postgres query optimizer
+(7 rows)
-- variant with Var rather than PHV coming from t6
explain (costs off)
@@ -6093,14 +6125,16 @@ from int4_tbl as t1
inner join int8_tbl as t7 on null)
on t5.q1 = t7.q2)
on false;
- QUERY PLAN
---------------------------------
- Nested Loop Left Join
- Join Filter: false
- -> Seq Scan on int4_tbl t1
- -> Result
- One-Time Filter: false
-(5 rows)
+ QUERY PLAN
+------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ Join Filter: false
+ -> Seq Scan on int4_tbl t1
+ -> Result
+ One-Time Filter: false
+ Optimizer: Postgres query optimizer
+(7 rows)
-- per further discussion of bug #17781
explain (costs off)
@@ -6108,15 +6142,16 @@ select ss1.x
from (select f1/2 as x from int4_tbl i4 left join a on a.id = i4.f1) ss1
right join int8_tbl i8 on true
where current_user is not null; -- this is to add a Result node
- QUERY PLAN
------------------------------------------------
- Result
- One-Time Filter: (CURRENT_USER IS NOT NULL)
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
-> Seq Scan on int8_tbl i8
-> Materialize
- -> Seq Scan on int4_tbl i4
-(6 rows)
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl i4
+ Optimizer: Postgres query optimizer
+(7 rows)
-- and further discussion of bug #17781
explain (costs off)
@@ -6126,39 +6161,49 @@ from int8_tbl t1
on t1.q2 = t2.q2
left join onek t4
on t2.q2 < t3.unique2;
- QUERY PLAN
--------------------------------------------------
- Nested Loop Left Join
- Join Filter: (t2.q2 < t3.unique2)
- -> Nested Loop Left Join
- Join Filter: (t2.q1 > t3.unique1)
- -> Hash Left Join
- Hash Cond: (t1.q2 = t2.q2)
- -> Seq Scan on int8_tbl t1
- -> Hash
- -> Seq Scan on int8_tbl t2
- -> Materialize
- -> Seq Scan on onek t3
- -> Materialize
- -> Seq Scan on onek t4
-(13 rows)
-
--- More tests of correct placement of pseudoconstant quals
--- simple constant-false condition
-explain (costs off)
-select * from int8_tbl t1 left join
- (int8_tbl t2 inner join int8_tbl t3 on false
- left join int8_tbl t4 on t2.q2 = t4.q2)
-on t1.q1 = t2.q1;
- QUERY PLAN
---------------------------------------
- Hash Left Join
- Hash Cond: (t1.q1 = q1)
- -> Seq Scan on int8_tbl t1
- -> Hash
- -> Result
- One-Time Filter: false
-(6 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Right Join
+ Hash Cond: (t2.q2 = t1.q2)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: t2.q2
+ -> Nested Loop Left Join
+ Join Filter: (t2.q2 < t3.unique2)
+ -> Nested Loop Left Join
+ Join Filter: (t2.q1 > t3.unique1)
+ -> Seq Scan on int8_tbl t2
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3; segments:
3)
+ -> Seq Scan on onek t3
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice4; segments: 3)
+ -> Seq Scan on onek t4
+ -> Hash
+ -> Redistribute Motion 3:3 (slice5; segments: 3)
+ Hash Key: t1.q2
+ -> Seq Scan on int8_tbl t1
+ Optimizer: Postgres query optimizer
+(21 rows)
+
+-- More tests of correct placement of pseudoconstant quals
+-- simple constant-false condition
+explain (costs off)
+select * from int8_tbl t1 left join
+ (int8_tbl t2 inner join int8_tbl t3 on false
+ left join int8_tbl t4 on t2.q2 = t4.q2)
+on t1.q1 = t2.q1;
+ QUERY PLAN
+--------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Left Join
+ Hash Cond: (t1.q1 = q1)
+ -> Seq Scan on int8_tbl t1
+ -> Hash
+ -> Result
+ One-Time Filter: false
+ Optimizer: Postgres query optimizer
+(8 rows)
-- deduce constant-false from an EquivalenceClass
explain (costs off)
@@ -6166,15 +6211,17 @@ select * from int8_tbl t1 left join
(int8_tbl t2 inner join int8_tbl t3 on (t2.q1-t3.q2) = 0 and (t2.q1-t3.q2) =
1
left join int8_tbl t4 on t2.q2 = t4.q2)
on t1.q1 = t2.q1;
- QUERY PLAN
---------------------------------------
- Hash Left Join
- Hash Cond: (t1.q1 = q1)
- -> Seq Scan on int8_tbl t1
- -> Hash
- -> Result
- One-Time Filter: false
-(6 rows)
+ QUERY PLAN
+--------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Left Join
+ Hash Cond: (t1.q1 = q1)
+ -> Seq Scan on int8_tbl t1
+ -> Hash
+ -> Result
+ One-Time Filter: false
+ Optimizer: Postgres query optimizer
+(8 rows)
-- pseudoconstant based on an outer-level Param
explain (costs off)
@@ -6184,25 +6231,34 @@ select exists(
left join int8_tbl t4 on t2.q2 = t4.q2)
on t1.q1 = t2.q1
) from int4_tbl x0;
- QUERY PLAN
----------------------------------------------------------------------
- Seq Scan on int4_tbl x0
- SubPlan 1
- -> Nested Loop Left Join
- Join Filter: (t2.q2 = t4.q2)
- -> Nested Loop Left Join
- Join Filter: (t1.q1 = t2.q1)
- -> Seq Scan on int8_tbl t1
- -> Materialize
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on int4_tbl x0
+ SubPlan 1
+ -> Hash Left Join
+ Hash Cond: (t2.q2 = t4.q2)
+ -> Hash Right Join
+ Hash Cond: (t2.q1 = t1.q1)
-> Result
One-Time Filter: (x0.f1 = 1)
-> Nested Loop
- -> Seq Scan on int8_tbl t2
-> Materialize
- -> Seq Scan on int8_tbl t3
- -> Materialize
- -> Seq Scan on int8_tbl t4
-(16 rows)
+ -> Broadcast Motion 3:3 (slice2;
segments: 3)
+ -> Seq Scan on int8_tbl t2
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3;
segments: 3)
+ -> Seq Scan on int8_tbl t3
+ -> Hash
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice4;
segments: 3)
+ -> Seq Scan on int8_tbl t1
+ -> Hash
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice5; segments: 3)
+ -> Seq Scan on int8_tbl t4
+ Optimizer: Postgres query optimizer
+(25 rows)
-- check that join removal works for a left join when joining a subquery
-- that is guaranteed to be unique by its GROUP BY clause
@@ -6272,17 +6328,24 @@ select d.* from d left join (select distinct * from b) s
explain (costs off)
select 1 from a t1
left join (a t2 left join a t3 on t2.id = 1) on t2.id = 1;
- QUERY PLAN
---------------------------------------------------------
- Nested Loop Left Join
- -> Seq Scan on a t1
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: (t2.id = 1)
- -> Index Only Scan using a_pkey on a t2
- Index Cond: (id = 1)
- -> Seq Scan on a t3
-(8 rows)
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: 1
+ -> Seq Scan on a t1
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: (t2.id = 1)
+ -> Seq Scan on a t2
+ Filter: (id = 1)
+ -> Materialize
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: 1
+ -> Seq Scan on a t3
+ Optimizer: Postgres query optimizer
+(15 rows)
-- check join removal works when uniqueness of the join condition is enforced
-- by a UNION
@@ -6326,26 +6389,38 @@ explain (costs off)
select c.id, ss.a from c
left join (select d.a from onerow, d left join b on d.a = b.id) ss
on c.id = ss.a;
- QUERY PLAN
---------------------------------
- Hash Right Join
- Hash Cond: (d.a = c.id)
- -> Nested Loop
- -> Seq Scan on onerow
- -> Seq Scan on d
- -> Hash
- -> Seq Scan on c
-(7 rows)
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Right Join
+ Hash Cond: (d.a = c.id)
+ -> Nested Loop
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on onerow
+ -> Seq Scan on d
+ -> Hash
+ -> Seq Scan on c
+ Optimizer: Postgres query optimizer
+(10 rows)
CREATE TEMP TABLE parted_b (id int PRIMARY KEY) partition by range(id);
CREATE TEMP TABLE parted_b1 partition of parted_b for values from (0) to (10);
-- test join removals on a partitioned table
explain (costs off)
select a.* from a left join parted_b pb on a.b_id = pb.id;
- QUERY PLAN
----------------
- Seq Scan on a
-(1 row)
+ QUERY PLAN
+------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Right Join
+ Hash Cond: (pb.id = a.b_id)
+ -> Seq Scan on parted_b1 pb
+ -> Hash
+ -> Partition Selector (selector id: $0)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: a.b_id
+ -> Seq Scan on a
+ Optimizer: Postgres query optimizer
+(10 rows)
rollback;
create temp table parent (k int primary key, pd int);
@@ -6408,12 +6483,15 @@ explain (costs off)
select p.* from
parent p left join child c on (p.k = c.k)
where p.k = 1 and p.k = 2;
- QUERY PLAN
------------------------------------
- Result
- One-Time Filter: false
+ QUERY PLAN
+------------------------------------------------------
+ Gather Motion 1:1 (slice1; segments: 1)
+ -> Result
+ One-Time Filter: false
+ -> Index Scan using parent_pkey on parent p
+ Index Cond: (k = 1)
Optimizer: Postgres query optimizer
-(3 rows)
+(6 rows)
select p.* from
(parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k
@@ -6477,14 +6555,19 @@ SELECT q2 FROM
(SELECT *
FROM int8_tbl LEFT JOIN innertab ON q2 = id) ss
WHERE COALESCE(dat1, 0) = q1;
- QUERY PLAN
-----------------------------------------------------------------
- Nested Loop Left Join
- Filter: (COALESCE(innertab.dat1, '0'::bigint) = int8_tbl.q1)
- -> Seq Scan on int8_tbl
- -> Index Scan using innertab_pkey on innertab
- Index Cond: (id = int8_tbl.q2)
-(5 rows)
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Right Join
+ Hash Cond: (innertab.id = int8_tbl.q2)
+ Filter: (COALESCE(innertab.dat1, '0'::bigint) = int8_tbl.q1)
+ -> Seq Scan on innertab
+ -> Hash
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: int8_tbl.q2
+ -> Seq Scan on int8_tbl
+ Optimizer: Postgres query optimizer
+(10 rows)
-- join removal bug #17773: otherwise-removable PHV appears in a qual condition
EXPLAIN (VERBOSE, COSTS OFF)
@@ -6493,18 +6576,21 @@ SELECT q2 FROM
FROM int8_tbl LEFT JOIN innertab ON q2 = id) ss
RIGHT JOIN int4_tbl ON NULL
WHERE x >= x;
- QUERY PLAN
-------------------------------------------------------
- Nested Loop Left Join
+ QUERY PLAN
+------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
Output: q2
- Join Filter: NULL::boolean
- Filter: (('constant'::text) >= ('constant'::text))
- -> Seq Scan on public.int4_tbl
- Output: int4_tbl.f1
- -> Result
- Output: q2, 'constant'::text
- One-Time Filter: false
-(9 rows)
+ -> Nested Loop Left Join
+ Output: q2
+ Join Filter: NULL::boolean
+ Filter: (('constant'::text) >= ('constant'::text))
+ -> Seq Scan on public.int4_tbl
+ Output: int4_tbl.f1
+ -> Result
+ Output: q2, 'constant'::text
+ One-Time Filter: false
+ Optimizer: Postgres query optimizer
+(12 rows)
-- join removal bug #17786: check that OR conditions are cleaned up
EXPLAIN (COSTS OFF)
@@ -6513,18 +6599,21 @@ FROM int4_tbl
JOIN ((SELECT 42 AS x FROM int8_tbl LEFT JOIN innertab ON q1 = id) AS ss1
RIGHT JOIN tenk1 ON NULL)
ON tenk1.unique1 = ss1.x OR tenk1.unique2 = ss1.x;
- QUERY PLAN
---------------------------------------------------------------------------
- Nested Loop
- -> Seq Scan on int4_tbl
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: NULL::boolean
- Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42)))
- -> Seq Scan on tenk1
- -> Result
- One-Time Filter: false
-(9 rows)
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: NULL::boolean
+ Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42)))
+ -> Seq Scan on tenk1
+ -> Result
+ One-Time Filter: false
+ Optimizer: Postgres query optimizer
+(12 rows)
rollback;
-- another join removal bug: we must clean up correctly when removing a PHV
@@ -6603,14 +6692,17 @@ from t t1
from t t2 left join t t3 on t2.a = t3.a) s
on true
where t1.a = s.c;
- QUERY PLAN
-------------------------------
- Nested Loop Left Join
- Filter: (t1.a = (2))
- -> Seq Scan on t t1
- -> Materialize
- -> Seq Scan on t t2
-(5 rows)
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ Filter: (t1.a = (2))
+ -> Seq Scan on t t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on t t2
+ Optimizer: Postgres query optimizer
+(8 rows)
select 1
from t t1
@@ -6635,17 +6727,21 @@ from t t1
on true
left join t t4 on true
where s.a < s.c;
- QUERY PLAN
--------------------------------------
- Nested Loop Left Join
- -> Nested Loop
- -> Seq Scan on t t1
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Nested Loop
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on t t1
+ -> Materialize
+ -> Seq Scan on t t2
+ Filter: (a < 1)
-> Materialize
- -> Seq Scan on t t2
- Filter: (a < 1)
- -> Materialize
- -> Seq Scan on t t4
-(8 rows)
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on t t4
+ Optimizer: Postgres query optimizer
+(12 rows)
explain (costs off)
select t1.a, s.*
@@ -6655,16 +6751,20 @@ from t t1
on true
left join t t4 on true
where s.a < s.c;
- QUERY PLAN
------------------------------------------------
- Nested Loop Left Join
- -> Nested Loop
- -> Seq Scan on t t1
- -> Seq Scan on t t2
- Filter: (a < COALESCE(t1.a, 1))
- -> Materialize
- -> Seq Scan on t t4
-(7 rows)
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Nested Loop
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on t t1
+ -> Seq Scan on t t2
+ Filter: (a < COALESCE(t1.a, 1))
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on t t4
+ Optimizer: Postgres query optimizer
+(11 rows)
select t1.a, s.*
from t t1
@@ -7102,84 +7202,12 @@ explain (costs off)
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
on x.q2 = ss.z
order by a.q1, a.q2, x.q1, x.q2, ss.z;
- QUERY PLAN
-------------------------------------------------
- Sort
- Sort Key: a.q1, a.q2, x.q1, x.q2, (a.q1)
- -> Nested Loop
- -> Seq Scan on int8_tbl a
- -> Hash Left Join
- Hash Cond: (x.q2 = (a.q1))
- -> Seq Scan on int8_tbl x
- -> Hash
- -> Seq Scan on int4_tbl y
-(9 rows)
-
+ERROR: could not devise a query plan for the given query (pathnode.c:285)
select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
on x.q2 = ss.z
order by a.q1, a.q2, x.q1, x.q2, ss.z;
- q1 | q2 | q1 | q2 |
z
-------------------+-------------------+------------------+-------------------+------------------
- 123 | 456 | 123 | 456 |
- 123 | 456 | 123 | 4567890123456789 |
- 123 | 456 | 4567890123456789 | -4567890123456789 |
- 123 | 456 | 4567890123456789 | 123 |
123
- 123 | 456 | 4567890123456789 | 123 |
123
- 123 | 456 | 4567890123456789 | 123 |
123
- 123 | 456 | 4567890123456789 | 123 |
123
- 123 | 456 | 4567890123456789 | 123 |
123
- 123 | 456 | 4567890123456789 | 4567890123456789 |
- 123 | 4567890123456789 | 123 | 456 |
- 123 | 4567890123456789 | 123 | 4567890123456789 |
- 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
- 123 | 4567890123456789 | 4567890123456789 | 123 |
123
- 123 | 4567890123456789 | 4567890123456789 | 123 |
123
- 123 | 4567890123456789 | 4567890123456789 | 123 |
123
- 123 | 4567890123456789 | 4567890123456789 | 123 |
123
- 123 | 4567890123456789 | 4567890123456789 | 123 |
123
- 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
- 4567890123456789 | -4567890123456789 | 123 | 456 |
- 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 |
- 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 |
- 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 123 | 456 |
- 4567890123456789 | 123 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 4567890123456789 | -4567890123456789 |
- 4567890123456789 | 123 | 4567890123456789 | 123 |
- 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 123 | 456 |
- 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
- 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 |
- 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
- 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
4567890123456789
-(57 rows)
-
+ERROR: could not devise a query plan for the given query (pathnode.c:285)
--end_ignore
-- lateral reference to a join alias variable
select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
@@ -7647,10 +7675,10 @@ select * from
-> 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
- Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2,
'42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))
+ 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, d.q1, (COALESCE(b.q2,
'42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))
+ Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)),
d.q1, (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))
-> Broadcast Motion 3:3 (slice2; segments: 3)
Output: a.q1, a.q2, b.q1, (COALESCE(b.q2,
'42'::bigint))
-> Hash Left Join
@@ -7808,22 +7836,33 @@ explain (costs off)
select * from int4_tbl t1,
lateral (select * from int4_tbl t2 inner join int4_tbl t3 on t1.f1 = 1
inner join (int4_tbl t4 left join int4_tbl t5 on true) on true) ss;
- QUERY PLAN
--------------------------------------------------
- Nested Loop Left Join
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
- -> Nested Loop
- -> Nested Loop
- -> Seq Scan on int4_tbl t1
- Filter: (f1 = 1)
- -> Seq Scan on int4_tbl t2
- -> Materialize
- -> Seq Scan on int4_tbl t3
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: 1
+ -> Seq Scan on int4_tbl t3
-> Materialize
- -> Seq Scan on int4_tbl t4
- -> Materialize
- -> Seq Scan on int4_tbl t5
-(13 rows)
+ -> Nested Loop
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: 1
+ -> Seq Scan on int4_tbl t2
+ -> Materialize
+ -> Nested Loop Left Join
+ -> Nested Loop
+ -> Seq Scan on int4_tbl t1
+ Filter: (f1 = 1)
+ -> Materialize
+ -> Redistribute Motion 3:3
(slice4; segments: 3)
+ Hash Key: 1
+ -> Seq Scan on int4_tbl t4
+ -> Materialize
+ -> Redistribute Motion 3:3 (slice5;
segments: 3)
+ Hash Key: 1
+ -> Seq Scan on int4_tbl t5
+ Optimizer: Postgres query optimizer
+(24 rows)
-- check dummy rels with lateral references (bug #15694)
explain (verbose, costs off)
@@ -8055,41 +8094,12 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
from join_pt1 t2 join join_ut1 t3 on
t2.a = t3.b) ss
on t1.a = ss.t2a order by t1.a;
- QUERY PLAN
---------------------------------------------------------------------
- Sort
- Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
- Sort Key: t1.a
- -> Nested Loop Left Join
- Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
- -> Seq Scan on public.join_ut1 t1
- Output: t1.a, t1.b, t1.c
- -> Hash Join
- Output: t2.a, LEAST(t1.a, t2.a, t3.a)
- Hash Cond: (t3.b = t2.a)
- -> Seq Scan on public.join_ut1 t3
- Output: t3.a, t3.b, t3.c
- -> Hash
- Output: t2.a
- -> Append
- -> Seq Scan on public.join_pt1p1p1 t2_1
- Output: t2_1.a
- Filter: (t1.a = t2_1.a)
- -> Seq Scan on public.join_pt1p2 t2_2
- Output: t2_2.a
- Filter: (t1.a = t2_2.a)
-(21 rows)
-
+ERROR: could not devise a query plan for the given query (pathnode.c:285)
select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
from join_pt1 t2 join join_ut1 t3 on
t2.a = t3.b) ss
on t1.a = ss.t2a order by t1.a;
- b | phv
------+-----
- 2 |
- 101 | 101
-(2 rows)
-
+ERROR: could not devise a query plan for the given query (pathnode.c:285)
-- end_ignore
drop table join_pt1;
drop table join_ut1;
@@ -8555,22 +8565,37 @@ left join j2 on j1.id1 = j2.id1 where j1.id2 = 1;
(17 rows)
create unique index j1_id2_idx on j1(id2) where id2 is not null;
+ERROR: UNIQUE index must contain all columns in the table's distribution key
+DETAIL: Distribution key column "id1" is not included in the constraint.
-- ensure we don't use a partial unique index as unique proofs
explain (verbose, costs off)
select * from j1
inner join j2 on j1.id2 = j2.id2;
- QUERY PLAN
-------------------------------------------
- Nested Loop
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
Output: j1.id1, j1.id2, j2.id1, j2.id2
- Join Filter: (j2.id2 = j1.id2)
- -> Seq Scan on public.j2
- Output: j2.id1, j2.id2
- -> Seq Scan on public.j1
- Output: j1.id1, j1.id2
-(7 rows)
+ -> Nested Loop
+ Output: j1.id1, j1.id2, j2.id1, j2.id2
+ Join Filter: (j1.id2 = j2.id2)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: j1.id1, j1.id2
+ Hash Key: j1.id2
+ -> Seq Scan on public.j1
+ Output: j1.id1, j1.id2
+ -> Materialize
+ Output: j2.id1, j2.id2
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: j2.id1, j2.id2
+ Hash Key: j2.id2
+ -> Seq Scan on public.j2
+ Output: j2.id1, j2.id2
+ Settings: enable_nestloop = 'on'
+ Optimizer: Postgres query optimizer
+(19 rows)
drop index j1_id2_idx;
+ERROR: index "j1_id2_idx" does not exist
-- validate logic in merge joins which skips mark and restore.
-- it should only do this if all quals which were used to detect the unique
-- are present as join quals, and not plain quals.
@@ -8759,18 +8784,25 @@ CREATE STATISTICS group_tbl_stat (ndistinct) ON a, b
FROM group_tbl;
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;
QUERY PLAN
---------------------------------------------------------------------------------
GroupAggregate
Group Key: t2.a, (COALESCE(t2.a))
- -> Sort
- Sort Key: t2.a, (COALESCE(t2.a))
- -> Nested Loop Left Join
- -> Seq Scan on group_tbl t1
- -> Seq Scan on group_tbl t2
-(7 rows)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: t2.a, (COALESCE(t2.a))
+ -> GroupAggregate
+ Group Key: t2.a, (COALESCE(t2.a))
+ -> Sort
+ Sort Key: t2.a, (COALESCE(t2.a))
+ -> Nested Loop Left Join
+ -> Seq Scan on group_tbl t1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments:
3)
+ -> Seq Scan on group_tbl t2
+ Optimizer: Postgres query optimizer
+(14 rows)
DROP TABLE group_tbl;
reset enable_hashjoin;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
