Обсуждение: how to speed ilike

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

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

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

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

Julius Tuskenis <julius@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