From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: String searching |
Date: | 2014-11-18 16:49:37 |
Message-ID: | CAAXGW-yytsfSa0Cord8fGpC+S2zXvghOWOQ4ERTgAphSDf1NoA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)2xlp(dot)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%';
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Vanasco | 2014-11-18 16:59:39 | Re: String searching |
Previous Message | Jonathan Vanasco | 2014-11-18 16:01:45 | Re: String searching |