使用PostgreSQL創(chuàng)建高級搜索引擎的代碼示例
如果您想跟隨并嘗試示例查詢（我們建議這樣做，這樣更有趣），可以使用來自Kaggle的Wikipedia電影情節(jié)數(shù)據(jù)集執(zhí)行代碼示例。要導入它，請下載CSV文件，然后創(chuàng)建以下表格：
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;
該數(shù)據(jù)集包含 34,000 個電影標題，CSV 格式大小約為 81 MB。
PostgreSQL全文搜索原語
PostgreSQL的全文搜索方法提供了一些基礎組件，您可以將它們組合起來創(chuàng)建自己的搜索引擎。這種方法非常靈活，但也意味著與Elasticsearch、Typesense或Mellisearch等搜索引擎相比，它通常感覺更低級，因為全文搜索并非主要用例。
主要的基礎組件，我們將通過示例進行介紹，包括：
- tsvector和tsquery數(shù)據(jù)類型
- match運算符@@，用于檢查tsquery是否與tsvector匹配
- 用于對每個匹配進行排名的函數(shù)（ts_rank、ts_rank_cd）
- GIN索引類型，用于高效查詢tsvector的倒排索引
我們將從這些基礎組件開始，然后深入研究更高級的主題，包括相關性提升、容錯處理和分面搜索。
tsvector
tsvector數(shù)據(jù)類型存儲了一個排序后的詞元列表。詞元是一個字符串，就像一個標記，但它已被規(guī)范化，以便生成不同形式的同一個詞。例如，規(guī)范化通常包括將大寫字母轉換為小寫字母，并經常涉及去除后綴（例如英語中的s或ing）。下面是一個示例，使用to_tsvector函數(shù)將一個英語短語解析為tsvector。
'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)正如您所見，停用詞（例如"I"、"to"或"an"）被移除，因為它們在搜索中沒有太大用處。這些詞被規(guī)范化并縮減到它們的詞根形式（例如"refuse"和"Refusing"都被轉換為"refus"）。標點符號被忽略。對于每個詞，記錄了它在原始短語中的位置（例如"refus"是文本中的第12和第13個詞），以及權重（在后面我們將討論它們在排名中的用途）。
在上面的示例中，詞到詞元的轉換規(guī)則是基于英語搜索配置的。使用簡單搜索配置運行相同的查詢將導致包含所有單詞的tsvector，這些單詞與文本中找到的單詞一致。
'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)正如您所見，"refuse"和"refusing"現(xiàn)在生成了不同的詞元。簡單配置在包含標簽或標記的列中非常有用。
PostgreSQL內置了一套相當不錯的語言配置。您可以運行以下命令查看列表：
SELECT cfgname FROM pg_ts_config;
值得注意的是，目前沒有適用于CJK（中日韓）語言的配置，如果您需要在這些語言中創(chuàng)建搜索查詢，這一點值得記住。雖然簡單配置在實踐中對不支持的語言應該工作得很好，但我不確定對于CJK語言是否足夠。
tsquery tsquery數(shù)據(jù)類型用于表示規(guī)范化的查詢。tsquery包含搜索術語，這些術語必須是已經規(guī)范化的詞元，并且可以使用AND、OR、NOT和FOLLOWED BY等運算符組合多個術語。有一些函數(shù)（如to_tsquery、plainto_tsquery和websearch_to_tsquery）可幫助將用戶編寫的文本轉換為正確的tsquery，主要是通過對文本中出現(xiàn)的單詞進行規(guī)范化。
為了對tsquery有所了解，讓我們通過websearch_to_tsquery看幾個示例：
SELECT websearch_to_tsquery('english', 'the dark vader');
websearch_to_tsquery
----------------------
'dark' & 'vader'這是一個邏輯上的AND，意味著文檔需要同時包含“quick”和“dog”才能匹配。您也可以進行邏輯上的OR操作：
SELECT websearch_to_tsquery('english', 'quick OR dog');
websearch_to_tsquery
----------------------
'dark' | 'vader'您還可以排除某些單詞：
SELECT websearch_to_tsquery('english', 'dark vader -wars');
websearch_to_tsquery
---------------------------
'dark' & 'vader' & !'war'此外，您還可以表示短語搜索：
SELECT websearch_to_tsquery('english', '"the dark vader son"');
websearch_to_tsquery
------------------------------
'dark' <-> 'vader' <-> 'son'這意味著：“dark”后面是“vader”，然后是“son”。
然而，請注意，“the”一詞被忽略了，因為它是根據(jù)英文搜索配置的停用詞。這可能會在像這樣的短語中引發(fā)問題：
SELECT websearch_to_tsquery('english', '"do or do not, there is no try"');
websearch_to_tsquery
----------------------
'tri'
(1 row)糟糕，幾乎整個短語都消失了。使用簡單配置可以得到預期的結果：
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', 'dark vader') @@
to_tsvector('english',
'Dark Vader is my father.');
?
?column?
----------
t雖然下面的例子不匹配：
SELECT websearch_to_tsquery('english', 'dark vader -father') @@
to_tsvector('english',
'Dark Vader is my father.');
?
?column?
----------
fGIN
既然我們已經看到了 tsvector 和 tsquery 的工作原理，現(xiàn)在讓我們來看另一個關鍵構建塊：GIN 索引類型是使其快速運行的關鍵。GIN 代表廣義倒排索引（Generalized Inverted Index）。GIN 專門用于處理需要對復合值進行索引的情況，以及需要在索引中搜索出現(xiàn)在復合項內的元素值的查詢。這意味著 GIN 不僅可以用于文本搜索，還可以用于 JSON 查詢等其他用途。
您可以在一組列上創(chuàng)建 GIN 索引，或者您可以首先創(chuàng)建一個 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;然后創(chuàng)建實際的索引：
CREATE INDEX idx_search ON movies USING GIN(search);
現(xiàn)在您可以執(zhí)行如下簡單的搜索測試：
SELECT title FROM movies WHERE search @@ websearch_to_tsquery('english','dark 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)為了看到索引的效果，您可以比較上述查詢的計時情況，包括有索引和無索引的情況。在我的計算機上，使用GIN索引的時間從200毫秒左右減少到約4毫秒。
ts_rank
到目前為止，我們已經看到了如何使用ts_vector和ts_query來匹配搜索查詢。然而，為了獲得良好的搜索體驗，重要的是首先顯示最佳結果，這意味著結果需要按相關性進行排序。
直接從文檔中摘錄：
PostgreSQL提供了兩個預定義的排名函數(shù)，它們考慮了詞匯、接近度和結構信息；也就是說，它們考慮查詢詞在文檔中出現(xiàn)的頻率、詞項在文檔中的接近程度以及它們出現(xiàn)的文檔部分的重要性。然而，"相關性"的概念是模糊的，并且非常應用程序特定。不同的應用可能需要額外的信息來進行排名，例如文檔的修改時間。內置的排名函數(shù)只是示例。您可以編寫自己的排名函數(shù)和/或將它們的結果與其他因素結合起來，以適應您的特定需求。
這兩個提到的排名函數(shù)是ts_rank和ts_rank_cd。它們之間的區(qū)別在于，雖然它們都考慮了詞項的頻率，但ts_rank_cd還考慮了匹配詞項之間的接近程度。
要在查詢中使用它們，可以這樣做：
SELECT title,
ts_rank(search, websearch_to_tsquery('english', 'dark vader')) rank
FROM movies
WHERE search @@ websearch_to_tsquery('english','dark vader')
ORDER BY rank DESC
LIMIT 10;
title | rank
--------------------------------------------------+------------
Return of the Jedi | 0.21563873
Star Wars: Episode III – Revenge of the Sith | 0.12592985
Star Wars Episode IV: A New Hope (aka Star Wars) | 0.05174401關于ts_rank需要注意的一點是它需要訪問每個結果的搜索列。這意味著如果WHERE條件匹配了很多行，PostgreSQL需要訪問它們所有以進行排名，這可能會很慢。舉個例子，上面的查詢在我的計算機上返回時間為5-7毫秒。如果我修改查詢以搜索dark OR vader，返回時間約為80毫秒，因為現(xiàn)在有1000多個匹配結果需要進行排名和排序。
相關性調整
盡管基于詞頻的相關性對于搜索排序來說是一個很好的默認設置，但數(shù)據(jù)通常包含比簡單的頻率更重要的指標。
以下是一些電影數(shù)據(jù)集的示例：
- 標題中的匹配應該比描述或劇情中的匹配更重要。
- 更受歡迎的電影可以根據(jù)評級和/或收到的投票數(shù)進行推廣。
- 考慮到用戶偏好，某些類別可以得到更大的提升。例如，如果某個用戶喜歡喜劇片，那么這些電影可以優(yōu)先考慮。
- 在對搜索結果進行排名時，較新的標題可以被認為比非常老的標題更相關。
這就是為什么專用的搜索引擎通常提供使用不同的列或字段來影響排名的方法。這里是來自Elastic、Typesense和Meilisearch的調優(yōu)示例指南。
數(shù)字、日期和精確值增強器
雖然PostgreSQL沒有直接支持基于其他列進行提升的功能，但排名實際上只是一個排序表達式，因此您可以向其中添加自定義信號。
例如，如果您想根據(jù)投票數(shù)量添加提升，可以執(zhí)行以下操作：
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;對數(shù)函數(shù)用于平滑影響，而0.01因子使得提升與排名得分具有可比性。
您還可以設計更復雜的增強器，例如，只有在排名有一定數(shù)量的投票時才提升評級。為此，您可以創(chuàng)建以下函數(shù)：
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;讓我們再舉一個例子。假設我們想提高喜劇的排名。你可以創(chuàng)建一個類似下面的 valueBooster 函數(shù)：
create function valueBooster (col text, val text, factor integer) returns integer as $$ select case when col = val then factor else 0 end;$$ language sql;
如果列的值與特定值匹配，則該函數(shù)返回一個因子；否則返回 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 詞元可以附帶權重嗎？PostgreSQL 支持 4 種權重，它們分別是 A、B、C 和 D。A 是最高的權重，而 D 是最低的，默認權重。您可以通過 setweight 函數(shù)來控制權重，通常在構建 tsvector 列時調用該函數(shù)：
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，搜索 dark vader OR 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)容忍錯別字/模糊搜索
PostgreSQL 在使用 tsvector 和 tsquery 時不直接支持模糊搜索或容忍錯別字。然而，基于以下假設，我們可以實現(xiàn)以下思路：
- 在單獨的表中索引內容中的所有詞元
- 對查詢中的每個單詞，使用相似度或Levenshtein距離在此表中進行搜索
- 修改查詢以包括找到的任何單詞
- 執(zhí)行搜索
以下是其工作原理。首先，使用 ts_stats 獲取所有單詞并存儲在一個物化視圖中：
CREATE MATERLIAZED VIEW unique_lexeme AS
SELECT word FROM ts_stat('SELECT search FROM movies');現(xiàn)在，對于查詢中的每個單詞，檢查它是否在 unique_lexeme 視圖中。如果不存在，則在該視圖中進行模糊搜索，以找到可能的拼寫錯誤：
SELECT * FROM unique_lexeme WHERE levenshtein_less_equal(word, 'pregant', 2) < 2; word ---------- premant pregrant pregnant paegant
在上面的代碼中，我們使用了Levenshtein距離，因為這是像Elasticsearch這樣的搜索引擎在模糊搜索中使用的算法。
一旦你有了候選詞列表，你需要調整查詢以包含它們所有。
分面搜索
分面搜索在電子商務網(wǎng)站上很受歡迎，特別是因為它幫助客戶逐步縮小他們的搜索范圍。以下是來自amazon.com的一個示例：
上述方法可以通過手動定義分類并將其作為搜索的 WHERE 條件添加來實現(xiàn)。另一種方法是根據(jù)現(xiàn)有數(shù)據(jù)以算法方式創(chuàng)建分類。例如，您可以使用以下代碼創(chuàng)建一個“年代”分面：
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)這還提供了每個年代的匹配計數(shù)，您可以在括號中顯示出來。
如果您想在單個查詢中獲取多個分面，可以將它們組合起來，例如使用公共表表達式（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)上述方法在小到中等規(guī)模的數(shù)據(jù)集上應該能夠很好地工作，但在非常大的數(shù)據(jù)集上可能會變得較慢。
結論
我們已經了解了PostgreSQL的全文搜索基礎知識，以及如何將它們組合起來創(chuàng)建一個相當高級的全文搜索引擎，這個引擎還支持諸如連接和ACID事務等功能。換句話說，它具有其他搜索引擎通常沒有的功能。
作者：Tudor Golubenco
更多技術干貨請關注公號“云原生數(shù)據(jù)庫”
squids.cn，目前可體驗全網(wǎng)zui低價云數(shù)據(jù)庫RDS，免費的數(shù)據(jù)庫遷移工具DBMotion、備份工具、SQL開發(fā)工具等。
以上就是使用PostgreSQL創(chuàng)建高級搜索引擎的代碼示例的詳細內容，更多關于PostgreSQL創(chuàng)建高級搜索引擎的資料請關注腳本之家其它相關文章！
相關文章
PostgreSQL 數(shù)據(jù)庫性能提升的幾個方面
PostgreSQL提供了一些幫助提升性能的功能。主要有一些幾個方面。2009-09-09
解決PostgreSQL數(shù)據(jù)庫連接報錯:psql:?error:?FATAL:?password?authent
這篇文章主要給大家介紹了關于如何解決PostgreSQL數(shù)據(jù)庫連接報錯:psql:?error:?FATAL:?password?authentication?failed?for?user?"postgres"的相關資料,在使用PostgreSQL時,一些關鍵配置的錯誤可能導致數(shù)據(jù)庫無法正常啟動,需要的朋友可以參考下2024-05-05
PostgreSQL中json數(shù)據(jù)類型詳解
json數(shù)據(jù)也可以被存儲為text，但是 與text數(shù)據(jù)類型相比，JSON 數(shù)據(jù)類型的優(yōu)勢在于能強制要求每個被存儲的值符合 JSON 規(guī)則，這篇文章主要介紹了PostgreSQL中json數(shù)據(jù)類型,需要的朋友可以參考下2023-04-04
PostgreSQL是一個免費的對象-關系型數(shù)據(jù)庫服務器（ORDBMS)，遵循靈活的開源協(xié)議BSD。這篇文章主要介紹了PostgreSQL入門簡介,需要的朋友可以參考下2020-12-12
PostgreSQL連接數(shù)過多報錯：too many clients already的解決
在使用 Navicat 連接 PostgreSQL 數(shù)據(jù)庫時,突然遭遇到了一個報錯：“FATAL: sorry, too many clients already”,這一錯誤提示表明數(shù)據(jù)庫連接數(shù)已經達到上限,無法再創(chuàng)建新連接,所以本文給大家介紹了相關的解決辦法,需要的朋友可以參考下2024-03-03
PostgreSQL 使用raise函數(shù)打印字符串
這篇文章主要介紹了PostgreSQL 使用raise函數(shù)打印字符串，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgresSql 多表關聯(lián)刪除語句的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql數(shù)據(jù)庫導出和導入及常用的數(shù)據(jù)庫管理工具
數(shù)據(jù)庫的導入導出是最常用的功能之一,而PostgreSQL提供的對應工具為pg_dump和pg_restore。pg_dump是用于備份PostgreSQL數(shù)據(jù)庫的工具,下面這篇文章主要給大家介紹了關于postgresql數(shù)據(jù)庫導出和導入及常用的數(shù)據(jù)庫管理工具使用的相關資料,需要的朋友可以參考下2023-04-04
PostgreSQL數(shù)據(jù)庫性能調優(yōu)的注意點以及pg數(shù)據(jù)庫性能優(yōu)化方式
這篇文章主要介紹了PostgreSQL數(shù)據(jù)庫性能調優(yōu)的注意點以及pg數(shù)據(jù)庫性能優(yōu)化方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
這篇文章主要介紹了PostgreSQL 刪除check約束的實現(xiàn)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02