|
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@xxxxxxxxxxxxxxxxx > To: udayabhanu1984@xxxxxxxxxxx > CC: kgrittn@xxxxxxxx; adrian.klaver@xxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx; pgadmin-support@xxxxxxxxxxxxxx; laurenz.albe@xxxxxxxxxx; chris.travers@xxxxxxxxx; magnus@xxxxxxxxxxxx > Subject: 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 |
