public inbox for [email protected]
help / color / mirror / Atom feedFrom: Richard Guo <[email protected]>
To: Tender Wang <[email protected]>
Cc: Tomasz Rybak <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: David Rowley <[email protected]>
Subject: Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
Date: Tue, 18 Jun 2024 17:24:05 +0800
Message-ID: <CAMbWs48TaubitHF+JXqysbSHLCzTLOU9PD3zpLGFUtizsonAMA@mail.gmail.com> (raw)
In-Reply-To: <CAHewXNmWbA9Oe6JCEc66dJrVyyBDv1uSx6z_zdz-BQRE0Jr9pQ@mail.gmail.com>
References: <CAHewXNkPmtEXNfVQMou_7NqQmFABca9f4etjBtdbbm0ZKDmWvw@mail.gmail.com>
<CAMbWs49LbQF_Z0iKPRPnTHfsRECT7M-4rF6ft5vpW1ARSpBkPA@mail.gmail.com>
<CA+TgmobPBCvTHnBVn+a-=MS7pO_PUvtiLJkYo=BOrUjOxTss8g@mail.gmail.com>
<CAMbWs49agQ1THL0XYv_4aeZXN2R1cRksevja05n-MFOzZtNsuA@mail.gmail.com>
<CAHewXN=-XQwFY0n5EMUTzvK4G0aHWkNzdOPCfwnmQw3kDed2zQ@mail.gmail.com>
<[email protected]>
<CAHewXN=rP49ySfue9FA437jW4Me+FLBY-58zqTy+A_jxGGW=Xg@mail.gmail.com>
<[email protected]>
<CAHewXNmWbA9Oe6JCEc66dJrVyyBDv1uSx6z_zdz-BQRE0Jr9pQ@mail.gmail.com>
On Tue, Jun 4, 2024 at 6:51 PM Tender Wang <[email protected]> wrote:
> Yeah, Richard commented the v1 patch about JOIN_UNIQUE_INNER in [1]
>
> * I think we should not consider materializing the cheapest inner path
> if we're doing JOIN_UNIQUE_INNER, because in this case we have to
> unique-ify the inner path.
>
> We don't consider material inner path if jointype is JOIN_UNIQUE_INNER in match_unsorted_order().
> So here is as same logic as match_unsorted_order(). I added comments to explain why.
I looked through the v4 patch and found an issue. For the plan diff:
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_p1 t1_1
+ -> Materialize
+ -> Sample Scan on prt1_p1 t2_1
+ Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
+ Filter: (t1_1.a = a)
This does not seem correct to me. The inner path is parameterized by
the outer rel, in which case it does not make sense to add a Materialize
node on top of it.
I updated the patch to include a check in consider_parallel_nestloop
ensuring that inner_cheapest_total is not parameterized by outerrel
before materializing it. I also tweaked the comments, test cases and
commit message.
Thanks
Richard
Attachments:
[application/octet-stream] v5-0001-Consider-materializing-the-cheapest-inner-path-in-parallel-nestloop.patch (4.9K, ../CAMbWs48TaubitHF+JXqysbSHLCzTLOU9PD3zpLGFUtizsonAMA@mail.gmail.com/2-v5-0001-Consider-materializing-the-cheapest-inner-path-in-parallel-nestloop.patch)
download | inline diff:
From 4ea6363d232ba3929fd7edd18e271217c85475ba Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Tue, 18 Jun 2024 16:25:19 +0900
Subject: [PATCH v5] Consider materializing the cheapest inner path in parallel
nestloop
When generating non-parallel nestloop paths for each available outer
path, we always consider materializing the cheapest inner path if
feasible. Similarly, in this patch, we also consider materializing the
cheapest inner path when building partial nestloop paths. This approach
potentially reduces the need to rescan the inner side of a partial
nestloop path for each outer tuple.
---
src/backend/optimizer/path/joinpath.c | 26 ++++++++++++++++
src/test/regress/expected/select_parallel.out | 30 +++++++++++++++++++
src/test/regress/sql/select_parallel.sql | 10 +++++++
3 files changed, 66 insertions(+)
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 5be8da9e09..11e1253bcd 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -2014,11 +2014,32 @@ consider_parallel_nestloop(PlannerInfo *root,
JoinPathExtraData *extra)
{
JoinType save_jointype = jointype;
+ Path *inner_cheapest_total = innerrel->cheapest_total_path;
+ Path *matpath = NULL;
ListCell *lc1;
if (jointype == JOIN_UNIQUE_INNER)
jointype = JOIN_INNER;
+ /*
+ * Consider materializing the cheapest inner path, unless:
+ * 1) we're doing JOIN_UNIQUE_INNER, because in this case we have to
+ * unique-ify the cheapest inner path,
+ * 2) enable_material is off,
+ * 3) the cheapest inner path is not parallel-safe,
+ * 4) the cheapest inner path is parameterized by the outer rel, or
+ * 5) the cheapest inner path materializes its output anyway.
+ */
+ if (save_jointype != JOIN_UNIQUE_INNER &&
+ enable_material && inner_cheapest_total->parallel_safe &&
+ !PATH_PARAM_BY_REL(inner_cheapest_total, outerrel) &&
+ !ExecMaterializesOutput(inner_cheapest_total->pathtype))
+ {
+ matpath = (Path *)
+ create_material_path(innerrel, inner_cheapest_total);
+ Assert(matpath->parallel_safe);
+ }
+
foreach(lc1, outerrel->partial_pathlist)
{
Path *outerpath = (Path *) lfirst(lc1);
@@ -2075,6 +2096,11 @@ consider_parallel_nestloop(PlannerInfo *root,
try_partial_nestloop_path(root, joinrel, outerpath, mpath,
pathkeys, jointype, extra);
}
+
+ /* Also consider materialized form of the cheapest inner path */
+ if (matpath != NULL)
+ try_partial_nestloop_path(root, joinrel, outerpath, matpath,
+ pathkeys, jointype, extra);
}
}
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 87273fa635..d13f812f36 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -653,6 +653,36 @@ select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
reset enable_hashjoin;
reset enable_nestloop;
+-- test parallel nestloop join path with materialization of the inner path.
+alter table tenk2 set (parallel_workers = 0);
+explain (costs off)
+ select * from tenk1 t1, tenk2 t2 where t1.two > t2.two;
+ QUERY PLAN
+-------------------------------------------
+ Gather
+ Workers Planned: 4
+ -> Nested Loop
+ Join Filter: (t1.two > t2.two)
+ -> Parallel Seq Scan on tenk1 t1
+ -> Materialize
+ -> Seq Scan on tenk2 t2
+(7 rows)
+
+-- this is not parallel-safe due to the OFFSET clause in the subquery
+explain (costs off)
+ select * from tenk1 t1, (select * from tenk2 t2 offset 0) t2 where t1.two > t2.two;
+ QUERY PLAN
+-------------------------------------------
+ Nested Loop
+ Join Filter: (t1.two > t2.two)
+ -> Gather
+ Workers Planned: 4
+ -> Parallel Seq Scan on tenk1 t1
+ -> Materialize
+ -> Seq Scan on tenk2 t2
+(7 rows)
+
+alter table tenk2 reset (parallel_workers);
-- test gather merge
set enable_hashagg = false;
explain (costs off)
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 20376c03fa..67dac7f62b 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -266,6 +266,16 @@ select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
reset enable_hashjoin;
reset enable_nestloop;
+-- test parallel nestloop join path with materialization of the inner path.
+alter table tenk2 set (parallel_workers = 0);
+explain (costs off)
+ select * from tenk1 t1, tenk2 t2 where t1.two > t2.two;
+
+-- this is not parallel-safe due to the OFFSET clause in the subquery
+explain (costs off)
+ select * from tenk1 t1, (select * from tenk2 t2 offset 0) t2 where t1.two > t2.two;
+alter table tenk2 reset (parallel_workers);
+
-- test gather merge
set enable_hashagg = false;
--
2.43.0
view thread (23+ 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: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
In-Reply-To: <CAMbWs48TaubitHF+JXqysbSHLCzTLOU9PD3zpLGFUtizsonAMA@mail.gmail.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