Re: [pgsql-hackers-win32] select like...not using index

>> > Sometime between yesterday and today queries in the form of
>> > "select * from t where t.f like 'k%'" have been broken so that they
>> > never use the index (on win32, not sure about others).
>> >
>> > On win32, at least, they have been broken for a while but this was
> due
>> > to a known issue based on the locales. AFAICT, the current cvs has
>> > addressed this issue and (show lc_collate returns C) there seems to
> be
>> > no reason why the queries aren't working properly.
>> >
>> > Merlin
>>
>> Did you do an ANALYZE on the table?
> Yes. Just for kicks, I also drop/rc the index...no help. Following
> that, I ran a fresh initdb which reported:
> The database cluster will be initialized with locale English_United
> States.1252.
>
> I then ran I just recently had the same issue (due to locale problems).
> This was recently fixed in cvs and replaced the hack I was using to work
> around the problem. The index search no longer works and I am very
> suspicious about a locale related issue. This is all off of a fresh
> copy of 7.5devel from the anonymous cvs server.
>
>> Are there a lot of duplicate keys?
>> How big is the table?
> About 250k with less than 1% duplicatation.
>> What does the "explain" look like?
>
> cpc=# explain select * from hchassis where vin_no = '2FTZX08W8WCA24365';
> QUERY PLAN
> ------------------------------------------------------------------------
> --------------
> Index Scan using hchassis_vin_no_idx on hchassis (cost=0.00..8.94
> rows=2 width=437)
> Index Cond: (vin_no = '2FTZX08W8WCA24365'::bpchar)
> (2 rows)
>
> cpc=# explain select * from hchassis where vin_no like
> '2FTZX08W8WCA24365%';
> QUERY PLAN
> --------------------------------------------------------------
> Seq Scan on hchassis (cost=0.00..19577.70 rows=1 width=437)
> Filter: (vin_no ~~ '2FTZX08W8WCA24365%'::text)
> (2 rows)
>
> cpc=#

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.