Re: citext like searches using index

"David E. Wheeler" <david@kineticode.com> writes:
> Hackers, what would be required to get an index on a CITEXT column to support LIKE?

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.

A bigger problem though is that the LIKE optimization is generally
pretty ineffective for ILIKE (which is what you're really asking for
here) because we can't assume that both case versions are consecutive
in the index.  I think the optimization just punts upon seeing any
letter anyway, if the operator is ILIKE.

Or in short: fixing this is a research problem.
        regards, tom lane