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

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(at)gmail(dot)com> wrote:
>ld_zju <ld_zju(at)126(dot)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