2010/8/17 Tom Lane <tgl@sss.pgh.pa.us>:
> Cédric Villemain <cedric.villemain.debian@gmail.com> writes:
>> Here we are. A simple usecase.
>
> The reason you have an issue here is that the column is char(n) while
> the parameter is text. So the non-USING execute is equivalent to
>
> regression=# explain SELECT flag FROM foo where uid = 'cfcd208495d565ef66e7dff9f98764da';
> QUERY PLAN
> --------------------------------------------------------------------
> Index Scan using foo_pkey on foo (cost=0.00..8.27 rows=1 width=1)
> Index Cond: (uid = 'cfcd208495d565ef66e7dff9f98764da'::bpchar)
> (2 rows)
>
> while the EXECUTE USING is equivalent to
>
> regression=# explain SELECT flag FROM foo where uid = 'cfcd208495d565ef66e7dff9f98764da'::text;
> QUERY PLAN
> --------------------------------------------------------------------
> Seq Scan on foo (cost=0.00..24.02 rows=5 width=1)
> Filter: ((uid)::text = 'cfcd208495d565ef66e7dff9f98764da'::text)
> (2 rows)
>
> and the reason you don't get an indexscan on the latter is that it's a
> TEXT comparison not a BPCHAR comparison; which is different because of
> the rules about ignoring trailing blanks.
>
> char(n) sucks. Avoid it if possible. If you insist on using it,
> be very very careful about which comparison semantics you're asking for.
Oh! Thank you very much for those clarifications.
... and I am sorry for the noisy report ...
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
