Re: varchar index joins not working?

Tom Lane wrote:
> Adam Gundy <adam@starsilk.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!)