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 <http://lucee.org/>
