public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Andy Fan <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: Andrey Lepikhov <[email protected]>
Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
Date: Fri, 13 Oct 2023 11:39:41 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKU4AWphxS_QKEeUhSkt46_+7W8Y3ddr9q9roK4oWt0g2BZVmQ@mail.gmail.com>
References: <CAKU4AWoZksNZ4VR-fLTdwmiR91WU8qViDBNQKNwY=7iyo+uV0w@mail.gmail.com>
<[email protected]>
<CAKU4AWpp8aANvwU1ivjPtF3AakFjTf1jBm_OFmPSdCqizjBQLw@mail.gmail.com>
<[email protected]>
<CAKU4AWrwggExWQ6mg7QGT59TVL6BGwBKbVn3ZQmmOAwOF3-w=g@mail.gmail.com>
<[email protected]>
<CAKU4AWphxS_QKEeUhSkt46_+7W8Y3ddr9q9roK4oWt0g2BZVmQ@mail.gmail.com>
On 13.10.2023 10:04, Andy Fan wrote:
>
> It seems to me that the expressions "=" and "IN" are equivalent
> here due to the fact that the aggregated subquery returns only one
> value, and the result with the "IN" operation can be considered as
> the intersection of elements on the left and right. In this query,
> we have some kind of set on the left, among which there will be
> found or not only one element on the right.
>
>
> Yes, they are equivalent at the final result, but there are some
> differences at the execution level. the '=' case will be transformed
> to a Subplan whose subPlanType is EXPR_SUBLINK, so if there
> is more than 1 rows is returned in the subplan, error will be raised.
>
> select * from tenk1 where
> ten = (select ten from tenk1 i where i.two = tenk1.two );
>
> ERROR: more than one row returned by a subquery used as an expression
>
> However the IN case would not.
> select * from tenk1 where
> ten = (select ten from tenk1 i where i.two = tenk1.two ) is OK.
>
> I think the test case you added is not related to this feature. the
> difference is there even without the patch. so I kept the code
> you changed, but not for the test case.
Yes, I understand and agree with you that we should delete the last
queries, except to one.
The query below have a different result compared to master, and it is
correct.
Without your patch:
explain (costs off)
+SELECT * FROM tenk1 A LEFT JOIN tenk2 B
ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop Left Join
-> Seq Scan on tenk1 a
-> Materialize
-> Seq Scan on tenk2 b
Filter: (SubPlan 2)
SubPlan 2
-> Result
InitPlan 1 (returns $1)
-> Limit
-> Index Scan using tenk2_hundred on
tenk2 c
Index Cond: (hundred IS NOT NULL)
Filter: (odd = b.odd)
(12 rows)
After your patch:
postgres=# explain (costs off)
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
QUERY PLAN
--------------------------------------------------------------
Nested Loop Left Join
-> Seq Scan on tenk1 a
-> Materialize
-> Nested Loop
-> Seq Scan on tenk2 b
*-> Subquery Scan on "ANY_subquery"
Filter: (b.hundred = "ANY_subquery".min)*
-> Aggregate
-> Seq Scan on tenk2 c
Filter: (odd = b.odd)
(10 rows)
>
>> I took the liberty of adding this to your patch and added
>> myself as reviewer, if you don't mind.
>>
>> Sure, the patch after your modification looks better than the
>> original.
>> I'm not sure how the test case around "because of got one row" is
>> relevant to the current changes. After we reach to some agreement
>> on the above discussion, I think v4 is good for committer to review!
>
> Thank you!) I am ready to discuss it.
>
> Actually I meant to discuss the "Unfortunately, I found a request..",
> looks
> we have reached an agreement there:)
>
Yes, we have)
--
Regards,
Alena Rybakina
Attachments:
[text/x-patch] pull-up.diff (2.1K, ../[email protected]/3-pull-up.diff)
download | inline diff:
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 17df6b5dc9c..e41b728df83 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -2028,3 +2028,27 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
-> Seq Scan on tenk2 b
(11 rows)
+-- we can pull up the aggregate sublink into RHS of a left join.
+explain (costs off)
+SELECT * FROM tenk1 A LEFT JOIN tenk2 B
+ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on tenk1 a
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on tenk2 b
+ -> Memoize
+ Cache Key: b.hundred, b.odd
+ Cache Mode: binary
+ -> Subquery Scan on "ANY_subquery"
+ Filter: (b.hundred = "ANY_subquery".min)
+ -> Result
+ InitPlan 1 (returns $1)
+ -> Limit
+ -> Index Scan using tenk2_hundred on tenk2 c
+ Index Cond: (hundred IS NOT NULL)
+ Filter: (odd = b.odd)
+(16 rows)
+
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 49ce5fc99a8..2f3601a0582 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -1000,3 +1000,8 @@ ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
explain (costs off)
SELECT * FROM tenk1 A INNER JOIN tenk2 B
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
+
+-- we can pull up the aggregate sublink into RHS of a left join.
+explain (costs off)
+SELECT * FROM tenk1 A LEFT JOIN tenk2 B
+ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
\ No newline at end of file
view thread (8+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
In-Reply-To: <[email protected]>
* 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