Thread: LC_COLLATE and index usage
According to the postgres 7.3.2 docs, initializing a database cluster with LC_COLLATE set to anything other than "C" will disable the use of indexes during LIKE searches. This presents a bit of a problem for me, since the client application I'm writing uses LIKE searches and ships internationally. As I understand it, I can have good performance, or locale-specific ORDER BY output, but not both. Is there a plan to remove this limitation?
How can I determine what LC_COLLATE setting was used initialize a postgres installation? At the moment, I'm trying to figure out what collation locale was chosen by the RPM install of postgres 7.3.2, but it would also be useful to know how to check this with any random installation.
> According to the postgres 7.3.2 docs, initializing a database cluster > with LC_COLLATE set to anything other than "C" will disable the use of > indexes during LIKE searches. This presents a bit of a problem for > me, since the client application I'm writing uses LIKE searches and > ships internationally. As I understand it, I can have good > performance, or locale-specific ORDER BY output, but not both. Is > there a plan to remove this limitation? Question seconded (and tacit suggestion for a TODO item) on behalf of GnuMed (www.gnumed.org). Karsten Hilbert, MD -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Personally,
I'd like to see LOCALE done away with, everything be in UTF-8, and have
a POSGRES extension that sets the sort order as an itemized listing of
mime type languages like:
SET COLLATION( en-us, fr, ru ).
Everything NOT in those character sets, falls below them in the sort
order, but stays in it's tabular order, otherwise.
PS. We are sure lucky in the western world that we have such a
simplistic alphabet.
Karsten Hilbert wrote:
>
> > According to the postgres 7.3.2 docs, initializing a database cluster
> > with LC_COLLATE set to anything other than "C" will disable the use of
> > indexes during LIKE searches. This presents a bit of a problem for
> > me, since the client application I'm writing uses LIKE searches and
> > ships internationally. As I understand it, I can have good
> > performance, or locale-specific ORDER BY output, but not both. Is
> > there a plan to remove this limitation?
> Question seconded (and tacit suggestion for a TODO item) on
> behalf of GnuMed (www.gnumed.org).
>
> Karsten Hilbert, MD
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
Hi Karsten, > Question seconded (and tacit suggestion for a TODO item) on > behalf of GnuMed (www.gnumed.org). Depending on how your LIKE searches look like, you can use a little work around, which improves speed dramatically. In case you need to search like "<String>%", you can use a second table containing each possible combination of the search string, a fixed length field and JOIN that helper table. So you can change your LIKE search to a search for a string of fixed length. Kind regards ... Ralph ...
> I'd like to see LOCALE done away with, everything be in UTF-8, and have > a POSGRES extension that sets the sort order as an itemized listing of > mime type languages like: > > SET COLLATION( en-us, fr, ru ). > > Everything NOT in those character sets, falls below them in the sort > order, but stays in it's tabular order, otherwise. Sure, I don't care either way as long as I get indexes on my localized sort order (I do know I don't have a right to request that unless I offer a patch ;-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, May 08, 2003 at 02:24:48AM +0200, Karsten Hilbert wrote: > (I do know I don't have a right to > request that unless I offer a patch ;-) IMHO, we as users have the right, and probably the duty as well, to request (reasonable?) things, because that keeps the project alive. Feeding the To-Do list is one of the tasks of users; reporting bugs, testing beta releases and appraising developers being some others. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Oh, great altar of passive entertainment, bestow upon me thy discordant images at such speed as to render linear thought impossible" (Calvin a la TV)
"Forest Wilkinson" <lyris-pg@tibit.com> writes:
> How can I determine what LC_COLLATE setting was used initialize a
> postgres installation?
The pg_controldata utility program can tell you.
regards, tom lane
On Wed, May 07, 2003 at 03:34:24PM -0700, Forest Wilkinson wrote:
> ships internationally. As I understand it, I can have good
> performance, or locale-specific ORDER BY output, but not both. Is
> there a plan to remove this limitation?
I think there would be, if anyone could come up with a comprehensive
set of rules about how to handle these cases. See the -hackers
archives for plenty of discussion of this.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110