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
