Re: Postgres case insensitive searches

Create database with UTF8 character with Collation Posix.
Also, modified the table column as below:
alter table tableA alter column colA type text COLLATE POSIX
create Index btree index on ColA Collate POSIX
Use the query lower(colA) like 'b%'
The results seems promissing. But, would like to do more research and come to conclusion.

From: udayabhanu1984(at)hotmail(dot)com
To: neilt(at)neiltiffin(dot)com
CC: pgsql-general(at)postgresql(dot)org
Subject: RE: [GENERAL] Postgres case insensitive searches
Date: Sun, 30 Jun 2013 22:35:32 +0530

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.

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