Re: Optimizing a like-cause

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Stefan Sturm
Sent: Tuesday, July 22, 2008 11:31 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Optimizing a like-cause

Hello,

I'm developing a autocomplete Feature using php and PostgreSQL 8.3.
To fill the autocomplete box I use the following SQL Statement:
select * from _table_ where upper( _field_ ) like '%STRING%';

This SQL Statement takes 900 ms on a Table with 300.000 entries.

What can I do to speed up the Statement? What Index can I set?
>>
If you are searching for words, you could use tsearch2.
If you are searching for arbitrary fragments, an idea like this might
prove helpful:
http://kaiv.wordpress.com/2007/12/11/postgresql-substring-search/

What you are asking for is very difficult, because an ordinary index
won't help (you have a wildcard on the front) and an index on the
reversed word won't help either (you have a wildcard on the back).  So
the standard sort of techniques used to solve it are not perfectly on
target.
<<