tgrm index for word_similarity

["Igal @ Lucee.org" <igal@xxxxxxxxx>]

Hello,

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

Which seems to yield pretty good results, but takes over 40+ ms on a table that's not that large.

So 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:

QUERY PLAN                                                                                                       |
-----------------------------------------------------------------------------------------------------------------|
Sort  (cost=264.42..269.91 rows=2198 width=43) (actual time=41.060..41.117 rows=1044 loops=1)                    |
  Sort Key: items3_v.popularity, (('kandels'::text <<-> items3_v.name))                                          |
  Sort Method: quicksort  Memory: 149kB                                                                          |
  ->  Seq Scan on items3_v  (cost=0.00..142.41 rows=2198 width=43) (actual time=0.217..40.471 rows=1044 loops=1) |
        Filter: (word_similarity('kandels'::text, name) > '0.01'::double precision)                              |
        Rows Removed by Filter: 5550                                                                             |
Planning time: 0.149 ms                                                                                          |
Execution time: 41.308 ms                                                                                        |

What index would be good for that kind of query?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org