Re: [ADMIN] how to speed ilike

2010-01-26 Thread Dimitri Fontaine 
Julius Tuskenis jul...@nsoft.lt writes:
 Tried that too, but seem to me that ILIKE doesn't use the index. I'm using
 username ilike '%blablabla%' , so maybe theres no way for ilike to benefit
 from an index.

See pg_trgm and Full Text Search.

  http://www.postgresql.org/docs/8.4/interactive/pgtrgm.html
  http://www.postgresql.org/docs/8.4/interactive/textsearch.html

Regards,
-- 
dim

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] how to speed ilike

2010-01-25 Thread Julius Tuskenis 

Hello

I have a task to make postgres find user records no matter if they are 
spelled correctly. In particular I have to find names with Lithuanian 
letters even if the user searches using latin letters. For example 
search criteria 'kestas' should find 'Kęstas'. I've made a function that 
converts lithuanian letters to latin and use it like fnk_latin(username) 
ILIKE fnk_latin('kestas'). It works OK.


Now the problem is performance. On test data base I have 2 records 
of users, and it takes 3 seconds to get result. On production database 
there could be a lot more. How would you advice to improve performance? 
Maybe some special index would help? As user names are update rarely 
it's the read speed I'm interested in.


--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] how to speed ilike

2010-01-25 Thread Kevin Grittner 
Julius Tuskenis  wrote:
 
 I've made a function that converts lithuanian letters to latin and
 use it like fnk_latin(username) ILIKE fnk_latin('kestas').
 
 Now the problem is performance.
 
 Maybe some special index would help?
 
create index tblname_username_latin on tblname
((fnk_latin(username)));
 
You might want to have that function force all letters to lowercase. 
It might also help to specify varchar_pattern_ops.

-Kevin

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] how to speed ilike

2010-01-25 Thread Julius Tuskenis 

Thank you for your answer Kevin.

create index tblname_username_latin on tblname
((fnk_latin(username)));
   

Tried this, but with no changes

You might want to have that function force all letters to lowercase.
   
Tried that too, but seem to me that ILIKE doesn't use the index. I'm 
using username ilike '%blablabla%' , so maybe theres no way for ilike to 
benefit from an index.

It might also help to specify varchar_pattern_ops.
   
I added varchar_pattern_ops to index declaration, but this didn't help 
either.


Do you have any other ideas?

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] how to speed ilike

2010-01-25 Thread Kenneth Marshall 
On Mon, Jan 25, 2010 at 05:33:10PM +0200, Julius Tuskenis wrote:
 Thank you for your answer Kevin.
 create index tblname_username_latin on tblname
 ((fnk_latin(username)));

 Tried this, but with no changes
 You might want to have that function force all letters to lowercase.

 Tried that too, but seem to me that ILIKE doesn't use the index. I'm using 
 username ilike '%blablabla%' , so maybe theres no way for ilike to benefit 
 from an index.

You cannot use an index for this search. It will work for 'blah%'
otherwise you need to use full-text indexes a la tsearch.

Cheers,
Ken

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin