Re: efficiency of wildcards at both ends

On 6/20/2012 12:10 PM, Sam Z J wrote:
> 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
>
> thanks
>
> --
> Zhongshi (Sam) Jiang
> sammyjiang721@gmail.com <mailto:sammyjiang721@gmail.com>

An index will not be used for that kind of search.  PG will scan the
entire table to find matches.  PG can only use an index if you have a
search LIKE 'str%'

There are options like full text search, and pg_trgm that you might be
able to use.

-Andy