Re: how to speed ilike

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