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?
Are there a lot of duplicate keys?
How big is the table?
What does the "explain" look like?

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:

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).

Not here ... and I've seen no commits that I'd have thought would affect
that. Check for local configuration changes.

regards, tom lane

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.

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.

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

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.

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 reran initdb --locale=C yesterday and that fixed the problem. Since I
am doing the nightly win32 builds I run initdb each night around 1am and
I missed the locale warning. I had a feeling it was something like
this. The part I don't understand is why psql was saying the locale
(show lc_ctype) was 'C' when pg_controldata was not. This, along with
recent code revisions tricked me for a while (not to mention the default
locale being changed).

Here is what I think happened (this might be a bug, might not): Each
night I run initdb but I use a special postgresql.conf which is
optimized for quick data loading. This is copied over the default one
after the server is started. This contains the locale information which
is 'initialized by initdb'. These were still 'C' because this file was
generated before the default locale was changed. psql shows this
information when you ask it for the locale info even if it is incorrect.
The real settings are of course built into the database itself. This
stuff is all new to me, I've never really had to deal with locales
before.

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.

I now accept this as dogma :)

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:

Here is what I think happened (this might be a bug, might not): Each
night I run initdb but I use a special postgresql.conf which is
optimized for quick data loading. This is copied over the default one
after the server is started. This contains the locale information which
is 'initialized by initdb'. These were still 'C' because this file was
generated before the default locale was changed. psql shows this
information when you ask it for the locale info even if it is
incorrect.

I don't believe this for a minute. lc_ctype and lc_collate can *not*
be set from postgresql.conf. Try it.

regards, tom lane