为什么Postgres Trigram word_similarity函数不使用gin索引? [英] Why is postgres trigram word_similarity function not using a gin index?

问题描述

postgres trigram文档指出:

The postgres trigram documentation states:

pg_trgm模块提供了GiST和GIN索引运算符类，允许您在文本列上创建索引，以实现非常快速的相似性搜索.这些索引类型支持上述相似性运算符，并且还支持针对LIKE，ILIKE，〜和〜*查询的基于Trigram的索引搜索.

The pg_trgm module provides GiST and GIN index operator classes that allow you to create an index over a text column for the purpose of very fast similarity searches. These index types support the above-described similarity operators, and additionally support trigram-based index searches for LIKE, ILIKE, ~ and ~* queries.

并显示以下示例:

SELECT t, word_similarity('word', t) AS sml
  FROM test_trgm
  WHERE 'word' <% t
  ORDER BY sml DESC, t;

太棒了！

但是，在运行以下查询时:

However, when running the following query:

SELECT * 
FROM place 
WHERE word_similarity(place.name, '__SOME_STRING__') > 0.5

未使用创建的索引.

但是，当使用 ILIKE 或％> 运算符时，似乎确实正在使用索引.为什么在 word_similarity 函数上不使用索引?

However, when using ILIKE or the %> operators, it does seem that the index is being used. Why is the index not used on the word_similarity function?

推荐答案

根据此

PostgreSQL不将索引扫描与WHERE子句中的函数一起使用.所以您始终需要使用运算符来代替.您可以尝试<％运算符和pg_trgm.word_similarity_threshold变量:

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;

=# SET pg_trgm.word_similarity_threshold TO 0.1;

=#SELECT名称，受欢迎程度FROM temp.items3_v，(值('某些短语'::文本))consts(输入)输入<％名称ORDER BY 2，输入<<->名称;

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

因此，查询可以更新为使用索引，如下所示:

So, the query can be updated to use the index as follows:

SET pg_trgm.word_similarity_threshold TO 0.1;
SELECT * 
FROM place 
WHERE place.name <<-> '__SOME_STRING__';

---

警告:操作员仅将索引与换向器对的一个版本一起使用.即，它仅在大小写为<-> 的情况下使用索引，而不在大小写为<->-> 的情况下使用索引.此堆栈溢出问题看起来它对原因给出了合理的解释:

---

Warning: the operator only uses the index with only one version of the commutator pair. I.e., it only used the index in the case <<-> and not the case <->>. This stack overflow q/a post looks like it gives a reasonable explanation as to why:

这些是不同的操作，索引仅支持其中之一.

These are different operations, and only one of them is supported by the index.

这篇关于为什么Postgres Trigram word_similarity函数不使用gin索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！