Re: [HACKERS] locales and encodings Oh MY!

On Fri, Nov 12, 2010 at 12:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> 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