On Fri, Nov 12, 2010 at 12:03 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Robert Haas <robertmhaas@xxxxxxxxx> writes: >>> With how similar straight C and en_US.UTF8 are it was suggested to me, >>> by persons who are far more C knowledgeable then I in my office, that >>> this is something the PG community could "fix" . A "fix" being so that >>> "col LIKE 'foo%' " could use btree indexes in locales like en_US.UTF8 >>> (and probably some others). > >>> is the request unreasonable ? anyone got any idea of the price tag to >>> make that happen ? > >> I thought it already did that. > > No, and the odds of it ever happening are insignificant. The sort order > associated with en_US (and other "dictionary order" locales) is just too > randomly different from what you need to optimize a LIKE search. > (Whoever told you en_US sorts similarly to C is nuts.) > > The solution if you want the database's prevailing sort order to be en_US > is to put an extra text_pattern_ops index on the column you want to do > LIKE searches on. We might eventually have the ability to spell that > "put a C-locale index on the column", but text_pattern_ops is the way to > do it today. > > regards, tom lane > Ok I hear you loud and clear. I am going to eat the overhead until I get to 9.0.1, currently on 8.3.X in some places. I will either take an outage and do a dump - re-init-restore or inplace upgrade and then do some locking, copy, drop old, rename new db path. thanks all. ..: Mark -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
