Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Michael PaquierDate: 2013-03-31 07:40:28
Subject: Re: Support for REINDEX CONCURRENTLY
Previous:From: Peter EisentrautDate: 2013-03-31 02:49:16
Subject: Re: pkg-config files for libpq and ecpg

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group