On 6/23/26 01:39, David Rowley wrote:
A couple of things about the patch:
1. I think the new est_input_groups field should be Cardinality rather
than double. Various other Path types and Plan nodes call this
"numGroups". I don't see any reason to deviate from that.
2. The header comment for cost_incremental_sort needs to be updated to
mention that p_input_groups may be passed as non-NULL to provide the
caller with the estimated number of sort groups.
Fixed in v2-patch.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
From 8478efd03dd22269cd9da8ee4772a0c068d11664 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Tue, 23 Jun 2026 11:37:45 +0300
Subject: [PATCH v2] Show estimated number of groups for IncrementalSort in
EXPLAIN
IncrementalSort cost heavily depends on the estimated number of groups
with equal presorted key values. A bad estimate can cause the planner
to choose IncrementalSort over Sort even when it would actually be
slower, with no visible indication of why.
---
src/backend/commands/explain.c | 5 +++++
src/backend/optimizer/path/costsize.c | 15 ++++++++++++---
src/backend/optimizer/plan/createplan.c | 4 +++-
src/backend/optimizer/util/pathnode.c | 6 ++++--
src/include/nodes/pathnodes.h | 1 +
src/include/nodes/plannodes.h | 2 ++
src/include/optimizer/cost.h | 3 ++-
7 files changed, 29 insertions(+), 7 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 112c17b0d64..3fc5831db9a 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3306,11 +3306,16 @@ static void
show_incremental_sort_info(IncrementalSortState *incrsortstate,
ExplainState *es)
{
+ IncrementalSort *plan = (IncrementalSort *) incrsortstate->ss.ps.plan;
IncrementalSortGroupInfo *fullsortGroupInfo;
IncrementalSortGroupInfo *prefixsortGroupInfo;
fullsortGroupInfo = &incrsortstate->incsort_info.fullsortGroupInfo;
+ if (es->costs)
+ ExplainPropertyFloat("Estimated Groups", NULL,
+ plan->numGroups, 0, es);
+
if (!es->analyze)
return;
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 1c575e56ff6..474618a78d0 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2045,6 +2045,9 @@ cost_tuplesort(Cost *startup_cost, Cost *run_cost,
* 'presorted_keys' is the number of leading pathkeys by which the input path
* is sorted.
*
+ * If p_numGroups is not NULL, *p_numGroups is set to the estimated number of
+ * sort groups (groups of tuples sharing equal presorted-key values).
+ *
* We estimate the number of groups into which the relation is divided by the
* leading pathkeys, and then calculate the cost of sorting a single group
* with tuplesort using cost_tuplesort().
@@ -2055,7 +2058,8 @@ cost_incremental_sort(Path *path,
int input_disabled_nodes,
Cost input_startup_cost, Cost input_total_cost,
double input_tuples, int width, Cost comparison_cost, int sort_mem,
- double limit_tuples)
+ double limit_tuples,
+ Cardinality *p_numGroups)
{
Cost startup_cost,
run_cost,
@@ -2171,6 +2175,9 @@ cost_incremental_sort(Path *path,
*/
run_cost += 2.0 * cpu_tuple_cost * input_groups;
+ if (p_numGroups)
+ *p_numGroups = input_groups;
+
path->rows = input_tuples;
/*
@@ -2406,7 +2413,8 @@ cost_append(AppendPath *apath, PlannerInfo *root)
subpath->pathtarget->width,
0.0,
work_mem,
- apath->limit_tuples);
+ apath->limit_tuples,
+ NULL);
}
else
{
@@ -3827,7 +3835,8 @@ initial_cost_mergejoin(PlannerInfo *root, JoinCostWorkspace *workspace,
outer_path->pathtarget->width,
0.0,
work_mem,
- -1.0);
+ -1.0,
+ NULL);
}
else
{
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index de6a183da79..64d89880602 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2067,6 +2067,7 @@ create_incrementalsort_plan(PlannerInfo *root, IncrementalSortPath *best_path,
best_path->nPresortedCols);
copy_generic_path_info(&plan->sort.plan, (Path *) best_path);
+ plan->numGroups = best_path->numGroups;
return plan;
}
@@ -5441,7 +5442,8 @@ label_incrementalsort_with_costsize(PlannerInfo *root, IncrementalSort *plan,
lefttree->plan_width,
0.0,
work_mem,
- limit_tuples);
+ limit_tuples,
+ &plan->numGroups);
plan->sort.plan.startup_cost = sort_path.startup_cost;
plan->sort.plan.total_cost = sort_path.total_cost;
plan->sort.plan.plan_rows = lefttree->plan_rows;
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 73518c8f870..5d483182fbe 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1605,7 +1605,8 @@ create_merge_append_path(PlannerInfo *root,
subpath->pathtarget->width,
0.0,
work_mem,
- pathnode->limit_tuples);
+ pathnode->limit_tuples,
+ NULL);
}
else
{
@@ -2883,7 +2884,8 @@ create_incremental_sort_path(PlannerInfo *root,
subpath->rows,
subpath->pathtarget->width,
0.0, /* XXX comparison_cost shouldn't be 0? */
- work_mem, limit_tuples);
+ work_mem, limit_tuples,
+ &sort->numGroups);
sort->nPresortedCols = presorted_keys;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 27a2c6815b7..185e2fb8761 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2548,6 +2548,7 @@ typedef struct IncrementalSortPath
{
SortPath spath;
int nPresortedCols; /* number of presorted columns */
+ Cardinality numGroups; /* estimated number of groups */
} IncrementalSortPath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 14a1dfed2b9..93edce75ddc 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1169,6 +1169,8 @@ typedef struct IncrementalSort
Sort sort;
/* number of presorted columns */
int nPresortedCols;
+ /* estimated number of groups (groups with equal presorted keys) */
+ Cardinality numGroups;
} IncrementalSort;
/* ---------------
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index f2fd5d31507..d9caa2b1a48 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -117,7 +117,8 @@ extern void cost_incremental_sort(Path *path,
int input_disabled_nodes,
Cost input_startup_cost, Cost input_total_cost,
double input_tuples, int width, Cost comparison_cost, int sort_mem,
- double limit_tuples);
+ double limit_tuples,
+ Cardinality *p_numGroups);
extern void cost_append(AppendPath *apath, PlannerInfo *root);
extern void cost_merge_append(Path *path, PlannerInfo *root,
List *pathkeys, int n_streams,
--
2.34.1
