时间戳

首页 > 解决方案 > 多列上的 trigram 索引是否会使搜索更快以及如何正确进行此类搜索？

postgresql - 多列上的 trigram 索引是否会使搜索更快以及如何正确进行此类搜索？

问题描述

假设我有一个包含多列的表。例如：

id int
name text
surname text
cars json

示例记录将是

+----+------+---------+------------------------------------+
| id | name | surname |              cars                  |
+----+------+---------+------------------------------------+
|  1 | John | Doe     | {"values":["Ford", "BMW", "Fiat"]} |
+----+------+---------+------------------------------------+

我想搜索所有这些表数据的相关性，如下所示：

select *,
       similarity(
          'Malcolm Joe likes Ferrary, but hates BMW',
          (name || (cars ->> 'values') || surname)
       ) sim
from public.test_table
where similarity(
         'Malcolm Joe likes Ferrary, but hates BMW',
         (name || (cars ->> 'values') || surname)
      ) > 0.05
order by sim desc;

有什么方法可以加快搜索速度吗？创建三元索引？如果是这样 - 如何更好地创造它？在一列上，在每一列上，在连接表达式上？另外，我不明白哪种类型的索引更好——GIN 或 GiST。我读过 GIN 通常更适合常规全文搜索，但 GiST 更适合 trigram 搜索。那是对的吗？

我还想问是否有更好的方法来编写上述查询？

如果有人想知道我为什么选择 trigram 而不是常规的全文搜索 - 这是因为搜索字符串将来自处理一些用户输入，因此可能会出现错误，甚至可能会出现错误，甚至会出现英文“o”或“c”被西里尔字母替换的情况。我的数据库记录或搜索也可以包含字母数字数据，这也可以用三元组更好地处理。

标签： postgresqlpg-trgm

解决方案

在这种情况下，您需要一个 GiST 索引，因为只有它才能用于ORDER BY使用三元距离运算符的查询：

CREATE INDEX ON public.test_table USING gist
   ((name || (cars ->> 'values') || surname) gist_trgm_ops);

然后应将查询重写为：

SELECT *,
       similarity(
          'Malcolm Joe likes Ferrary, but hates BMW',
          (name || (cars ->> 'values') || surname)
       ) sim
FROM public.test_table
WHERE ((name || (cars ->> 'values') || surname)
       <->
       'Malcolm Joe likes Ferrary, but hates BMW')
      < 0.95
ORDER BY (name || (cars ->> 'values') || surname)
         <->   /* trigram distance */
         'Malcolm Joe likes Ferrary, but hates BMW'
LIMIT 50;

查询必须重写，因为索引支持<->，但不支持similarity()inORDER BY表达式。

我添加了LIMIT提示优化器，并酌情设置限制。

我认为通常 GIN 索引对于大型表的性能更好，但我不确定。无论如何，您对这个查询别无选择，因为 GIN 索引不支持该ORDER BY子句。

推荐阅读