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
