public inbox for [email protected]  

help / color / mirror / Atom feed

Index usage with differing string types
2+ messages / 2 participants
[nested] [flat]

* Index usage with differing string types
@ 2025-02-04 16:23  Henning Garus <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Henning Garus @ 2025-02-04 16:23 UTC (permalink / raw)
  To: [email protected]

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Index usage with differing string types
@ 2025-02-04 16:29  Adrian Klaver <[email protected]>
  parent: Henning Garus <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Adrian Klaver @ 2025-02-04 16:29 UTC (permalink / raw)
  To: [email protected]; [email protected]

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
[email protected]







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-02-04 16:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-04 16:23 Index usage with differing string types Henning Garus <[email protected]>
2025-02-04 16:29 ` Adrian Klaver <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox