postgresql - 使用Postgresql全文搜索模糊匹配所有搜索词

Question

我有 2 个表(项目和任务)，它们都包含一个名称字段。我希望用户在输入新项目时能够同时搜索两个表。我想根据输入的所有术语对结果进行排名。用户应该能够按照他/她选择的任何顺序输入文本。

例如，搜索:

office bmt

应该产生这些结果:

PR BMT Time - Office
BMT Office - Development
BMT Office - Development
...

以下搜索也应该有效:

BMT canter

应该包含这个结果:

Canterburry - BMT time

所以部分匹配也需要起作用。

理想情况下，如果用户输入一个小错误，例如:

办公室 bmt

结果应该仍然出现。

我现在使用这样的东西:

where to_tsvector(projects.name || ' - ' || tasks.name) @@ to_tsquery('OFF:*&BMT:*')

我通过根据空格拆分用户条目，在 Ruby 后端构建搜索字符串本身。

这工作正常，但在某些情况下它不能，我相信这是因为它像英语一样解释它并忽略了一些词，如 of、off、in 等......

例如搜索:

off bmt

给出完全不包含 Off 的结果，因为 off 被完全忽略了。

有没有办法避免这种情况但仍然具有良好的性能和模糊搜索？我不喜欢为此将我的 PG 与 ElasticSearch 同步。

我可以通过在带有 LIKE '% ... %' 的 WHERE 子句中构建一个 AND 语句列表来做到这一点，但这可能会影响性能并且不支持模糊搜索。

Answer 1

Ideally if the user would type a small error like: ofice bmt The results should still appear.

除了尽最大努力之外，这可能很难做到。如果有人输入“Canter”，系统如何知道他们是指 Canterburry 的缩写，还是“cancer”或“cantor”的拼写错误，或者他们是否真的意味着马的步态？也许您可以为您的特定领域创建一个常见拼写错误的字典？此外，如果没有具体了解时区是预期的和常见的，“bmt”似乎是某种东西的拼写错误。

This works fine, however in some cases it doesn't and I believe that's because it interprets it like English and ignores some words like of, off, in, etc...

不要只是相信，检查看看!

select to_tsquery('english','OFF:*&BMT:*');
 to_tsquery 
------------
 'bmt':*

是的，to_tsquery 确实省略了停用词，即使是 :* 东西也是如此。

一种选择是使用“简单”而不是“英语”作为您的配置:

select to_tsquery('simple','OFF:*&BMT:*');
    to_tsquery     
-------------------
 'off':* & 'bmt':*

另一种选择是直接写tsquery而不是通过to_tsquery处理。请注意，在这种情况下，您必须自己将其小写:

select 'off:*&bmt:*'::tsquery;
      tsquery      
-------------------
 'off':* & 'bmt':*

另请注意，如果您使用“office:*”执行此操作，您将永远不会在“english”配置中获得匹配项，因为文档中的“office”被词干化为“offic”，而当您写'办公室:*'::tsquery。因此，您可以使用“简单”而不是“英语”来避免词干和停用词。或者，您可以单独测试查询中的每个词，看看它是否被词干化，然后再决定向其中添加 :*。

Is there a way to avoid this but still have good performance and fuzzy search? I'm not keen on having to sync my PG with ElasticSearch for this.

模糊搜索是什么意思？您现在似乎没有使用它。您只是在使用前缀匹配，不小心使用了词干和停用词。你要搜索多大的表，什么样的性能是可以接受的？

如果您使用 ElasticSearch，您会如何表达您的搜索？如果您解释了如何在 ES 中进行搜索，也许有人可以帮助您在 PostgreSQL 中做同样的事情。我不认为我们可以认为切换到 ES 会神奇地做正确的事情。

I could do it by building a list of AND statements in the WHERE clause with LIKE '% ... %' but that would probably hurt performance and doesn't support fuzzysearch.

你看过pg_trgm了吗？ ？它可以使这些类型的查询非常快。此外， LIKE '%...%' 比你目前正在做的要模糊得多，所以我不明白你将如何失去它。 pg_trgm 还提供了 '<->' 运算符，它更加模糊，可能是您最好的选择。当嵌入长字符串时，它可以很好地处理拼写错误，但在短字符串中它们确实是个问题。