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/>
