Re: Index usage with differing string types

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