DO NOT pull up a sublink when it has no join condition with the upper relation

ld_zju <ld_zju@126.com> 于2026年7月31日周五 00:16写道：

Hi,

I've encountered a scenario where pulling up a sublink not only brings no benefit but actually degrades the final plan significantly.

Here is the test case:

create table t1(a int,b int,c int,d int);
create table t2(a int,b int,c int,d int);
create table t3(a int,b int,c int,d int);
insert into t1 select i,i,i,i from generate_series(1,1000) i;
insert into t2 select i,i,i,i from generate_series(1,1000) i;
insert into t3 select i,i,i,i from generate_series(1,10) i;

explain select * from t1 where exists(select 1 from t2 where t2.a in(select t3.a from t3 where t3.b=t1.b));
QUERY PLAN
-------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..28418232.67 rows=925 width=16)
Join Filter: (ANY (t2.a = (SubPlan any_1).col1))
-> Seq Scan on t1 (cost=0.00..28.50 rows=1850 width=16)
-> Materialize (cost=0.00..37.75 rows=1850 width=4)
-> Seq Scan on t2 (cost=0.00..28.50 rows=1850 width=4)
SubPlan any_1
-> Seq Scan on t3 (cost=0.00..33.12 rows=9 width=4)
Filter: (b = t1.b)
(8 rows)

The EXISTS sublink is pulled up and joined with t1 via a Nested Loop Semi Join. However, since there is no join condition between t1 and the sublink (the condition t3.b = t1.b is inside the subplan), this results in a Cartesian product between t1 and t2, followed by filtering through the subplan. With t1 and t2 both having 1000 rows, this produces a large intermediate result set (1,000,000 rows) when the actual result set is much smaller.

Would it be possible that the sublink is pulled up only when it has any join conditions with the upper relation? If no such conditions exist, a Cartesian product is likely and pulling up should be avoided.

Any thoughts or suggestions would be appreciated!

You can add "offset 0" into the subquery; then the plan should be what you want.
postgres=# explain select * from t1 where exists(select 1 from t2
where t2.a in(select t1.b from t3 where t3.b=t1.b) offset 0);
QUERY PLAN
------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..19651.00 rows=500 width=16)
Filter: EXISTS(SubPlan exists_1)
SubPlan exists_1
-> Nested Loop Semi Join (cost=0.00..19.64 rows=1 width=4)
-> Seq Scan on t2 (cost=0.00..18.50 rows=1 width=0)
Filter: (a = t1.b)
-> Seq Scan on t3 (cost=0.00..1.12 rows=1 width=0)
Filter: (b = t1.b)
(8 rows)

And the Execution Time: 178.877 ms; without "offset 0", it is 4048.970
ms on my machine.

In convert_EXISTS_sublink_to_join(), we have:
/*
* On the other hand, the WHERE clause must contain some Vars of the
* parent query, else it's not gonna be a join.
*/
if (!contain_vars_of_level(whereClause, 1))
return NULL;

When we recurse into the third sublink in
contain_vars_of_level_walker(), the levelsup was +1(i.e. 2)
t1.b in "t3.b = t1.b" is Var [varno=1 varattno=2 vartype=23
varlevelsup=2 varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1
varattnosyn=2]
You can see that varlevelsup is 2, so
contain_vars_of_level(whereClause, 1) returns true. Then the sublink
is pulled up.

I made some attempts.

#1
We can't simply remove the"(*sublevels_up)++; " in
contain_vars_of_level_walker(); because some other places also call
this function.
If you do this, the regression will crash.
#2
I rewrote a separate version based on the current implementation
specifically for SubLink pull-up. My goal was simply to see whether it
would cause any regression test failures.
The attached is my test. It's only for testing.
To my surprise, all the regression tests passed.

I'm not sure it is a bug. The code was committed 17 years ago by Tom.
And I'm not sure you're the first to report this issue.
I feel that in most cases, the second query will refer to the top
query's column, and the third query will refer to the second query's
column.

--
Thanks,
Tender Wang

Thank you for your quick response.

The reason why I thought it was a bug is only because oracle optimizer can generate a plan seems to be more reasonable. Its execution plan goes like "select * from t1 where exists(select 1 from t2, t3 where t3.b=t1.b and t2.a=t3.a);"

I have tested the suggested approach with "offset 0" in our test environment. It does resolve the immediate issue we encountered, and the performance impact is acceptable.

At 2026-07-31 10:52:55, "Tender Wang" <tndrwang@gmail.com> wrote: