Re: [GENERAL] Postgres case insensitive searches
bhanu udaya wrote: What is the best way of doing case insensitive searches in postgres using Like. Table laurenz.t Column | Type | Modifiers +-+--- id | integer | not null val | text | not null Indexes: t_pkey PRIMARY KEY, btree (id) CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops); ANALYZE t; EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%'; QUERY PLAN -- Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4) Index Cond: ((upper(val) ~=~ 'AB'::text) AND (upper(val) ~~ 'AC'::text)) Filter: (upper(val) ~~ 'AB%'::text) (3 rows) Thanks. But, I do not want to convert into upper and show the result. Example, if I have records as below: id type 1. abcd 2. Abcdef 3. ABcdefg 4. aaadf The below query should report all the above No, it shouldn't :^) select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type collation POSIX, but it did not really help. My solution is fast and efficient, it will call upper() only once per query. I don't see your problem. Different database systems do things in different ways, but as long as you can do what you need to do, that should be good enough. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
On Mon, Jul 1, 2013 at 10:01 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: bhanu udaya wrote: What is the best way of doing case insensitive searches in postgres using Like. Table laurenz.t Column | Type | Modifiers +-+--- id | integer | not null val | text | not null Indexes: t_pkey PRIMARY KEY, btree (id) CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops); ANALYZE t; EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%'; QUERY PLAN -- Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4) Index Cond: ((upper(val) ~=~ 'AB'::text) AND (upper(val) ~~ 'AC'::text)) Filter: (upper(val) ~~ 'AB%'::text) (3 rows) My solution is fast and efficient, it will call upper() only once per query. I don't see your problem. Different database systems do things in different ways, but as long as you can do what you need to do, that should be good enough. Yours, Laurenz Albe I was toying around a little bit with this example, just for my understanding, the function upper is called for every row in the result. I think this has something to to with the filter in the plan. This is what I did create table foo as (select md5(random()::text) from generate_series(1,2.5e6::integer)); -- create a little wrapper function to see when it is called create ': create or replace function test_upper(text_in TEXT) RETURNS TEXT AS $func$ begin raise warning 'called'; return upper(text_in); end; $func$ LANGUAGE plpgsql IMMUTABLE; create index foo_ind on foo (test_upper(md5) text_pattern_ops); --lots of 'called' ouptut analyze foo; -- here you see that the function is called for every row in the result postgres=# select * from foo where test_upper(md5) like 'ABAAB%'; WARNING: called WARNING: called WARNING: called md5 -- abaab10ff1690418d69c360d2dc9c8fc abaab339fb14a7a10324f6007d35599a abaab34f0cebabee89fa222bfee7b6ea (3 rows) postgres=# explain select * from foo where test_upper(md5) like 'ABAAB%'; QUERY PLAN -- Index Scan using foo_ind on foo (cost=0.50..14.02 rows=250 width=33) Index Cond: ((test_upper(md5) ~=~ 'ABAAB'::text) AND (test_upper(md5) ~~ 'ABAAC'::text)) Filter: (test_upper(md5) ~~ 'ABAAB%'::text) (3 rows) So under my assumption that it is the filter that causes the function execution, I don't understand how a row can satisfy --which I read as = 'ABAAB' and 'ABAAC' ((test_upper(md5) ~=~ 'ABAAB'::text) AND (test_upper(md5) ~~ 'ABAAC'::text)) and not (test_upper(md5) ~~ 'ABAAB%'::text) Ingmar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
Ingmar Brouns wrote: My solution is fast and efficient, it will call upper() only once per query. I don't see your problem. Different database systems do things in different ways, but as long as you can do what you need to do, that should be good enough. I was toying around a little bit with this example, just for my understanding, the function upper is called for every row in the result. I think this has something to to with the filter in the plan. You are right, and the function is also called once per result row. The point I was really trying to make is that it is *not* called once per row in the table. postgres=# explain select * from foo where test_upper(md5) like 'ABAAB%'; QUERY PLAN -- Index Scan using foo_ind on foo (cost=0.50..14.02 rows=250 width=33) Index Cond: ((test_upper(md5) ~=~ 'ABAAB'::text) AND (test_upper(md5) ~~ 'ABAAC'::text)) Filter: (test_upper(md5) ~~ 'ABAAB%'::text) (3 rows) So under my assumption that it is the filter that causes the function execution, I don't understand how a row can satisfy --which I read as = 'ABAAB' and 'ABAAC' ((test_upper(md5) ~=~ 'ABAAB'::text) AND (test_upper(md5) ~~ 'ABAAC'::text)) and not (test_upper(md5) ~~ 'ABAAB%'::text) I don't know, but I suspect it has to do with collations. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
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: 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
Re: [GENERAL] Postgres case insensitive searches
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
Re: [GENERAL] 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: 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
Re: [GENERAL] Postgres case insensitive searches
On Jun 30, 2013 7:07 PM, bhanu udaya udayabhanu1...@hotmail.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: 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
Re: [GENERAL] Postgres case insensitive searches
On Jun 29, 2013, at 3:59, bhanu udaya udayabhanu1...@hotmail.com wrote: Thanks. But, I do not want to convert into upper and show the result. Why not? It won't modify your results, just the search condition: SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val; Or: SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY upper(val), val; Example, if I have records as below: id type 1. abcd 2. Abcdef 3. ABcdefg 4. aaadf The below query should report all the above select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type collation POSIX, but it did not really help. I was under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS collation mechanics). So, what OS are you running the server on? From: laurenz.a...@wien.gv.at To: udayabhanu1...@hotmail.com; pgsql-general@postgresql.org Subject: RE: Postgres case insensitive searches Date: Fri, 28 Jun 2013 12:32:00 + Please do not top-post on this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches
Hello, I agree that it is just search condition. But, in a 2.5 million record table search, upper function is not that fast. The expectation is to get the query retrieved in 100 ms...with all indexes used. I tried with upper, Citext, but the result set was more than a second. The OS server we are using is Linux 64 bit. Thanks and Regards Radha Krishna Subject: Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches From: haram...@gmail.com Date: Sat, 29 Jun 2013 09:37:51 +0200 CC: laurenz.a...@wien.gv.at; pgsql-general@postgresql.org; pgadmin-supp...@postgresql.org To: udayabhanu1...@hotmail.com On Jun 29, 2013, at 3:59, bhanu udaya udayabhanu1...@hotmail.com wrote: Thanks. But, I do not want to convert into upper and show the result. Why not? It won't modify your results, just the search condition: SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val; Or: SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY upper(val), val; Example, if I have records as below: id type 1. abcd 2. Abcdef 3. ABcdefg 4. aaadf The below query should report all the above select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type collation POSIX, but it did not really help. I was under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS collation mechanics). So, what OS are you running the server on? From: laurenz.a...@wien.gv.at To: udayabhanu1...@hotmail.com; pgsql-general@postgresql.org Subject: RE: Postgres case insensitive searches Date: Fri, 28 Jun 2013 12:32:00 + Please do not top-post on this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgadmin-support mailing list (pgadmin-supp...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches
On Jun 29, 2013, at 15:02, bhanu udaya udayabhanu1...@hotmail.com wrote: I agree that it is just search condition. But, in a 2.5 million record table search, upper function is not that fast. Suit yourself, the solution is there. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
On 06/29/2013 09:02 AM, bhanu udaya wrote: Hello, I agree that it is just search condition. But, in a2.5 million record table search, upper function is not that fast. The expectation is to get the query retrieved in 100 ms...with all indexes used. I tried with upper, Citext, but the result set was more than a second. The OS server we are using is Linux 64 bit. Thanks and Regards Radha Krishna Subject: Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches From: haram...@gmail.com Date: Sat, 29 Jun 2013 09:37:51 +0200 CC: laurenz.a...@wien.gv.at; pgsql-general@postgresql.org; pgadmin-supp...@postgresql.org To: udayabhanu1...@hotmail.com On Jun 29, 2013, at 3:59, bhanu udaya udayabhanu1...@hotmail.com wrote: Thanks. But, I do not want to convert into upper and show the result. Why not? It won't modify your results, just the search condition: SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val; Or: SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY upper(val), val; Example, if I have records as below: id type 1. abcd 2. Abcdef 3. ABcdefg 4. aaadf The below query should report all the above select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type collation POSIX, but it did not really help. I was under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS collation mechanics). So, what OS are you running the server on? Duplicate the column with an upper or lowercase version and run all queries against that. CREATE TABLE foo ( id serial PRIMARY KEY, val text, val_lower text ); Index val_lower. Use triggers to keep val and val_lower in sync and discard all attempts to write directly to val_lower. Then all queries would be of the form SELECT id, val FROM foo WHERE val_lower LIKE 'ab%'; Wouldn't want to write every table like this, but if (a) query speed trumps all other requirements and (b) functional index, CITEXT, etc. have all been rejected as not fast enough --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches
Subject: Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches From: haram...@gmail.com Date: Sat, 29 Jun 2013 09:37:51 +0200 CC: laurenz.a...@wien.gv.at; pgsql-general@postgresql.org; pgadmin-supp...@postgresql.org To: udayabhanu1...@hotmail.com On Jun 29, 2013, at 3:59, bhanu udaya udayabhanu1...@hotmail.com wrote: Thanks. But, I do not want to convert into upper and show the result. Why not? It won't modify your results, just the search condition: SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val; Or: SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY upper(val), val; Example, if I have records as below: id type 1. abcd 2. Abcdef 3. ABcdefg 4. aaadf The below query should report all the above select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type collation POSIX, but it did not really help. I was under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS collation mechanics). So, what OS are you running the server on? Duplicate the column with an upper or lowercase version and run all queries against that. CREATE TABLE foo ( id serial PRIMARY KEY, val text, val_lower text ); Index val_lower. Use triggers to keep val and val_lower in sync and discard all attempts to write directly to val_lower. Then all queries would be of the form SELECT id, val FROM foo WHERE val_lower LIKE 'ab%'; Wouldn't want to write every table like this, but if (a) query speed trumps all other requirements and (b) functional index, CITEXT, etc. have all been rejected as not fast enough… --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu It is a good idea to have a duplicate column and index and use that column. But, we have heavyinserts/updates on this table. I am afraid that it would slow down the insert performance. But, I would definately like to test this option. Isn't it better to convert Postgres DB to case insensitive ?How difficult is that ? I want the DB to support UTF8 and be case insensitive like SQL Server. Thanks
Re: [GENERAL] Postgres case insensitive searches
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. Date: Sat, 29 Jun 2013 09:02:12 -0700 From: j...@commandprompt.com To: udayabhanu1...@hotmail.com CC: kgri...@mail.com; adrian.kla...@gmail.com; pgsql-general@postgresql.org; pgadmin-supp...@postgresql.org; laurenz.a...@wien.gv.at; chris.trav...@gmail.com; mag...@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
Re: [GENERAL] Postgres case insensitive searches
On 6/29/2013 9:24 AM, bhanu udaya wrote: 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. you need an INDEX on lower(field) or upper(field). this is only computed when values are inserted. if you like a specific feature of SQL Server, then by all means, use SQL Server. postgres does not and will not support automatic case insensitive data. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
On 06/29/2013 09:24 AM, bhanu udaya wrote: Upper and Lower functions are not right choice when the table is 2.5 million and where we also have heavy insert transactions. Prove it. Seriously, just run a test case against it. See how it works for you. Inserts are generally a very inexpensive operation with Postgres. 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. Postgres does not. And as Jon said, maybe Postgres isn't the right solution for you. That would be a bummer but we can't be all things to all people. JD -- 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
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
Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches
I have a table called jobs with ~17 millions records. Without an index on the queue column, the following query select count(*) from jobs where lower(queue) = 'normal' found ~2.6 millions records in 10160ms With the following index: create index lower_queue on jobs (lower(queue)) the same query only took 3850ms On Sat, Jun 29, 2013 at 2:08 PM, Joshua D. Drake j...@commandprompt.comwrote: On 06/29/2013 09:24 AM, bhanu udaya wrote: Upper and Lower functions are not right choice when the table is 2.5 million and where we also have heavy insert transactions. Prove it. Seriously, just run a test case against it. See how it works for you. Inserts are generally a very inexpensive operation with Postgres. 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. Postgres does not. And as Jon said, maybe Postgres isn't the right solution for you. That would be a bummer but we can't be all things to all people. JD -- 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 -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.**orgpgadmin-supp...@postgresql.org ) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgadmin-supporthttp://www.postgresql.org/mailpref/pgadmin-support
[GENERAL] Postgres case insensitive searches
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. Thanks and Regards Radha Krishna
Re: [GENERAL] Postgres case insensitive searches
bhanu udaya wrote: What is the best way of doing case insensitive searches in postgres using Like. Table laurenz.t Column | Type | Modifiers +-+--- id | integer | not null val| text| not null Indexes: t_pkey PRIMARY KEY, btree (id) CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops); ANALYZE t; EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%'; QUERY PLAN -- Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4) Index Cond: ((upper(val) ~=~ 'AB'::text) AND (upper(val) ~~ 'AC'::text)) Filter: (upper(val) ~~ 'AB%'::text) (3 rows) Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
Thanks. But, I do not want to convert into upper and show the result. Example, if I have records as below: id type 1. abcd 2. Abcdef 3. ABcdefg 4. aaadf The below query should report all the above select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type collation POSIX, but it did not really help. Thanks and Regards Radha Krishna From: laurenz.a...@wien.gv.at To: udayabhanu1...@hotmail.com; pgsql-general@postgresql.org Subject: RE: Postgres case insensitive searches Date: Fri, 28 Jun 2013 12:32:00 + bhanu udaya wrote: What is the best way of doing case insensitive searches in postgres using Like. Table laurenz.t Column | Type | Modifiers +-+--- id | integer | not null val| text| not null Indexes: t_pkey PRIMARY KEY, btree (id) CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops); ANALYZE t; EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%'; QUERY PLAN -- Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4) Index Cond: ((upper(val) ~=~ 'AB'::text) AND (upper(val) ~~ 'AC'::text)) Filter: (upper(val) ~~ 'AB%'::text) (3 rows) Yours, Laurenz Albe
Re: [GENERAL] Postgres case insensitive searches
On 6/28/2013 6:59 PM, bhanu udaya wrote: select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type collation POSIX, but it did not really help. use ILIKE -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
