agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: ld_zju <ld_zju@126.com>
To: pgsql-bugs@lists.postgresql.org
Subject: DO NOT pull up a sublink when it has no join condition with the upper relation
Date: Fri, 31 Jul 2026 00:15:43 +0800 (CST)
Message-ID: <13beb150.7b23.19fb3cf6efc.Coremail.ld_zju@126.com> (raw)
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
view thread (3+ messages) latest in thread
Message-ID: <13beb150.7b23.19fb3cf6efc.Coremail.ld_zju@126.com>
Permalink: ../13beb150.7b23.19fb3cf6efc.Coremail.ld_zju@126.com/
Also on: postgresql.org/message-id/13beb150.7b23.19fb3cf6efc.Coremail.ld_zju@126.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: ld_zju@126.com, pgsql-bugs@lists.postgresql.org
Subject: Re: DO NOT pull up a sublink when it has no join condition with the upper relation
In-Reply-To: <13beb150.7b23.19fb3cf6efc.Coremail.ld_zju@126.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox