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

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!

Best regards,
Deng, LU