Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2010-01-25 16:28:23
Subject: Re: max_fsm_pages question
Previous:From: Michael MonnerieDate: 2010-01-25 15:35:15
Subject: Re: max_fsm_pages question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group