(forked from: Test timings are increasing too fast for cfbot)
On Wed, 25 Mar 2026 at 04:15, Andres Freund <[email protected]> wrote:
It seems decidedly not optimal that "\d tablename", without any patterns, ends up doing a seqscan. That's bad enough in the regression database, but there are many PG instances with many many entries in pg_class. I don't think this was always the case? If I remove the COLLATE pg_catalog.default, a sane plan is chosen. That's obviously not the right fix, but seemed interesting enough to mention.
Due to a very similar problem I faced in the past[1], I thought I had a good sense of where roughly the problem was. And I indeed quickly found it. Attached is a patch that addresses this issue and starts using index scans again for \d tablename. This should be backpatched to PG18 where the regression was introduced by 85b7efa1cdd [1]: https://www.postgresql.org/message-id/flat/CAGECzQRqysy0eJMKR5he3gwtLrT87f9u5CQQua6B_XNwMnUtFA%40mail.gmail.com
From 0ab6bef30ab0b19e4704328624cb926d09a07876 Mon Sep 17 00:00:00 2001 From: Jelte Fennema-Nio <[email protected]> Date: Wed, 25 Mar 2026 09:29:05 +0100 Subject: [PATCH v1] Fix LIKE optimization for prefix scan with deterministic collations In 85b7efa1cdd support was introduced for LIKE on non-deterministic collations. By moving some conditionals around, that accidentally broke the LIKE optimization for deterministic collations when the index collation did not match the filter collation. This re-introduces the optimization. Important benefactors of this optimization are the "\d tablename" and "\d tablename*" commands in psql. Without this optimization they do a sequence scan instead of an index lookup/prefix scan. Discussion: https://postgr.es/m/mtkrkkcn2tlhytumitpch5ubxiprv2jzvprf5r5m3mjeczvq4q@p6wkzbfxuyv2 Backpatch-through: 18 --- src/backend/utils/adt/like_support.c | 3 ++- src/test/regress/expected/collate.out | 18 ++++++++++++++++++ src/test/regress/sql/collate.sql | 11 +++++++++++ 3 files changed, 31 insertions(+), 1 deletion(-) diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c index 01cd6b10730..dd6524fcec4 100644 --- a/src/backend/utils/adt/like_support.c +++ b/src/backend/utils/adt/like_support.c @@ -386,7 +386,8 @@ match_pattern_prefix(Node *leftop, { if (!op_in_opfamily(eqopr, opfamily)) return NIL; - if (indexcollation != expr_coll) + if (indexcollation != expr_coll && + expr_coll && !get_collation_isdeterministic(expr_coll)) return NIL; expr = make_opclause(eqopr, BOOLOID, false, (Expr *) leftop, (Expr *) prefix, diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out index 25818f09ad2..c4cf7cfc644 100644 --- a/src/test/regress/expected/collate.out +++ b/src/test/regress/expected/collate.out @@ -768,6 +768,24 @@ 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 collate_tests.regex_idx_test (x text); +CREATE INDEX ON collate_tests.regex_idx_test (x COLLATE "C"); +SET enable_seqscan = off; +EXPLAIN (costs off) SELECT * FROM collate_tests.regex_idx_test WHERE x ~ '^(abc)$' COLLATE "default"; + QUERY PLAN +------------------------------------------------- + Bitmap Heap Scan on regex_idx_test + Filter: (x ~ '^(abc)$'::text) + -> Bitmap Index Scan on regex_idx_test_x_idx + Index Cond: (x = 'abc'::text) +(4 rows) + +RESET enable_seqscan; +DROP TABLE collate_tests.regex_idx_test; -- -- 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.sql b/src/test/regress/sql/collate.sql index 4b0e4472c3f..4f4f607c70e 100644 --- a/src/test/regress/sql/collate.sql +++ b/src/test/regress/sql/collate.sql @@ -302,6 +302,17 @@ 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 collate_tests.regex_idx_test (x text); +CREATE INDEX ON collate_tests.regex_idx_test (x COLLATE "C"); +SET enable_seqscan = off; +EXPLAIN (costs off) SELECT * FROM collate_tests.regex_idx_test WHERE x ~ '^(abc)$' COLLATE "default"; +RESET enable_seqscan; +DROP TABLE collate_tests.regex_idx_test; + -- -- 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: c79e4141273caa1b4fb88c479bb90dc40f2fbbf2 -- 2.53.0
