On Mon Jul 6, 2026 at 7:16 PM CEST, Tom Lane wrote:
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.
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.
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 protected]>
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(-)
diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c
index 4c8db9147ee..c396b4f4724 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;
@@ -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;
}
/*
- * 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 +694,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 +1035,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 +1055,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 +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,
/* 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 +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,
/* 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 +1311,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 fcfcc658bea..3b5232df5de 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2128,12 +2128,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;
@@ -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
