Re: Optimizing a like-cause

"Dann Corbit" <DCorbit(at)connx(dot)com> writes:
>>> 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 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).

Actually ... if the usage is autocompletion, why in the world is the
OP searching for '%STRING%' and not 'STRING%'? If I type "bar<TAB>",
I certainly don't expect to be offered "foobar" as one of the possible
completions.

This makes a difference since an anchored-left pattern *can* be searched
for using a standard index ...

regards, tom lane