Re: citext like searches using index

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> 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.

Well, you do if you want to search for general patterns, but there's
been enough interest in the LIKE optimization as-is to make me think
we could not get away with removing it.

>> 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?

Well, indxpath.c knows about text LIKE and network subset operators,
and it would be nice if it knew how to do the same type of optimization
for range inclusion, ie btree_indexed_col <@ range_constant. The latter
doesn't seem implementable in the current infrastructure because ranges
aren't all built-in types. I agree that the citext case isn't too
compelling in isolation, but surely the range case is interesting.

regards, tom lane