Re: tgrm index for word_similarity

On 10/21/2017 5:01 AM, Arthur Zakirov wrote:
> 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;

Thank you, your solution does show that the index is used when I do
`explain analyze`, and makes the query finish in about 20ms so it's
about 1.5 - 2 times faster than without the index, but that raises a few
questions for me:

1) I thought that the whole idea behind indexes on expressions is that
the index would be used in a WHERE clause?  See
https://www.postgresql.org/docs/10/static/indexes-expressional.html - Am
I missing something?

2) A query with `WHERE input <% name` utilizes the index, but a query
without a WHERE clause at all does not?

3) What happens if I do not create an index at all?  Does the query that
I run in 30 - 40ms, the one that does not utilize an index, creates all
of the tri-grams on the fly each time that it runs?  Would it be
possible for me to create a TABLE or a VIEW with the tri-grams so that
there is no need to create them each time the query runs?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/>