Re: citext like searches using index

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
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

In response to

Responses

Browse pgsql-hackers by date

  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