"" The locale settings influence the following SQL features:
Sort order in queries using ORDER BY or the standard comparison operators on textual data
The upper, lower, and initcap functions
Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions
The to_char family of functions
The ability to use indexes with LIKE clauses
The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them.
As a workaround to allow PostgreSQL to use indexes with LIKE clauses under a non-C locale, several custom operator classes exist. These allow the creation of an index that performs a strict character-by-character comparison, ignoring locale comparison rules. Refer to Section 11.9 for more information. Another approach is to create indexes using the C collation, as discussed in Section 23.2. ""
the performance impact sometimes is huge.
Sorting Geohash with "C" locale is sometimes 40% faster: " ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(geom),4326),10) COLLATE "C";"
Debraj Manna wrote: > Can someone let me know what is the difference we are expected to observe if we set > Collate & Ctype to C as against en_US.UTF-8 with encoding set to UTF8 in both the cases ?
For one, the ordering will be substantially different.
Compare the result of these two queries:
SELECT chr(i) FROM generate_series(1, 50000) i ORDER BY chr(i) COLLATE "C"; SELECT chr(i) FROM generate_series(1, 50000) i ORDER BY chr(i) COLLATE "en_US.utf8";