"Jelte Fennema-Nio" <[email protected]> writes:
> On Sat, 4 Jul 2026 at 22:27, Tom Lane <[email protected]> wrote:
>> 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?
> 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).
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.)
> 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.
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)
--
-- Clean up. Many of these table names will be re-used if the user is
