Re: 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?
>

The open-ended search is what's killing you. Can you change your
query to be like this?

select * from _table_ where _field_ like 'STRING%';

That allows the database to use an index. You'll still have to
either store the data already in upper-case format, or use a
functional index on upper(field).

http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html