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