全文搜索返回太多不相关的结果，导致性能差

Question

我正在使用Postgres的全文搜索功能，在大多数情况下它工作得很好。

我有一个列在我的数据库表称为documentFts，这基本上是body字段的ts_vector版本，这是一个文本列，并与GIN索引索引。

这是我的查询:

select
count(*) OVER() AS full_count,
id,
url, 
(("urlScore" / 100) + ts_rank("documentFts", websearch_to_tsquery($4, $1))) as "finalScore",
ts_headline('english_unaccent', title, websearch_to_tsquery($4, $1)) as title,
ts_headline('english_unaccent', body, websearch_to_tsquery($4, $1)) as body,
"possibleEncoding",
"responseYear"
from "Entries"
where 
"language" = $3 and 
"documentFts" @@ websearch_to_tsquery($4, $1)
order by (("urlScore" / 100) + ts_rank("documentFts", websearch_to_tsquery($4, $1))) desc limit 20 offset $2;

这个字典是english_unaccent，因为我创建了一个基于english的字典，它使用unaccent扩展:

CREATE TEXT SEARCH CONFIGURATION english_unaccent (
COPY = english
);
ALTER TEXT SEARCH CONFIGURATION english_unaccent
ALTER MAPPING FOR hword, hword_part, word WITH unaccent,
english_stem;

我对其他语言也做了同样的处理。

然后我对我的条目db:

这样做

ALTER TABLE "Entries"
ADD COLUMN "documentFts" tsvector;
UPDATE
"Entries"
SET
"documentFts" = (setweight(to_tsvector('english_unaccent', coalesce(title)), 'A') || setweight(to_tsvector('english_unaccent', coalesce(body)), 'C'))
WHERE
"language" = 'english';

我的表中有一列，其中包含条目的语言，因此是"language" = 'english'。

所以，我遇到的问题是，对于像animal,anime或animation这样的词，它们都作为anim进入向量，这意味着如果我搜索这些词中的任何一个，我得到的结果都是所有这些变化。

返回一个巨大的数据集，导致查询与返回较少项的搜索相比非常慢。同样，如果我搜索Anime，我的第一个结果包含Animal,Animated，并且第一个包含单词Anime的结果是第12个。

在矢量中，animation不应该被转换为animat,animal不应该被转换为animal，因为它的其他变体是animals或animalia吗?

我一直在寻找一个解决方案，没有太多的运气，有任何方法我可以改善这一点，我很高兴安装扩展，重新索引列或其他。

Answer 1

这里有很多小细节。最佳解决方案取决于具体情况和具体要求。

两个简单的选项:

简单调整1

如果您想对title或body中包含以'Anime'(完全)开头的单词(不区分大小写)的行进行排序，请添加ORDER BY表达式，如:

ORDER  BY unaccent(concat_ws(' ', title, body) !~* ('m' || f_regexp_escape($4))
, (("urlScore" / 100) + ts_rank("documentFts", websearch_to_tsquery($4, $1))) DESC

辅助函数f_regexp_escape()转义特殊的regexp字符，在这里定义:

• 正则表达式或LIKE模式的转义函数

该表达式的开销相当大，但由于它只应用于过滤后的结果，因此效果有限。您可能需要进行微调，因为其他搜索词会带来其他困难。想想'body'/'bodies'源于'body'…

简单调整2

要完全删除英语词干，请基于"简单"TEXT SEARCH CONFIGURATION:

CREATE TEXT SEARCH CONFIGURATION simple_unaccent (
COPY = simple
);

等。

那么文本的实际语言是不相关的。索引变得更大，搜索是根据字面拼写完成的。现在可以用前缀匹配来扩大搜索范围如:

WHERE  "documentFts" @@ to_tsquery('simple_unaccent', ($1 || ':*')

再一次，你需要微调。这个简单的例子只适用于单个单词的模式。我怀疑你是否想要完全摆脱词干。可能太激进了。

:

• 从GIN索引的TSVECTOR列中获取部分匹配

正确的解决方案:同义词字典

您需要访问Postgres服务器的安装驱动器。对于大多数托管服务来说，这通常是不可能的。

要否决一些词干决定，请使用您自己的同义词(规则)集来否决。在$SHAREDIR/tsearch_data/my_synonyms.syn中创建映射文件。这是/usr/share/postgresql/13/tsearch_data/my_synonyms.syn在我的Linux安装:

让它包含(默认不区分大小写):

anime anime

:

CREATE TEXT SEARCH DICTIONARY my_synonym (
TEMPLATE = synonym,
SYNONYMS = my_synonyms
);

手册中有一章说明。一个引用:

同义词词典可以用来克服语言问题，例如，防止英语词干词典将单词"Paris"略读为"pari"。在同义词字典中有Paris paris行并将其放在english_stem字典之前就足够了。

:

CREATE TEXT SEARCH CONFIGURATION my_english_unaccent (
COPY = english
);
ALTER TEXT SEARCH CONFIGURATION my_english_unaccent
ALTER MAPPING FOR hword, hword_part, word
WITH unaccent, my_synonym, english_stem;   -- added my_synonym!

你必须用my_english_unaccent更新你的列"documentFts"。在使用时，请使用适当的小写列名，如document_fts，并考虑GENERATED列。看到:

• PostgreSQL中的Computed/Computed/virtual/derived列
PostgreSQL列名是否区分大小写?

现在，搜索Anime(或ánime，就此而言)将不再找到animal。而搜索animal是找不到Anime的。