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

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


pgsql-admin by date

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

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