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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
