Re: String searching

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%';