Re: Arg! PG not using index on an analyzed table

On Wed, 2 Feb 2005, Reece Hart wrote:

> On Wed, 2005-02-02 at 11:10 -0800, Stephan Szabo wrote:
>
> > You may also be able to get away with another index on the column using
> > text_pattern_ops as the operator class if you need/want other en_US
> > features.
>
>
> Indeed! Yippee! I was fearing another initdb and restore for several
> databases. Of course, I'm chagrined that I now need to go figure out
> which indexes need redefinition.
>
> Stephan, do you know what overhead (space or time) this induces, if any?

Well, the problem is that you probably need both a normal index and a
pattern_ops unless you're only going to be using LIKE/regexp queries. So
you'll be maintaining two indexes on insert/update and be paying the cost
for two indexes in size.

>Suppose I got to en_US by accident and need none of its features. Is
>there a reason any reason I should initdb --locale=C and
>restore?
>
>What benefits and detriments does en_US have over C? I'm having a hard
>time caring which I use (as long they give what I want).

Well, the biggest thing to look at is sorting. en_US ignores spaces and
symbols in its first pass of collation which often surprises people who
aren't expecting it.

Given
A b
AA
aa
#z

on my machine en_US sorts them as:
aa
AA
A b
#z

C sorts them as
#z
A b
AA
aa