Fix the miss consideration of tuple_fraction during add_paths_to_append_rel

From e98e0a0e5be48026f972a188dba39fa061f9f4ab Mon Sep 17 00:00:00 2001
From: Andy Fan <zhihui.fan1213@gmail.com>
Date: Wed, 12 Apr 2023 01:32:04 +0800
Subject: [PATCH v2] Considering root->tuple_fraction during create_append_path

Only apply this optimization when bms_equal(rel->relids,
root->all_query_rels).
---
 src/backend/optimizer/path/allpaths.c  | 19 +++++++++++++--
 src/backend/optimizer/plan/planner.c   | 22 ++++++++++++++----
 src/backend/optimizer/plan/subselect.c |  2 +-
 src/backend/optimizer/prep/prepunion.c |  3 ++-
 src/include/optimizer/planner.h        |  3 ++-
 src/test/regress/expected/join.out     | 32 ++++++++++++++++++++++++++
 src/test/regress/sql/join.sql          | 12 ++++++++++
 7 files changed, 83 insertions(+), 10 deletions(-)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 244957a2483..a9497bfeaf2 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1312,6 +1312,7 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 	List	   *pa_nonpartial_subpaths = NIL;
 	bool		partial_subpaths_valid = true;
 	bool		pa_subpaths_valid;
+	bool	   include_all_rels = false;
 	List	   *all_child_pathkeys = NIL;
 	List	   *all_child_outers = NIL;
 	ListCell   *l;
@@ -1319,6 +1320,7 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 
 	/* If appropriate, consider parallel append */
 	pa_subpaths_valid = enable_parallel_append && rel->consider_parallel;
+	include_all_rels = root && bms_equal(rel->relids, root->all_query_rels);
 
 	/*
 	 * For every non-dummy child, remember the cheapest path.  Also, identify
@@ -1330,7 +1332,12 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 		RelOptInfo *childrel = lfirst(l);
 		ListCell   *lcp;
 		Path	   *cheapest_partial_path = NULL;
+		Path	   *cheapest_startup_path = NULL;
 
+		if (childrel->pathlist != NIL && childrel->consider_startup && include_all_rels)
+		{
+			cheapest_startup_path = get_cheapest_fractional_path(childrel, root->tuple_fraction, false);
+		}
 		/*
 		 * If child has an unparameterized cheapest-total path, add that to
 		 * the unparameterized Append path we are constructing for the parent.
@@ -1339,7 +1346,10 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 		 * With partitionwise aggregates, the child rel's pathlist may be
 		 * empty, so don't assume that a path exists here.
 		 */
-		if (childrel->pathlist != NIL &&
+		if (cheapest_startup_path && cheapest_startup_path->param_info == NULL)
+			accumulate_append_subpath(cheapest_startup_path,
+									  &subpaths, NULL);
+		else if (childrel->pathlist != NIL &&
 			childrel->cheapest_total_path->param_info == NULL)
 			accumulate_append_subpath(childrel->cheapest_total_path,
 									  &subpaths, NULL);
@@ -1349,7 +1359,12 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 		/* Same idea, but for a partial plan. */
 		if (childrel->partial_pathlist != NIL)
 		{
-			cheapest_partial_path = linitial(childrel->partial_pathlist);
+			if (childrel->consider_startup && include_all_rels)
+				cheapest_partial_path = get_cheapest_fractional_path(childrel,
+																	 root->tuple_fraction,
+																	 true);
+			else
+				cheapest_partial_path = linitial(childrel->partial_pathlist);
 			accumulate_append_subpath(cheapest_partial_path,
 									  &partial_subpaths, NULL);
 		}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index a1873ce26d4..bfcd3519543 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -413,7 +413,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 
 	/* Select best Path and turn it into a Plan */
 	final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL);
-	best_path = get_cheapest_fractional_path(final_rel, tuple_fraction);
+	best_path = get_cheapest_fractional_path(final_rel, tuple_fraction, false);
 
 	top_plan = create_plan(root, best_path);
 
@@ -6272,10 +6272,22 @@ make_sort_input_target(PlannerInfo *root,
  * We assume set_cheapest() has been run on the given rel.
  */
 Path *
-get_cheapest_fractional_path(RelOptInfo *rel, double tuple_fraction)
+get_cheapest_fractional_path(RelOptInfo *rel, double tuple_fraction, bool is_partial)
 {
-	Path	   *best_path = rel->cheapest_total_path;
+	Path	   *best_path;
 	ListCell   *l;
+	List	   *pathlist;
+
+	if (is_partial)
+	{
+		best_path = linitial(rel->partial_pathlist);
+		pathlist = rel->partial_pathlist;
+	}
+	else
+	{
+		best_path = rel->cheapest_total_path;
+		pathlist = rel->pathlist;
+	}
 
 	/* If all tuples will be retrieved, just return the cheapest-total path */
 	if (tuple_fraction <= 0.0)
@@ -6285,11 +6297,11 @@ get_cheapest_fractional_path(RelOptInfo *rel, double tuple_fraction)
 	if (tuple_fraction >= 1.0 && best_path->rows > 0)
 		tuple_fraction /= best_path->rows;
 
-	foreach(l, rel->pathlist)
+	foreach(l, pathlist)
 	{
 		Path	   *path = (Path *) lfirst(l);
 
-		if (path == rel->cheapest_total_path ||
+		if (path == best_path ||
 			compare_fractional_path_costs(best_path, path, tuple_fraction) <= 0)
 			continue;
 
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 052263aea6d..e3224f99f53 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -231,7 +231,7 @@ make_subplan(PlannerInfo *root, Query *orig_subquery,
 	 * seems no reason to postpone doing that.
 	 */
 	final_rel = fetch_upper_rel(subroot, UPPERREL_FINAL, NULL);
-	best_path = get_cheapest_fractional_path(final_rel, tuple_fraction);
+	best_path = get_cheapest_fractional_path(final_rel, tuple_fraction, false);
 
 	plan = create_plan(subroot, best_path);
 
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index 0c68ec011be..4cc392c7b6c 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -283,7 +283,8 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
 		 * set_subquery_pathlist).
 		 */
 		subpath = get_cheapest_fractional_path(final_rel,
-											   root->tuple_fraction);
+											   root->tuple_fraction,
+											   false);
 
 		/*
 		 * Stick a SubqueryScanPath atop that.
diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
index fc2e15496dd..76a568b7f69 100644
--- a/src/include/optimizer/planner.h
+++ b/src/include/optimizer/planner.h
@@ -54,7 +54,8 @@ extern bool limit_needed(Query *parse);
 extern void mark_partial_aggref(Aggref *agg, AggSplit aggsplit);
 
 extern Path *get_cheapest_fractional_path(RelOptInfo *rel,
-										  double tuple_fraction);
+										  double tuple_fraction,
+										  bool is_partial);
 
 extern Expr *preprocess_phv_expression(PlannerInfo *root, Expr *expr);
 
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 5d59ed7890f..4fa63185b95 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -7368,3 +7368,35 @@ where exists (select 1 from j3
 (13 rows)
 
 drop table j3;
+explain (costs off)
+(select * from tenk1 order by hundred)
+union all
+(select * from tenk2 order by hundred)
+limit 3;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Limit
+   ->  Append
+         ->  Index Scan using tenk1_hundred on tenk1
+         ->  Index Scan using tenk2_hundred on tenk2
+(4 rows)
+
+explain (costs off)
+SELECT * FROM tenk1 t1 join tenk2 t2 using (unique2)
+union all
+SELECT * FROM tenk1 t1 join tenk2 t2 using (unique2)
+limit 3;
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Limit
+   ->  Append
+         ->  Merge Join
+               Merge Cond: (t1.unique2 = t2.unique2)
+               ->  Index Scan using tenk1_unique2 on tenk1 t1
+               ->  Index Scan using tenk2_unique2 on tenk2 t2
+         ->  Merge Join
+               Merge Cond: (t1_1.unique2 = t2_1.unique2)
+               ->  Index Scan using tenk1_unique2 on tenk1 t1_1
+               ->  Index Scan using tenk2_unique2 on tenk2 t2_1
+(10 rows)
+
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index a630f58b571..a7e5dbdaa3e 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2666,3 +2666,15 @@ where exists (select 1 from j3
       and t1.unique1 < 1;
 
 drop table j3;
+
+explain (costs off)
+(select * from tenk1 order by hundred)
+union all
+(select * from tenk2 order by hundred)
+limit 3;
+
+explain (costs off)
+SELECT * FROM tenk1 t1 join tenk2 t2 using (unique2)
+union all
+SELECT * FROM tenk1 t1 join tenk2 t2 using (unique2)
+limit 3;
-- 
2.21.0