v8-0002-Optimized-reverse-pathkeys.patch

From dd1e35faf8fba5af616e9156624d3508e4f0982a Mon Sep 17 00:00:00 2001
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Date: Fri, 3 Apr 2026 17:34:30 +0100
Subject: [PATCH 2/6] Optimized reverse pathkeys

Instead of calling build_index_pathkeys twice (once for forward and once
for backward scan direction), compute pathkeys only for the forward scan
and derive backward pathkeys using reverse_pathkeys().

This removes the ScanDirection parameter from build_index_pathkeys and
adds two new helper functions:
- make_reversed_pathkey: reverses a single pathkey's sort direction
- reverse_pathkeys: reverses a list of pathkeys

This gives measurable performance improvement in the planner benchmark.
Additionally, it will reduce the per-index cost of the SLOPE analysis
in the upcomming commits.
---
 src/backend/optimizer/path/indxpath.c | 12 ++---
 src/backend/optimizer/path/pathkeys.c | 68 +++++++++++++++++++++------
 src/include/optimizer/paths.h         |  5 +-
 3 files changed, 63 insertions(+), 22 deletions(-)

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 3f5d4fa3182..e5c0c5fcda6 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -917,8 +917,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
 	index_is_ordered = (index->sortopfamily != NULL);
 	if (index_is_ordered && pathkeys_possibly_useful)
 	{
-		index_pathkeys = build_index_pathkeys(root, index,
-											  ForwardScanDirection);
+		index_pathkeys = build_index_pathkeys(root, index);
 		useful_pathkeys = truncate_useless_pathkeys(root, rel,
 													index_pathkeys);
 		orderbyclauses = NIL;
@@ -1009,13 +1008,14 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
 
 	/*
 	 * 5. If the index is ordered, a backwards scan might be interesting.
+	 * Only consider it if we have forward pathkeys to reverse.
 	 */
-	if (index_is_ordered && pathkeys_possibly_useful)
+	if (index_is_ordered && pathkeys_possibly_useful && index_pathkeys != NIL)
 	{
-		index_pathkeys = build_index_pathkeys(root, index,
-											  BackwardScanDirection);
+		List	   *backward_pathkeys = reverse_pathkeys(root, index_pathkeys);
+
 		useful_pathkeys = truncate_useless_pathkeys(root, rel,
-													index_pathkeys);
+													backward_pathkeys);
 		if (useful_pathkeys != NIL)
 		{
 			ipath = create_index_path(root, index,
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 5eb71635d15..3e3eb720c1f 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -98,6 +98,54 @@ make_canonical_pathkey(PlannerInfo *root,
 	return pk;
 }
 
+/*
+ * make_reversed_pathkey
+ *	  Create a pathkey with reversed sort direction.
+ *
+ * This flips COMPARE_LT <-> COMPARE_GT and inverts nulls_first.
+ */
+PathKey *
+make_reversed_pathkey(PlannerInfo *root, PathKey *pathkey)
+{
+	CompareType reversed_cmptype;
+
+	if (pathkey->pk_cmptype == COMPARE_LT)
+		reversed_cmptype = COMPARE_GT;
+	else if (pathkey->pk_cmptype == COMPARE_GT)
+		reversed_cmptype = COMPARE_LT;
+	else
+		reversed_cmptype = pathkey->pk_cmptype;
+
+	return make_canonical_pathkey(root,
+								  pathkey->pk_eclass,
+								  pathkey->pk_opfamily,
+								  reversed_cmptype,
+								  !pathkey->pk_nulls_first);
+}
+
+/*
+ * reverse_pathkeys
+ *	  Create a list of pathkeys with reversed sort directions.
+ *
+ * This is useful for deriving backward index scan pathkeys from
+ * forward scan pathkeys without recomputing them from scratch.
+ */
+List *
+reverse_pathkeys(PlannerInfo *root, List *pathkeys)
+{
+	List	   *result = NIL;
+	ListCell   *lc;
+
+	foreach(lc, pathkeys)
+	{
+		PathKey    *pk = lfirst_node(PathKey, lc);
+
+		result = lappend(result, make_reversed_pathkey(root, pk));
+	}
+
+	return result;
+}
+
 /*
  * append_pathkeys
  *		Append all non-redundant PathKeys in 'source' onto 'target' and
@@ -722,8 +770,8 @@ get_cheapest_parallel_safe_total_inner(List *paths)
  *	  scan using the given index.  (Note that an unordered index doesn't
  *	  induce any ordering, so we return NIL.)
  *
- * If 'scandir' is BackwardScanDirection, build pathkeys representing a
- * backwards scan of the index.
+ * This always builds pathkeys for a forward scan of the index.  For backward
+ * scans, the caller should use reverse_pathkeys() on the result.
  *
  * We iterate only key columns of covering indexes, since non-key columns
  * don't influence index ordering.  The result is canonical, meaning that
@@ -738,8 +786,7 @@ get_cheapest_parallel_safe_total_inner(List *paths)
  */
 List *
 build_index_pathkeys(PlannerInfo *root,
-					 IndexOptInfo *index,
-					 ScanDirection scandir)
+					 IndexOptInfo *index)
 {
 	List	   *retval = NIL;
 	ListCell   *lc;
@@ -767,16 +814,9 @@ build_index_pathkeys(PlannerInfo *root,
 		/* We assume we don't need to make a copy of the tlist item */
 		indexkey = indextle->expr;
 
-		if (ScanDirectionIsBackward(scandir))
-		{
-			reverse_sort = !index->reverse_sort[i];
-			nulls_first = !index->nulls_first[i];
-		}
-		else
-		{
-			reverse_sort = index->reverse_sort[i];
-			nulls_first = index->nulls_first[i];
-		}
+		/* Use the index's natural sort order (forward scan) */
+		reverse_sort = index->reverse_sort[i];
+		nulls_first = index->nulls_first[i];
 
 		/*
 		 * OK, try to make a canonical pathkey for this sort key.
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 17f2099ec3b..4ad3e8eaa89 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -235,8 +235,9 @@ extern Path *get_cheapest_fractional_path_for_pathkeys(List *paths,
 													   Relids required_outer,
 													   double fraction);
 extern Path *get_cheapest_parallel_safe_total_inner(List *paths);
-extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
-								  ScanDirection scandir);
+extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index);
+extern PathKey *make_reversed_pathkey(PlannerInfo *root, PathKey *pathkey);
+extern List *reverse_pathkeys(PlannerInfo *root, List *pathkeys);
 extern List *build_partition_pathkeys(PlannerInfo *root, RelOptInfo *partrel,
 									  ScanDirection scandir, bool *partialkeys);
 extern List *build_expression_pathkey(PlannerInfo *root, Expr *expr,
-- 
2.53.0