v2-0003-Don-t-treat-WITHOUT-OVERLAPS-indexes-as-unique-in.patch

From 7e80c503d3a179315810d61587b9f18462f772f7 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 2 Apr 2024 15:39:04 -0700
Subject: [PATCH v2 3/4] Don't treat WITHOUT OVERLAPS indexes as unique in
 planner

Because the special rangetype 'empty' never overlaps another value, it
is possible for WITHOUT OVERLAPS tables to have two rows with the same
key, despite being indisunique, if the application-time range is
'empty'. So to be safe we should not treat WITHOUT OVERLAPS indexes as
unique in any proofs.

This still needs a test, but I'm having trouble finding a query that
gives wrong results.
---
 src/backend/optimizer/path/indxpath.c     | 5 +++--
 src/backend/optimizer/plan/analyzejoins.c | 6 +++---
 src/backend/optimizer/util/plancat.c      | 1 +
 src/include/nodes/pathnodes.h             | 2 ++
 4 files changed, 9 insertions(+), 5 deletions(-)

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 2230b131047..f3a93834ef2 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3515,13 +3515,14 @@ relation_has_unique_index_ext(PlannerInfo *root, RelOptInfo *rel,
 
 		/*
 		 * If the index is not unique, or not immediately enforced, or if it's
-		 * a partial index, it's useless here.  We're unable to make use of
+		 * a partial index, or if it's a WITHOUT OVERLAPS index (so not
+		 * literally unique), it's useless here.  We're unable to make use of
 		 * predOK partial unique indexes due to the fact that
 		 * check_index_predicates() also makes use of join predicates to
 		 * determine if the partial index is usable. Here we need proofs that
 		 * hold true before any joins are evaluated.
 		 */
-		if (!ind->unique || !ind->immediate || ind->indpred != NIL)
+		if (!ind->unique || !ind->immediate || ind->indpred != NIL || ind->hasperiod)
 			continue;
 
 		/*
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 506fccd20c9..3d332e208f1 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -848,8 +848,8 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
 		 * For a plain relation, we only know how to prove uniqueness by
 		 * reference to unique indexes.  Make sure there's at least one
 		 * suitable unique index.  It must be immediately enforced, and not a
-		 * partial index. (Keep these conditions in sync with
-		 * relation_has_unique_index_for!)
+		 * partial index, and not WITHOUT OVERLAPS (Keep these conditions
+		 * in sync with relation_has_unique_index_for!)
 		 */
 		ListCell   *lc;
 
@@ -857,7 +857,7 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
 		{
 			IndexOptInfo *ind = (IndexOptInfo *) lfirst(lc);
 
-			if (ind->unique && ind->immediate && ind->indpred == NIL)
+			if (ind->unique && ind->immediate && ind->indpred == NIL && !ind->hasperiod)
 				return true;
 		}
 	}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index a398d7a78d1..429b0a284f1 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -457,6 +457,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 			info->predOK = false;	/* set later, in indxpath.c */
 			info->unique = index->indisunique;
 			info->immediate = index->indimmediate;
+			info->hasperiod = index->indisunique && index->indisexclusion;
 			info->hypothetical = false;
 
 			/*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index b8141f141aa..13422951164 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1176,6 +1176,8 @@ struct IndexOptInfo
 	bool		unique;
 	/* is uniqueness enforced immediately? */
 	bool		immediate;
+	/* true if index has WITHOUT OVERLAPS */
+	bool		hasperiod;
 	/* true if index doesn't really exist */
 	bool		hypothetical;
 
-- 
2.42.0