This is an automated email from the ASF dual-hosted git repository.
reshke pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 99c62cffc17 Fix JOIN motion type selection for join quals containing
outer refs (#1895)
99c62cffc17 is described below
commit 99c62cffc17ed623ea4c687da91c3bca96237493
Author: reshke <[email protected]>
AuthorDate: Tue Aug 18 23:58:09 2026 +0500
Fix JOIN motion type selection for join quals containing outer refs (#1895)
In some cases planner failed to mark join qual restrict clauses as ones
referring outer query levels. This makes join motion planning code to make
wrong query: it uses redistribute motion in cases it shouldn't.
In this simple reproduces this leads to motion plan node rescan (this is
execute-time ERROR normally). Note that for `generate_series(1,1) t1` it would
work, because our parametrized plan would execute only once.
```
CREATE TABLE t2(i int);
CREATE TABLE t3(i int);
INSERT INTO t2 SELECT generate_series(1,10);
INSERT INTO t3 SELECT generate_series(1,10);
select * from generate_series(1,2) t1, lateral (select t3.i from t2 join
t3 on t2.i = t3.i + t1 order by 1) z;
ERROR: illegal rescan of motion node: invalid plan (nodeMotion.c:1368)
(seg1 slice1 127.0.1.1:7003 pid=879693) (nodeMotion.c:1368)
HINT: Likely caused by bad NL-join, try setting enable_nestloop to off
reshke=# explain select * from generate_series(1,1) t1, lateral (select
t3.i from t2 join t3 on t2.i = t3.i + t1 order by 1) z;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
(cost=10000623579.03..10000785868.61 rows=9273690 width=8)
-> Nested Loop (cost=10000623579.03..10000662219.41 rows=3091230
width=8)
-> Function Scan on generate_series t1 (cost=0.00..0.01 rows=1
width=4)
-> Materialize (cost=623579.02..646763.25 rows=3091230 width=4)
-> Sort (cost=623579.02..631307.10 rows=3091230 width=4)
Sort Key: t3.i
-> Hash Join (cost=756.25..290348.30 rows=3091230
width=4)
Hash Cond: ((t3.i + t1.t1) = t2.i)
-> Redistribute Motion 3:3 (slice2; segments:
3) (cost=0.00..997.00 rows=32100 width=4)
Hash Key: (t3.i + t1.t1)
-> Seq Scan on t3 (cost=0.00..355.00
rows=32100 width=4)
-> Hash (cost=355.00..355.00 rows=32100
width=4)
-> Seq Scan on t2 (cost=0.00..355.00
rows=32100 width=4)
Optimizer: Postgres query optimizer
(14 rows)
```
As we can see, Redistribute Motion uses `Hash Key: (t3.i + t1.t1)` which
is parametrized by outer rel (t1).
With fix:
```
reshke=# explain select * from generate_series(1,1) t1, lateral (select
t3.i from t2 join t3 on t2.i = t3.i + t1 order by 1) z;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=10000000117.94..10000000129.99 rows=963 width=8)
-> Function Scan on generate_series t1 (cost=0.00..0.01 rows=1 width=4)
-> Materialize (cost=117.94..125.16 rows=963 width=4)
-> Sort (cost=117.94..120.34 rows=963 width=4)
Sort Key: t3.i
-> Hash Join (cost=33.90..70.21 rows=963 width=4)
Hash Cond: (t2.i = (t3.i + t1.t1))
-> Materialize (cost=0.00..21.87 rows=963 width=4)
-> Gather Motion 3:1 (slice1; segments: 3)
(cost=0.00..17.05 rows=963 width=4)
-> Seq Scan on t2 (cost=0.00..4.21
rows=321 width=4)
-> Hash (cost=21.87..21.87 rows=963 width=4)
-> Materialize (cost=0.00..21.87 rows=963
width=4)
-> Gather Motion 3:1 (slice2; segments:
3) (cost=0.00..17.05 rows=963 width=4)
-> Seq Scan on t3 (cost=0.00..4.21
rows=321 width=4)
Optimizer: Postgres query optimizer
(15 rows)
```
The before-plan is better in term of performance, expect it is not valid
;). We receive executor-time error becuase of motion rescan. The after-fix plan
is worse, but can be executed correctly. In fact, the sole thing this PR do is
correctly use infrastructure committed at 00e25afe119c
in fact, I think pushing down join below the motion is possible, so some
types of plans. But this is separate problem, in this PR I merely try to fix
ERROR: illegal rescan of motion node
---
src/backend/optimizer/plan/initsplan.c | 3 --
src/backend/optimizer/util/restrictinfo.c | 9 ++++
src/test/regress/expected/join_gp.out | 53 +++++++++++++++++++++++
src/test/regress/expected/join_gp_optimizer.out | 53 +++++++++++++++++++++++
src/test/regress/expected/join_hash.out | 42 +++++++++++-------
src/test/regress/expected/join_hash_optimizer.out | 43 +++++++++++-------
src/test/regress/sql/join_gp.sql | 16 +++++++
src/test/regress/sql/join_hash.sql | 2 +
8 files changed, 186 insertions(+), 35 deletions(-)
diff --git a/src/backend/optimizer/plan/initsplan.c
b/src/backend/optimizer/plan/initsplan.c
index b65897043ca..ce05a4301a4 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2970,9 +2970,6 @@ distribute_restrictinfo_to_rels(PlannerInfo *root,
Relids relids = restrictinfo->required_relids;
RelOptInfo *rel;
- if (contains_outer_params((Node *) restrictinfo->clause, root))
- restrictinfo->contain_outer_query_references = true;
-
switch (bms_membership(relids))
{
case BMS_SINGLETON:
diff --git a/src/backend/optimizer/util/restrictinfo.c
b/src/backend/optimizer/util/restrictinfo.c
index 45552d53aaf..c1a9404e91d 100644
--- a/src/backend/optimizer/util/restrictinfo.c
+++ b/src/backend/optimizer/util/restrictinfo.c
@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "cdb/cdbmutate.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
@@ -263,6 +264,14 @@ make_restrictinfo_internal(PlannerInfo *root,
restrictinfo->left_hasheqoperator = InvalidOid;
restrictinfo->right_hasheqoperator = InvalidOid;
+ /*
+ * Determine whether this clause references any var in outer query
levels.
+ * Such clauses must be evaluated in the same slice as the parent query,
+ * so we set this planner hint to later use in join motion planning.
+ */
+ restrictinfo->contain_outer_query_references =
+ contains_outer_params((Node *) clause, root);
+
return restrictinfo;
}
diff --git a/src/test/regress/expected/join_gp.out
b/src/test/regress/expected/join_gp.out
index e459d425fff..a8f99c64e64 100644
--- a/src/test/regress/expected/join_gp.out
+++ b/src/test/regress/expected/join_gp.out
@@ -3619,3 +3619,56 @@ drop table if exists repli_t1_pk;
drop table if exists repli_t2_pk;
drop table if exists repli_t3_pk;
drop table if exists repli_t4_pk;
+--
+-- Test that a join qual containing an outer-level reference
+-- is correctly identified as referring to the outer
+-- query, so that the join motion is planned in the parent slice.
+--
+create table lat_oq_t2(i int) distributed by (i);
+create table lat_oq_t3(i int) distributed by (i);
+insert into lat_oq_t2 select generate_series(1,10);
+insert into lat_oq_t3 select generate_series(1,10);
+explain (costs off) select * from generate_series(1,2) t1, lateral (select
t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Nested Loop
+ -> Function Scan on generate_series t1
+ -> Materialize
+ -> Sort
+ Sort Key: t3.i
+ -> Hash Join
+ Hash Cond: (t2.i = (t3.i + t1.t1))
+ -> Materialize
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on lat_oq_t2 t2
+ -> Hash
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on lat_oq_t3 t3
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2
join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+ t1 | i
+----+---
+ 1 | 1
+ 1 | 2
+ 1 | 3
+ 1 | 4
+ 1 | 5
+ 1 | 6
+ 1 | 7
+ 1 | 8
+ 1 | 9
+ 2 | 1
+ 2 | 2
+ 2 | 3
+ 2 | 4
+ 2 | 5
+ 2 | 6
+ 2 | 7
+ 2 | 8
+(17 rows)
+
+drop table lat_oq_t2;
+drop table lat_oq_t3;
diff --git a/src/test/regress/expected/join_gp_optimizer.out
b/src/test/regress/expected/join_gp_optimizer.out
index 2c80ee0add7..ead29d2dd3b 100644
--- a/src/test/regress/expected/join_gp_optimizer.out
+++ b/src/test/regress/expected/join_gp_optimizer.out
@@ -3581,3 +3581,56 @@ drop table if exists repli_t1_pk;
drop table if exists repli_t2_pk;
drop table if exists repli_t3_pk;
drop table if exists repli_t4_pk;
+--
+-- Test that a join qual containing an outer-level reference
+-- is correctly identified as referring to the outer
+-- query, so that the join motion is planned in the parent slice.
+--
+create table lat_oq_t2(i int) distributed by (i);
+create table lat_oq_t3(i int) distributed by (i);
+insert into lat_oq_t2 select generate_series(1,10);
+insert into lat_oq_t3 select generate_series(1,10);
+explain (costs off) select * from generate_series(1,2) t1, lateral (select
t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Nested Loop
+ -> Function Scan on generate_series t1
+ -> Materialize
+ -> Sort
+ Sort Key: t3.i
+ -> Hash Join
+ Hash Cond: (t2.i = (t3.i + t1.t1))
+ -> Materialize
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on lat_oq_t2 t2
+ -> Hash
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on lat_oq_t3 t3
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2
join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+ t1 | i
+----+---
+ 1 | 1
+ 1 | 2
+ 1 | 3
+ 1 | 4
+ 1 | 5
+ 1 | 6
+ 1 | 7
+ 1 | 8
+ 1 | 9
+ 2 | 1
+ 2 | 2
+ 2 | 3
+ 2 | 4
+ 2 | 5
+ 2 | 6
+ 2 | 7
+ 2 | 8
+(17 rows)
+
+drop table lat_oq_t2;
+drop table lat_oq_t3;
diff --git a/src/test/regress/expected/join_hash.out
b/src/test/regress/expected/join_hash.out
index 681084c5318..f446822e861 100644
--- a/src/test/regress/expected/join_hash.out
+++ b/src/test/regress/expected/join_hash.out
@@ -1282,6 +1282,8 @@ ROLLBACK;
-- Verify that we behave sanely when the inner hash keys contain parameters
-- (that is, outer or lateral references). This situation has to defeat
-- re-use of the inner hash table across rescans.
+-- CBDB_FIXME: our planner implemention forces not-very-effitient
+-- plan here, we can enhace by pushin join below motion.
begin;
set local enable_hashjoin = on;
explain (costs off)
@@ -1289,28 +1291,36 @@ select i8.q2, ss.* from
int8_tbl i8,
lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4
on t1.fivethous = i4.f1+i8.q2 order by 1,2) ss;
- QUERY PLAN
+ QUERY PLAN
---------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Nested Loop
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Seq Scan on int8_tbl i8
- -> Materialize
- -> Sort
- Sort Key: t1.fivethous, i4.f1
- -> Hash Join
- Hash Cond: (t1.fivethous = (i4.f1 + i8.q2))
- -> Seq Scan on tenk1 t1
- -> Hash
- -> Broadcast Motion 3:3 (slice3; segments:
3)
+ Nested Loop
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on int8_tbl i8
+ -> Materialize
+ -> Sort
+ Sort Key: t1.fivethous, i4.f1
+ -> Hash Join
+ Hash Cond: (t1.fivethous = (i4.f1 + i8.q2))
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on tenk1 t1
+ -> Hash
+ -> Materialize
+ -> Gather Motion 3:1 (slice3; segments: 3)
-> Seq Scan on int4_tbl i4
Optimizer: Postgres query optimizer
-(14 rows)
+(16 rows)
select i8.q2, ss.* from
int8_tbl i8,
lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4
on t1.fivethous = i4.f1+i8.q2 order by 1,2) ss;
-ERROR: illegal rescan of motion node: invalid plan (nodeMotion.c:XXX)
-HINT: Likely caused by bad NL-join, try setting enable_nestloop to off
+ q2 | fivethous | f1
+-----+-----------+----
+ 123 | 123 | 0
+ 123 | 123 | 0
+ 456 | 456 | 0
+ 456 | 456 | 0
+(4 rows)
+
rollback;
diff --git a/src/test/regress/expected/join_hash_optimizer.out
b/src/test/regress/expected/join_hash_optimizer.out
index cb7511c6ebc..85e8053f468 100644
--- a/src/test/regress/expected/join_hash_optimizer.out
+++ b/src/test/regress/expected/join_hash_optimizer.out
@@ -1390,6 +1390,8 @@ ROLLBACK;
-- Verify that we behave sanely when the inner hash keys contain parameters
-- (that is, outer or lateral references). This situation has to defeat
-- re-use of the inner hash table across rescans.
+-- CBDB_FIXME: our planner implemention forces not-very-effitient
+-- plan here, we can enhace by pushin join below motion.
begin;
set local enable_hashjoin = on;
explain (costs off)
@@ -1398,26 +1400,35 @@ int8_tbl i8,
lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4
on t1.fivethous = i4.f1+i8.q2 order by 1,2) ss;
QUERY PLAN
---------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Nested Loop
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Seq Scan on int8_tbl i8
- -> Materialize
- -> Sort
- Sort Key: t1.fivethous, i4.f1
- -> Hash Join
- Hash Cond: (t1.fivethous = (i4.f1 + i8.q2))
- -> Seq Scan on tenk1 t1
- -> Hash
- -> Broadcast Motion 3:3 (slice3; segments:
3)
+---------------------------------------------------------------------------------
+ Nested Loop
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on int8_tbl i8
+ -> Materialize
+ -> Sort
+ Sort Key: t1.fivethous, i4.f1
+ -> Hash Join
+ Hash Cond: (t1.fivethous = (i4.f1 + i8.q2))
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on tenk1 t1
+ -> Hash
+ -> Materialize
+ -> Gather Motion 3:1 (slice3; segments: 3)
-> Seq Scan on int4_tbl i4
-(14 rows)
+ Optimizer: Postgres query optimizer
+(16 rows)
select i8.q2, ss.* from
int8_tbl i8,
lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4
on t1.fivethous = i4.f1+i8.q2 order by 1,2) ss;
-ERROR: illegal rescan of motion node: invalid plan (nodeMotion.c:XXX)
-HINT: Likely caused by bad NL-join, try setting enable_nestloop to off
+ q2 | fivethous | f1
+-----+-----------+----
+ 123 | 123 | 0
+ 123 | 123 | 0
+ 456 | 456 | 0
+ 456 | 456 | 0
+(4 rows)
+
rollback;
diff --git a/src/test/regress/sql/join_gp.sql b/src/test/regress/sql/join_gp.sql
index 6a96d9b98e1..f632cc5e0ac 100644
--- a/src/test/regress/sql/join_gp.sql
+++ b/src/test/regress/sql/join_gp.sql
@@ -1316,3 +1316,19 @@ drop table if exists repli_t1_pk;
drop table if exists repli_t2_pk;
drop table if exists repli_t3_pk;
drop table if exists repli_t4_pk;
+
+--
+-- Test that a join qual containing an outer-level reference
+-- is correctly identified as referring to the outer
+-- query, so that the join motion is planned in the parent slice.
+--
+create table lat_oq_t2(i int) distributed by (i);
+create table lat_oq_t3(i int) distributed by (i);
+insert into lat_oq_t2 select generate_series(1,10);
+insert into lat_oq_t3 select generate_series(1,10);
+
+explain (costs off) select * from generate_series(1,2) t1, lateral (select
t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2
join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+
+drop table lat_oq_t2;
+drop table lat_oq_t3;
diff --git a/src/test/regress/sql/join_hash.sql
b/src/test/regress/sql/join_hash.sql
index 6607047360e..85559b7d3d0 100644
--- a/src/test/regress/sql/join_hash.sql
+++ b/src/test/regress/sql/join_hash.sql
@@ -668,6 +668,8 @@ ROLLBACK;
-- Verify that we behave sanely when the inner hash keys contain parameters
-- (that is, outer or lateral references). This situation has to defeat
-- re-use of the inner hash table across rescans.
+-- CBDB_FIXME: our planner implemention forces not-very-effitient
+-- plan here, we can enhace by pushin join below motion.
begin;
set local enable_hashjoin = on;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
