Re: LIKE search and performance

[Rigmor Ukuhe <rigmor(dot)ukuhe(at)finestmedia(dot)ee>]

Andy wrote:
> Hi,
>  
> I have a table with varchar and text columns, and I have to search 
> through these text in the whole table.
>  
> An example would be:
> SELECT * FROM table
>                              WHERE name like '%john%' or street like '%srt%'
>  
> Anyway, the query planner always does seq scan on the whole table and 
> that takes some time. How can this be optimized or made in another way 
> to be faster?

Use tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/) for full text indexing.

Rigmor

>  
> I tried to make indexes on the columns but no success.
>  
> PG 8.2
>  
> Regards,
> Andy.