Planner patch to allow parallel paths with expensive functions on the target list to survive winnowing. From Amit Kapala, with small change to apply cleaning to v10 https://www.postgresql.org/message-id/CAA4eK1+1H5Urm0_Wp-n5XszdLX1YXBqS_zW0f-vvWKwdh3eCJA@mail.gmail.com

parallel_paths_include_tlist_cost_v5.patch

diff --git a/src/backend/optimizer/geqo/geqo_eval.c b/src/backend/optimizer/geqo/geqo_eval.c
index b5cab0c..faa5bb7 100644
--- a/src/backend/optimizer/geqo/geqo_eval.c
+++ b/src/backend/optimizer/geqo/geqo_eval.c
@@ -40,7 +40,7 @@ typedef struct
 } Clump;
 
 static List *merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump,
-			bool force);
+			int num_gene, bool force);
 static bool desirable_join(PlannerInfo *root,
 			   RelOptInfo *outer_rel, RelOptInfo *inner_rel);
 
@@ -196,7 +196,7 @@ gimme_tree(PlannerInfo *root, Gene *tour, int num_gene)
 		cur_clump->size = 1;
 
 		/* Merge it into the clumps list, using only desirable joins */
-		clumps = merge_clump(root, clumps, cur_clump, false);
+		clumps = merge_clump(root, clumps, cur_clump, num_gene, false);
 	}
 
 	if (list_length(clumps) > 1)
@@ -210,7 +210,7 @@ gimme_tree(PlannerInfo *root, Gene *tour, int num_gene)
 		{
 			Clump	   *clump = (Clump *) lfirst(lc);
 
-			fclumps = merge_clump(root, fclumps, clump, true);
+			fclumps = merge_clump(root, fclumps, clump, num_gene, true);
 		}
 		clumps = fclumps;
 	}
@@ -235,7 +235,8 @@ gimme_tree(PlannerInfo *root, Gene *tour, int num_gene)
  * "desirable" joins.
  */
 static List *
-merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, bool force)
+merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, int num_gene,
+			bool force)
 {
 	ListCell   *prev;
 	ListCell   *lc;
@@ -264,8 +265,14 @@ merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, bool force)
 			/* Keep searching if join order is not valid */
 			if (joinrel)
 			{
-				/* Create GatherPaths for any useful partial paths for rel */
-				generate_gather_paths(root, joinrel);
+				/*
+				 * Create GatherPaths for any useful partial paths for rel
+				 * other than top-level rel.  The gather path for top-level
+				 * rel is generated once path target is available.  See
+				 * grouping_planner.
+				*/
+				if (old_clump->size + new_clump->size < num_gene)
+					generate_gather_paths(root, joinrel, NULL);
 
 				/* Find and save the cheapest paths for this joinrel */
 				set_cheapest(joinrel);
@@ -283,7 +290,7 @@ merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, bool force)
 				 * others.  When no further merge is possible, we'll reinsert
 				 * it into the list.
 				 */
-				return merge_clump(root, clumps, old_clump, force);
+				return merge_clump(root, clumps, old_clump, num_gene, force);
 			}
 		}
 		prev = lc;
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index a53b545..9978580 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -479,14 +479,18 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
 	}
 
 	/*
-	 * If this is a baserel, consider gathering any partial paths we may have
-	 * created for it.  (If we tried to gather inheritance children, we could
-	 * end up with a very large number of gather nodes, each trying to grab
-	 * its own pool of workers, so don't do this for otherrels.  Instead,
-	 * we'll consider gathering partial paths for the parent appendrel.)
+	 * If this is a baserel and not the top-level rel, consider gathering any
+	 * partial paths we may have created for it.  (If we tried to gather
+	 * inheritance children, we could end up with a very large number of
+	 * gather nodes, each trying to grab its own pool of workers, so don't do
+	 * this for otherrels.  Instead, we'll consider gathering partial paths
+	 * for the parent appendrel.).  The gather path for top-level rel is
+	 * generated once path target is available.  See grouping_planner.
 	 */
-	if (rel->reloptkind == RELOPT_BASEREL)
-		generate_gather_paths(root, rel);
+	if (rel->reloptkind == RELOPT_BASEREL &&
+		root->simple_rel_array_size > 2 &&
+		!root->append_rel_list)
+		generate_gather_paths(root, rel, NULL);
 
 	/*
 	 * Allow a plugin to editorialize on the set of Paths for this base
@@ -2226,11 +2230,12 @@ set_worktable_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
  *		Gather Merge on top of a partial path.
  *
  * This must not be called until after we're done creating all partial paths
- * for the specified relation.  (Otherwise, add_partial_path might delete a
- * path that some GatherPath or GatherMergePath has a reference to.)
+ * for the specified relation (Otherwise, add_partial_path might delete a
+ * path that some GatherPath or GatherMergePath has a reference to.) and path
+ * target for top level scan/join node is available.
  */
 void
-generate_gather_paths(PlannerInfo *root, RelOptInfo *rel)
+generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, PathTarget *target)
 {
 	Path	   *cheapest_partial_path;
 	Path	   *simple_gather_path;
@@ -2249,6 +2254,11 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel)
 	simple_gather_path = (Path *)
 		create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
 						   NULL, NULL);
+
+	/* Add projection step if needed */
+	if (target && simple_gather_path->pathtarget != target)
+		simple_gather_path = apply_projection_to_path(root, rel, simple_gather_path, target);
+
 	add_path(rel, simple_gather_path);
 
 	/*
@@ -2258,14 +2268,18 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel)
 	foreach(lc, rel->partial_pathlist)
 	{
 		Path	   *subpath = (Path *) lfirst(lc);
-		GatherMergePath *path;
+		Path	   *path;
 
 		if (subpath->pathkeys == NIL)
 			continue;
 
-		path = create_gather_merge_path(root, rel, subpath, rel->reltarget,
-										subpath->pathkeys, NULL, NULL);
-		add_path(rel, &path->path);
+		path = (Path *) create_gather_merge_path(root, rel, subpath, rel->reltarget,
+												 subpath->pathkeys, NULL, NULL);
+		/* Add projection step if needed */
+		if (target && path->pathtarget != target)
+			path = apply_projection_to_path(root, rel, path, target);
+
+		add_path(rel, path);
 	}
 }
 
@@ -2430,8 +2444,13 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
 		{
 			rel = (RelOptInfo *) lfirst(lc);
 
-			/* Create GatherPaths for any useful partial paths for rel */
-			generate_gather_paths(root, rel);
+			/*
+			 * Create GatherPaths for any useful partial paths for rel other
+			 * than top-level rel.  The gather path for top-level rel is
+			 * generated once path target is available.  See grouping_planner.
+			 */
+			if (lev < levels_needed)
+				generate_gather_paths(root, rel, NULL);
 
 			/* Find and save the cheapest paths for this rel */
 			set_cheapest(rel);
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index ea37ba8..cc3d7af 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1832,6 +1832,18 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
 		}
 
 		/*
+		 * Consider ways to implement parallel paths.  We always skip
+		 * generating parallel path for top level scan/join nodes till the
+		 * pathtarget is computed.  This is to ensure that we can account for
+		 * the fact that most of the target evaluation work will be performed
+		 * in workers.
+		 */
+		generate_gather_paths(root, current_rel, scanjoin_target);
+
+		/* Set or update cheapest_total_path and related fields */
+		set_cheapest(current_rel);
+
+		/*
 		 * Upper planning steps which make use of the top scan/join rel's
 		 * partial pathlist will expect partial paths for that rel to produce
 		 * the same output as complete paths ... and we just changed the
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index f2d6385..d8992eb 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -2407,16 +2407,27 @@ apply_projection_to_path(PlannerInfo *root,
 		 * projection-capable, so as to avoid modifying the subpath in place.
 		 * It seems unlikely at present that there could be any other
 		 * references to the subpath, but better safe than sorry.
-		 *
-		 * Note that we don't change the GatherPath's cost estimates; it might
-		 * be appropriate to do so, to reflect the fact that the bulk of the
-		 * target evaluation will happen in workers.
 		 */
 		gpath->subpath = (Path *)
 			create_projection_path(root,
 								   gpath->subpath->parent,
 								   gpath->subpath,
 								   target);
+
+		/*
+		 * Adjust the cost of GatherPath to reflect the fact that the bulk of
+		 * the target evaluation will happen in workers.
+		 */
+		if (((ProjectionPath *) gpath->subpath)->dummypp)
+		{
+			path->total_cost -= (target->cost.per_tuple - oldcost.per_tuple) * path->rows;
+			path->total_cost += (target->cost.per_tuple - oldcost.per_tuple) * gpath->subpath->rows;
+		}
+		else
+		{
+			path->total_cost -= (target->cost.per_tuple - oldcost.per_tuple) * path->rows;
+			path->total_cost += (cpu_tuple_cost + target->cost.per_tuple) * gpath->subpath->rows;
+		}
 	}
 	else if (path->parallel_safe &&
 			 !is_parallel_safe(root, (Node *) target->exprs))
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 4e06b2e..61694a0 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -53,7 +53,8 @@ extern void set_dummy_rel_pathlist(RelOptInfo *rel);
 extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed,
 					 List *initial_rels);
 
-extern void generate_gather_paths(PlannerInfo *root, RelOptInfo *rel);
+extern void generate_gather_paths(PlannerInfo *root, RelOptInfo *rel,
+					  PathTarget *target);
 extern int compute_parallel_worker(RelOptInfo *rel, double heap_pages,
 						double index_pages);
 extern void create_partial_bitmap_paths(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index f3e8b56..b62daf2 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -121,6 +121,23 @@ execute tenk1_count(1);
 (1 row)
 
 deallocate tenk1_count;
+-- test that parallel plan gets selected when target list contains costly
+-- function
+create or replace function costly_func(var1 integer) returns integer
+as $$
+begin
+        return var1 + 10;
+end;
+$$ language plpgsql PARALLEL SAFE Cost 100000;
+explain (costs off) select ten, costly_func(ten) from tenk1;
+            QUERY PLAN            
+----------------------------------
+ Gather
+   Workers Planned: 4
+   ->  Parallel Seq Scan on tenk1
+(3 rows)
+
+drop function costly_func(var1 integer);
 -- test parallel plans for queries containing un-correlated subplans.
 alter table tenk2 set (parallel_workers = 0);
 explain (costs off)
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index d2eee14..18d9eaa 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -45,6 +45,17 @@ explain (costs off) execute tenk1_count(1);
 execute tenk1_count(1);
 deallocate tenk1_count;
 
+-- test that parallel plan gets selected when target list contains costly
+-- function
+create or replace function costly_func(var1 integer) returns integer
+as $$
+begin
+        return var1 + 10;
+end;
+$$ language plpgsql PARALLEL SAFE Cost 100000;
+explain (costs off) select ten, costly_func(ten) from tenk1;
+drop function costly_func(var1 integer);
+
 -- test parallel plans for queries containing un-correlated subplans.
 alter table tenk2 set (parallel_workers = 0);
 explain (costs off)