Help understanding locale

[Chris Hoover]

I need some help understanding locales and what the impact upon my
database/application would be.

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

[Peter Eisentraut]

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.

The sort order will be different.

[Chris Hoover]

Would you please be so kind to provide a bit more explanation? How will
the sort differ? Are the any good pages out there that would help me
understand the ramifications?

Thanks,

Chris

Post by Peter Eisentraut

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.

The sort order will be different.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Peter Eisentraut]

Post by Chris Hoover
Would you please be so kind to provide a bit more explanation? How will
the sort differ?

C sorts by byte value, which ends up being AB...Zab...z. en_US sorts using
"dictionary order", which means aAbB...zZ, accented characters appear at
their correct place, whitespace is ignored. The latter in principle applies
to all other language-specific locales as well, except that the particular
relative order of the letters may be different.