Re: Working with huge amount of data.

On Feb 11, 2008, at 10:37 AM, Mario Lopez wrote:

> SELECT * FROM names WHERE name LIKE ‘keyword%’

If you use the C locale, PG can use an index for this query
> Or
> SELECT * FROM names WHERE name LIKE ‘%keyword%’
>

But not this one - substring searches are painful.

However, there is some hope - you can checkout the pg_trgm module
which provides trigrams and indexing methods so '%foo%'  type
searches can be done very quickly.

Things like google don't usually index substrings, just full words,
which makes life easier.  They may also use trigrams and other things
of that nature if they need to do subtrings (trigrams can also be
used to provide "Did you mean...." results.

--
Jeff Trout <jeff@jefftrout.com>
www.dellsmartexitin.com
www.stuarthamm.net