Postgres 全文搜索：数据库中的搜索引擎

在我的 SQL 之旅的早期，我认为在数据库中搜索一段文本主要涉及这样的查询：如果我想获得更具体的信息，我会加入一些通配符运算符或正则表达式。后来，我与一位希望在应用程序中使用搜索功能的客户合作，因此“LIKE”和正则表达式不会削减它。我一直知道的只是模式匹配。对于某些目的它工作得非常好，但是当它不仅仅是检查单个文本字段中的简单模式时会发生什么？例如，如果您想跨多个字段进行搜索怎么办？即使搜索词碰巧拼错了，如何返回可能的匹配项？另外，如果您有大量数据要搜索怎么办？当然，您可以为要查询模式匹配的列创建索引，但这会有限制（例如，B 树索引不适用于 col LIKE '%substring%'）。因此，当我们说 PostgreSQL 是“包含电池的数据库”时，这只是原因之一。使用 Postgres，您无需立即寻找比您自己的数据库管理系统更远的全文搜索解决方案。如果您还没有尝试过 Postgres 的内置全文搜索，请继续阅读简单的介绍。词干分析，其中搜索匹配可以基于单词的“根”形式或词干（“run”匹配“runs”和“running”甚至“ran”）。权重和排名搜索匹配（因此最佳匹配可以排序到结果列表的顶部）。

文档是您要对其进行全文搜索的一组数据。在 Postgres 中，这可以从单个列或列的组合构建，甚至可以从多个表构建。文档被解析为标记，这些标记是来自文档文本的小片段（例如单词、短语等）。然后将标记转换为更有意义的文本单元，称为词素。在 Postgres 中，这种转换是通过字典完成的——有内置的字典，但如果需要，可以创建自定义字典。这些词典有助于确定应该被忽略的停用词，以及不同派生词是否具有相同的词干。大多数词典是针对特定语言（英语、德语等）的，但您也可以拥有针对特定领域的词典。文档中词素的排序列表存储在 tsvector 数据类型中。我有一个包含美国国家气象局收集的风暴事件数据的表。为简单起见，我不会在下面的语句中包含所有可能的字段，但此存储库中有数据的副本和一些进一步的信息可用。 CREATE TABLE se_details ( episode_id int, event_id int 主键, state text, event_type text, begin_date_time timestamp, episode_narrative text, event_narrative text, ...);假设我们要对 event_narrative 列上的数据进行全文搜索。我们可以在表中添加一个新列来存储预处理的搜索文档（即词素列表）：

ts 是一个生成的列（从 Postgres 12 开始），它会自动与源数据同步。 tsquery 是 Postgres 中的另一种全文搜索数据类型。它表示也已作为词素处理的搜索词，因此我们将输入词传递给 to_tsquery 函数，以优化我们的全文搜索查询。 （@@ 是一个匹配运算符。）我们通过这个查询得到的是“tornado”在文本字符串中某处的记录，但除此之外，结果集中还有一些记录，其中也有匹配"tornado" as lexeme ("tornado-like" and "tornadoes"): state |肯塔基州 begin_date_time | 2018-04-03 18:08:00 事件类型 |雷暴风事件_narrative |一股 1.5 英里宽的大风以每小时 95 英里的速度在肯塔基州埃德蒙斯的 259 号高速公路上造成龙卷风般的破坏。风在蜜蜂泉以北和以南延伸 3/4 英里，摧毁或严重损坏了几座小型附属建筑，撕毁了一个房屋的部分屋顶，连根拔起并折断了许多树木的树干，并折断了大约十几个电力极点。其他几所房屋的屋顶遭到破坏，许多建筑物的乙烯基壁板被风吹过。状态 |威斯康星州 begin_date_time | 2018-08-28 15:30:00 事件类型 |雷暴风事件_narrative |该县南部有大片大片的树木和农作物受损。树木和农作物的部分完全被夷为平地，并且由于倒下的树木或强烈的下击暴风造成了一些结构性损坏。多条道路因树木倒塌而封闭。两辆半卡车在 Waldo 的 57 号高速公路上翻车。广泛的风灾和龙卷风对许多房屋造成结构性损坏，70 所房屋受到影响，3 所房屋受到轻微损坏，2 所房屋受到严重损坏，1 所房屋被毁，2 家企业受到轻微损坏。将短语作为搜索词处理的一种方法是在 tsquery 中使用 & (AND) 或 <-> (FOLLOWED BY) 布尔运算符。搜索短语被标准化为“rain”和“debri”。只要 'rain' 和 'debri' 在文档中都有匹配项，顺序就无关紧要，例如这个例子：

大雨在饱和的山坡上引起的泥石流阻塞了与 97 号国道交叉口以南一英里处的奥马克河路。值是'rain' <2> 'debri'，这意味着它只会匹配'rain'后面紧跟着'debri'两个位置的地方，例如这里：大雨导致Coal Hollow Fire和Tank Hollow Fire上的泥石流烧伤疤痕。 （这实际上是唯一的匹配项，因此使用 <-> 运算符的限制要多一些。）phraseto_tsquery 函数也可以解析短语本身，并在词素之间插入 <N>，其中 N 是下一个词素的整数位置当从前一个计数时。与 to_tsquery 不同，此函数无法识别运算符；例如，我们可以像这样传递整个短语： tsquery 值是 'rain' <2> 'debri' 像上面一样，所以phraseto_tsquery 也考虑了定位。分配不同权重和排名的一个非常常见的用例是搜索文章。例如，您可能希望将文章标题和摘要或内容合并在一起进行搜索，但希望标题上的匹配被认为更相关并因此排名更高。

回到我们的风暴事件示例，我们的数据表除了 event_narrative 之外还有一个 episode_narrative 列。对于风暴数据，事件是单独类型的风暴事件（例如洪水、冰雹），而事件是整个风暴系统并且可能包含许多不同类型的事件。假设我们希望能够对事件和情节叙述进行全文搜索，但已决定事件叙述应该比情节叙述更重要。我们可以这样定义 ts 列： ALTER TABLE se_details ADD COLUMN ts tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(event_narrative, '')), 'A') || setweight(to_tsvector('english) ', 合并(episode_narrative, '')), 'B')) 存储; setweight 是一个全文函数，它为文档的组件分配一个权重。该函数采用字符“A”、“B”、“C”或“D”（按权重从大到小）。我们还在此处使用了合并，以便如果 episode_narrative 或 event_narrative 包含空值，则连接不会导致空值。然后，您可以在 ORDER BY 子句中使用 ts_rank 函数将结果从最相关到​​较少。状态 | MISSISSIPPI begin_date_time | 2018-04-06 22:18:00 事件类型 | Tornado event_narrative |这场龙卷风沿卢卡斯空心路在杰斐逊戴维斯-卡文顿县线附近降落。它继续向东南，越过县线。一些大树枝和树木在这个位置被折断并连根拔起。然后它再次穿过卢卡斯空心路，然后穿过伦纳德路。在这些位置的雷达上显示了龙卷风碎片特征。龙卷风连根拔起并折断了该地区的许多树木。它还在 Oakvale Road 翻倒了一辆小型拖拉机拖车，并对房屋造成了一些轻微的木瓦损坏。两次穿越奥克维尔路后，龙卷风在穿越 35 号高速公路之前升起。这次龙卷风中的最大风速为 105 英里/小时，总路径长度为 2.91 英里。最大路径宽度为 440 码。 episode_narrative | 4 月 6 日，一股暖锋席卷该地区。由于扰动沿着这条停滞不前的锋面而来，由于充足的水分，它为该地区带来了大量降雨。随着白天的供暖发生，一些风暴发展为该地区带来了恶劣的天气。与此相比，在episode_narrative 中匹配“tornado”但没有匹配event_narrative：

状态 |内布拉斯加州 begin_date_time | 2018-06-06 18:10:00 事件类型 |万岁 event_narrative |冰雹主要是便士大小的冰雹，还有一些四分之一大小的冰雹。episode_narrative | 6 月 6 日傍晚时分，内布拉斯加州狭长地带形成了强风暴。随着这一活动向东移动，出现了强到强雷暴的断线。据报道，冰雹达到乒乓球大小，雷暴风速达到每小时 70 英里，并有一次短暂的龙卷风触地得分。大雨还导致基思县西部发生山洪暴发。提示： ts_rank 返回一个浮点值，因此您可以在 SELECT 中包含该表达式以查看这些匹配项的得分情况。在我的情况下，密西西比事件的得分约为 0.890，内布拉斯加事件的得分为 0.243。通过实现突出显示结果等功能，或者编写自己的自定义词典或函数，您可以更深入地进行 Postgres 全文搜索。您还可以考虑启用扩展，例如 unaccent（从词素中删除变音符号）或 pg_trgm（用于模糊搜索）。说到扩展，那只是 Crunchy Bridge 支持的两个扩展。我们已经构建了我们的托管云 Postgres 服务，这样您就可以直接进入并利用所有这些 Postgres 功能。尽管如此：如您所见，您不需要非常复杂的设置即可开始。尝试一下您是否刚刚开始探索全文搜索解决方案，或者甚至只是重新评估是否需要全力以赴获得专用的全文搜索服务，特别是如果您的堆栈中已经有了 Postgres，这是一个好主意.公平地说，Postgres 没有一些可在 Elasticsearch 等平台上使用的搜索功能。但一个主要优点是您不必维护和同步单独的数据存储。如果您不太需要超大规模搜索，那么通过最小化依赖关系可能会有更多收获。另外，您已经知道的 Postgres 查询语法添加了一些新函数和运算符，可以让您走得更远。对使用 Postgres 进行全文搜索还有其他问题或想法吗？我们很高兴在@crunchydata 上听到他们的声音。