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=#

Merlin