postgres.git / summary / log / commit / refs
commit 67cf73ddbe3334ee1fecc29ffb6d4cd2e10ab9ac
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon Jul 06 17:06:21 2026 +0000
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
expression collation. That should be allowed if the expression
collation is 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 <andres@anarazel.de>
Author: Jelte Fennema-Nio <postgres@jeltef.nl>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/DHBQIZX8SZVI.ZX614ZMFL645@jeltef.nl
Backpatch-through: 18
src/backend/utils/adt/like_support.c | 20 ++++++++++++++++----
src/test/regress/expected/collate.icu.utf8.out | 23 +++++++++++++++++++++++
src/test/regress/expected/collate.out | 23 +++++++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 10 ++++++++++
src/test/regress/sql/collate.sql | 10 ++++++++++
5 files changed, 82 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c
index 01cd6b10730..4c8db9147ee 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,22 @@ 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.
+ *
+ * This case will work for LIKE/regex expressions with nondeterministic
+ * collation, so long as the index's collation is the same. If the
+ * expression's collation is deterministic, we can even use an index whose
+ * collation differs from the expression's. All deterministic collations
+ * agree on equality (it's bitwise), while we assume that an index with
+ * 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.
*/
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 +414,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 cf2c55ba92e..fb95eee9b7c 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2084,6 +2084,29 @@ SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
{A,NULL,C,D,E,F,G,H,I}
(1 row)
+-- These queries should be able to use the index on test1ci.x:
+SET enable_seqscan = off;
+SET enable_indexonlyscan = off;
+EXPLAIN (COSTS OFF)
+SELECT * FROM test1ci WHERE x ~ '^abc$' COLLATE "C";
+ QUERY PLAN
+-------------------------------------------
+ Index Scan using test1ci_x_idx on test1ci
+ Index Cond: (x = 'abc'::text)
+ Filter: (x ~ '^abc$'::text COLLATE "C")
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM test1ci WHERE x LIKE 'abc' COLLATE case_insensitive;
+ QUERY PLAN
+-------------------------------------------------------
+ Index Scan using test1ci_x_idx on test1ci
+ Index Cond: (x = 'abc'::text)
+ Filter: (x ~~ 'abc'::text COLLATE case_insensitive)
+(3 rows)
+
+RESET enable_seqscan;
+RESET enable_indexonlyscan;
-- Test HAVING-to-WHERE pushdown with nondeterministic collations.
-- When a HAVING clause uses a different collation than the GROUP BY's
-- nondeterministic collation, it must not be pushed to WHERE, otherwise
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index 25818f09ad2..b17c5abaddc 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -768,6 +768,29 @@ 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 an index even when the expression
+-- and index have different collations, so long as the expression's collation
+-- is deterministic. This example tests what we want because the optimizer
+-- does not perceive "C" collation (used by the system catalogs) as identical
+-- to "POSIX" collation.
+EXPLAIN (COSTS OFF)
+SELECT * FROM pg_class WHERE relname ~ '^pg_class$' COLLATE "POSIX";
+ 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)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pg_class WHERE relname LIKE 'pg\_class' COLLATE "POSIX";
+ 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)
+
--
-- 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 a1f10708c96..954d8ea6cb4 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -745,6 +745,16 @@ CREATE UNIQUE INDEX ON test3ci (x); -- error
SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc');
SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
+-- These queries should be able to use the index on test1ci.x:
+SET enable_seqscan = off;
+SET enable_indexonlyscan = off;
+EXPLAIN (COSTS OFF)
+SELECT * FROM test1ci WHERE x ~ '^abc$' COLLATE "C";
+EXPLAIN (COSTS OFF)
+SELECT * FROM test1ci WHERE x LIKE 'abc' COLLATE case_insensitive;
+RESET enable_seqscan;
+RESET enable_indexonlyscan;
+
-- Test HAVING-to-WHERE pushdown with nondeterministic collations.
-- When a HAVING clause uses a different collation than the GROUP BY's
-- nondeterministic collation, it must not be pushed to WHERE, otherwise
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sql
index 4b0e4472c3f..b018da13f24 100644
--- a/src/test/regress/sql/collate.sql
+++ b/src/test/regress/sql/collate.sql
@@ -302,6 +302,16 @@ 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 an index even when the expression
+-- and index have different collations, so long as the expression's collation
+-- is deterministic. This example tests what we want because the optimizer
+-- does not perceive "C" collation (used by the system catalogs) as identical
+-- to "POSIX" collation.
+EXPLAIN (COSTS OFF)
+SELECT * FROM pg_class WHERE relname ~ '^pg_class$' COLLATE "POSIX";
+EXPLAIN (COSTS OFF)
+SELECT * FROM pg_class WHERE relname LIKE 'pg\_class' COLLATE "POSIX";
+
--
-- 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
[parent: 2ddc4566214d]