Re: Postgres case insensitive searches

On Jun 30, 2013 7:07 PM, "bhanu udaya" <udayabhanu1984(at)hotmail(dot)com> wrote:
>
>
> I almost used every option ; upper, posix, gist, gin, citext, etc.
feature of the postgres to get the query most optimal.. If a particular
query is taking 1 + second for one user/thread, then for many users
accessing it concurrently would take lot of resources and the performance
would be dropped in no time may be for 10 users .. I am trying to get the
best way of achieving things with postgres.
>
> I do not know what else can be done to get the performance more optimal.
if there are any good suggestions in tweaking db parameters or with some
index that can help, then I would love to experiment it and achieve it.
>
> We have observed that inserts are ok, but the selects are dropping
performance and not acceptable. Show me an index that can retrieve a
simple select query (case insensitive) in 100 -200 ms. from a table which
has 2- 10 million records. Is this possible ? I could have gone for
partitions, etc., but it is plan B and more over partitions in postgres has
to undergo more manual process.
>

How many rows are in the result? Can you use a partial index? What's the
usage pattern? Can you cache the result in a materialized view?

In general, getting one row from an index from a table that fits in your
RAM is possible in a few ms. Case insensitive or not.

Can you show us a explain analyze.

>
> Thanks for all replies and help.
> ________________________________
> Subject: Re: [GENERAL] Postgres case insensitive searches
> From: neilt(at)neiltiffin(dot)com
> Date: Sat, 29 Jun 2013 14:08:47 -0500
> CC: pgsql-general(at)postgresql(dot)org
> To: udayabhanu1984(at)hotmail(dot)com
>
>
> On Jun 29, 2013, at 11:24 AM, bhanu udaya <udayabhanu1984(at)hotmail(dot)com>
wrote:
>
>> Upper and Lower functions are not right choice when the table is > 2.5
million and where we also have heavy insert transactions.
>
>
> PostgreSQL and SQL Server are completely different. Rules that apply to
SQL Server do not necessarily apply to PostgreSQL.
>
> You problem is not the use of upper() or lower() it is the assumption
what works in SQL Server is the best way to use PostgreSQL. You'll get
farther if you benchmark several of the suggestions, then if the
performance is not good enough, ask how to improve the performance. This
will take a little work on your part, but that is how you learn.
>
> Neil