Re: how to speed ilike

From: Kenneth Marshall <ktm(at)rice(dot)edu>
To: Julius Tuskenis <julius(at)nsoft(dot)lt>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: how to speed ilike
Date: 2010-01-25 15:38:21
Message-ID: 20100125153821.GC1221@it.is.rice.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2010-01-25 16:28:23 Re: max_fsm_pages question
Previous Message Michael Monnerie 2010-01-25 15:35:15 Re: max_fsm_pages question