Postgres Pro
Downloads
Home   >   mailing lists

Thread: Index usage with differing string types

Index usage with differing string types

From
Henning Garus
Date:
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

Re: Index usage with differing string types

From
Adrian Klaver
Date:
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.klaver@aklaver.com

Re: Index usage with differing string types

From
Tom Lane
Date:
Henning Garus <henning.garus@gmail.com> writes:
> However when the String is cast to text the index isn't used:
> explain select * from test where id = 'foo'::text;

That's because "text" is considered a preferred type, so it wins
the contest over whether '=' means texteq or bpchareq:

# explain select * from test where id = 'foo'::varchar;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Only Scan using test_pkey on test  (cost=0.15..8.17 rows=1 width=12)
   Index Cond: (id = 'foo'::bpchar)
(2 rows)

# explain select * from test where id = 'foo'::text;
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on test  (cost=0.00..40.60 rows=10 width=12)
   Filter: ((id)::text = 'foo'::text)
(2 rows)

Your index supports bpchar comparison semantics, not text,
so it doesn't work for this query.

You could work around this by creating an index on id::text,
but TBH I'd question the choice to use a bpchar column in
the first place.  It's pretty much the poster child for
dubious legacy datatypes.

            regards, tom lane