On 2013-03-30 23:35:24 -0400, Tom Lane wrote: > "David E. Wheeler" <da...@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. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
