MIME-Version: 1.0
References: <f4aefa40-a25e-413f-a7be-66e4455c0337@manitou-mail.org>
 <0a4f41b84efa5d821aba166658fb845f1ab97f03.camel@j-davis.com>
 <CAHgHdKt+_+QhHK8WXQSoMNeUz43Cp2zGNEVX6=0RSaksA9zyJw@mail.gmail.com>
 <449d14b3cb80e259bbd9d8728ea204b15b6a025a.camel@j-davis.com>
In-Reply-To: <449d14b3cb80e259bbd9d8728ea204b15b6a025a.camel@j-davis.com>
From: Mark Dilger <mark.dilger@enterprisedb.com>
Date: Wed, 25 Mar 2026 07:40:23 -0700
Message-ID: 
 <CAHgHdKtb2jD+DaTJU+3jnQRZ9hEXSDcPCR8DCCzZTTVeo4jQcA@mail.gmail.com>
Subject: Re: Use CASEFOLD() internally rather than LOWER()
To: Jeff Davis <pgsql@j-davis.com>
Cc: Daniel Verite <daniel@manitou-mail.org>, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000128e05064dda402f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHgHdKtb2jD%2BDaTJU%2B3jnQRZ9hEXSDcPCR8DCCzZTTVeo4jQcA%40mail.gmail.com>
Precedence: bulk

--000000000000128e05064dda402f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 24, 2026 at 4:07=E2=80=AFPM Jeff Davis <pgsql@j-davis.com> wrot=
e:

> On Sat, 2026-03-21 at 20:14 -0700, Mark Dilger wrote:
> > After v2-0001, ILIKE uses str_casefold() for matching, but pg_trgm
> > still
> > uses str_tolower() for trigram extraction (trgm_op.c:352 and :948).
> > With builtin collations, these produce different results.
>
> Interesting, thank you. As stated in the original message, I was unsure
> about changing pg_trgm without adjusting the regex logic, also:
>
>
> https://www.postgresql.org/message-id/64d7949bad90545f981ac7513fb0b4954da=
ca2c9.camel@j-davis.com
>
> do you have a suggestion about an easy way to do that, or should we
> revisit in the next cycle?
>

pg_trgm appears to be lossy, with recheck logic.  I would think you just
need to make it give answers which at least include everything that a regex
would match, and then allow recheck to prune that down.  My concern is
having pg_trgm give less than all the answers, so that after recheck you
get fewer results than a seqscan would have returned.  Would switching to
casefold be strictly broader than regex?  If so, you would just need to
convert pg_trgm to use casefold and then rely on the recheck machinery.

Sorry if this misses something discussed upthread.  I'm clearly assuming
here that you don't mind that such a change necessitates a REINDEX.

--=20

*Mark Dilger*

--000000000000128e05064dda402f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Mar 24,=
 2026 at 4:07=E2=80=AFPM Jeff Davis &lt;<a href=3D"mailto:pgsql@j-davis.com=
">pgsql@j-davis.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-styl=
e:solid;border-left-color:rgb(204,204,204);padding-left:1ex">On Sat, 2026-0=
3-21 at 20:14 -0700, Mark Dilger wrote:<br>
&gt; After v2-0001, ILIKE uses str_casefold() for matching, but pg_trgm<br>
&gt; still<br>
&gt; uses str_tolower() for trigram extraction (trgm_op.c:352 and :948).<br=
>
&gt; With builtin collations, these produce different results.<br>
<br>
Interesting, thank you. As stated in the original message, I was unsure<br>
about changing pg_trgm without adjusting the regex logic, also:<br>
<br>
<a href=3D"https://www.postgresql.org/message-id/64d7949bad90545f981ac7513f=
b0b4954daca2c9.camel@j-davis.com" rel=3D"noreferrer" target=3D"_blank">http=
s://www.postgresql.org/message-id/64d7949bad90545f981ac7513fb0b4954daca2c9.=
camel@j-davis.com</a><br>
<br>
do you have a suggestion about an easy way to do that, or should we<br>
revisit in the next cycle?<br></blockquote><div><br></div><div>pg_trgm appe=
ars to be lossy, with recheck logic.=C2=A0 I would think you just need to m=
ake it give answers which at least include everything that a regex would ma=
tch, and then allow recheck to prune that down.=C2=A0 My concern is having =
pg_trgm give less than all the answers, so that after recheck you get fewer=
 results than a seqscan would have returned.=C2=A0 Would switching to casef=
old be strictly broader than regex?=C2=A0 If so, you would just need to con=
vert pg_trgm to use casefold and then rely on the recheck machinery.</div><=
div><br></div><div>Sorry if this misses something discussed upthread.=C2=A0=
 I&#39;m clearly assuming here that you don&#39;t mind that such a change n=
ecessitates a REINDEX.=C2=A0</div></div><div><br></div><span class=3D"gmail=
_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"=
><div dir=3D"ltr"><img src=3D"https://lh7-us.googleusercontent.com/80Q0n8A1=
8y4ur9BMmN9zDAoEOCSfqCbmGOQ2lI1IqwUyarDBFhlxOOMrLytOC5x5Zo8-TD9NOpLXzn1w2sr=
XBDsTderYUy9-mH4Gb1KBetRMWRtZ2Tlsbk7YUDupvd1b1zTB3hZ2AQdJ5rZJ_AKjBPs" width=
=3D"220" height=3D"47.07423580786026" style=3D"color: rgb(0, 0, 0); margin-=
left: 0px;"><br style=3D"color:rgb(0,0,0);font-family:Helvetica;font-size:1=
8px"><div><font size=3D"4" face=3D"garamond, times new roman, serif"><b>Mar=
k Dilger</b></font></div></div></div></div>

--000000000000128e05064dda402f--