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 <postg...@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.
> ...where name ilike '%bob%';
>
>
