Re: citext like searches using index

On 4/2/13 10:26 AM, Tom Lane wrote:
> The issue with the LIKE special case is that left-anchored patterns
> are (to some extent) indexable with ordinary btree indexes, and so we
> want to exploit that rather than tell people they have to have a whole
> other index.

In practice, you need an index specifically for pattern matching anyway.
The difference would be that instead of using a different operator
class that has no recorded association with the original operator, you'd
use a different access method that is associated with the operator in
normal ways.

> So it's a pretty special case, but there are just enough instances of it
> to wish for some not-so-hard-wired way to deal with it.

Are there any widely known non-built-in cases besides citext?
Presumably the reason you use citext is that you use a lot of letters.
So I kind of doubt that there are going to be a lot of cases of pattern
searches on citext with left-anchored patterns starting with (a
sufficient number of) non-letters. It's possible, of course, but
doesn't seem important enough by itself.