I am currently running 7.3.4 and have a database with tons of varchar fields. However, when we query on one of the fields with a like, it does not use an index. Apparently from the documentation, this is due to the locale not being set to C. The locale command gives me the following on my server:
LANG=en_US
LC_CTYPE="en_US"
LC_NUMERIC="en_US"
LC_TIME="en_US"
LC_COLLATE="en_US"
LC_MONETARY="en_US"
LC_MESSAGES="en_US"
LC_PAPER="en_US"
LC_NAME="en_US"
LC_ADDRESS="en_US"
LC_TELEPHONE="en_US"
LC_MEASUREMENT="en_US"
LC_IDENTIFICATION="en_US"
LC_ALL=
I am just starting a project to do an upgrade to our application and looking at trying to fix this problem since it is affecting our performance. I have upgraded our dbserver to 7.3.7 (our next project is a major db upgrade to 7.4 or even 8), and created a new cluster with the locale specifically set to C (initdb --locale=C ...). This has fixed the index usage problem, but I don't understand what ramifications if any it has on my database. Please help me understand what this means to me as the dba.
Thanks,
Chris
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
