how to speed ilike

From: Julius Tuskenis <julius(at)nsoft(dot)lt>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: how to speed ilike
Date: 2010-01-25 12:23:09
Message-ID: 4B5D8D2D.1080807@nsoft.lt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2010-01-25 13:18:43 Re: how to speed ilike
Previous Message Michael Monnerie 2010-01-25 11:19:12 max_fsm_pages question