public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jelte Fennema-Nio <[email protected]>
To: Andres Freund <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Noah Misch <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Subject: Make \d tablename fast again, regression introduced by 85b7efa1cdd
Date: Wed, 25 Mar 2026 09:54:00 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <ogohyk54vjyejn3kdibrc73wcrdhwsq57fawxitycen6ntbzo3@gnnwuagu2qqj>
References: <mtkrkkcn2tlhytumitpch5ubxiprv2jzvprf5r5m3mjeczvq4q@p6wkzbfxuyv2>
<ogohyk54vjyejn3kdibrc73wcrdhwsq57fawxitycen6ntbzo3@gnnwuagu2qqj>
(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%40mai...
Attachments:
[text/x-patch] v1-0001-Fix-LIKE-optimization-for-prefix-scan-with-determ.patch (4.2K, 2-v1-0001-Fix-LIKE-optimization-for-prefix-scan-with-determ.patch)
download | inline diff:
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
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Make \d tablename fast again, regression introduced by 85b7efa1cdd
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox