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).
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 protected]> 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. 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 protected]> Author: Jelte Fennema-Nio <[email protected]> Reviewed-by: Tom Lane <[email protected]> Discussion: https://postgr.es/m/[email protected] 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(-) diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c index 01cd6b10730..960e118307b 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,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"); +-- 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"; +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 base-commit: e0ff7fd9aa2e6f77c38825e71200ced742220d55 -- 2.54.0
