public inbox for [email protected]  

help / color / mirror / Atom feed

From: Henning Garus <[email protected]>
To: [email protected]
Subject: Index usage with differing string types
Date: Tue, 4 Feb 2025 17:23:39 +0100
Message-ID: <CAM_PoRsgiecCESyTTfL2iZJB376yaDX1-pfzi=ofPfVf0_Cfeg@mail.gmail.com> (raw)

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Index usage with differing string types
  In-Reply-To: <CAM_PoRsgiecCESyTTfL2iZJB376yaDX1-pfzi=ofPfVf0_Cfeg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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