Either I'm not that smart or I am working on too many things at once (or both) but making Full Text work seems super tedious. I just have a single VARCHAR field for name, so the full name "William S. Burroughs" is a single row and column. I want to as simply as possible have the ability to search find this record with Will, will, Burr, burroughs, etc.
As far as I can tell, the trigram extension would be the easiest way to implement this. It looks like I wouldn't need to mess with vectors, etc. It would just look like a standard index and query, right? It seems that if I need something more powerful in the future that I could always move to ElasticSearch, Sphinx, or something similar.
Does this sound about right?
On Tue, Nov 18, 2014 at 8:01 AM, Jonathan Vanasco <postgres@2xlp.com> wrote:
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.
