Subject: Re: Make \d tablename fast again, regression introduced by 85b7efa1cdd

From: Tom Lane <[email-masked]>
Date: Sat, 04 Jul 2026 16:27:05 -0400

"Jelte Fennema-Nio" <[email-masked]> writes:
> (forked from: Test timings are increasing too fast for cfbot)
> On Wed, 25 Mar 2026 at 04:15, Andres Freund <[email-masked]> wrote:
>> It seems decidedly not optimal that "\d tablename", without any patterns, ends
>> up doing a seqscan.
>> ...
>> I don't think this was always the case?

> Due to a very similar problem I faced in the past[1], I thought I had a
> good sense of where roughly the problem was. And I indeed quickly found
> it.
> Attached is a patch that addresses this issue and starts using index
> scans again for \d tablename.

I looked this over, and I think it's basically right, but don't
we need to check that *both* of the collations are deterministic?

Also the commit message had a lot of details wrong.  AFAICS this
only affects the exact-match case not prefix-match, and it applies
to regexes as well as LIKE.

See attached v2.

			regards, tom lane

From 841b299cfd0910e9c324252953824365a725ecd3 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email-masked]>
Date: Sat, 4 Jul 2026 16:21:42 -0400
Subject: [PATCH v2] Fix LIKE/regex optimization for indexscan with exact-match
 pattern.

Commit 85b7efa1c introduced support for LIKE with non-deterministic
collations.  By moving some conditionals around, it accidentally broke
the optimization for converting a LIKE or regex exact-match pattern
to an equality indexqual when the index collation doesn't match the
filter collation.  We can't do that conversion if either collation is
nondeterministic, but we can if both are deterministic.  This patch
re-introduces the optimization for that common case.

One important beneficiary of this optimization is the "\d tablename"
command in psql.  Without this fix that will do a seqscan on pg_class
instead of an index point lookup.

Reported-by: Andres Freund <[email-masked]>
Author: Jelte Fennema-Nio <[email-masked]>
Reviewed-by: Tom Lane <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]
Backpatch-through: 18
---
 src/backend/utils/adt/like_support.c  | 15 +++++++++++----
 src/test/regress/expected/collate.out | 21 ++++++++++++++++++++-
 src/test/regress/sql/collate.sql      | 11 +++++++++++
 3 files changed, 42 insertions(+), 5 deletions(-)

diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c
index 01cd6b10730..dea6a147c69 100644
--- a/src/backend/utils/adt/like_support.c
+++ b/src/backend/utils/adt/like_support.c
@@ -69,6 +69,10 @@ typedef enum
 	Pattern_Prefix_None, Pattern_Prefix_Partial, Pattern_Prefix_Exact,
 } Pattern_Prefix_Status;
 
+/* non-collatable comparisons, eg for bytea, are always deterministic */
+#define NONDETERMINISTIC(coll) \
+	(OidIsValid(coll) && !get_collation_isdeterministic(coll))
+
 static Node *like_regex_support(Node *rawreq, Pattern_Type ptype);
 static List *match_pattern_prefix(Node *leftop,
 								  Node *rightop,
@@ -381,12 +385,17 @@ match_pattern_prefix(Node *leftop,
 	 * us to not be concerned with specific opclasses (except for the legacy
 	 * "pattern" cases); any index that correctly implements the operators
 	 * will work.
+	 *
+	 * Also, since all deterministic collations agree on equality, we can use
+	 * an index that uses a different collation so long as both collations are
+	 * deterministic.  Otherwise, fail quietly.
 	 */
 	if (pstatus == Pattern_Prefix_Exact)
 	{
 		if (!op_in_opfamily(eqopr, opfamily))
 			return NIL;
-		if (indexcollation != expr_coll)
+		if (indexcollation != expr_coll &&
+			(NONDETERMINISTIC(indexcollation) || NONDETERMINISTIC(expr_coll)))
 			return NIL;
 		expr = make_opclause(eqopr, BOOLOID, false,
 							 (Expr *) leftop, (Expr *) prefix,
@@ -400,10 +409,8 @@ match_pattern_prefix(Node *leftop,
 	 * expression collation is nondeterministic.  The optimized equality or
 	 * prefix tests use bytewise comparisons, which is not consistent with
 	 * nondeterministic collations.
-	 *
-	 * expr_coll is not set for a non-collation-aware data type such as bytea.
 	 */
-	if (expr_coll && !get_collation_isdeterministic(expr_coll))
+	if (NONDETERMINISTIC(expr_coll))
 		return NIL;
 
 	/*
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index 25818f09ad2..a6c46347647 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -768,13 +768,31 @@ DETAIL:  LOCALE cannot be specified together with LC_COLLATE or LC_CTYPE.
 CREATE COLLATION coll_dup_chk (FROM = "C", VERSION = "1");
 ERROR:  conflicting or redundant options
 DETAIL:  FROM cannot be specified together with any other options.
+-- Regex exact-match optimization should use index even when the expression
+-- has COLLATE "default" and the index has a different (but deterministic)
+-- collation OID, because equality is collation-insensitive for deterministic
+-- collations.
+CREATE TABLE regex_idx_test (x text);
+CREATE INDEX ON regex_idx_test (x COLLATE "C");
+SET enable_seqscan = off;
+EXPLAIN (costs off)
+SELECT * FROM regex_idx_test WHERE x ~ '^(abc)$' COLLATE "default";
+                   QUERY PLAN                    
+-------------------------------------------------
+ Bitmap Heap Scan on regex_idx_test
+   Filter: (x ~ '^(abc)$'::text)
+   ->  Bitmap Index Scan on regex_idx_test_x_idx
+         Index Cond: (x = 'abc'::text)
+(4 rows)
+
+RESET enable_seqscan;
 --
 -- Clean up.  Many of these table names will be re-used if the user is
 -- trying to run any platform-specific collation tests later, so we
 -- must get rid of them.
 --
 DROP SCHEMA collate_tests CASCADE;
-NOTICE:  drop cascades to 20 other objects
+NOTICE:  drop cascades to 21 other objects
 DETAIL:  drop cascades to table collate_test1
 drop cascades to table collate_test_like
 drop cascades to table collate_test2
@@ -795,3 +813,4 @@ drop cascades to collation builtin_c
 drop cascades to collation mycoll2
 drop cascades to table collate_test23
 drop cascades to view collate_on_int
+drop cascades to table regex_idx_test
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sql
index 4b0e4472c3f..78507e5a89b 100644
--- a/src/test/regress/sql/collate.sql
+++ b/src/test/regress/sql/collate.sql
@@ -302,6 +302,17 @@ CREATE COLLATION coll_dup_chk (LC_CTYPE = "POSIX", LOCALE = '');
 -- FROM conflicts with any other option
 CREATE COLLATION coll_dup_chk (FROM = "C", VERSION = "1");
 
+-- Regex exact-match optimization should use index even when the expression
+-- has COLLATE "default" and the index has a different (but deterministic)
+-- collation OID, because equality is collation-insensitive for deterministic
+-- collations.
+CREATE TABLE regex_idx_test (x text);
+CREATE INDEX ON regex_idx_test (x COLLATE "C");
+SET enable_seqscan = off;
+EXPLAIN (costs off)
+SELECT * FROM regex_idx_test WHERE x ~ '^(abc)$' COLLATE "default";
+RESET enable_seqscan;
+
 --
 -- Clean up.  Many of these table names will be re-used if the user is
 -- trying to run any platform-specific collation tests later, so we
-- 
2.52.0

---

From: "Jelte Fennema-Nio" <[email-masked]>
Date: Mon, 06 Jul 2026 13:21:30 +0200

> we need to check that *both* of the collations are deterministic?

That's not what the code did before 85b7efa1c at least. And I think that's 
correct because I think it's fine if the index collation is 
nondeterministic: it'll return a superset of the matches that the 
expression collation thinks are equal, and then the recheck condition will 
filter out the conflicting cases (because it's marked as lossy).

Attached is v3, which adds a test for this case and expands the comment. I 
left your commit message improvements as is, I agree that the message is a 
lot better after your changes (I was indeed sorely mistaken about the
general prefix pattern optimization being impacted, the regex addition
is nice but seems relatively minor).

P.S. I think we could mark the comparisons in certain cases as
non-lossy, but after trying that for a bit the details turn out more
complicated than I expected. And that's definitely not something to backport.
From e9e231a90624e6d669202f3a4b79de974846cfcf Mon Sep 17 00:00:00 2001
From: Tom Lane <[email-masked]>
Date: Sat, 4 Jul 2026 16:21:42 -0400
Subject: [PATCH v3] Fix LIKE/regex optimization for indexscan with exact-match
 pattern

Commit 85b7efa1c introduced support for LIKE with non-deterministic
collations.  By moving some conditionals around, it accidentally broke
the optimization for converting a LIKE or regex exact-match pattern
to an equality indexqual when the index collation doesn't match the
filter collation.  We can always do that conversion if the expression
collation is deterministic. This patch re-introduces the optimization
for that common case.

Reported-by: Andres Freund <[email-masked]>
Author: Jelte Fennema-Nio <[email-masked]>
Reviewed-by: Tom Lane <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]
Backpatch-through: 18
---
 src/backend/utils/adt/like_support.c          | 23 +++++++++++++++----
 .../regress/expected/collate.icu.utf8.out     | 19 +++++++++++++++
 src/test/regress/expected/collate.out         | 22 +++++++++++++++++-
 src/test/regress/sql/collate.icu.utf8.sql     | 12 ++++++++++
 src/test/regress/sql/collate.sql              | 13 +++++++++++
 5 files changed, 84 insertions(+), 5 deletions(-)

index 01cd6b10730..960e118307b 100644
 
+/* non-collatable comparisons, eg for bytea, are always deterministic */
+#define NONDETERMINISTIC(coll) \
+	(OidIsValid(coll) && !get_collation_isdeterministic(coll))
+
 static Node *like_regex_support(Node *rawreq, Pattern_Type ptype);
 static List *match_pattern_prefix(Node *leftop,
 								  Node *rightop,
@@ -381,12 +385,25 @@ match_pattern_prefix(Node *leftop,
 	 * us to not be concerned with specific opclasses (except for the legacy
 	 * "pattern" cases); any index that correctly implements the operators
 	 * will work.
+	 *
+	 * Also, we can use an index whose collation differs from the
+	 * expression's, so long as the expression's collation is deterministic.
+	 * It doesn't matter whether the index collation is deterministic or not:
+	 * if it's deterministic it agrees on equality with the expression
+	 * collation, and if it's nondeterministic the "=" indexqual merely treats
+	 * a superset of values as equal.  The latter is fine because the
+	 * indexqual is lossy (the original pattern is rechecked), so the recheck
+	 * filters out the rows where the index collation disagrees with the
+	 * expression collation.  A nondeterministic expression collation, on the
+	 * other hand, is only safe when the index uses that exact same collation,
+	 * since otherwise the indexqual could omit matching rows.  Otherwise,
+	 * fail quietly.
 	 */
 	if (pstatus == Pattern_Prefix_Exact)
 	{
 		if (!op_in_opfamily(eqopr, opfamily))
 			return NIL;
-		if (indexcollation != expr_coll)
+		if (indexcollation != expr_coll && NONDETERMINISTIC(expr_coll))
 			return NIL;
 		expr = make_opclause(eqopr, BOOLOID, false,
 							 (Expr *) leftop, (Expr *) prefix,
@@ -400,10 +417,8 @@ match_pattern_prefix(Node *leftop,
 	 * expression collation is nondeterministic.  The optimized equality or
 	 * prefix tests use bytewise comparisons, which is not consistent with
 	 * nondeterministic collations.
-	 *
-	 * expr_coll is not set for a non-collation-aware data type such as bytea.
 	 */
-	if (expr_coll && !get_collation_isdeterministic(expr_coll))
+	if (NONDETERMINISTIC(expr_coll))
 		return NIL;
 
 	/*
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 04e2f6df037..eb58009e57a 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1561,6 +1561,25 @@ SELECT x FROM test3cs WHERE x ~ 'a';
  abc
 (1 row)
 
+-- An exact-match regex is converted to an "=" indexqual.  We can use an index
+-- whose collation differs from the expression's as long as the expression's
+-- collation is deterministic, even when the index's collation is
+-- nondeterministic.  Check that we get an index scan rather than a seqscan.
+CREATE INDEX test3cs_ci ON test3cs (x COLLATE case_insensitive);
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+EXPLAIN (costs off)
+SELECT x FROM test3cs WHERE x ~ '^(abc)$';
+                 QUERY PLAN                  
+---------------------------------------------
+ Index Only Scan using test3cs_ci on test3cs
+   Index Cond: (x = 'abc'::text)
+   Filter: (x ~ '^(abc)$'::text)
+(3 rows)
+
+RESET enable_seqscan;
+RESET enable_bitmapscan;
+DROP INDEX test3cs_ci;
 SET enable_hashagg TO off;
 SELECT x FROM test1cs UNION SELECT x FROM test2cs ORDER BY x;
   x  
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index 25818f09ad2..c7252c79b0d 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -768,13 +768,32 @@ DETAIL:  LOCALE cannot be specified together with LC_COLLATE or LC_CTYPE.
 CREATE COLLATION coll_dup_chk (FROM = "C", VERSION = "1");
 ERROR:  conflicting or redundant options
 DETAIL:  FROM cannot be specified together with any other options.
+-- Regex exact-match optimization should use index even when the expression
+-- has COLLATE "default" and the index has a different (but deterministic)
+-- collation OID, because equality is collation-insensitive for deterministic
+-- collations.
+CREATE TABLE regex_idx_test (x text);
+CREATE INDEX ON regex_idx_test (x COLLATE "C");
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+EXPLAIN (costs off)
+SELECT * FROM regex_idx_test WHERE x ~ '^(abc)$' COLLATE "default";
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Index Only Scan using regex_idx_test_x_idx on regex_idx_test
+   Index Cond: (x = 'abc'::text)
+   Filter: (x ~ '^(abc)$'::text)
+(3 rows)
+
+RESET enable_seqscan;
+RESET enable_bitmapscan;
 --
 -- Clean up.  Many of these table names will be re-used if the user is
 -- trying to run any platform-specific collation tests later, so we
 -- must get rid of them.
 --
 DROP SCHEMA collate_tests CASCADE;
-NOTICE:  drop cascades to 20 other objects
+NOTICE:  drop cascades to 21 other objects
 DETAIL:  drop cascades to table collate_test1
 drop cascades to table collate_test_like
 drop cascades to table collate_test2
@@ -795,3 +814,4 @@ drop cascades to collation builtin_c
 drop cascades to collation mycoll2
 drop cascades to table collate_test23
 drop cascades to view collate_on_int
+drop cascades to table regex_idx_test
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 18c47e6e05a..49003af1bb1 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -594,6 +594,18 @@ SELECT x FROM test3cs WHERE x LIKE 'a%';
 SELECT x FROM test3cs WHERE x ILIKE 'a%';
 SELECT x FROM test3cs WHERE x SIMILAR TO 'a%';
 SELECT x FROM test3cs WHERE x ~ 'a';
+-- An exact-match regex is converted to an "=" indexqual.  We can use an index
+-- whose collation differs from the expression's as long as the expression's
+-- collation is deterministic, even when the index's collation is
+-- nondeterministic.  Check that we get an index scan rather than a seqscan.
+CREATE INDEX test3cs_ci ON test3cs (x COLLATE case_insensitive);
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+EXPLAIN (costs off)
+SELECT x FROM test3cs WHERE x ~ '^(abc)$';
+RESET enable_seqscan;
+RESET enable_bitmapscan;
+DROP INDEX test3cs_ci;
 SET enable_hashagg TO off;
 SELECT x FROM test1cs UNION SELECT x FROM test2cs ORDER BY x;
 SELECT x FROM test2cs UNION SELECT x FROM test1cs ORDER BY x;
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sql
index 4b0e4472c3f..68345068430 100644
--- a/src/test/regress/sql/collate.sql
+++ b/src/test/regress/sql/collate.sql
@@ -302,6 +302,19 @@ CREATE COLLATION coll_dup_chk (LC_CTYPE = "POSIX", LOCALE = '');
 -- FROM conflicts with any other option
 CREATE COLLATION coll_dup_chk (FROM = "C", VERSION = "1");
 
+SET enable_bitmapscan = off;
+RESET enable_bitmapscan;

base-commit: e0ff7fd9aa2e6f77c38825e71200ced742220d55
-- 
2.54.0

---

From: Tom Lane <[email-masked]>
Date: Mon, 06 Jul 2026 13:16:21 -0400

> filter out the conflicting cases (because it's marked as lossy).

Hmm.  Okay, but the comment had better make it explicit that we're
relying on that superset assumption.

> Attached is v3, which adds a test for this case and expands the comment.

I fooled with the comments and test cases some more and pushed it.
(I don't like test cases that create one-off tables, especially not
when there are existing tables that will serve the purpose just as
well.  The regression tests are slow enough already.)

> complicated than I expected. And that's definitely not something to backport.

Yeah, that was my immediate reaction to your message.  I agree it's
only material for HEAD, but here's a draft patch to do that.

			regards, tom lane

diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c
index 4c8db9147ee..d12a3e833d2 100644
--- a/src/backend/utils/adt/like_support.c
+++ b/src/backend/utils/adt/like_support.c
@@ -66,7 +66,10 @@ typedef enum
 
 typedef enum
 {
-	Pattern_Prefix_None, Pattern_Prefix_Partial, Pattern_Prefix_Exact,
+	Pattern_Prefix_None,		/* cannot produce an indexqual */
+	Pattern_Prefix_Partial,		/* maps to an index range restriction */
+	Pattern_Prefix_Exact,		/* maps to a simple equality test */
+	Pattern_Prefix_Exact_Lossy, /* maps to an equality test, but recheck */
 } Pattern_Prefix_Status;
 
 /* non-collatable comparisons, eg for bytea, are always deterministic */
@@ -79,7 +82,8 @@ static List *match_pattern_prefix(Node *leftop,
 								  Pattern_Type ptype,
 								  Oid expr_coll,
 								  Oid opfamily,
-								  Oid indexcollation);
+								  Oid indexcollation,
+								  bool *lossy);
 static double patternsel_common(PlannerInfo *root,
 								Oid oprid,
 								Oid opfuncid,
@@ -215,7 +219,8 @@ like_regex_support(Node *rawreq, Pattern_Type ptype)
 									 ptype,
 									 clause->inputcollid,
 									 req->opfamily,
-									 req->indexcollation);
+									 req->indexcollation,
+									 &req->lossy);
 		}
 		else if (is_funcclause(req->node))	/* be paranoid */
 		{
@@ -228,7 +233,8 @@ like_regex_support(Node *rawreq, Pattern_Type ptype)
 									 ptype,
 									 clause->inputcollid,
 									 req->opfamily,
-									 req->indexcollation);
+									 req->indexcollation,
+									 &req->lossy);
 		}
 	}
 
@@ -238,6 +244,10 @@ like_regex_support(Node *rawreq, Pattern_Type ptype)
 /*
  * match_pattern_prefix
  *	  Try to generate an indexqual for a LIKE or regex operator.
+ *
+ * We return a list of indexqual expressions on success, or NIL on failure.
+ * On success, *lossy is changed to false (from its initial state of true)
+ * if the indexqual expression(s) can fully replace the operator.
  */
 static List *
 match_pattern_prefix(Node *leftop,
@@ -245,7 +255,8 @@ match_pattern_prefix(Node *leftop,
 					 Pattern_Type ptype,
 					 Oid expr_coll,
 					 Oid opfamily,
-					 Oid indexcollation)
+					 Oid indexcollation,
+					 bool *lossy)
 {
 	List	   *result;
 	Const	   *patt;
@@ -394,9 +405,12 @@ match_pattern_prefix(Node *leftop,
 	 * nondeterministic collation will return a superset of the bitwise-equal
 	 * entries.  Since the "=" indexqual is marked as lossy by default, we'll
 	 * apply the LIKE/regex operator as a recheck, and that will filter out
-	 * any non-matching entries.
+	 * any non-matching entries.  Furthermore, we can get away without a
+	 * recheck if the expression is LIKE (but not regex) and the index
+	 * collation is also deterministic or matches the expression's.
 	 */
-	if (pstatus == Pattern_Prefix_Exact)
+	if (pstatus == Pattern_Prefix_Exact ||
+		pstatus == Pattern_Prefix_Exact_Lossy)
 	{
 		if (!op_in_opfamily(eqopr, opfamily))
 			return NIL;
@@ -405,14 +419,17 @@ match_pattern_prefix(Node *leftop,
 		expr = make_opclause(eqopr, BOOLOID, false,
 							 (Expr *) leftop, (Expr *) prefix,
 							 InvalidOid, indexcollation);
+		if (pstatus == Pattern_Prefix_Exact &&
+			(indexcollation == expr_coll || !NONDETERMINISTIC(indexcollation)))
+			*lossy = false;
 		result = list_make1(expr);
 		return result;
 	}
 
 	/*
-	 * Anything other than Pattern_Prefix_Exact is not supported if the
-	 * expression collation is nondeterministic.  The optimized equality or
-	 * prefix tests use bytewise comparisons, which is not consistent with
+	 * Anything other than Pattern_Prefix_Exact[_Lossy] is not supported if
+	 * the expression collation is nondeterministic.  The optimized equality
+	 * or prefix tests use bytewise comparisons, which is not consistent with
 	 * nondeterministic collations.
 	 */
 	if (NONDETERMINISTIC(expr_coll))
@@ -645,7 +662,8 @@ patternsel_common(PlannerInfo *root,
 		prefix->consttype = rdatatype;
 	}
 
-	if (pstatus == Pattern_Prefix_Exact)
+	if (pstatus == Pattern_Prefix_Exact ||
+		pstatus == Pattern_Prefix_Exact_Lossy)
 	{
 		/*
 		 * Pattern specifies an exact match, so estimate as for '='
@@ -985,7 +1003,7 @@ icnlikejoinsel(PG_FUNCTION_ARGS)
 /*
  * Extract the fixed prefix, if any, for a pattern.
  *
- * *prefix is set to a palloc'd prefix string (in the form of a Const node),
+ * *prefix_const is set to a palloc'd string (in the form of a Const node),
  *	or to NULL if no fixed prefix exists for the pattern.
  * If rest_selec is not NULL, *rest_selec is set to an estimate of the
  *	selectivity of the remainder of the pattern (without any fixed prefix).
@@ -1005,6 +1023,7 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,
 	Oid			typeid = patt_const->consttype;
 	int			pos,
 				match_pos;
+	bool		badpattern = false;
 
 	/* the right-hand const is type text or bytea */
 	Assert(typeid == BYTEAOID || typeid == TEXTOID);
@@ -1038,7 +1057,10 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,
 		{
 			pos++;
 			if (pos >= pattlen)
+			{
+				badpattern = true;
 				break;
+			}
 		}
 
 		match[match_pos++] = patt[pos];
@@ -1059,7 +1081,16 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,
 
 	/* in LIKE, an empty pattern is an exact match! */
 	if (pos == pattlen)
-		return Pattern_Prefix_Exact;	/* reached end of pattern, so exact */
+	{
+		/*
+		 * Reached end of pattern, so exact -- unless the operator would throw
+		 * an error at runtime.  In that case our representation is lossy,
+		 * since we don't want to throw that error here.
+		 */
+		if (badpattern)
+			return Pattern_Prefix_Exact_Lossy;
+		return Pattern_Prefix_Exact;
+	}
 
 	if (match_pos > 0)
 		return Pattern_Prefix_Partial;
@@ -1086,6 +1117,7 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,
 	char	   *match;
 	int			match_mblen;
 	pg_locale_t locale = 0;
+	bool		badpattern = false;
 
 	/* the right-hand const is type text or bytea */
 	Assert(typeid == BYTEAOID || typeid == TEXTOID);
@@ -1125,7 +1157,10 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,
 		{
 			wpos++;
 			if (wpos >= wpattlen)
+			{
+				badpattern = true;
 				break;
+			}
 		}
 
 		/*
@@ -1165,7 +1200,16 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,
 
 	/* in LIKE, an empty pattern is an exact match! */
 	if (wpos == wpattlen)
-		return Pattern_Prefix_Exact;	/* reached end of pattern, so exact */
+	{
+		/*
+		 * Reached end of pattern, so exact -- unless the operator would throw
+		 * an error at runtime.  In that case our representation is lossy,
+		 * since we don't want to throw that error here.
+		 */
+		if (badpattern)
+			return Pattern_Prefix_Exact_Lossy;
+		return Pattern_Prefix_Exact;
+	}
 
 	if (wmatch_pos > 0)
 		return Pattern_Prefix_Partial;
@@ -1235,8 +1279,13 @@ regex_fixed_prefix(Const *patt_const, bool case_insensitive, Oid collation,
 
 	pfree(prefix);
 
+	/*
+	 * Because regexp_fixed_prefix() doesn't analyze the regexp fully, even an
+	 * "exact" match is lossy: we know that a match must be this string, but
+	 * it could still fail to match.
+	 */
 	if (exact)
-		return Pattern_Prefix_Exact;	/* pattern specifies exact match */
+		return Pattern_Prefix_Exact_Lossy;
 	else
 		return Pattern_Prefix_Partial;
 }
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index fb95eee9b7c..1574433d5ef 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2098,12 +2098,11 @@ SELECT * FROM test1ci WHERE x ~ '^abc$' COLLATE "C";
 
 EXPLAIN (COSTS OFF)
 SELECT * FROM test1ci WHERE x LIKE 'abc' COLLATE case_insensitive;
-                      QUERY PLAN                       
--------------------------------------------------------
+                QUERY PLAN                 
+-------------------------------------------
  Index Scan using test1ci_x_idx on test1ci
    Index Cond: (x = 'abc'::text)
-   Filter: (x ~~ 'abc'::text COLLATE case_insensitive)
-(3 rows)
+(2 rows)
 
 RESET enable_seqscan;
 RESET enable_indexonlyscan;
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index b17c5abaddc..da301358ada 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -784,12 +784,11 @@ SELECT * FROM pg_class WHERE relname ~ '^pg_class$' COLLATE "POSIX";
 
 EXPLAIN (COSTS OFF)
 SELECT * FROM pg_class WHERE relname LIKE 'pg\_class' COLLATE "POSIX";
-                        QUERY PLAN                        
-----------------------------------------------------------
+                       QUERY PLAN                        
+---------------------------------------------------------
  Index Scan using pg_class_relname_nsp_index on pg_class
    Index Cond: (relname = 'pg_class'::text)
-   Filter: (relname ~~ 'pg\_class'::text COLLATE "POSIX")
-(3 rows)
+(2 rows)

---

From: "Jelte Fennema-Nio" <[email-masked]>
Date: Mon, 06 Jul 2026 23:35:56 +0200

> only material for HEAD, but here's a draft patch to do that.

I found two other edge cases worth considering for this. Which is why I
didn't pursue it further, because I assumed with those two existing
there were probably some more that I missed (and indeed I had missed
that regexes should always be lossy, and there might be more still):

1. bpchar columns should be lossy because LIKE and = behave differently
   for padded values.
2. When the index has a collation, but no collation can be detected for
   the expression, then we currently throw an error during the recheck.
   It seems strange to me to throw that error during execution instead
   of planning, but it seems even weirder that removing the recheck
   would make the query succeed.

Attached a v2 draft patch that adds some tests for those cases and
checks to handle them (and some comments by an LLM that I haven't
cleaned up much). I think issue 2 might need a different approach
altogether. 

To be clear, I don't plan to pursue this further right now, just wanted
to share the details of my investigation in case you want to take it
further.
From d3b72b2b4b6a69545cfe33725c5c58063cfb85fc Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 6 Jul 2026 22:08:02 +0200
Subject: [PATCH v2] Treat exact-match LIKE pattern as non-lossy in certain
 cases

---
 src/backend/utils/adt/like_support.c          | 109 +++++++++++++++---
 .../regress/expected/collate.icu.utf8.out     |  31 ++++-
 src/test/regress/expected/collate.out         |  31 ++++-
 src/test/regress/sql/collate.icu.utf8.sql     |  12 ++
 src/test/regress/sql/collate.sql              |  18 +++
 5 files changed, 180 insertions(+), 21 deletions(-)

index 4c8db9147ee..c396b4f4724 100644
 
 typedef enum
 {

@@ -238,6 +244,10 @@ like_regex_support(Node *rawreq, Pattern_Type ptype)
 /*
  * match_pattern_prefix
  *	  Try to generate an indexqual for a LIKE or regex operator.
+ *
+ * We return a list of indexqual expressions on success, or NIL on failure.
+ * On success, *lossy is changed to false (from its initial state of true)
+ * if the indexqual expression(s) can fully replace the operator.
  */
 static List *
 match_pattern_prefix(Node *leftop,
@@ -245,7 +255,8 @@ match_pattern_prefix(Node *leftop,
 					 Pattern_Type ptype,
 					 Oid expr_coll,
 					 Oid opfamily,
-					 Oid indexcollation)
+					 Oid indexcollation,
+					 bool *lossy)
 {
 	List	   *result;
 	Const	   *patt;
@@ -396,7 +407,8 @@ match_pattern_prefix(Node *leftop,
 	 * apply the LIKE/regex operator as a recheck, and that will filter out
 	 * any non-matching entries.
 	 */
-	if (pstatus == Pattern_Prefix_Exact)
+	if (pstatus == Pattern_Prefix_Exact ||
+		pstatus == Pattern_Prefix_Exact_Lossy)
 	{
 		if (!op_in_opfamily(eqopr, opfamily))
 			return NIL;
@@ -405,14 +417,51 @@ match_pattern_prefix(Node *leftop,
 		expr = make_opclause(eqopr, BOOLOID, false,
 							 (Expr *) leftop, (Expr *) prefix,
 							 InvalidOid, indexcollation);
+
+		/*
+		 * We can even drop the recheck (mark the indexqual non-lossy) when
+		 * "=" means exactly what the exact-match pattern means.  That
+		 * requires all of the following:
+		 *
+		 * - The pattern is Pattern_Prefix_Exact.
+		 *
+		 * - The left-hand type is not bpchar.  bpchar's "=" ignores trailing
+		 * blanks while LIKE does not, so "= 'abc'" matches a stored 'abc  '
+		 * that "LIKE 'abc'" rejects; the recheck is what removes it.
+		 *
+		 * - The index and expression collations agree on equality.  That
+		 * holds when they're the same collation, or (having passed the guard
+		 * above, which rejected a nondeterministic expression collation that
+		 * differs from the index's) when the index collation is
+		 * deterministic: a deterministic collation treats two strings as
+		 * equal iff they're bytewise equal, so it can't disagree with the
+		 * expression's equality. A nondeterministic index collation that
+		 * differs from the expression's makes "=" match a superset of the
+		 * pattern, so the recheck stays.
+		 *
+		 * - The expression actually has a collation whenever the index does.
+		 * A collatable expression with an indeterminate collation (e.g. a
+		 * concatenation of differently-collated columns) carries InvalidOid,
+		 * and the pattern match is supposed to fail at runtime with "could
+		 * not determine which collation to use".  Dropping the recheck would
+		 * instead let the "=" indexqual silently resolve the comparison under
+		 * the index's collation.  A genuinely non-collatable type (bytea)
+		 * also carries InvalidOid, but then the index collation is InvalidOid
+		 * too, so indexcollation == expr_coll and we still optimize.
+		 */
+		if (pstatus == Pattern_Prefix_Exact &&
+			ldatatype != BPCHAROID &&
+			(indexcollation == expr_coll ||
+			 (OidIsValid(expr_coll) && !NONDETERMINISTIC(indexcollation))))
+			*lossy = false;
 		result = list_make1(expr);
 		return result;
 	}
 
 	/*
@@ -645,7 +694,8 @@ patternsel_common(PlannerInfo *root,
 
@@ -985,7 +1035,7 @@ icnlikejoinsel(PG_FUNCTION_ARGS)
@@ -1005,6 +1055,7 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,
 
 	/* the right-hand const is type text or bytea */
 	Assert(typeid == BYTEAOID || typeid == TEXTOID);
@@ -1038,7 +1089,10 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,
 		{
 			pos++;
 			if (pos >= pattlen)
+			{
+				badpattern = true;
 				break;
+			}
 		}
 
 		match[match_pos++] = patt[pos];
@@ -1059,7 +1113,16 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,

 	if (match_pos > 0)
 		return Pattern_Prefix_Partial;
@@ -1086,6 +1149,7 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,
 	char	   *match;
 	int			match_mblen;
 	pg_locale_t locale = 0;
+	bool		badpattern = false;
 
 	/* the right-hand const is type text or bytea */
 	Assert(typeid == BYTEAOID || typeid == TEXTOID);
@@ -1125,7 +1189,10 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,
 		{
 			wpos++;
 			if (wpos >= wpattlen)
+			{
+				badpattern = true;
 				break;
+			}
 		}
 
 		/*
@@ -1165,7 +1232,16 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,

 	if (wmatch_pos > 0)
 		return Pattern_Prefix_Partial;
@@ -1235,8 +1311,13 @@ regex_fixed_prefix(Const *patt_const, bool case_insensitive, Oid collation,
 
 	pfree(prefix);
 
+	/*
index fcfcc658bea..3b5232df5de 100644
@@ -2128,12 +2128,11 @@ SELECT * FROM test1ci WHERE x ~ '^abc$' COLLATE "C";
 
 EXPLAIN (COSTS OFF)
 
 RESET enable_seqscan;
 RESET enable_indexonlyscan;
@@ -2799,6 +2798,30 @@ SELECT x FROM test4c WHERE x LIKE 'ABC%' COLLATE case_insensitive;  -- ok
  abc
 (1 row)
 
+RESET enable_seqscan;
+-- For text (test4c above) an exact-match LIKE with the index and expression
+-- sharing the collation is non-lossy, so the recheck is dropped.  bpchar is
+-- different: its "=" ignores trailing blanks while LIKE does not, so the
+-- recheck must stay to reject a padded 'abc  ' that "=" would treat as equal
+-- to 'abc'.  The Index Cond therefore still carries a "~~" Filter here.
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF)
+SELECT x FROM test4c WHERE x LIKE 'abc' COLLATE case_insensitive;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Only Scan using test4c_x_idx on test4c
+   Index Cond: (x = 'abc'::text)
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT x FROM test1bpci WHERE x LIKE 'abc' COLLATE case_insensitive;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Index Only Scan using test1bpci_x_idx on test1bpci
+   Index Cond: (x = 'abc'::bpchar)
+   Filter: (x ~~ 'abc'::text COLLATE case_insensitive)
+(3 rows)
+
 RESET enable_seqscan;
 -- Unicode special case: different variants of Greek lower case sigma.
 -- A naive implementation like citext that just does lower(x) =
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index b17c5abaddc..77616fd297b 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -784,13 +784,38 @@ SELECT * FROM pg_class WHERE relname ~ '^pg_class$' COLLATE "POSIX";
 
 EXPLAIN (COSTS OFF)
 SELECT * FROM pg_class WHERE relname LIKE 'pg\_class' COLLATE "POSIX";
-                        QUERY PLAN                        
-----------------------------------------------------------
+                       QUERY PLAN                        
+---------------------------------------------------------
  Index Scan using pg_class_relname_nsp_index on pg_class
    Index Cond: (relname = 'pg_class'::text)
-   Filter: (relname ~~ 'pg\_class'::text COLLATE "POSIX")
+(2 rows)
+
+-- Conversely, when the expression's collation is indeterminate (here a
+-- concatenation of a "C" and a "POSIX" column) but the index pins a collation,
+-- the exact-match "=" indexqual must stay lossy so the LIKE recheck still runs.
+-- Otherwise the query would silently resolve the comparison under the index's
+-- collation instead of failing with "could not determine which collation to
+-- use", making the result depend on whether an index scan is chosen.
+CREATE INDEX collate_test10_expr_idx ON collate_test10 ((x || y) COLLATE "C");
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+EXPLAIN (COSTS OFF)
+SELECT a FROM collate_test10 WHERE (x || y) LIKE 'hijhij';
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Index Scan using collate_test10_expr_idx on collate_test10
+   Index Cond: ((x || y) = 'hijhij'::text)
+   Filter: ((x || y) ~~ 'hijhij'::text)
 (3 rows)
 
+-- must error (rather than silently return a row via the index) so that the
+-- outcome does not depend on the chosen plan
+SELECT a FROM collate_test10 WHERE (x || y) LIKE 'hijhij';
+ERROR:  could not determine which collation to use for LIKE
+HINT:  Use the COLLATE clause to set the collation explicitly.
+RESET enable_seqscan;
+RESET enable_bitmapscan;
+DROP INDEX collate_test10_expr_idx;
 --
 -- Clean up.  Many of these table names will be re-used if the user is
 -- trying to run any platform-specific collation tests later, so we
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index ce4e2bb3ffd..2660b1cb42d 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -999,6 +999,18 @@ SELECT x FROM test4c WHERE x LIKE 'ABC' COLLATE case_insensitive;  -- ok
 SELECT x FROM test4c WHERE x LIKE 'ABC%' COLLATE case_insensitive;  -- ok
 RESET enable_seqscan;
 
+-- For text (test4c above) an exact-match LIKE with the index and expression
+-- sharing the collation is non-lossy, so the recheck is dropped.  bpchar is
+-- different: its "=" ignores trailing blanks while LIKE does not, so the
+-- recheck must stay to reject a padded 'abc  ' that "=" would treat as equal
+-- to 'abc'.  The Index Cond therefore still carries a "~~" Filter here.
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF)
+SELECT x FROM test4c WHERE x LIKE 'abc' COLLATE case_insensitive;
+EXPLAIN (COSTS OFF)
+SELECT x FROM test1bpci WHERE x LIKE 'abc' COLLATE case_insensitive;
+RESET enable_seqscan;
+
 -- Unicode special case: different variants of Greek lower case sigma.
 -- A naive implementation like citext that just does lower(x) =
 -- lower(y) will do the wrong thing here, because lower('Σ') is 'σ'
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sql
index b018da13f24..791e6af0a71 100644
--- a/src/test/regress/sql/collate.sql
+++ b/src/test/regress/sql/collate.sql
@@ -312,6 +312,24 @@ SELECT * FROM pg_class WHERE relname ~ '^pg_class$' COLLATE "POSIX";
 EXPLAIN (COSTS OFF)
 SELECT * FROM pg_class WHERE relname LIKE 'pg\_class' COLLATE "POSIX";
 
+-- Conversely, when the expression's collation is indeterminate (here a
+-- concatenation of a "C" and a "POSIX" column) but the index pins a collation,
+-- the exact-match "=" indexqual must stay lossy so the LIKE recheck still runs.
+-- Otherwise the query would silently resolve the comparison under the index's
+-- collation instead of failing with "could not determine which collation to
+-- use", making the result depend on whether an index scan is chosen.
+CREATE INDEX collate_test10_expr_idx ON collate_test10 ((x || y) COLLATE "C");
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+EXPLAIN (COSTS OFF)
+SELECT a FROM collate_test10 WHERE (x || y) LIKE 'hijhij';
+-- must error (rather than silently return a row via the index) so that the
+-- outcome does not depend on the chosen plan
+SELECT a FROM collate_test10 WHERE (x || y) LIKE 'hijhij';
+RESET enable_seqscan;
+RESET enable_bitmapscan;
+DROP INDEX collate_test10_expr_idx;
+
 --
 -- Clean up.  Many of these table names will be re-used if the user is
 -- trying to run any platform-specific collation tests later, so we

base-commit: 4c84545067822bcc8697b7d8f3082c5cf1937d1b
-- 
2.54.0