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,