Re: Postgres case insensitive searches - Mailing list pgsql-general
| From | bhanu udaya |
|---|---|
| Subject | Re: Postgres case insensitive searches |
| Date | |
| Msg-id | COL127-W3885E0490871813722B8A1D3770@phx.gbl Whole thread Raw |
| In response to | Re: Postgres case insensitive searches ("Joshua D. Drake" <jd@commandprompt.com>) |
| Responses |
Re: Postgres case insensitive searches
(John R Pierce <pierce@hogranch.com>)
Re: Postgres case insensitive searches ("Joshua D. Drake" <jd@commandprompt.com>) Re: Postgres case insensitive searches (Neil Tiffin <neilt@neiltiffin.com>) |
| List | pgsql-general |
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.
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@commandprompt.com
> To: udayabhanu1984@hotmail.com
> CC: kgrittn@mail.com; adrian.klaver@gmail.com; pgsql-general@postgresql.org; pgadmin-support@postgresql.org; laurenz.albe@wien.gv.at; chris.travers@gmail.com; magnus@hagander.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?
>
> From: jd@commandprompt.com
> To: udayabhanu1984@hotmail.com
> CC: kgrittn@mail.com; adrian.klaver@gmail.com; pgsql-general@postgresql.org; pgadmin-support@postgresql.org; laurenz.albe@wien.gv.at; chris.travers@gmail.com; magnus@hagander.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
> 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
pgsql-general by date: