Yes. I have used analyze table, and also I have explain plan the CITEXT query. It was not using Index. It is not primary and it is surprised to know that CITEXT would use index only if it is a primary key column. Interesting and new thing to know.
Upper and Lower functions are not right choice when the table is > 2.5 million and where we also have heavy insert transactions.
I doubt, if we can cache the table if there are frequent inserts/updates. The good idea would be to get the DB to case insenstive configuration like SQL Server. I would go for this solution, if postgres supports.
Thanks for all the replies and help.
> Date: Sat, 29 Jun 2013 09:02:12 -0700
> From: jd(at)commandprompt(dot)com
> To: udayabhanu1984(at)hotmail(dot)com
> CC: kgrittn(at)mail(dot)com; adrian(dot)klaver(at)gmail(dot)com; pgsql-general(at)postgresql(dot)org; pgadmin-support(at)postgresql(dot)org; laurenz(dot)albe(at)wien(dot)gv(dot)at; chris(dot)travers(at)gmail(dot)com; magnus(at)hagander(dot)net
> Subject: Re: [GENERAL] 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