Re: tgrm index for word_similarity

On Thu, Oct 19, 2017 at 04:54:19PM -0700, Igal @ Lucee.org wrote:
>
> 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
>

PostgreSQL doesn't use index scan with functions within WHERE clause. So
you always need to use operators instead. You can try <% operator and
pg_trgm.word_similarity_threshold variable:

=# SET pg_trgm.word_similarity_threshold TO 0.1;
=# SELECT name, popularity
FROM temp.items3_v
,(values ('some phrase'::text)) consts(input)
WHERE input <% name
ORDER BY 2, input <<-> name;

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company