Re: 7.3 no longer using indexes for LIKE queries

Can someone please elaborate on why it stops doing this optimization?  The
only reasons for it that I can think of for it to be unsafe in a locale is
that two characters that are not the same character still compare as being
equal (does this ever really happen?).  Otherwise, saying "foo LIKE 'xxx%'"
is really saying are the first 3 characters of foo equal to 'xxx'.
Regardless of sort order, in a sorted list of values, anything starting with
'xxx' should appear together, and thus be optimizable by an index unless
there are values of x and y for which 'x' = 'y'.  If that were the case,
though, you couldn't use the index for "foo = 'xxx'" either, which is
obviously not the case.

So, can someone enlighten me as to where I'm making a wrong assumption or
something here?


    -Matt


-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Tuesday, December 03, 2002 19:31

Whats the output of pg_controldata, specifically, what is LC_COLLATE? If it
isn't "C", then LIKE won't use your index.

See:
http://developer.postgresql.org/docs/postgres/charset.html