这是博客迷你系列的第1部分，我们在其中探索PostgreSQL中的全文搜索功能，并研究我们可以复制多少典型搜索引擎功能。在第2部分中，我们将在PostgreSQL的全文搜索和Elasticsearch之间进行比较。
如果您想关注并尝试示例查询（我们建议；以这种方式更有趣），则代码示例将针对来自Kaggle的Wikipedia Movie Plots数据集执行。要导入它，请下载CSV文件，然后创建此表：
CREATE TABLE movies(
ReleaseYear int,
Title text,
Origin text,
Director text,
Casting text,
Genre text,
WikiPage text,
Plot text);
并以这样的方式导入CSV文件：
\COPY movies(ReleaseYear, Title, Origin, Director, Casting, Genre, WikiPage, Plot)
FROM 'wiki_movie_plots_deduped.csv' DELIMITER ',' CSV HEADER;
数据集包含34,000个电影标题，大约为CSV格式的81 MB。
PostgreSQL全文搜索原语
全文搜索的Postgres方法提供可以组合的构建块以创建自己的搜索引擎。这非常灵活，但这也意味着与诸如Elasticsearch，typesense或Mellisearch之类的搜索引擎相比，它通常会感觉到较低的水平，为此，全文搜索是主要用例。
。我们将通过示例介绍的主要构建块是：
-
tsvector和tsquery数据类型 - 匹配操作员
@@检查tsquery是否与tsvector匹配 - 函数对每个匹配（
ts_rank，ts_rank_cd）进行排名 - 杜松子酒索引类型，一种有效查询
tsvector的倒置索引
我们将首先查看这些构建块，然后将涉及更高级的主题，涵盖相关助推器，错字耐受性和方面的搜索。
TSVECTOR
tsvector数据类型存储 lexemes 的排序列表。 a lexeme 是一个字符串，就像一个令牌一样，但是已被归一化，因此可以制作出不同形式的同一单词。例如，归一化几乎总是包括将上箱字母折叠给下案例，并且通常涉及删除后缀（例如s或ing英语）。这是一个示例，使用to_tsvector函数将英语短语解析为tsvector。
SELECT * FROM unnest(to_tsvector('english',
'I''m going to make him an offer he can''t refuse. Refusing is not an option.'));
lexeme | positions | weights
--------+-----------+---------
go | {3} | {D}
m | {2} | {D}
make | {5} | {D}
offer | {8} | {D}
option | {17} | {D}
refus | {12,13} | {D,D}
(6 rows)
您可以看到，删除了'，to'或''之类的单词，因为它们太常见了，无法对搜索有用。这些词被标准化并简化为根源（例如，拒绝和拒绝都转化为拒绝）。标点符号被忽略。对于每个单词，记录了原始短语中的位置（例如，拒绝是文本中的第12个和第13个单词）和 strights （这对于排名很有用，我们将在后面讨论）。
）。）。在上面的示例中，从单词到 lexemes 的转换规则基于english搜索配置。使用simple搜索配置运行相同的查询在tsvector中导致包含文本中发现的所有单词：
SELECT * FROM unnest(to_tsvector('simple',
'I''m going to make him an offer he can''t refuse. Refusing is not an option.'));
lexeme | positions | weights
----------+-----------+---------
an | {7,16} | {D,D}
can | {10} | {D}
going | {3} | {D}
he | {9} | {D}
him | {6} | {D}
i | {1} | {D}
is | {14} | {D}
m | {2} | {D}
make | {5} | {D}
not | {15} | {D}
offer | {8} | {D}
option | {17} | {D}
refuse | {12} | {D}
refusing | {13} | {D}
t | {11} | {D}
to | {4} | {D}
(16 rows)
您可以看到，拒绝和拒绝产生不同的词汇。当您具有包含标签或标签的列时，simple配置特别有用。
PostgreSQL具有一组不错的语言的内置配置。您可以通过运行：
查看列表
SELECT cfgname FROM pg_ts_config;
值得注意的是，CJK（中文 - korean）没有配置，如果您需要用这些语言创建搜索查询，这是值得注意的。虽然simple配置在实践中应该很好地适用于不受支持的语言，但我不确定这是否足以用于CJK。
Tsquery
tsquery数据类型用于表示标准化查询。 tsquery包含搜索术语，必须已经是正态化的词汇，并且可以使用和，或者，或者不及操作员结合多个术语。诸如to_tsquery，plainto_tsquery和websearch_to_tsquery之类的函数有助于将用户写入的文本转换为适当的tsquery，主要是通过标准化文本中出现的单词。
要得到一种tsquery的感觉，让我们使用websearch_to_tsquery来查看一些示例：
SELECT websearch_to_tsquery('english', 'the darth vader');
websearch_to_tsquery
----------------------
'darth' & 'vader'
这是一个逻辑，这意味着文档需要同时包含“ darth”和“ vader”才能匹配。您可以做逻辑或也可以：
SELECT websearch_to_tsquery('english', 'darth OR vader');
websearch_to_tsquery
----------------------
'darth' | 'vader'
您可以排除单词：
SELECT websearch_to_tsquery('english', 'darth vader -wars');
websearch_to_tsquery
---------------------------
'darth' & 'vader' & !'war'
另外，您可以表示短语搜索：
SELECT websearch_to_tsquery('english', '"the darth vader son"');
websearch_to_tsquery
------------------------------
'darth' <-> 'vader' <-> 'son'
这意味着：darthâ，其次是vaderâ，其次是son。
但是，请注意，该单词被忽略了，因为根据english搜索配置，它是一个停止字。这可能是这样的短语的问题：SELECT websearch_to_tsquery('english', '"do or do not, there is no try"');
websearch_to_tsquery
----------------------
'tri'
(1 row)
糟糕，几乎整个短语都丢失了。使用simple配置给出了预期的结果：
SELECT websearch_to_tsquery('simple', '"do or do not, there is no try"');
websearch_to_tsquery
--------------------------------------------------------------------------
'do' <-> 'or' <-> 'do' <-> 'not' <-> 'there' <-> 'is' <-> 'no' <-> 'try'
您可以使用匹配操作员@@检查tsquery是否匹配tsvector。
SELECT websearch_to_tsquery('english', 'darth vader') @@
to_tsvector('english',
'Darth Vader is my father.');
?column?
----------
t
虽然以下示例不匹配：
SELECT websearch_to_tsquery('english', 'darth vader -father') @@
to_tsvector('english',
'Darth Vader is my father.');
?column?
----------
f
杜松子酒
现在，我们已经在工作中看到了tsvector和tsquery，让我们看一下另一个关键的构建块：杜松子酒索引类型是使其快速的原因。 GIN代表概括的倒置索引。 GIN专为处理索引的项目为复合值的案例而设计，并且索引需要处理的查询需要搜索复合项目中出现的元素值。这意味着杜松子酒不仅可以用于文本搜索，尤其是用于JSON查询。
您可以在一组列上创建杜松子酒索引，也可以首先创建类型tsvector的列以包含所有可搜索的列。这样的东西：
ALTER TABLE movies ADD search tsvector GENERATED ALWAYS AS
(to_tsvector('english', Title) || ' ' ||
to_tsvector('english', Plot) || ' ' ||
to_tsvector('simple', Director) || ' ' ||
to_tsvector('simple', Genre) || ' ' ||
to_tsvector('simple', Origin) || ' ' ||
to_tsvector('simple', Casting)
) STORED;
然后创建实际索引：
CREATE INDEX idx_search ON movies USING GIN(search);
您现在可以执行这样的简单测试搜索：
SELECT title FROM movies WHERE search @@ websearch_to_tsquery('english','darth vader');
title
--------------------------------------------------
Star Wars Episode IV: A New Hope (aka Star Wars)
Return of the Jedi
Star Wars: Episode III – Revenge of the Sith
(3 rows)
要查看索引的效果，您可以在有或没有索引的情况下比较上述查询的时间。杜松子酒指数将其从我的计算机上的200毫秒到大约4毫秒。
ts_rank
到目前为止，我们已经看到了ts_vector和ts_query如何匹配搜索查询。但是，要获得良好的搜索体验，首先要显示最佳结果很重要 - 这意味着结果需要通过相关性。
直接从docs拿起它：
PostgreSQL提供了两个预定义的排名函数，考虑了词汇，接近性和结构信息；也就是说，他们考虑查询术语出现在文档中的频率，文档中的术语在一起有多近，以及文档中的部分内容的重要性。但是，相关性的概念含糊不清，非常特定于应用。不同的应用程序可能需要其他信息以进行排名，例如文档修改时间。内置排名功能只是示例。您可以编写自己的排名功能和/或将其结果与适合您特定需求的其他因素相结合。
提到的两个排名函数是ts_rank和ts_rank_cd。它们之间的区别在于，尽管它们都考虑到了术语的频率，但ts_rank_cd还考虑了匹配词汇的接近性。
要在查询中使用它们，您可以做这样的事情：
SELECT title,
ts_rank(search, websearch_to_tsquery('english', 'darth vader')) rank
FROM movies
WHERE search @@ websearch_to_tsquery('english','darth vader')
ORDER BY rank DESC
LIMIT 10;
title | rank
--------------------------------------------------+-------------
The Empire Strikes Back | 0.26263964
Star Wars Episode IV: A New Hope (aka Star Wars) | 0.18902963
Star Wars: Episode III – Revenge of the Sith | 0.10292397
Rogue One: A Star Wars Story (film) | 0.10049681
Return of the Jedi | 0.09910346
American Honey | 0.09910322
关于ts_rank的一件事是，它需要为每个结果访问search列。这意味着，如果WHERE条件与许多行匹配，那么PostgreSQL需要访问所有行才能进行排名，这可能很慢。为了举例说明，上面的查询在我的计算机上以5-7毫秒返回。如果我修改要搜索darth OR vader的查询，它将在大约80毫秒内返回，因为现在有1000多个匹配结果需要排名和排序。
相关调整
虽然基于单词频率的相关性是搜索排序的良好默认值，但通常，数据包含重要指标，这些指标比频率更相关。
这是电影数据集的一些示例：
- 标题中的匹配应比描述或情节中的匹配项更高。
- 可以根据收到的评分和/或收到的选票数来推广更多受欢迎的电影。
- 考虑用户偏好，可以增加某些类别。例如，如果特定用户喜欢喜剧，那么这些电影可以得到更高的优先级。
- 在排名搜索结果时，可以将较新的标题比非常旧的标题更重要。
这就是为什么专用搜索引擎通常提供使用不同列或字段来影响排名的方法的原因。这是Elastic，Typesense和Meilisearch的示例调整指南。
如果您想对相关调整的影响进行视觉演示，这是一个快速的4分钟视频：
数字，日期和确切的价值助推器
虽然Postgres没有直接支持基于其他列的提升，但排名最终只是一个排序表达式，因此您可以向其添加自己的信号。
例如，如果要为投票数添加提升，则可以做这样的事情：
SELECT title,
ts_rank(search, websearch_to_tsquery('english', 'jedi'))
-- numeric booster example
+ log(NumberOfVotes)*0.01
FROM movies
WHERE search @@ websearch_to_tsquery('english','jedi')
ORDER BY rank DESC LIMIT 10;
对数可以使冲击平滑，而0.01因子将助推器带到了与排名分数的相当规模。
您还可以设计更复杂的助推器，例如，通过评级来提升，但前提是排名有一定数量的选票。为此，您可以创建这样的函数：
create function numericBooster(rating numeric, votes numeric, voteThreshold numeric)
returns numeric as $$
select case when votes < voteThreshold then 0 else rating end;
$$ language sql;
并这样使用：
SELECT title,
ts_rank(search, websearch_to_tsquery('english', 'jedi'))
-- numeric booster example
+ numericBooster(Rating, NumberOfVotes, 100)*0.005
FROM movies
WHERE search @@ websearch_to_tsquery('english','jedi')
ORDER BY rank DESC LIMIT 10;
让我们以另一个例子为例。说我们想提高喜剧的排名。您可以创建一个看起来像这样的valueBooster函数：
create function valueBooster (col text, val text, factor integer)
returns integer as $$
select case when col = val then factor else 0 end;
$$ language sql;
如果列匹配特定值，则该函数将返回一个因子，而0则返回。在这样的查询中使用它：
SELECT title, genre,
ts_rank(search, websearch_to_tsquery('english', 'jedi'))
-- value booster example
+ valueBooster(Genre, 'comedy', 0.05) rank
FROM movies
WHERE search @@ websearch_to_tsquery('english','jedi') ORDER BY rank DESC LIMIT 10;
title | genre | rank
--------------------------------------------------+------------------------------------+---------------------
The Men Who Stare at Goats | comedy | 0.1107927106320858
Clerks | comedy | 0.1107927106320858
Star Wars: The Clone Wars | animation | 0.09513916820287704
Star Wars: Episode I – The Phantom Menace 3D | sci-fi | 0.09471701085567474
Star Wars: Episode I – The Phantom Menace | space opera | 0.09471701085567474
Star Wars: Episode II – Attack of the Clones | science fiction | 0.09285612404346466
Star Wars: Episode III – Revenge of the Sith | science fiction, action | 0.09285612404346466
Star Wars: The Last Jedi | action, adventure, fantasy, sci-fi | 0.0889768898487091
Return of the Jedi | science fiction | 0.07599088549613953
Star Wars Episode IV: A New Hope (aka Star Wars) | science fiction | 0.07599088549613953
(10 rows)
柱重量
还记得当我们谈论tsvector lexemes时，它们可以附上重量吗？ Postgres支持4个权重，名为A，B，C和D。A是最大的权重，而D是最低和默认值。您可以通过setweight函数来控制权重，通常在构建tsvector列时通常会调用：
ALTER TABLE movies ADD search tsvector GENERATED ALWAYS AS
(setweight(to_tsvector('english', Title), 'A') || ' ' ||
to_tsvector('english', Plot) || ' ' ||
to_tsvector('simple', Director) || ' ' ||
to_tsvector('simple', Genre) || ' ' ||
to_tsvector('simple', Origin) || ' ' ||
to_tsvector('simple', Casting)
) STORED;
让我们看看它的影响。没有setweight，搜索jedi返回：
SELECT title, ts_rank(search, websearch_to_tsquery('english', 'jedi')) rank
FROM movies
WHERE search @@ websearch_to_tsquery('english','jedi')
ORDER BY rank DESC;
title | rank
--------------------------------------------------+-------------
Star Wars: The Clone Wars | 0.09513917
Star Wars: Episode I – The Phantom Menace | 0.09471701
Star Wars: Episode I – The Phantom Menace 3D | 0.09471701
Star Wars: Episode III – Revenge of the Sith | 0.092856124
Star Wars: Episode II – Attack of the Clones | 0.092856124
Star Wars: The Last Jedi | 0.08897689
Return of the Jedi | 0.075990885
Star Wars Episode IV: A New Hope (aka Star Wars) | 0.075990885
Clerks | 0.06079271
The Empire Strikes Back | 0.06079271
The Men Who Stare at Goats | 0.06079271
How to Deal | 0.06079271
(12 rows)
和标题列中的setweight：
SELECT title, ts_rank(search, websearch_to_tsquery('english', 'jedi')) rank
FROM movies
WHERE search @@ websearch_to_tsquery('english','jedi')
ORDER BY rank DESC;
title | rank
--------------------------------------------------+-------------
Star Wars: The Last Jedi | 0.6361112
Return of the Jedi | 0.6231253
Star Wars: The Clone Wars | 0.09513917
Star Wars: Episode I – The Phantom Menace | 0.09471701
Star Wars: Episode I – The Phantom Menace 3D | 0.09471701
Star Wars: Episode III – Revenge of the Sith | 0.092856124
Star Wars: Episode II – Attack of the Clones | 0.092856124
Star Wars Episode IV: A New Hope (aka Star Wars) | 0.075990885
The Empire Strikes Back | 0.06079271
Clerks | 0.06079271
The Men Who Stare at Goats | 0.06079271
How to Deal | 0.06079271
(12 rows)
请注意，与Jedi的名字的电影标题如何跳到榜首，他们的等级也有所提高。
值得指出的是，只有四个体重是有些限制的，并且在计算tsvector时需要应用它们。
类型容忍 /模糊搜索
使用tsvector和tsquery时，PostgreSQL不直接支持模糊搜索或错字。但是，处理错误的假设在查询部分中，我们可以实现以下想法：
- 索引所有 lexemes 来自单独表中的内容
- 对于查询中的每个单词，使用相似性或Levenshtein距离在此表中搜索
- 修改查询以包含任何发现的单词
- 执行搜索
这是它的工作原理。首先，使用ts_stats在实现的视图中获取所有单词：
CREATE MATERIALIZED VIEW unique_lexeme AS
SELECT word FROM ts_stat('SELECT search FROM movies');
现在，对于查询中的每个单词，请检查它是否在unique_lexeme视图中。如果不是，请在该观点中进行模糊搜索以找到可能的拼写错误：
SELECT * FROM unique_lexeme
WHERE levenshtein_less_equal(word, 'pregant', 2) < 2;
word
----------
premant
pregrant
pregnant
paegant
在上面我们使用Levenshtein distance，因为这是搜索引擎（例如elasticsearch for fozzy搜索）的搜索。
一旦您拥有候选单词列表，就需要调整查询包括所有内容。
面搜索
faceted搜索很受欢迎，尤其是在电子商务网站上，因为它可以帮助客户迭代地缩小搜索范围。这是Amazon.com的一个示例：
可以通过手动定义类别来实现以上，然后将它们作为WHERE条件添加到搜索中。另一种方法是基于现有数据来创建类别算法。例如，您可以使用以下内容创建十年的方面：
SELECT ReleaseYear/10*10 decade, count(Title) cnt FROM movies
WHERE search @@ websearch_to_tsquery('english','star wars')
GROUP BY decade ORDER BY cnt DESC;
decade | cnt
--------+-----
2000 | 39
2010 | 31
1990 | 29
1950 | 28
1940 | 26
1980 | 22
1930 | 13
1960 | 11
1970 | 7
1910 | 3
1920 | 3
(11 rows)
这还提供了每个十年的匹配项，您可以在括号中显示。
如果要在单个查询中获取多个方面，则可以使用CTES组合：
WITH releaseYearFacets AS (
SELECT 'Decade' facet, (ReleaseYear/10*10)::text val, count(Title) cnt
FROM movies
WHERE search @@ websearch_to_tsquery('english','star wars')
GROUP BY val ORDER BY cnt DESC),
genreFacets AS (
SELECT 'Genre' facet, Genre val, count(Title) cnt FROM movies
WHERE search @@ websearch_to_tsquery('english','star wars')
GROUP BY val ORDER BY cnt DESC LIMIT 5)
SELECT * FROM releaseYearFacets UNION SELECT * FROM genreFacets;
facet | val | cnt
--------+---------+-----
Decade | 1910 | 3
Decade | 1920 | 3
Decade | 1930 | 13
Decade | 1940 | 26
Decade | 1950 | 28
Decade | 1960 | 11
Decade | 1970 | 7
Decade | 1980 | 22
Decade | 1990 | 29
Decade | 2000 | 39
Decade | 2010 | 31
Genre | comedy | 21
Genre | drama | 35
Genre | musical | 9
Genre | unknown | 13
Genre | war | 15
(16 rows)
上面的数据集应很好地工作，但是在非常大的数据集上可能会变得慢。
结论
我们已经看到了PostgreSQL全文搜索原始图，以及如何将它们组合起来创建一个非常高级的全文搜索引擎，这也恰好支持加入和酸性交易之类的东西。换句话说，它具有其他搜索引擎通常没有的功能。
有更多高级搜索主题值得详细介绍：
- 建议者 /自动完成< / li>
- 确切的短语匹配
- 混合搜索（语义 +关键字）与PG-vector结合
每个中的每一个都值得自己的博客文章（来临！），但是到目前为止，您应该对它们有一种直观的感觉：使用PostgreSQL，它们很有可能，但是它们要求您完成将原始人结合起来的工作，在某些情况下，性能可能会在很大的数据集中受苦。
在第2部分中，我们将与Elasticsearch进行详细的比较，以回答有关何时将搜索实施到PostgreSQL中，而不是将Elasticsearch添加到基础架构并同步数据是值得的。如果您想在发布时被通知，可以在Twitter上关注我们或加入我们的Discord。