Re: efficiency of wildcards at both ends

Sam Z J wrote on 20.06.2012 19:10:
> Hi all
>
> I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
> How efficient is it if that's the only search criteria against a large table? how much does indexing the column help and roughly how much more space is needed for the index?
>
> if the answers are too long, please point me to the relavant text =D
>

Since 9.1 you can speed up such a query using a trigram index.

http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/
http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html

Another option might be to use the wildspeed extension

http://www.sai.msu.su/~megera/wiki/wildspeed

(never used that myself though)