>> It looks to me like you have an index of type "bpchar" but are
> searching
>> with type "text." I find type conversions very limited with "LIKE."
>>
>> I would create an index on 'vin_no' using a cast to TEXT. This should
> work
>> on both queries.
>
> Not in this case. Just to be sure, I created a new column as text type,
> created index, analyzed, and searched and got the same behavior.
Hmmm, snipped from your reply was the explain plan from the query where it
was clear you were using two different character data types: bpchat and
text. That, alone, may have been a problem.
Looking at your defaults, did you do:
initdb --locale=C somepath
?
I found, at some point, 'C' used to be the default, now it seems initdb
wants to fish out what locale your system is using.
Personally, I think, if I do not specify a locale, I don't want a specific
locale. Period. I haven't been paying too close attention to the hackers
list to say when this happened, but it bit me a couple times.
>
> Furthermore, I did this:
> cpc=# show lc_collate;
> lc_collate
> ------------
> C
> (1 row)
>
> cpc=# show lc_ctype;
> lc_ctype
> ----------
> C
> (1 row)
>
> followed by this:
> C:\postgres\pgsql\src\test\locale>pg_controldata
> [...]
> LC_COLLATE: English_United States.1252
> LC_CTYPE: English_United States.1252
>
>
> At this point I'm about 90% sure I've turned up a locale related
> bug...initdb warned me wrt the locale but psql is still reporting 'C'.
> Plus, my queries don't work where they used to about a week ago. My
> next step is to initdb --locale=C to confirm this. I've informed Magnus
> about this and he is looking into it.
>
> Merlin
>