From: | Andres Freund <andres(at)2ndquadrant(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "David E(dot) Wheeler" <david(at)kineticode(dot)com>, Thorbjørn Weidemann <thorbjoern(at)weidemann(dot)name>, "pgsql-hackers(at)postgresql(dot)org Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: citext like searches using index |
Date: | 2013-03-31 08:32:55 |
Message-ID: | 20130331083255.GM28736@alap2.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2013-03-31 13:28:16 | Re: Getting to 9.3 beta |
Previous Message | Michael Paquier | 2013-03-31 07:40:28 | Re: Support for REINDEX CONCURRENTLY |