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: udayabhanu1...@hotmail.com
To: ne...@neiltiffin.com
CC: pgsql-general@postgresql.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: ne...@neiltiffin.com
Date: Sat, 29 Jun 2013 14:08:47 -0500
CC: pgsql-general@postgresql.org
To: udayabhanu1...@hotmail.com
On Jun 29, 2013, at 11:24 AM, bhanu udaya <udayabhanu1...@hotmail.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
