Re: String searching

[Jonathan Vanasco <postgres@xxxxxxxx>]

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