agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Fix LIKE/regex optimization for indexscan with exact-match patte 3+ messages / 1 participants [nested] [flat]
* pgsql: Fix LIKE/regex optimization for indexscan with exact-match patte @ 2026-07-06 17:06 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2026-07-06 17:06 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/2d7808e6fc2cb5d7a964e7a24801a6fa133a3261 Modified Files -------------- 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(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix LIKE/regex optimization for indexscan with exact-match patte @ 2026-07-06 17:06 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2026-07-06 17:06 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/67cf73ddbe3334ee1fecc29ffb6d4cd2e10ab9ac Modified Files -------------- 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(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix LIKE/regex optimization for indexscan with exact-match patte @ 2026-07-06 17:06 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2026-07-06 17:06 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/d0bb49e61168953f0f07c3beeb2b8fb63a3521a2 Modified Files -------------- 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(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-07-06 17:06 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-07-06 17:06 pgsql: Fix LIKE/regex optimization for indexscan with exact-match patte Tom Lane <tgl@sss.pgh.pa.us> 2026-07-06 17:06 pgsql: Fix LIKE/regex optimization for indexscan with exact-match patte Tom Lane <tgl@sss.pgh.pa.us> 2026-07-06 17:06 pgsql: Fix LIKE/regex optimization for indexscan with exact-match patte Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox