MIME-Version: 1.0
Reply-To: henning.garus@gmail.com
From: Henning Garus <henning.garus@gmail.com>
Date: Tue, 4 Feb 2025 17:23:39 +0100
Message-ID: <CAM_PoRsgiecCESyTTfL2iZJB376yaDX1-pfzi=ofPfVf0_Cfeg@mail.gmail.com>
Subject: Index usage with differing string types
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000251028062d536f1a"
Archived-At: <https://www.postgresql.org/message-id/CAM_PoRsgiecCESyTTfL2iZJB376yaDX1-pfzi%3DofPfVf0_Cfeg%40mail.gmail.com>
Precedence: bulk

--000000000000251028062d536f1a
Content-Type: text/plain; charset="UTF-8"

Hi,

I stumbled upon this behaviour when digging into the performance of some
merge statements generated by hibernate.

Looking at different String types (varchar, text and bpchar) in some cases
an index is used when the index type differs from the type in the query, in
some cases it isn't used.

Given a table with an index on a bpchar column:

create table test (id bpchar(8) primary key);

Both of the following queries use the index:

explain select * from test where id = 'foo'::bpachar(3);
explain select * from test where id = 'foo'::varchar;

However when the String is cast to text the index isn't used:

explain select * from test where id = 'foo'::text;

This behavior seems to be consistent across postgres 12, 16 and 17.

I find it surprising that the cast to varchar behaves differently than the
cast to text, is this intended behaviour?

Cheers
  Henning

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

<div dir=3D"ltr">Hi,<div><br></div><div>I stumbled upon this behaviour when=
 digging into the performance of some merge statements generated by hiberna=
te.</div><div><br></div><div>Looking at different String types (varchar, te=
xt and bpchar) in some cases an index is used when the index type differs f=
rom the type in the query, in some cases it isn&#39;t used.</div><div><br><=
/div><div>Given a table with an index on a bpchar column:</div><div><br></d=
iv><div>create=C2=A0table test (id bpchar(8) primary key);</div><div><br></=
div><div>Both of the following queries use the index:</div><div><br></div><=
div>explain select * from test where id =3D &#39;foo&#39;::bpachar(3);</div=
><div><div>explain select * from test where id =3D &#39;foo&#39;::varchar;<=
/div><div><br></div><div>However when the String is cast to text the index =
isn&#39;t used:</div><div><br></div><div>explain select * from test where i=
d =3D &#39;foo&#39;::text;</div><div><br></div><div>This behavior seems to =
be consistent across postgres 12, 16 and 17.</div><div><br></div><div>I fin=
d it surprising that the cast to varchar behaves differently than the cast =
to text, is this intended behaviour?</div><div><br></div><div>Cheers</div><=
div>=C2=A0 Henning</div><div><br></div></div></div>

--000000000000251028062d536f1a--