Re: tgrm index for word_similarity

["Igal @ Lucee.org" <igal@xxxxxxxxx>]

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