Re: varchar index joins not working?

Tom Lane wrote:
> Adam Gundy <adam(at)starsilk(dot)net> writes:
>> hmm. unfortunately it did turn out to be (part) of the issue. I've
>> discovered that mixing char and varchar in a stored procedure does not
>> coerce the types, and ends up doing seq scans all the time.
>
> Oh, it coerces the type all right, just not in the direction you'd like.
>
> regression=# create table v (f1 varchar(32) primary key);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "v_pkey" for table "v"
> CREATE TABLE
> regression=# explain select * from v where f1 = 'abc'::varchar;
> QUERY PLAN
> -----------------------------------------------------------------
> Index Scan using v_pkey on v (cost=0.00..8.27 rows=1 width=34)
> Index Cond: ((f1)::text = 'abc'::text)
> (2 rows)
>
> regression=# explain select * from v where f1 = 'abc'::char(3);
> QUERY PLAN
> ---------------------------------------------------
> Seq Scan on v (cost=0.00..25.88 rows=1 width=34)
> Filter: ((f1)::bpchar = 'abc'::character(3))
> (2 rows)

yeah. not terribly helpful.. you'd have to assume I'm not the only one
this has bitten..

is there a reason it doesn't coerce to a type that's useful to the
planner (ie varchar in my case), or the planner doesn't accept any type
of string as a valid match for index scan? I would think the benefits of
being able to index scan always outweigh the cost of type conversion...

hmm. I only saw this with stored procs, but it's obviously generic. I
think the reason I didn't see it with straight SQL or views is that it
seems to work correctly with string constants.. coercing them to the
correct type for the index scan. with a stored proc, all the constants
are passed in as args, with char() type (until I fixed it, obviously!)