public inbox for [email protected]
help / color / mirror / Atom feedRe: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
23+ messages / 10 participants
[nested] [flat]
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
@ 2023-09-07 19:14 Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 23:41 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() David Rowley <[email protected]>
2024-06-14 03:02 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
0 siblings, 3 replies; 23+ messages in thread
From: Robert Haas @ 2023-09-07 19:14 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: tender wang <[email protected]>; [email protected]
On Tue, Sep 5, 2023 at 8:07 AM Richard Guo <[email protected]> wrote:
> Yeah, this seems an omission in commit 45be99f8.
It's been a while, but I think I omitted this deliberately because I
didn't really understand the value of it and wanted to keep the
planning cost down.
The example query provided here seems rather artificial. Surely few
people write a join clause that references neither of the tables being
joined. Is there a more realistic case where this makes a big
difference?
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
@ 2023-09-08 06:06 ` Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2023-09-27 23:49 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() David Rowley <[email protected]>
2 siblings, 2 replies; 23+ messages in thread
From: Richard Guo @ 2023-09-08 06:06 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: tender wang <[email protected]>; [email protected]
On Fri, Sep 8, 2023 at 3:15 AM Robert Haas <[email protected]> wrote:
> The example query provided here seems rather artificial. Surely few
> people write a join clause that references neither of the tables being
> joined. Is there a more realistic case where this makes a big
> difference?
Yes the given example query is not that convincing. I tried a query
with plans as below (after some GUC setting) which might be more
realistic in real world.
unpatched:
explain select * from partsupp join lineitem on l_partkey > ps_partkey;
QUERY PLAN
--------------------------------------------------------------------------------------
Gather (cost=0.00..5522666.44 rows=160466667 width=301)
Workers Planned: 4
-> Nested Loop (cost=0.00..5522666.44 rows=40116667 width=301)
Join Filter: (lineitem.l_partkey > partsupp.ps_partkey)
-> Parallel Seq Scan on lineitem (cost=0.00..1518.44 rows=15044
width=144)
-> Seq Scan on partsupp (cost=0.00..267.00 rows=8000 width=157)
(6 rows)
patched:
explain select * from partsupp join lineitem on l_partkey > ps_partkey;
QUERY PLAN
--------------------------------------------------------------------------------------
Gather (cost=0.00..1807085.44 rows=160466667 width=301)
Workers Planned: 4
-> Nested Loop (cost=0.00..1807085.44 rows=40116667 width=301)
Join Filter: (lineitem.l_partkey > partsupp.ps_partkey)
-> Parallel Seq Scan on lineitem (cost=0.00..1518.44 rows=15044
width=144)
-> Materialize (cost=0.00..307.00 rows=8000 width=157)
-> Seq Scan on partsupp (cost=0.00..267.00 rows=8000
width=157)
(7 rows)
The execution time (ms) are (avg of 3 runs):
unpatched: 71769.21
patched: 65510.04
So we can see some (~9%) performance gains in this case.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
@ 2023-09-27 13:06 ` tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: tender wang @ 2023-09-27 13:06 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
Hi tom,
Do you have any comments or suggestions on this issue? Thanks.
Richard Guo <[email protected]> 于2023年9月8日周五 14:06写道：
>
> On Fri, Sep 8, 2023 at 3:15 AM Robert Haas <[email protected]> wrote:
>
>> The example query provided here seems rather artificial. Surely few
>> people write a join clause that references neither of the tables being
>> joined. Is there a more realistic case where this makes a big
>> difference?
>
>
> Yes the given example query is not that convincing. I tried a query
> with plans as below (after some GUC setting) which might be more
> realistic in real world.
>
> unpatched:
>
> explain select * from partsupp join lineitem on l_partkey > ps_partkey;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------
> Gather (cost=0.00..5522666.44 rows=160466667 width=301)
> Workers Planned: 4
> -> Nested Loop (cost=0.00..5522666.44 rows=40116667 width=301)
> Join Filter: (lineitem.l_partkey > partsupp.ps_partkey)
> -> Parallel Seq Scan on lineitem (cost=0.00..1518.44 rows=15044
> width=144)
> -> Seq Scan on partsupp (cost=0.00..267.00 rows=8000 width=157)
> (6 rows)
>
> patched:
>
> explain select * from partsupp join lineitem on l_partkey > ps_partkey;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------
> Gather (cost=0.00..1807085.44 rows=160466667 width=301)
> Workers Planned: 4
> -> Nested Loop (cost=0.00..1807085.44 rows=40116667 width=301)
> Join Filter: (lineitem.l_partkey > partsupp.ps_partkey)
> -> Parallel Seq Scan on lineitem (cost=0.00..1518.44 rows=15044
> width=144)
> -> Materialize (cost=0.00..307.00 rows=8000 width=157)
> -> Seq Scan on partsupp (cost=0.00..267.00 rows=8000
> width=157)
> (7 rows)
>
> The execution time (ms) are (avg of 3 runs):
>
> unpatched: 71769.21
> patched: 65510.04
>
> So we can see some (~9%) performance gains in this case.
>
> Thanks
> Richard
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
@ 2024-04-08 09:40 ` Andrey M. Borodin <[email protected]>
2024-04-08 10:54 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
0 siblings, 2 replies; 23+ messages in thread
From: Andrey M. Borodin @ 2024-04-08 09:40 UTC (permalink / raw)
To: tender wang <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
> On 27 Sep 2023, at 16:06, tender wang <[email protected]> wrote:
>
> Do you have any comments or suggestions on this issue? Thanks.
Hi Tender,
there are some review comments in the thread, that you might be interested in.
I'll mark this [0] entry "Waiting on Author" and move to next CF.
Thanks!
Best regards, Andrey Borodin.
[0]https://commitfest.postgresql.org/47/4549/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
@ 2024-04-08 10:54 ` Tender Wang <[email protected]>
1 sibling, 0 replies; 23+ messages in thread
From: Tender Wang @ 2024-04-08 10:54 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]; David Rowley <[email protected]>; Richard Guo <[email protected]>; [email protected]; PostgreSQL Hackers <[email protected]>
Andrey M. Borodin <[email protected]> 于2024年4月8日周一 17:40写道：
>
>
> > On 27 Sep 2023, at 16:06, tender wang <[email protected]> wrote:
> >
> > Do you have any comments or suggestions on this issue? Thanks.
> Hi Tender,
>
> there are some review comments in the thread, that you might be interested
> in.
> I'll mark this [0] entry "Waiting on Author" and move to next CF.
>
Thank you for the reminder. I will update the patch later.
I also deeply hope to get more advice about this patch.
(even the advice that not worth continuint to work on this patch).
Thanks.
Thanks!
>
>
> Best regards, Andrey Borodin.
>
> [0]https://commitfest.postgresql.org/47/4549/
--
Tender Wang
OpenPie: https://en.openpie.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
@ 2024-04-23 08:59 ` Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: Tender Wang @ 2024-04-23 08:59 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
Andrey M. Borodin <[email protected]> 于2024年4月8日周一 17:40写道：
>
>
> > On 27 Sep 2023, at 16:06, tender wang <[email protected]> wrote:
> >
> > Do you have any comments or suggestions on this issue? Thanks.
> Hi Tender,
>
> there are some review comments in the thread, that you might be interested
> in.
> I'll mark this [0] entry "Waiting on Author" and move to next CF.
>
> Thanks!
>
>
> Best regards, Andrey Borodin.
>
> [0]https://commitfest.postgresql.org/47/4549/
I have rebased master and fixed a plan diff case.
--
Tender Wang
OpenPie: https://en.openpie.com/
Attachments:
[application/octet-stream] v3-0001-Support-materializing-inner-path-on-parallel-oute.patch (10.1K, ../../CAHewXN=rP49ySfue9FA437jW4Me+FLBY-58zqTy+A_jxGGW=Xg@mail.gmail.com/3-v3-0001-Support-materializing-inner-path-on-parallel-oute.patch)
download | inline diff:
From 5cb3f4eb501fcacebc9551d46724eda8662fde42 Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Tue, 23 Apr 2024 16:36:23 +0800
Subject: [PATCH v3] Support materializing inner path on parallel outer path.
In some cases, the inner path of nestloop join may take very long
time. For example, the inner relation is a big table or a complex
subquery. Parallel outer path and materialize inner path may get
serval times the performance improvement at a small plan time cost.
---
src/backend/optimizer/path/joinpath.c | 19 +++
src/test/regress/expected/partition_join.out | 108 ++++++++++--------
src/test/regress/expected/select_parallel.out | 24 ++++
src/test/regress/sql/select_parallel.sql | 9 ++
4 files changed, 112 insertions(+), 48 deletions(-)
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 5be8da9e09..f9d6502f2c 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -2015,10 +2015,25 @@ consider_parallel_nestloop(PlannerInfo *root,
{
JoinType save_jointype = jointype;
ListCell *lc1;
+ Path *matpath = NULL;
+ Path *inner_cheapest_total = innerrel->cheapest_total_path;
if (jointype == JOIN_UNIQUE_INNER)
jointype = JOIN_INNER;
+ /*
+ * Consider materializing the cheapest inner path, unless we're
+ * doing JOIN_UNIQUE_INNER or enable_material is off or the subpath
+ * is parallel unsafe or the path in question materializes its output anyway.
+ */
+ if (save_jointype != JOIN_UNIQUE_INNER &&
+ enable_material &&
+ inner_cheapest_total != NULL &&
+ inner_cheapest_total->parallel_safe &&
+ !ExecMaterializesOutput(inner_cheapest_total->pathtype))
+ matpath = (Path *)
+ create_material_path(innerrel, inner_cheapest_total);
+
foreach(lc1, outerrel->partial_pathlist)
{
Path *outerpath = (Path *) lfirst(lc1);
@@ -2075,6 +2090,10 @@ 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 && matpath->parallel_safe)
+ try_partial_nestloop_path(root, joinrel, outerpath, matpath,
+ pathkeys, jointype, extra);
}
}
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6d07f86b9b..0f379012fc 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -510,25 +510,30 @@ EXPLAIN (COSTS OFF)
SELECT * FROM prt1 t1 JOIN LATERAL
(SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
ON t1.a = s.a;
- QUERY PLAN
--------------------------------------------------------------
- Append
- -> Nested Loop
- -> Seq Scan on prt1_p1 t1_1
- -> Sample Scan on prt1_p1 t2_1
- Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
- Filter: (t1_1.a = a)
- -> Nested Loop
- -> Seq Scan on prt1_p2 t1_2
- -> Sample Scan on prt1_p2 t2_2
- Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
- Filter: (t1_2.a = a)
- -> Nested Loop
- -> Seq Scan on prt1_p3 t1_3
- -> Sample Scan on prt1_p3 t2_3
- Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
- Filter: (t1_3.a = a)
-(16 rows)
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Parallel Append
+ -> 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)
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_p2 t1_2
+ -> Materialize
+ -> Sample Scan on prt1_p2 t2_2
+ Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
+ Filter: (t1_2.a = a)
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_p3 t1_3
+ -> Materialize
+ -> Sample Scan on prt1_p3 t2_3
+ Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
+ Filter: (t1_3.a = a)
+(21 rows)
-- lateral reference in scan's restriction clauses
EXPLAIN (COSTS OFF)
@@ -2041,35 +2046,42 @@ EXPLAIN (COSTS OFF)
SELECT * FROM prt1_l t1 JOIN LATERAL
(SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
ON t1.a = s.a AND t1.b = s.b AND t1.c = s.c;
- QUERY PLAN
-----------------------------------------------------------------------------------------
- Append
- -> Nested Loop
- -> Seq Scan on prt1_l_p1 t1_1
- -> Sample Scan on prt1_l_p1 t2_1
- Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
- Filter: ((t1_1.a = a) AND (t1_1.b = b) AND ((t1_1.c)::text = (c)::text))
- -> Nested Loop
- -> Seq Scan on prt1_l_p2_p1 t1_2
- -> Sample Scan on prt1_l_p2_p1 t2_2
- Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
- Filter: ((t1_2.a = a) AND (t1_2.b = b) AND ((t1_2.c)::text = (c)::text))
- -> Nested Loop
- -> Seq Scan on prt1_l_p2_p2 t1_3
- -> Sample Scan on prt1_l_p2_p2 t2_3
- Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
- Filter: ((t1_3.a = a) AND (t1_3.b = b) AND ((t1_3.c)::text = (c)::text))
- -> Nested Loop
- -> Seq Scan on prt1_l_p3_p1 t1_4
- -> Sample Scan on prt1_l_p3_p1 t2_4
- Sampling: system (t1_4.a) REPEATABLE (t1_4.b)
- Filter: ((t1_4.a = a) AND (t1_4.b = b) AND ((t1_4.c)::text = (c)::text))
- -> Nested Loop
- -> Seq Scan on prt1_l_p3_p2 t1_5
- -> Sample Scan on prt1_l_p3_p2 t2_5
- Sampling: system (t1_5.a) REPEATABLE (t1_5.b)
- Filter: ((t1_5.a = a) AND (t1_5.b = b) AND ((t1_5.c)::text = (c)::text))
-(26 rows)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Parallel Append
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p1 t1_1
+ -> Materialize
+ -> Sample Scan on prt1_l_p1 t2_1
+ Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
+ Filter: ((t1_1.a = a) AND (t1_1.b = b) AND ((t1_1.c)::text = (c)::text))
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p2_p2 t1_3
+ -> Materialize
+ -> Sample Scan on prt1_l_p2_p2 t2_3
+ Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
+ Filter: ((t1_3.a = a) AND (t1_3.b = b) AND ((t1_3.c)::text = (c)::text))
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p2_p1 t1_2
+ -> Materialize
+ -> Sample Scan on prt1_l_p2_p1 t2_2
+ Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
+ Filter: ((t1_2.a = a) AND (t1_2.b = b) AND ((t1_2.c)::text = (c)::text))
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p3_p1 t1_4
+ -> Materialize
+ -> Sample Scan on prt1_l_p3_p1 t2_4
+ Sampling: system (t1_4.a) REPEATABLE (t1_4.b)
+ Filter: ((t1_4.a = a) AND (t1_4.b = b) AND ((t1_4.c)::text = (c)::text))
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p3_p2 t1_5
+ -> Materialize
+ -> Sample Scan on prt1_l_p3_p2 t2_5
+ Sampling: system (t1_5.a) REPEATABLE (t1_5.b)
+ Filter: ((t1_5.a = a) AND (t1_5.b = b) AND ((t1_5.c)::text = (c)::text))
+(33 rows)
-- partitionwise join with lateral reference in scan's restriction clauses
EXPLAIN (COSTS OFF)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 87273fa635..57027a5098 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -868,6 +868,30 @@ select * from
(12 rows)
reset enable_material;
+-- test materialized form of the cheapest inner path
+set min_parallel_table_scan_size = '512kB';
+explain(costs off)
+select count(*) from tenk1, int4_tbl where tenk1.two < int4_tbl.f1;
+ QUERY PLAN
+------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 4
+ -> Partial Aggregate
+ -> Nested Loop
+ Join Filter: (tenk1.two < int4_tbl.f1)
+ -> Parallel Seq Scan on tenk1
+ -> Materialize
+ -> Seq Scan on int4_tbl
+(9 rows)
+
+select count(*) from tenk1, int4_tbl where tenk1.two < int4_tbl.f1;
+ count
+-------
+ 20000
+(1 row)
+
+set min_parallel_table_scan_size = 0;
reset enable_hashagg;
-- check parallelized int8 aggregate (bug #14897)
explain (costs off)
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 20376c03fa..0b91d1f6c0 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -320,6 +320,15 @@ select * from
reset enable_material;
+-- test materialized form of the cheapest inner path
+set min_parallel_table_scan_size = '512kB';
+
+explain(costs off)
+select count(*) from tenk1, int4_tbl where tenk1.two < int4_tbl.f1;
+
+select count(*) from tenk1, int4_tbl where tenk1.two < int4_tbl.f1;
+
+set min_parallel_table_scan_size = 0;
reset enable_hashagg;
-- check parallelized int8 aggregate (bug #14897)
--
2.34.1
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
@ 2024-05-30 20:21 ` Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Tomasz Rybak @ 2024-05-30 20:21 UTC (permalink / raw)
To: [email protected]; +Cc: Tender Wang <[email protected]>; Andrey M. Borodin <[email protected]>
On Tue, 2024-04-23 at 16:59 +0800, Tender Wang wrote:
>
[ cut ]
>
> I have rebased master and fixed a plan diff case.
We (me, Paul Jungwirth, and Yuki Fujii) reviewed this patch
at PgConf.dev Patch Review Workshop.
Here are our findings.
Patch tries to allow for using materialization together
with parallel subqueries.
It applies cleanly on 8fea1bd5411b793697a4c9087c403887e050c4ac
(current HEAD).
Tests pass locally on macOS and Linux in VM under Windows.
Tests are also green in cfbot (for last 2 weeks; they were
red previously, probably because of need to rebase).
Please add more tests. Especially please add some negative tests;
current patch checks that it is safe to apply materialization. It would
be helpful to add tests checking that materialization is not applied
in both checked cases:
1. when inner join path is not parallel safe
2. when matpath is not parallel safe
This patch tries to apply materialization only when join type
is not JOIN_UNIQUE_INNER. Comment mentions this, but does not
explain why. So please either add comment describing reason for that
or try enabling materialization in such a case.
Best regards.
--
Tomasz Rybak, Debian Developer <[email protected]>
GPG: A565 CE64 F866 A258 4DDC F9C7 ECB7 3E37 E887 AA8C
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
@ 2024-06-04 10:51 ` Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Tender Wang @ 2024-06-04 10:51 UTC (permalink / raw)
To: Tomasz Rybak <[email protected]>; +Cc: [email protected]; [email protected]; Richard Guo <[email protected]>; David Rowley <[email protected]>
Tomasz Rybak <[email protected]> 于2024年5月31日周五 04:21写道：
> On Tue, 2024-04-23 at 16:59 +0800, Tender Wang wrote:
> >
> [ cut ]
> >
> > I have rebased master and fixed a plan diff case.
>
> We (me, Paul Jungwirth, and Yuki Fujii) reviewed this patch
> at PgConf.dev Patch Review Workshop.
>
Thanks for reviewing this patch.
> Here are our findings.
>
> Patch tries to allow for using materialization together
> with parallel subqueries.
> It applies cleanly on 8fea1bd5411b793697a4c9087c403887e050c4ac
> (current HEAD).
> Tests pass locally on macOS and Linux in VM under Windows.
> Tests are also green in cfbot (for last 2 weeks; they were
> red previously, probably because of need to rebase).
>
> Please add more tests. Especially please add some negative tests;
> current patch checks that it is safe to apply materialization. It would
> be helpful to add tests checking that materialization is not applied
> in both checked cases:
> 1. when inner join path is not parallel safe
> 2. when matpath is not parallel safe
>
I added a test case that inner rel is not parallel safe. Actually, matpath
will not create
if inner rel is not parallel safe. So I didn't add test case for the
second scenario.
This patch tries to apply materialization only when join type
> is not JOIN_UNIQUE_INNER. Comment mentions this, but does not
> explain why. So please either add comment describing reason for that
> or try enabling materialization in such a case.
>
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.
[1]
https://www.postgresql.org/message-id/CAMbWs49LbQF_Z0iKPRPnTHfsRECT7M-4rF6ft5vpW1ARSpBkPA%40mail.gma...
--
Tender Wang
OpenPie: https://en.openpie.com/
Attachments:
[application/octet-stream] v4-0001-Support-materializing-inner-path-on-parallel-oute.patch (11.0K, ../../CAHewXNmWbA9Oe6JCEc66dJrVyyBDv1uSx6z_zdz-BQRE0Jr9pQ@mail.gmail.com/3-v4-0001-Support-materializing-inner-path-on-parallel-oute.patch)
download | inline diff:
From 9e92be221e83a72e99e0bad8c234f311714200f0 Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Tue, 23 Apr 2024 16:36:23 +0800
Subject: [PATCH v4] Support materializing inner path on parallel outer path.
In some cases, the inner path of nestloop join may take very long
time. For example, the inner relation is a big table or a complex
subquery. Parallel outer path and materialize inner path may get
serval times the performance improvement at a small plan time cost.
---
src/backend/optimizer/path/joinpath.c | 21 ++++
src/test/regress/expected/partition_join.out | 108 ++++++++++--------
src/test/regress/expected/select_parallel.out | 40 +++++++
src/test/regress/sql/select_parallel.sql | 16 +++
4 files changed, 137 insertions(+), 48 deletions(-)
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 5be8da9e09..734b7ddc20 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -2015,10 +2015,27 @@ consider_parallel_nestloop(PlannerInfo *root,
{
JoinType save_jointype = jointype;
ListCell *lc1;
+ Path *matpath = NULL;
+ Path *inner_cheapest_total = innerrel->cheapest_total_path;
if (jointype == JOIN_UNIQUE_INNER)
jointype = JOIN_INNER;
+ /*
+ * Consider materializing the cheapest inner path, unless we're
+ * doing JOIN_UNIQUE_INNER because in this case we have to
+ * unique-ify the inner path. The check that subpath is parallel safe
+ * here is probably redundant because we will not enter this func if
+ * joinrel is not parallel safe.
+ */
+ if (save_jointype != JOIN_UNIQUE_INNER &&
+ enable_material &&
+ inner_cheapest_total != NULL &&
+ inner_cheapest_total->parallel_safe &&
+ !ExecMaterializesOutput(inner_cheapest_total->pathtype))
+ matpath = (Path *)
+ create_material_path(innerrel, inner_cheapest_total);
+
foreach(lc1, outerrel->partial_pathlist)
{
Path *outerpath = (Path *) lfirst(lc1);
@@ -2075,6 +2092,10 @@ 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 && matpath->parallel_safe)
+ try_partial_nestloop_path(root, joinrel, outerpath, matpath,
+ pathkeys, jointype, extra);
}
}
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6d07f86b9b..0f379012fc 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -510,25 +510,30 @@ EXPLAIN (COSTS OFF)
SELECT * FROM prt1 t1 JOIN LATERAL
(SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
ON t1.a = s.a;
- QUERY PLAN
--------------------------------------------------------------
- Append
- -> Nested Loop
- -> Seq Scan on prt1_p1 t1_1
- -> Sample Scan on prt1_p1 t2_1
- Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
- Filter: (t1_1.a = a)
- -> Nested Loop
- -> Seq Scan on prt1_p2 t1_2
- -> Sample Scan on prt1_p2 t2_2
- Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
- Filter: (t1_2.a = a)
- -> Nested Loop
- -> Seq Scan on prt1_p3 t1_3
- -> Sample Scan on prt1_p3 t2_3
- Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
- Filter: (t1_3.a = a)
-(16 rows)
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Parallel Append
+ -> 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)
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_p2 t1_2
+ -> Materialize
+ -> Sample Scan on prt1_p2 t2_2
+ Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
+ Filter: (t1_2.a = a)
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_p3 t1_3
+ -> Materialize
+ -> Sample Scan on prt1_p3 t2_3
+ Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
+ Filter: (t1_3.a = a)
+(21 rows)
-- lateral reference in scan's restriction clauses
EXPLAIN (COSTS OFF)
@@ -2041,35 +2046,42 @@ EXPLAIN (COSTS OFF)
SELECT * FROM prt1_l t1 JOIN LATERAL
(SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
ON t1.a = s.a AND t1.b = s.b AND t1.c = s.c;
- QUERY PLAN
-----------------------------------------------------------------------------------------
- Append
- -> Nested Loop
- -> Seq Scan on prt1_l_p1 t1_1
- -> Sample Scan on prt1_l_p1 t2_1
- Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
- Filter: ((t1_1.a = a) AND (t1_1.b = b) AND ((t1_1.c)::text = (c)::text))
- -> Nested Loop
- -> Seq Scan on prt1_l_p2_p1 t1_2
- -> Sample Scan on prt1_l_p2_p1 t2_2
- Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
- Filter: ((t1_2.a = a) AND (t1_2.b = b) AND ((t1_2.c)::text = (c)::text))
- -> Nested Loop
- -> Seq Scan on prt1_l_p2_p2 t1_3
- -> Sample Scan on prt1_l_p2_p2 t2_3
- Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
- Filter: ((t1_3.a = a) AND (t1_3.b = b) AND ((t1_3.c)::text = (c)::text))
- -> Nested Loop
- -> Seq Scan on prt1_l_p3_p1 t1_4
- -> Sample Scan on prt1_l_p3_p1 t2_4
- Sampling: system (t1_4.a) REPEATABLE (t1_4.b)
- Filter: ((t1_4.a = a) AND (t1_4.b = b) AND ((t1_4.c)::text = (c)::text))
- -> Nested Loop
- -> Seq Scan on prt1_l_p3_p2 t1_5
- -> Sample Scan on prt1_l_p3_p2 t2_5
- Sampling: system (t1_5.a) REPEATABLE (t1_5.b)
- Filter: ((t1_5.a = a) AND (t1_5.b = b) AND ((t1_5.c)::text = (c)::text))
-(26 rows)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Parallel Append
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p1 t1_1
+ -> Materialize
+ -> Sample Scan on prt1_l_p1 t2_1
+ Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
+ Filter: ((t1_1.a = a) AND (t1_1.b = b) AND ((t1_1.c)::text = (c)::text))
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p2_p2 t1_3
+ -> Materialize
+ -> Sample Scan on prt1_l_p2_p2 t2_3
+ Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
+ Filter: ((t1_3.a = a) AND (t1_3.b = b) AND ((t1_3.c)::text = (c)::text))
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p2_p1 t1_2
+ -> Materialize
+ -> Sample Scan on prt1_l_p2_p1 t2_2
+ Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
+ Filter: ((t1_2.a = a) AND (t1_2.b = b) AND ((t1_2.c)::text = (c)::text))
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p3_p1 t1_4
+ -> Materialize
+ -> Sample Scan on prt1_l_p3_p1 t2_4
+ Sampling: system (t1_4.a) REPEATABLE (t1_4.b)
+ Filter: ((t1_4.a = a) AND (t1_4.b = b) AND ((t1_4.c)::text = (c)::text))
+ -> Nested Loop
+ -> Parallel Seq Scan on prt1_l_p3_p2 t1_5
+ -> Materialize
+ -> Sample Scan on prt1_l_p3_p2 t2_5
+ Sampling: system (t1_5.a) REPEATABLE (t1_5.b)
+ Filter: ((t1_5.a = a) AND (t1_5.b = b) AND ((t1_5.c)::text = (c)::text))
+(33 rows)
-- partitionwise join with lateral reference in scan's restriction clauses
EXPLAIN (COSTS OFF)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 87273fa635..69980bc14d 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -868,6 +868,46 @@ select * from
(12 rows)
reset enable_material;
+-- test materialized form of the cheapest inner path
+set min_parallel_table_scan_size = '512kB';
+explain(costs off)
+select count(*) from tenk1, int4_tbl where tenk1.two < int4_tbl.f1;
+ QUERY PLAN
+------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 4
+ -> Partial Aggregate
+ -> Nested Loop
+ Join Filter: (tenk1.two < int4_tbl.f1)
+ -> Parallel Seq Scan on tenk1
+ -> Materialize
+ -> Seq Scan on int4_tbl
+(9 rows)
+
+select count(*) from tenk1, int4_tbl where tenk1.two < int4_tbl.f1;
+ count
+-------
+ 20000
+(1 row)
+
+-- don't consider parallel nestloop if inner path is not parallel-safe
+set enable_memoize = off;
+explain(costs off)
+select * from tenk1,
+ lateral (select * from int4_tbl where tenk1.two < int4_tbl.f1 for share);
+ QUERY PLAN
+----------------------------------------------
+ Nested Loop
+ -> Seq Scan on tenk1
+ -> Subquery Scan on unnamed_subquery
+ -> LockRows
+ -> Seq Scan on int4_tbl
+ Filter: (tenk1.two < f1)
+(6 rows)
+
+set min_parallel_table_scan_size = 0;
+reset enable_memoize;
reset enable_hashagg;
-- check parallelized int8 aggregate (bug #14897)
explain (costs off)
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 20376c03fa..2d64846398 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -320,6 +320,22 @@ select * from
reset enable_material;
+-- test materialized form of the cheapest inner path
+set min_parallel_table_scan_size = '512kB';
+
+explain(costs off)
+select count(*) from tenk1, int4_tbl where tenk1.two < int4_tbl.f1;
+
+select count(*) from tenk1, int4_tbl where tenk1.two < int4_tbl.f1;
+
+-- don't consider parallel nestloop if inner path is not parallel-safe
+set enable_memoize = off;
+explain(costs off)
+select * from tenk1,
+ lateral (select * from int4_tbl where tenk1.two < int4_tbl.f1 for share);
+
+set min_parallel_table_scan_size = 0;
+reset enable_memoize;
reset enable_hashagg;
-- check parallelized int8 aggregate (bug #14897)
--
2.34.1
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
@ 2024-06-18 09:24 ` Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2024-06-18 09:24 UTC (permalink / raw)
To: Tender Wang <[email protected]>; +Cc: Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
@ 2024-06-19 02:55 ` Tender Wang <[email protected]>
2024-07-06 09:32 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Tender Wang @ 2024-06-19 02:55 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
Richard Guo <[email protected]> 于2024年6月18日周二 17:24写道：
> 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.
>
Yeah, you're right.
>
> 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 for the work. Now it looks better.
I have changed the status from "need review" to "ready for commiters" on
the commitfest.
--
Tender Wang
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
@ 2024-07-06 09:32 ` Richard Guo <[email protected]>
2024-07-12 02:29 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2024-07-06 09:32 UTC (permalink / raw)
To: Tender Wang <[email protected]>; +Cc: Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
On Wed, Jun 19, 2024 at 10:55 AM Tender Wang <[email protected]> wrote:
> Richard Guo <[email protected]> 于2024年6月18日周二 17:24写道：
>> 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 for the work. Now it looks better.
> I have changed the status from "need review" to "ready for commiters" on the commitfest.
Here is a new rebase.
I'm planning to push it next week, barring any objections.
Thanks
Richard
Attachments:
[application/octet-stream] v6-0001-Consider-materializing-the-cheapest-inner-path-in-parallel-nestloop.patch (5.1K, ../../CAMbWs48jJHxQfb0F0G1c6O=qgzNxRbJ-uE8FwpH=adAVmvnxSg@mail.gmail.com/2-v6-0001-Consider-materializing-the-cheapest-inner-path-in-parallel-nestloop.patch)
download | inline diff:
From 9e6051d194ac6292ac1cc7de2eaa78d665f36036 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Tue, 18 Jun 2024 16:25:19 +0900
Subject: [PATCH v6] 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.
Author: Tender Wang
Reviewed-by: Richard Guo, Alena Rybakina, Tomasz Rybak, Paul Jungwirth, Yuki Fujii
Discussion: https://postgr.es/m/CAHewXNkPmtEXNfVQMou_7NqQmFABca9f4etjBtdbbm0ZKDmWvw@mail.gmail.com
---
src/backend/optimizer/path/joinpath.c | 25 ++++++++++++++++
src/test/regress/expected/select_parallel.out | 30 +++++++++++++++++++
src/test/regress/sql/select_parallel.sql | 10 +++++++
3 files changed, 65 insertions(+)
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 40eb58341c..02dd972492 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -2021,11 +2021,31 @@ 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);
@@ -2082,6 +2102,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 c96285d1bb..7487ea0b82 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)
+
+-- the joinrel 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..9b019d31ed 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;
+
+-- the joinrel 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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-07-06 09:32 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
@ 2024-07-12 02:29 ` Richard Guo <[email protected]>
2024-07-12 02:43 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-07-18 08:00 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Alexander Lakhin <[email protected]>
0 siblings, 2 replies; 23+ messages in thread
From: Richard Guo @ 2024-07-12 02:29 UTC (permalink / raw)
To: Tender Wang <[email protected]>; +Cc: Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
On Sat, Jul 6, 2024 at 5:32 PM Richard Guo <[email protected]> wrote:
> Here is a new rebase.
>
> I'm planning to push it next week, barring any objections.
Pushed.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-07-06 09:32 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-12 02:29 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
@ 2024-07-12 02:43 ` Tender Wang <[email protected]>
1 sibling, 0 replies; 23+ messages in thread
From: Tender Wang @ 2024-07-12 02:43 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
Richard Guo <[email protected]> 于2024年7月12日周五 10:30写道：
> On Sat, Jul 6, 2024 at 5:32 PM Richard Guo <[email protected]> wrote:
> > Here is a new rebase.
> >
> > I'm planning to push it next week, barring any objections.
>
> Pushed.
>
> Thanks
> Richard
>
Thanks for pushing.
--
Tender Wang
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-07-06 09:32 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-12 02:29 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
@ 2024-07-18 08:00 ` Alexander Lakhin <[email protected]>
2024-07-18 08:11 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: Alexander Lakhin @ 2024-07-18 08:00 UTC (permalink / raw)
To: Richard Guo <[email protected]>; Tender Wang <[email protected]>; +Cc: Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
Hello Richard,
12.07.2024 05:29, Richard Guo wrote:
> On Sat, Jul 6, 2024 at 5:32 PM Richard Guo <[email protected]> wrote:
>> Here is a new rebase.
>>
>> I'm planning to push it next week, barring any objections.
> Pushed.
Please look at a recent buildfarm failure [1], which shows some
instability of that test addition:
-- the joinrel 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
--------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Nested Loop
Join Filter: (t1.two > t2.two)
- -> Gather
- Workers Planned: 4
- -> Parallel Seq Scan on tenk1 t1
+ -> Seq Scan on tenk2 t2
-> Materialize
- -> Seq Scan on tenk2 t2
+ -> Gather
+ Workers Planned: 4
+ -> Parallel Seq Scan on tenk1 t1
(7 rows)
I've managed to reproduce this plan change when running
multiple 027_stream_regress.pl instances simultaneously, with
parallel_schedule reduced to:
test: test_setup
test: create_misc
test: create_index
test: sanity_check
test: select_parallel
I've added the following to the test and got two verbose plans for
comparison (see the attachment).
-- the joinrel 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;
+\o plan.txt
+explain (verbose)
+ select * from tenk1 t1, (select * from tenk2 t2 offset 0) t2 where t1.two > t2.two;
+\o
alter table tenk2 reset (parallel_workers);
[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=tamandua&dt=2024-07-17%2017%3A12%3A53
Best regards,
Alexander
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1500840.00 rows=33333333 width=488)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4, t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
Join Filter: (t1.two > t2.two)
-> Gather (cost=0.00..370.00 rows=10000 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..370.00 rows=2500 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
-> Materialize (cost=0.00..495.00 rows=10000 width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
-> Seq Scan on public.tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
(12 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1500089.98 rows=33316667 width=488)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4, t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
Join Filter: (t1.two > t2.two)
-> Seq Scan on public.tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
-> Materialize (cost=0.00..419.96 rows=9995 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
-> Gather (cost=0.00..369.99 rows=9995 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..369.99 rows=2499 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
(12 rows)
Attachments:
[text/plain] plan.expected.txt (2.3K, ../../[email protected]/2-plan.expected.txt)
download | inline:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1500840.00 rows=33333333 width=488)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4, t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
Join Filter: (t1.two > t2.two)
-> Gather (cost=0.00..370.00 rows=10000 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..370.00 rows=2500 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
-> Materialize (cost=0.00..495.00 rows=10000 width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
-> Seq Scan on public.tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
(12 rows)
[text/plain] plan.unexpected.txt (2.3K, ../../[email protected]/3-plan.unexpected.txt)
download | inline:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1500089.98 rows=33316667 width=488)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4, t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
Join Filter: (t1.two > t2.two)
-> Seq Scan on public.tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
-> Materialize (cost=0.00..419.96 rows=9995 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
-> Gather (cost=0.00..369.99 rows=9995 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..369.99 rows=2499 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
(12 rows)
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-07-06 09:32 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-12 02:29 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-18 08:00 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Alexander Lakhin <[email protected]>
@ 2024-07-18 08:11 ` Richard Guo <[email protected]>
2024-07-18 14:30 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2024-07-18 08:11 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: Tender Wang <[email protected]>; Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
On Thu, Jul 18, 2024 at 4:00 PM Alexander Lakhin <[email protected]> wrote:
> Please look at a recent buildfarm failure [1], which shows some
> instability of that test addition:
> -- the joinrel 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
> --------------------------------------------
> + QUERY PLAN
> +-------------------------------------------------
> Nested Loop
> Join Filter: (t1.two > t2.two)
> - -> Gather
> - Workers Planned: 4
> - -> Parallel Seq Scan on tenk1 t1
> + -> Seq Scan on tenk2 t2
> -> Materialize
> - -> Seq Scan on tenk2 t2
> + -> Gather
> + Workers Planned: 4
> + -> Parallel Seq Scan on tenk1 t1
> (7 rows)
Thank you for the report and investigation. Will have a look.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-07-06 09:32 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-12 02:29 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-18 08:00 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Alexander Lakhin <[email protected]>
2024-07-18 08:11 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
@ 2024-07-18 14:30 ` Richard Guo <[email protected]>
2024-07-19 04:00 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Alexander Lakhin <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2024-07-18 14:30 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: Tender Wang <[email protected]>; Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
On Thu, Jul 18, 2024 at 4:11 PM Richard Guo <[email protected]> wrote:
> On Thu, Jul 18, 2024 at 4:00 PM Alexander Lakhin <[email protected]> wrote:
> > Please look at a recent buildfarm failure [1], which shows some
> > instability of that test addition:
> > -- the joinrel 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
> > --------------------------------------------
> > + QUERY PLAN
> > +-------------------------------------------------
> > Nested Loop
> > Join Filter: (t1.two > t2.two)
> > - -> Gather
> > - Workers Planned: 4
> > - -> Parallel Seq Scan on tenk1 t1
> > + -> Seq Scan on tenk2 t2
> > -> Materialize
> > - -> Seq Scan on tenk2 t2
> > + -> Gather
> > + Workers Planned: 4
> > + -> Parallel Seq Scan on tenk1 t1
> > (7 rows)
>
> Thank you for the report and investigation. Will have a look.
The problemed plan is a non-parallel nestloop join. It's just chance
which join order the planner will pick, and slight variations in
underlying statistics could result in a different displayed plan.
From the two verbose plans, we can see slight variations in the
statistics for the parallel seqscan of tenk1.
-> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..370.00 rows=2500
width=244)
VS.
-> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..369.99 rows=2499
width=244)
I have no idea why the underlying statistics changed, but it seems
that this slight change is sufficent to result in a different plan.
According to the discussion in [1], I think what we wanted to test
with this query is that parallel nestloop join is not generated if the
inner path is not parallel-safe. Therefore, I modified this test case
to use a lateral join, rendering the inner path not parallel-safe
while also enforcing the join order. Please see attached.
[1] https://postgr.es/m/[email protected]
Thanks
Richard
Attachments:
[application/octet-stream] v1-0001-Fix-unstable-test-in-select_parallel.sql.patch (3.6K, ../../CAMbWs4-A4+ugdRXGeTSNRhhBcZHf9-YtwcL7iCeUH4meSq4P1A@mail.gmail.com/2-v1-0001-Fix-unstable-test-in-select_parallel.sql.patch)
download | inline diff:
From 99ed5a97c502582d5dc5cc8eacfb347ccf31ea18 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Thu, 18 Jul 2024 19:19:03 +0900
Subject: [PATCH v1] Fix unstable test in select_parallel.sql
One test case added in 22d946b0f verifies the plan of a non-parallel
nestloop join. The planner's choice of join order is arbitrary, and
slight variations in underlying statistics could result in a different
displayed plan. To stabilize the test result, here we enforce the
join order using a lateral join.
While here, modify the test case to verify that parallel nestloop join
is not generated if the inner path is not parallel-safe, which is what
we wanted to test in 22d946b0f.
Reported-by: Alexander Lakhin as per buildfarm
Author: Richard Guo
Discussion: https://postgr.es/m/[email protected]
---
src/test/regress/expected/select_parallel.out | 17 +++++++++++------
src/test/regress/sql/select_parallel.sql | 11 ++++++++---
2 files changed, 19 insertions(+), 9 deletions(-)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 7487ea0b82..5a603f86b7 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -656,7 +656,7 @@ 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;
+select * from tenk1 t1, tenk2 t2 where t1.two > t2.two;
QUERY PLAN
-------------------------------------------
Gather
@@ -668,18 +668,23 @@ explain (costs off)
-> Seq Scan on tenk2 t2
(7 rows)
--- the joinrel is not parallel-safe due to the OFFSET clause in the subquery
+-- test that parallel nestloop join is not generated if the inner path is
+-- not parallel-safe
explain (costs off)
- select * from tenk1 t1, (select * from tenk2 t2 offset 0) t2 where t1.two > t2.two;
+select * from tenk1 t1
+ left join lateral
+ (select t1.unique1 as x, * from tenk2 t2 order by 1) t2
+ on true
+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
+ -> Subquery Scan on t2
+ Filter: (t1.two > t2.two)
+ -> Seq Scan on tenk2 t2_1
(7 rows)
alter table tenk2 reset (parallel_workers);
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 9b019d31ed..c7df8f775c 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -269,11 +269,16 @@ 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;
+select * from tenk1 t1, tenk2 t2 where t1.two > t2.two;
--- the joinrel is not parallel-safe due to the OFFSET clause in the subquery
+-- test that parallel nestloop join is not generated if the inner path is
+-- not parallel-safe
explain (costs off)
- select * from tenk1 t1, (select * from tenk2 t2 offset 0) t2 where t1.two > t2.two;
+select * from tenk1 t1
+ left join lateral
+ (select t1.unique1 as x, * from tenk2 t2 order by 1) t2
+ on true
+where t1.two > t2.two;
alter table tenk2 reset (parallel_workers);
-- test gather merge
--
2.43.0
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-07-06 09:32 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-12 02:29 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-18 08:00 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Alexander Lakhin <[email protected]>
2024-07-18 08:11 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-18 14:30 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
@ 2024-07-19 04:00 ` Alexander Lakhin <[email protected]>
2024-07-22 02:43 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Alexander Lakhin @ 2024-07-19 04:00 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tender Wang <[email protected]>; Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
Hello Richard,
18.07.2024 17:30, Richard Guo wrote:
> The problemed plan is a non-parallel nestloop join. It's just chance
> which join order the planner will pick, and slight variations in
> underlying statistics could result in a different displayed plan.
> From the two verbose plans, we can see slight variations in the
> statistics for the parallel seqscan of tenk1.
>
> -> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..370.00 rows=2500
> width=244)
>
> VS.
>
> -> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..369.99 rows=2499
> width=244)
>
> I have no idea why the underlying statistics changed, but it seems
> that this slight change is sufficent to result in a different plan.
I think it could be caused by the same reason as [1] and I really can
easily (without multiple instances/loops. just with `make check`) reproduce
the failure with cranky-ConditionalLockBufferForCleanup.patch (but
targeted for "VACUUM ANALYZE tenk1;").
> According to the discussion in [1], I think what we wanted to test
> with this query is that parallel nestloop join is not generated if the
> inner path is not parallel-safe. Therefore, I modified this test case
> to use a lateral join, rendering the inner path not parallel-safe
> while also enforcing the join order. Please see attached.
The modified test survives my testing procedure. Thank you for the patch!
[1] https://www.postgresql.org/message-id/flat/66eb9a6e-fc67-a230-c5b1-2a741e8b88c6%40gmail.com
Best regards,
Alexander
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 13:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() tender wang <[email protected]>
2024-04-08 09:40 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Andrey M. Borodin <[email protected]>
2024-04-23 08:59 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-05-30 20:21 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-06-18 09:24 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-06-19 02:55 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Tender Wang <[email protected]>
2024-07-06 09:32 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-12 02:29 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-18 08:00 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Alexander Lakhin <[email protected]>
2024-07-18 08:11 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-18 14:30 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2024-07-19 04:00 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Alexander Lakhin <[email protected]>
@ 2024-07-22 02:43 ` Richard Guo <[email protected]>
0 siblings, 0 replies; 23+ messages in thread
From: Richard Guo @ 2024-07-22 02:43 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: Tender Wang <[email protected]>; Tomasz Rybak <[email protected]>; [email protected]; [email protected]; David Rowley <[email protected]>; [email protected] <[email protected]>
On Fri, Jul 19, 2024 at 12:00 PM Alexander Lakhin <[email protected]> wrote:
> 18.07.2024 17:30, Richard Guo wrote:
> > I have no idea why the underlying statistics changed, but it seems
> > that this slight change is sufficent to result in a different plan.
>
> I think it could be caused by the same reason as [1] and I really can
> easily (without multiple instances/loops. just with `make check`) reproduce
> the failure with cranky-ConditionalLockBufferForCleanup.patch (but
> targeted for "VACUUM ANALYZE tenk1;").
Yeah. Anyway I think we need to make the test more tolerant of slight
variations in the statistics.
> > According to the discussion in [1], I think what we wanted to test
> > with this query is that parallel nestloop join is not generated if the
> > inner path is not parallel-safe. Therefore, I modified this test case
> > to use a lateral join, rendering the inner path not parallel-safe
> > while also enforcing the join order. Please see attached.
>
> The modified test survives my testing procedure. Thank you for the patch!
Thanks for testing this patch. I've pushed it.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
@ 2023-09-27 23:49 ` David Rowley <[email protected]>
2023-10-18 13:44 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Alena Rybakina <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: David Rowley @ 2023-09-27 23:49 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Robert Haas <[email protected]>; tender wang <[email protected]>; [email protected]
On Fri, 8 Sept 2023 at 19:14, Richard Guo <[email protected]> wrote:
> explain select * from partsupp join lineitem on l_partkey > ps_partkey;
> QUERY PLAN
> --------------------------------------------------------------------------------------
> Gather (cost=0.00..1807085.44 rows=160466667 width=301)
> Workers Planned: 4
> -> Nested Loop (cost=0.00..1807085.44 rows=40116667 width=301)
> Join Filter: (lineitem.l_partkey > partsupp.ps_partkey)
> -> Parallel Seq Scan on lineitem (cost=0.00..1518.44 rows=15044 width=144)
> -> Materialize (cost=0.00..307.00 rows=8000 width=157)
> -> Seq Scan on partsupp (cost=0.00..267.00 rows=8000 width=157)
> (7 rows)
>
> The execution time (ms) are (avg of 3 runs):
>
> unpatched: 71769.21
> patched: 65510.04
This gap would be wider if the partsupp Seq Scan were filtering off
some rows and wider still if you added more rows to lineitem.
However, a clauseless seqscan is not the most compelling use case
below a material node. The inner side of the nested loop could be some
subquery that takes 6 days to complete. Running the 6 day query ~15044
times seems like something that would be good to avoid.
It seems worth considering Material paths to me. I think that the
above example could be tuned any way you like to make it look better
or worse.
David
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Richard Guo <[email protected]>
2023-09-27 23:49 ` Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() David Rowley <[email protected]>
@ 2023-10-18 13:44 ` Alena Rybakina <[email protected]>
0 siblings, 0 replies; 23+ messages in thread
From: Alena Rybakina @ 2023-10-18 13:44 UTC (permalink / raw)
To: tender wang <[email protected]>; +Cc: Robert Haas <[email protected]>; [email protected]; David Rowley <[email protected]>; Richard Guo <[email protected]>
Hi!
Thank you for your work on the subject.
I reviewed your patch and found that your commit message does not fully
explain your code, in addition, I found several spelling mistakes.
I think it's better to change to:
With parallel seqscan, we should consider materializing the cheapest
inner path in
case of nested loop if it doesn't contain a unique node or it is unsafe
to use it in a subquery.
Besides, I couldn't understand why we again check that material path is
safe?
if (matpath != NULL && matpath->parallel_safe)
try_partial_nestloop_path(root, joinrel, outerpath, matpath,
pathkeys, jointype, extra);
--
Regards,
Alena Rybakina
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
@ 2023-09-27 23:41 ` David Rowley <[email protected]>
2 siblings, 0 replies; 23+ messages in thread
From: David Rowley @ 2023-09-27 23:41 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Richard Guo <[email protected]>; tender wang <[email protected]>; [email protected]
On Fri, 8 Sept 2023 at 09:41, Robert Haas <[email protected]> wrote:
>
> On Tue, Sep 5, 2023 at 8:07 AM Richard Guo <[email protected]> wrote:
> > Yeah, this seems an omission in commit 45be99f8.
>
> It's been a while, but I think I omitted this deliberately because I
> didn't really understand the value of it and wanted to keep the
> planning cost down.
I think the value is potentially not having to repeatedly execute some
expensive rescan to the nested loop join once for each outer-side
tuple.
The planning cost is something to consider for sure, but it seems
strange that we deemed it worthy to consider material paths for the
non-parallel input paths but draw the line for the parallel/partial
ones. It seems to me that the additional costs and the possible
benefits are the same for both.
David
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
@ 2024-06-14 03:02 ` Tender Wang <[email protected]>
2 siblings, 0 replies; 23+ messages in thread
From: Tender Wang @ 2024-06-14 03:02 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Richard Guo <[email protected]>; [email protected]; Tomasz Rybak <[email protected]>; [email protected] <[email protected]>; David Rowley <[email protected]>
Hi Robert,
Since this patch had been reviewed at PgConf.dev Patch Review
Workshop. And I have updated
the patch according to the review advice. Now there are no others to
comment this patch.
The status of this patch on commitfest have stayed "need review" for a long
time.
I want to know if it is ready to move to the next status "Ready for
commiter".
Thanks.
--
Tender Wang
^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 1/3] pg_lfind32: process "tail" with SIMD intructions
@ 2024-03-15 17:26 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 23+ messages in thread
From: Nathan Bossart @ 2024-03-15 17:26 UTC (permalink / raw)
---
src/include/port/pg_lfind.h | 16 ++++++++++++++--
1 file changed, 14 insertions(+), 2 deletions(-)
diff --git a/src/include/port/pg_lfind.h b/src/include/port/pg_lfind.h
index b8dfa66eef..9d21284724 100644
--- a/src/include/port/pg_lfind.h
+++ b/src/include/port/pg_lfind.h
@@ -103,7 +103,7 @@ pg_lfind32(uint32 key, uint32 *base, uint32 nelem)
const uint32 nelem_per_iteration = 4 * nelem_per_vector;
/* round down to multiple of elements per iteration */
- const uint32 tail_idx = nelem & ~(nelem_per_iteration - 1);
+ uint32 tail_idx = nelem & ~(nelem_per_iteration - 1);
#if defined(USE_ASSERT_CHECKING)
bool assert_result = false;
@@ -117,9 +117,11 @@ pg_lfind32(uint32 key, uint32 *base, uint32 nelem)
break;
}
}
+ i = 0;
#endif
- for (i = 0; i < tail_idx; i += nelem_per_iteration)
+retry:
+ for (; i < tail_idx; i += nelem_per_iteration)
{
Vector32 vals1,
vals2,
@@ -157,6 +159,16 @@ pg_lfind32(uint32 key, uint32 *base, uint32 nelem)
return true;
}
}
+
+ if (i == nelem)
+ return false;
+ else if (tail_idx > 0)
+ {
+ tail_idx = nelem;
+ i = nelem - nelem_per_iteration;
+ goto retry;
+ }
+
#endif /* ! USE_NO_SIMD */
/* Process the remaining elements one at a time. */
--
2.25.1
--gBBFr7Ir9EOA20Yy
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v3-0002-add-avx2-support-in-simd.h.patch"
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2024-07-22 02:43 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-07 19:14 Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() Robert Haas <[email protected]>
2023-09-08 06:06 ` Richard Guo <[email protected]>
2023-09-27 13:06 ` tender wang <[email protected]>
2024-04-08 09:40 ` Andrey M. Borodin <[email protected]>
2024-04-08 10:54 ` Tender Wang <[email protected]>
2024-04-23 08:59 ` Tender Wang <[email protected]>
2024-05-30 20:21 ` Tomasz Rybak <[email protected]>
2024-06-04 10:51 ` Tender Wang <[email protected]>
2024-06-18 09:24 ` Richard Guo <[email protected]>
2024-06-19 02:55 ` Tender Wang <[email protected]>
2024-07-06 09:32 ` Richard Guo <[email protected]>
2024-07-12 02:29 ` Richard Guo <[email protected]>
2024-07-12 02:43 ` Tender Wang <[email protected]>
2024-07-18 08:00 ` Alexander Lakhin <[email protected]>
2024-07-18 08:11 ` Richard Guo <[email protected]>
2024-07-18 14:30 ` Richard Guo <[email protected]>
2024-07-19 04:00 ` Alexander Lakhin <[email protected]>
2024-07-22 02:43 ` Richard Guo <[email protected]>
2023-09-27 23:49 ` David Rowley <[email protected]>
2023-10-18 13:44 ` Alena Rybakina <[email protected]>
2023-09-27 23:41 ` David Rowley <[email protected]>
2024-06-14 03:02 ` Tender Wang <[email protected]>
2024-03-15 17:26 [PATCH v3 1/3] pg_lfind32: process "tail" with SIMD intructions Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox