Re: citext like searches using index

On 2013-03-30 23:35:24 -0400, Tom Lane wrote:
> "David E. Wheeler" <david(at)kineticode(dot)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.

I think the most realistic way to get this part - while being far from easy -
is to support case-insensitive locales. Then it would "only" need some
magic to link a normal locale to its case-insensitive locale which then
could be used to check for the presence of an appropriate index.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services