Re: Index usage with differing string types

On 2/4/25 08:23, Henning Garus wrote:
> 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;

The output from EXPLAIN ANALYZE on each of the queries would be useful.

>
> 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
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com