v2-0001-append-limit-v1.patch

From eb7e3c6247d9844db2a95f804144f370ddd758a8 Mon Sep 17 00:00:00 2001
From: Nikita Malakhov <n.malakhov@postgrespro.ru>
Date: Thu, 31 Oct 2024 21:56:28 +0300
Subject: [PATCH] Make add_paths_to_append_rel() to consider fractional paths
 while calculating the cheapest one.

When add_paths_to_append_rel evaluates path costs
if tuple_fraction has valid value (it has fractional paths)
fractional paths costs should be taken into account while
searching for the cheapest one, analogous to the
generate_orderedappend_paths() case root->tuple_fraction > 0.

Author: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
---
 src/backend/optimizer/path/allpaths.c | 15 +++++++++++++--
 src/backend/optimizer/plan/planner.c  | 24 ++++++++++++++++++++++--
 src/include/optimizer/planner.h       |  4 ++++
 src/test/regress/expected/union.out   | 15 +++++++--------
 4 files changed, 46 insertions(+), 12 deletions(-)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 172edb643a..9976907d3a 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1364,9 +1364,20 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 		 */
 		if (rel->consider_startup && childrel->cheapest_startup_path != NULL)
 		{
+			Path	   *cheapest_path;
+
+			if (root->tuple_fraction > 0.0)
+			{
+				cheapest_path =
+					get_cheapest_fractional_path_ext(childrel,
+													 root->tuple_fraction, true);
+			}
+			else
+				cheapest_path = childrel->cheapest_startup_path;
+
 			/* cheapest_startup_path must not be a parameterized path. */
-			Assert(childrel->cheapest_startup_path->param_info == NULL);
-			accumulate_append_subpath(childrel->cheapest_startup_path,
+			Assert(cheapest_path->param_info == NULL);
+			accumulate_append_subpath(cheapest_path,
 									  &startup_subpaths,
 									  NULL);
 		}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 0f423e9684..3c81614141 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -6517,16 +6517,17 @@ make_sort_input_target(PlannerInfo *root,
 }
 
 /*
- * get_cheapest_fractional_path
+ * get_cheapest_fractional_path_ext
  *	  Find the cheapest path for retrieving a specified fraction of all
  *	  the tuples expected to be returned by the given relation.
+ *	  If nonparameterized is set, return only non-parameterized paths
  *
  * We interpret tuple_fraction the same way as grouping_planner.
  *
  * 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_ext(RelOptInfo *rel, double tuple_fraction, bool nonparameterized)
 {
 	Path	   *best_path = rel->cheapest_total_path;
 	ListCell   *l;
@@ -6543,6 +6544,10 @@ get_cheapest_fractional_path(RelOptInfo *rel, double tuple_fraction)
 	{
 		Path	   *path = (Path *) lfirst(l);
 
+		/* Skip parameterized paths if requested */
+		if (nonparameterized && path->param_info)
+			continue;
+
 		if (path == rel->cheapest_total_path ||
 			compare_fractional_path_costs(best_path, path, tuple_fraction) <= 0)
 			continue;
@@ -6553,6 +6558,21 @@ get_cheapest_fractional_path(RelOptInfo *rel, double tuple_fraction)
 	return best_path;
 }
 
+/*
+ * get_cheapest_fractional_path
+ *	  Find the cheapest path for retrieving a specified fraction of all
+ *	  the tuples expected to be returned by the given relation.
+ *
+ * We interpret tuple_fraction the same way as grouping_planner.
+ *
+ * We assume set_cheapest() has been run on the given rel.
+ */
+Path *
+get_cheapest_fractional_path(RelOptInfo *rel, double tuple_fraction)
+{
+	return get_cheapest_fractional_path_ext(rel, tuple_fraction, false);
+}
+
 /*
  * adjust_paths_for_srfs
  *		Fix up the Paths of the given upperrel to handle tSRFs properly.
diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
index 5aeff21b96..6d033d367f 100644
--- a/src/include/optimizer/planner.h
+++ b/src/include/optimizer/planner.h
@@ -57,6 +57,10 @@ extern void mark_partial_aggref(Aggref *agg, AggSplit aggsplit);
 extern Path *get_cheapest_fractional_path(RelOptInfo *rel,
 										  double tuple_fraction);
 
+extern Path *get_cheapest_fractional_path_ext(RelOptInfo *rel,
+										  double tuple_fraction,
+										  bool nonparameterized);
+
 extern Expr *preprocess_phv_expression(PlannerInfo *root, Expr *expr);
 
 #endif							/* PLANNER_H */
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index c73631a9a1..62eb4239ad 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1461,18 +1461,17 @@ select t1.unique1 from tenk1 t1
 inner join tenk2 t2 on t1.tenthous = t2.tenthous and t2.thousand = 0
    union all
 (values(1)) limit 1;
-                       QUERY PLAN                       
---------------------------------------------------------
+                             QUERY PLAN                              
+---------------------------------------------------------------------
  Limit
    ->  Append
          ->  Nested Loop
-               Join Filter: (t1.tenthous = t2.tenthous)
-               ->  Seq Scan on tenk1 t1
-               ->  Materialize
-                     ->  Seq Scan on tenk2 t2
-                           Filter: (thousand = 0)
+               ->  Seq Scan on tenk2 t2
+                     Filter: (thousand = 0)
+               ->  Index Scan using tenk1_thous_tenthous on tenk1 t1
+                     Index Cond: (tenthous = t2.tenthous)
          ->  Result
-(9 rows)
+(8 rows)
 
 -- Ensure there is no problem if cheapest_startup_path is NULL
 explain (costs off)
-- 
2.25.1