Re: Make \d tablename fast again, regression introduced by 85b7efa1cdd - Mailing list pgsql-hackers

"Jelte Fennema-Nio" <postgres@jeltef.nl> writes:
> On Sat, 4 Jul 2026 at 22:27, Tom Lane <tgl@sss.pgh.pa.us> 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