Tom Lane wrote:
Adam Gundy <[EMAIL PROTECTED]> 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!)
smime.p7s
Description: S/MIME Cryptographic Signature
