Re: tgrm index for word_similarity

On 10/19/2017 4:54 PM, Igal @ Lucee.org wrote:
> I want to use Postgres for a fuzzy auto-suggest search field.  As the
> user will be typing their search phrase, Postgres will show a list of
> items that fuzzy-matches what they typed so far, ordered by popularity
> (ntile(20)) and distance, i.e. 1 - word_similarity().
>
> I created a Materialized View with two columns: name text, popularity int.
>
> My query at the moment is:
>
>     SELECT name, popularity
>     FROM   temp.items3_v
>           ,(values ('some phrase'::text)) consts(input)
>     WHERE  true
>         and word_similarity(input, name) > 0.01  -- be lenient as some
> names are 75 characters long and we want to match even on a few
> characters of input
>     ORDER BY 2, input <<-> name
>
> I tried to add a GIN trgm index on `name`:
>
>     CREATE INDEX temp_items3_v_tgrm_item_name ON temp.items3_v USING
> GIN(name gin_trgm_ops);
>
> But it is not used
>
> What index would be good for that kind of query?

I see that when I use LIKE or ILIKE the index is used, but I lose all of
the "fuzzy" benefits by doing that.

Is there any type of INDEX or even building my own COLUMN of trgm that
can help speed my word_similarity() results?  When used in auto-suggest
there are usually several queries for each user in a relatively short
period of time, so speed is important.

Thanks,

Igal