From 2e436e636b083169b716e47e155542fad3fe0d99 Mon Sep 17 00:00:00 2001
From: Mayrom Rabinovich <mayromrabinovich@gmail.com>
Date: Thu, 5 Feb 2026 11:55:15 +0200
Subject: [PATCH] perf(optimizer): try to create reverse pathkey for non null
 indexes

This is done in order to prevent full table scan and sorts on queries
that that do "...order by x desc nulls last limit 1" where the the index
nulls order is not the same as the query BUT the column is non null.

diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 5eb7163..6b0b7e4 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -778,6 +778,29 @@ build_index_pathkeys(PlannerInfo *root,
 			nulls_first = index->nulls_first[i];
 		}
 
+		/*
+		 * For not null columns nulls_first order is irrelevant since there are no nulls,
+		 * We try to create an alternative pathkey with the reverse nulls_first direction and search
+		 * if its present in our query pathkeys, if so we should use it as it's a prefect match.
+		 */
+		if (index->indexkeys[i] > 0 &&
+			bms_is_member(index->indexkeys[i], index->rel->notnullattnums))
+		{
+			cpathkey = make_pathkey_from_sortinfo(root,
+												 indexkey,
+												 index->sortopfamily[i],
+												 index->opcintype[i],
+												 index->indexcollations[i],
+												 reverse_sort,
+												 !nulls_first,
+												 0,
+												 index->rel->relids,
+												 false);
+
+			if (cpathkey != NULL && list_member_ptr(root->sort_pathkeys, cpathkey))
+				goto reverse_pathkey_found;
+		}
+
 		/*
 		 * OK, try to make a canonical pathkey for this sort key.
 		 */
@@ -791,7 +814,7 @@ build_index_pathkeys(PlannerInfo *root,
 											  0,
 											  index->rel->relids,
 											  false);
-
+reverse_pathkey_found:
 		if (cpathkey)
 		{
 			/*
-- 
2.52.0