Re: [GENERAL] String searching

[Jonathan Vanasco]

On Nov 18, 2014, at 7:38 AM, Albe Laurenz wrote:
> 
> That index wouldn't help with the query at all.
> 
> If you really need a full substring search (i.e., you want to find
> "howardjohnson"), the only thing that could help are trigram indexes.

I stand corrected.  

I ran a sample query on my test database of 100k names

using a function index `lower(name)`

        this runs an index scan in .2ms
                ... where lower(name) = lower('bob');

        but this runs a sequential scan in 90ms:
                ... where lower(name) like lower('%bob%');

        I didn't know that 'like' doesn't run on indexes!

using a trigaram index, 

        this runs a bitmap index on the trigram, then a bitmap heap on the 
table.  13ms.
                ...where name ilike '%bob%';