Обсуждение: 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
>> > 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.