Re: 7.3 no longer using indexes for LIKE queries

Tom Lane <tgl@sss.pgh.pa.us> writes:

> [tgl@g3]$ echo -e  "a\na a\naa\na  a\nab\na b" | LC_ALL=en_US sort
> a
> aa
> a a
> a  a
> ab
> a b
>
> There's no way to use an index ordered like this to look for strings
> beginning "a ", because the sorting of spaces depends on what comes
> after them.

It seems like there's an obvious easy fix for this. Allow indexes to be
created a simple non-locale dependent lexical sort order. They wouldn't be
useful for sorting in the locale sort order but they would be useful for the
case at hand.

This has the disadvantage of requiring two indexes if you really do want both
"WHERE x BETWEEN ? and ?" and "WHERE x LIKE 'foo%' to be fast, but then you're
saying that if the user really wants one of these "unsafe" locales then that's
what it'll cost the achieve it.

Perhaps a warning about it in the initdb stage since it's probably usually not
what the user wants would be nice too.

--
greg