| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "David E(dot) Wheeler" <david(at)kineticode(dot)com> |
| Cc: | 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 03:35:24 |
| Message-ID: | 27270.1364700924@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
"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.
Or in short: fixing this is a research problem.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Paquier | 2013-03-31 07:40:28 | Re: Support for REINDEX CONCURRENTLY |
| Previous Message | Peter Eisentraut | 2013-03-31 02:49:16 | Re: pkg-config files for libpq and ecpg |