MIME-Version: 1.0
References: <CA+jeBrejfchM60epUo1J3M8mMAQOcCWotpDP9QZYgEL=j0b=pg@mail.gmail.com>
 <3287814.1767077500@sss.pgh.pa.us>
In-Reply-To: <3287814.1767077500@sss.pgh.pa.us>
From: Rahman Duran <a.rahman.duran@gmail.com>
Date: Tue, 30 Dec 2025 10:07:22 +0300
Message-ID: <CA+jeBreFqkGc9f=_cBcMWkw_E5tTU_Rw4FQi6ehCDrTR17anUw@mail.gmail.com>
Subject: Re: PostgreSQL 18.1 non deterministic collation "LIKE %abc%" performance
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000005c2f61064726049a"
Archived-At: <https://www.postgresql.org/message-id/CA%2BjeBreFqkGc9f%3D_cBcMWkw_E5tTU_Rw4FQi6ehCDrTR17anUw%40mail.gmail.com>
Precedence: bulk

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

Tom Lane <tgl@sss.pgh.pa.us>, 30 Ara 2025 Sal, 09:51 tarihinde =C5=9Funu ya=
zd=C4=B1:
>The fundamental problem here is not wanting to make assumptions about
>which character strings a non-deterministic collation will consider
>equal to which other character strings.  If you have concrete ideas
>about how to improve that, let's hear them.

Thanks for the explanations. I am just a user, not a DB developer :) So no
ideas I am afraid. I was just surprised that running the lower() function
on every column of every row is way faster than using nondeterministic
collation. I will test GIN index with trigram to see if it helps. If not I
will leave the code as is with lower() functions.

Regards,
Rahman Duran

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Tom Lane &lt;<a href=3D"mailto:tgl@s=
ss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;, 30 Ara 2025 Sal, 09:51 tarihinde =
=C5=9Funu yazd=C4=B1:</div><div>&gt;The fundamental problem here is not wan=
ting to make assumptions about<br>&gt;which character strings a non-determi=
nistic collation will consider<br>&gt;equal to which other character string=
s.=C2=A0 If you have concrete ideas<br>&gt;about how to improve that, let&#=
39;s hear them.</div><div><br></div><div>Thanks for the explanations. I am =
just a user, not a DB developer :) So no ideas I am afraid. I was just surp=
rised=C2=A0that running the lower() function on every column of every row i=
s way faster than using nondeterministic collation. I will test GIN index w=
ith trigram to see if it helps. If not I will leave the code as is with low=
er() functions.</div><div><br></div><div>Regards,</div><div><div dir=3D"ltr=
" class=3D"gmail_signature"><div dir=3D"ltr">Rahman Duran<br></div><div dir=
=3D"ltr"><br></div></div></div></div></div>

--0000000000005c2f61064726049a--