Thanks everyone.
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@xxxxxxxx> 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 namesusing 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%';
