Re: Index usage with differing string types

Henning Garus <henning(dot)garus(at)gmail(dot)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