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. 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.
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