Re: citext like searches using index

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On 3/30/13 11:35 PM, Tom Lane wrote:
>> The LIKE index optimization is hard-wired into
>> match_special_index_operator(), which never heard of citext's ~~
>> operators.
>>
>> I've wanted for years to replace that mechanism with something that
>> would support plug-in extensions, but have no very good idea how to
>> do it.

> I have been thinking there should be a GiST index that associates with
> the pattern matching operators. I haven't worked out the details, but
> at least this seems it might be the right framework to solve this problem.

No, not really. You can already build things like that with GiST, see
pg_trgm (which already does LIKE and will soon be able to do regexes).

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. It doesn't make sense IMO to try to mark ~~ as a btree
operator, because btree doesn't really have any ability for
operator-specific code to do what would have to be done. What does make
sense is for the planner to understand how to extract a btree-indexable
clause out of what's in the query, as that (a) keeps the complexity
out of the run-time machinery, and (b) provides an opportunity for the
planner to estimate whether the whole thing is worth the trouble or
not, which it frequently isn't in LIKE cases.

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.

regards, tom lane