0001-Parallel-seq-scan-should-consider-materila-inner-pat.patch

From 7b652382928cc80ab6fbe782feada9f0eab4c5a8 Mon Sep 17 00:00:00 2001
From: "tender.wang" <tender.wang@openpie.com>
Date: Tue, 5 Sep 2023 14:33:24 +0800
Subject: [PATCH] Parallel seq scan should consider materila inner path in
 nestloop case.

---
 src/backend/optimizer/path/joinpath.c         | 16 +++++++++++
 src/test/regress/expected/select_parallel.out | 27 +++++++++++++++++++
 src/test/regress/sql/select_parallel.sql      | 10 +++++++
 3 files changed, 53 insertions(+)

diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 821d282497..5a10fb7f4b 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -2004,10 +2004,22 @@ 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
+	 * enable_material is off or the path in question materializes its
+	 * output anyway.
+	 */
+	if (enable_material && inner_cheapest_total != NULL &&
+		!ExecMaterializesOutput(inner_cheapest_total->pathtype))
+		matpath = (Path *)
+			create_material_path(innerrel, inner_cheapest_total);
+
 	foreach(lc1, outerrel->partial_pathlist)
 	{
 		Path	   *outerpath = (Path *) lfirst(lc1);
@@ -2064,6 +2076,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/select_parallel.out b/src/test/regress/expected/select_parallel.out
index d88353d496..452a3aed07 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -844,6 +844,33 @@ 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 suq.two, suq.four
+from
+ (select t1.two, t2.four, t1.ten,t2.twenty
+ from tenk1 t1 left join tenk2 t2
+ on ((select q1 from int8_tbl order by q1 limit 1)is not null) where t2.ten is null) as suq;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ($0 IS NOT NULL)
+   Filter: (t2.ten IS NULL)
+   InitPlan 1 (returns $0)
+     ->  Limit
+           ->  Sort
+                 Sort Key: int8_tbl.q1
+                 ->  Seq Scan on int8_tbl
+   ->  Gather
+         Workers Planned: 4
+         ->  Parallel Seq Scan on tenk1 t1
+   ->  Materialize
+         ->  Gather
+               Workers Planned: 2
+               ->  Parallel Seq Scan on tenk2 t2
+(15 rows)
+
+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 80c914dc02..ded44a0c09 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -312,6 +312,16 @@ select * from
 
 reset enable_material;
 
+-- test materialized form of the cheapest inner path
+set min_parallel_table_scan_size = '512kB';
+
+explain(costs off) select suq.two, suq.four
+from
+ (select t1.two, t2.four, t1.ten,t2.twenty
+ from tenk1 t1 left join tenk2 t2
+ on ((select q1 from int8_tbl order by q1 limit 1)is not null) where t2.ten is null) as suq;
+
+set min_parallel_table_scan_size = 0;
 reset enable_hashagg;
 
 -- check parallelized int8 aggregate (bug #14897)
-- 
2.25.1