Re: [pgsql-hackers-win32] [HACKERS] select like...not using index
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-hackers-win32] [HACKERS] select like...not using index
Merlin Moncure [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-hackers-win32] [HACKERS] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] 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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [HACKERS] select like...not using index
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\localepg_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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [HACKERS] select like...not using index
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. 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\localepg_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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
