Postgres case insensitive searches

bhanu udaya <udayabhanu1984@hotmail.com>

From: bhanu udaya <udayabhanu1984@hotmail.com>

To: Kevin Grittner <kgrittn@mail.com>, Adrian Klaver <adrian.klaver@gmail.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>, "pgadmin-support@postgresql.org" <pgadmin-support@postgresql.org>

Cc: Albe Laurenz <laurenz.albe@wien.gv.at>, Chris Travers <chris.travers@gmail.com>, Magnus Hagander <magnus@hagander.net>

Date: 2013-06-28T10:21:33Z

Lists: pgsql-general

Hello,
 
Grettings,
 
What is the best way of doing case insensitive searches in postgres using Like. 
 
Ilike - does not use indexes
function based indexes are not as fast as required.
CITEXT - it still taking 600 ms - 1 second on a 2.2 million rows... does not use index
Collation Indexes creation with POSIX - does not really work.
GIST/GIN indexes are faster when using like, but not case insenstive.
 
Is there a better way of resolving this case insenstive searches with fast retrieval. 
 
Thanks and Regards
Radha Krishna