Consider ndistinct on the first column in cost_sort().

danolivo · danolivo · commit 8bbfdc62d875 · 2024-07-25T09:33:07.000+07:00
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
@@ -1863,7 +1863,7 @@ cost_recursive_union(Path *runion, Path *nrterm, Path *rterm)
  */
 static void
 cost_tuplesort(Cost *startup_cost, Cost *run_cost,
-			   double tuples, int width, int cmpMultiplier,
+			   double tuples, int width, double cmpMultiplier,
 			   Cost comparison_cost, int sort_mem,
 			   double limit_tuples)
 {
@@ -2132,8 +2132,34 @@ cost_sort(Path *path, PlannerInfo *root,
 {
 	Cost		startup_cost;
 	Cost		run_cost;
-	int			cmpMultiplier =
-						(pathkeys == NIL) ? 2.0 : list_length(pathkeys) + 1.0;
+	int			n = list_length(pathkeys);
+	double		cmpMultiplier = (n == 0) ? 2.0 : n + 1;
+
+	if (root != NULL && tuples > 1 && n > 1)
+	{
+		PathKey			    *key = linitial_node(PathKey, pathkeys);
+		bool				isdefault;
+		VariableStatData	vardata;
+		double				nd = -1;
+		Bitmapset *relids = pull_varnos(root, (Node *) key->source_expr);
+
+		if (!bms_is_member(0, relids))
+		{
+			examine_variable(root, (Node *) key->source_expr, 0, &vardata);
+			if (HeapTupleIsValid(vardata.statsTuple))
+			{
+				nd = get_variable_numdistinct(&vardata, &isdefault);
+
+				if (!isdefault)
+				{
+					if (tuples >= nd)
+						cmpMultiplier =
+								2.0 + ((tuples - nd) / (tuples - 1)) * (n - 1);
+				}
+			}
+			ReleaseVariableStats(vardata);
+		}
+	}
 
 	cost_tuplesort(&startup_cost, &run_cost,
 				   tuples, width, cmpMultiplier,
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
@@ -2842,19 +2842,18 @@ SELECT count(*)
                                   QUERY PLAN                                   
 -------------------------------------------------------------------------------
  GroupAggregate
-   Group Key: t1.z, t1.w, t1.x, t1.y
-   ->  Incremental Sort
-         Sort Key: t1.z, t1.w, t1.x, t1.y
-         Presorted Key: t1.z, t1.w, t1.x
+   Group Key: t1.x, t1.y, t1.z, t1.w
+   ->  Sort
+         Sort Key: t1.x, t1.y, t1.z, t1.w
          ->  Merge Join
-               Merge Cond: ((t1.z = t2.z) AND (t1.w = t2.w) AND (t1.x = t2.x))
+               Merge Cond: ((t1.w = t2.w) AND (t1.z = t2.z) AND (t1.x = t2.x))
                ->  Sort
-                     Sort Key: t1.z, t1.w, t1.x
+                     Sort Key: t1.w, t1.z, t1.x
                      ->  Index Scan using btg_x_y_idx on btg t1
                ->  Sort
-                     Sort Key: t2.z, t2.w, t2.x
+                     Sort Key: t2.w, t2.z, t2.x
                      ->  Index Scan using btg_x_y_idx on btg t2
-(13 rows)
+(12 rows)
 
 RESET enable_nestloop;
 RESET enable_hashjoin;
@@ -2878,12 +2877,11 @@ SELECT count(*) FROM btg GROUP BY w, x, y, z ORDER BY x*x, z;
  Sort
    Sort Key: ((x * x)), z
    ->  GroupAggregate
-         Group Key: x, y, w, z
-         ->  Incremental Sort
-               Sort Key: x, y, w, z
-               Presorted Key: x, y
+         Group Key: w, x, y, z
+         ->  Sort
+               Sort Key: w, x, y, z
                ->  Index Scan using btg_x_y_idx on btg
-(8 rows)
+(7 rows)
 
 -- Test the case where the number of incoming subtree path keys is more than
 -- the number of grouping keys.
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
@@ -641,13 +641,13 @@ SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
                ->  Sort
                      Sort Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
                      ->  Merge Full Join
-                           Merge Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
+                           Merge Cond: ((prt1_2.b = p2_2.b) AND (prt1_2.a = p2_2.a))
                            Filter: ((COALESCE(prt1_2.a, p2_2.a) >= 490) AND (COALESCE(prt1_2.a, p2_2.a) <= 510))
                            ->  Sort
-                                 Sort Key: prt1_2.a, prt1_2.b
+                                 Sort Key: prt1_2.b, prt1_2.a
                                  ->  Seq Scan on prt1_p3 prt1_2
                            ->  Sort
-                                 Sort Key: p2_2.a, p2_2.b
+                                 Sort Key: p2_2.b, p2_2.a
                                  ->  Seq Scan on prt2_p3 p2_2
 (43 rows)
 
