Re: Postgres case insensitive searches

On 06/28/2013 03:21 AM, bhanu udaya wrote:
> 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.

O.k. there is not anywhere near enough information here to provide you
with a proper answer but here are the two things you should look at:

CITEXT: You said it takes 600ms - 1 second. Is that a first run or is
the relation cached? Second how do you know it isn't using the index?
Have you ran an explain analyze? In order for CITEXT to use an index it
the value being searched must be the PRIMARY KEY, is your column the
primary key?

Second, you have provided us with zero information on your hardware
configuration. 2.2 million rows is a low of rows to seqscan, if they
aren't cached or if you don't have reasonable hardware it is going to
take time no matter what you do.

Third, have you tried this with unlogged tables (for performance)?

Fourth, there was another person that suggested using UPPER() that is a
reasonable suggestion. The docs clearly suggest using lower(), I don't
actually know if there is a difference but that is the common way to do
it and it will use an index IF you make a functional index on the column
using lower.

JD

>
> Thanks and Regards
> Radha Krishna
>

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats