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