Postgres全文搜索功能

来源:https://www.cnblogs.com/ohbonsai/archive/2019/02/18/postgres_fulltext_search.html
-Advertisement-
Play Games

當構建一個Web應用時，經常被要求加上搜索功能。其實有時候我們也不知道我要搜索個啥，反正就是要有這個功能。搜索確實很重要的特性，這也是為什麼像Elasticsearch和Solr這樣基於Lucene的資料庫越來越流行。這兩個是好用，但是在構建Web應用時，有時候感覺像是殺雞用牛刀。所以我們需要選擇輕 ...


當構建一個Web應用時，經常被要求加上搜索功能。其實有時候我們也不知道我要搜索個啥，反正就是要有這個功能。搜索確實很重要的特性，這也是為什麼像Elasticsearch和Solr這樣基於Lucene的資料庫越來越流行。這兩個是好用，但是在構建Web應用時，有時候感覺像是殺雞用牛刀。所以我們需要選擇輕量好用的東西來搜索。

如何定義足夠好用？ 足夠好用的搜索引擎需要有以下幾點特征：

  • Stemming 關鍵詞，詞乾
  • Ranking/Boost 排名和權重分配
  • 多語言支持
  • 錯誤糾正/模糊查詢
  • 口音支持 類似我們的一二三四聲

非常幸運，PostgreSql支持以上所有特征。
這篇文章旨在幫助以下的人群：

  • 用了PG資料庫，不想再加一個搜索引擎
  • 用了其他資料庫，想有一個更好的全文檢索體驗

在這片文章，我們將基於以下表和數據逐步的闡述PG中的全文搜索功能。

CREATE TABLE author(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL
);

CREATE TABLE post(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   author_id INT NOT NULL references author(id) 
);

CREATE TABLE tag(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL 
);

CREATE TABLE posts_tags(
   post_id INT NOT NULL references post(id),
   tag_id INT NOT NULL references tag(id)
 );

INSERT INTO author (id, name) 
VALUES (1, 'Pete Graham'), 
       (2, 'Rachid Belaid'), 
       (3, 'Robert Berry');

INSERT INTO tag (id, name) 
VALUES (1, 'scifi'), 
       (2, 'politics'), 
       (3, 'science');

INSERT INTO post (id, title, content, author_id) 
VALUES (1, 'Endangered species', 
        'Pandas are an endangered species', 1 ), 
       (2, 'Freedom of Speech', 
        'Freedom of speech is a necessary right', 2), 
       (3, 'Star Wars vs Star Trek', 
        'Few words from a big fan', 3);


INSERT INTO posts_tags (post_id, tag_id) 
VALUES (1, 3), 
       (2, 2), 
       (3, 1);

以上是一個比較典型的Blog類型的資料庫。有post, post有title和content。post通過外鍵與author建立關係。post也有自己的多個tag。

什麼是Full-Text Search全文檢索

首先讓我們看看定義

In text retrieval, full-text search refers to techniques for searching a single computer-stored document or a collection in a full-text database. The full-text search is distinguished from searches based on metadata or on parts of the original texts represented in databases.
-- Wikipedia

這段定義指出了一個重要的概念-document。當你執行一個查詢的時候，你實際上是為了尋找一些具有實際含義的實體。那些就是documents。PostgreSQL文檔解釋的有點耐人尋味

A document is the unit of searching in a full-text search system; for example, a magazine article or email message.
-- Postgres documentation

document 能夠跨越多個表，它代表的是一個邏輯上的獨立個體。

建立我們的document

在上一節我們介紹了document的概念，document和我們的表結構沒關係，它只和數據有關係。以我們的資料庫為例，一個document可以有以下幾個field組成：

  • post.title
  • post.content
  • 該post的author.name
  • 所有與該post相關的tag.name
    通過以下查詢，我們可以建立一個document的二維表數據
SELECT post.title || ' ' || 
       post.content || ' ' ||
       author.name || ' ' ||
       coalesce((string_agg(tag.name, ' ')), '') as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id;

               document
 --------------------------------------------------
Endangered species Pandas are an endangered species Pete Graham politics
Freedom of Speech Freedom of speech is a necessary right Rachid Belaid politics
Star Wars vs Star Trek Few words from a big fan Robert Berry politics

在查詢語句中，我們通過post和author進行group， 通過string_agg對tag.name進行聚合。同時我們用了coalesce來防止聚合之後tag出現null。
此時，我們的document是一個簡單的長字元串，當然對我們起不到什麼作用。我們需要通過to_tsvector()對這個長字元串操作一頓。

SELECT to_tsvector(post.title) || 
       to_tsvector(post.content) ||
       to_tsvector(author.name) ||
       to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id;
           
               document
 --------------------------------------------------
 'endang':1,6 'graham':9 'panda':3 'pete':8 'polit':10 'speci':2,7
 'belaid':12 'freedom':1,4  'necessari':9 'polit':13 'rachid':11 'right':10 'speech':3,6
 'berri':13 'big':10 'fan':11 'polit':14 'robert':12 'star':1,4 'trek':5 'vs':3 'war':2 'word':7
(3 rows)

這個查詢以tsvector的形式回傳我們的document，這種格式特別適合來做full-text search。讓我們在試一下將一個簡單的字元串轉化成tsvector類型

SELECT to_tsvector('Try not to become a man of success, but rather try to become a man of value');

query will return the following result:

                             to_tsvector
----------------------------------------------------------------------
'becom':4,13 'man':6,15 'rather':10 'success':8 'tri':1,11 'valu':17
(1 row)

見證奇跡的時刻到啦。 首先有一些單詞是來自於原句子的，但是有一些單詞變化了(try變成了tri)。同時所有單詞後面都帶了數字，為啥子?

一個tsvector是由一組不同的詞元組成。詞元是分詞之後的變種，就是被操作過了。操作的專業名詞叫做normalization。這波操作主包含大小寫轉化，尾碼移除等等。搜索意味著要面對多重選項，減少一些選項便於引擎去搜搜。單詞後面帶的數字表示詞元原來在的位置。比如man就是在6和15。您可以自個兒數數對不對。

預設情況下，Postgres在to_tsvector中用的是english的配置。它會直接忽略非英文的單詞。

Querying

現在，我們知道如何構建一個文檔。但是我們的目的是為了搜索。我們用@@來對tsvector的數據進行搜索。官方文檔走一波。讓我們看看一些例子：

> select to_tsvector('If you can dream it, you can do it') @@ 'dream';
 ?column?
----------
 f
(1 row)

> select to_tsvector('It''s kind of fun to do the impossible') @@ 'impossible';

 ?column?
----------
 t
(1 row)

上面主要是簡單的查一下是否存在這個關鍵詞。接下去的例子可以展示強制類型轉化和to_tsquery這個函數的區別。

SELECT 'impossible'::tsquery, to_tsquery('impossible');
   tsquery    | to_tsquery
--------------+------------
 'impossible' | 'imposs'
(1 row)

to_tsquery函數用於寫一些簡單的搜索語句。支持布爾操作符&(AND), |(OR), !(NOT)。在to_tsquery也可以用進行表達式優先順序的調整。

> SELECT to_tsvector('If the facts don't fit the theory, change the facts') @@ to_tsquery('! fact');

 ?column?
----------
 f
(1 row)

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts') @@ to_tsquery('theory & !fact');

 ?column?
----------
 f
(1 row)

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('fiction | theory');

 ?column?
----------
 t
(1 row)

同時可以用:*進行startwith搜索。

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('theo:*');

 ?column?
----------
 t
(1 row)

現在我們知道如何做一個full-text search。我們回到我們的資料庫嘗試對我們文檔進行查詢。

SELECT pid, p_title
FROM (SELECT post.id as pid,
             post.title as p_title,
             to_tsvector(post.title) || 
             to_tsvector(post.content) ||
             to_tsvector(author.name) ||
             to_tsvector(coalesce(string_agg(tag.name, ' '))) as document
      FROM post
      JOIN author ON author.id = post.author_id
      JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
      JOIN tag ON tag.id = posts_tags.tag_id
      GROUP BY post.id, author.id) p_search
WHERE p_search.document @@ to_tsquery('Endangered & Species');

 pid |      p_title
-----+--------------------
   1 | Endangered species

語言支持

Posgres內置了一些語言: Danish, Dutch, English, Finnish, French, German, Hungarian, Italian, Norwegian, Portuguese, Romanian, Russian, Spanish, Swedish, Turkish.（沒看到中文，這段就不說了）

Accented Character

音調的不同導致意思也不同，（沒有內置中文，這段就不說了）

Ranking

構建一個搜索引擎，您會希望你的查詢結果是根據相關度來排序的。與documents的排名相關的指標在下麵這段引用中解釋的很清楚。

Ranking attempts to measure how relevant documents are to a particular query, so that when there are many matches the most relevant ones can be shown first. PostgreSQL provides two predefined ranking functions, which take into account lexical, proximity, and structural information; that is, they consider how often the query terms appear in the document, how close together the terms are in the document, and how important is the part of the document where they occur.
-- PostgreSQL documentation

在PostgreSQL中提供了兩個函數來進行相關度調整。分別是ts_rank() 和setweight()

函數setweight允許給予tsvector一個權重，權重的值可以是A,B,C,D。

SELECT pid, p_title
FROM (SELECT post.id as pid,
             post.title as p_title,
             setweight(to_tsvector(post.title), 'A') ||
             setweight(to_tsvector(post.content), 'B') ||
             setweight(to_tsvector('simple', author.name), 'C') ||
             setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'B') as document
      FROM post
      JOIN author ON author.id = post.author_id
      JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
      JOIN tag ON tag.id = posts_tags.tag_id
      GROUP BY post.id, author.id) p_search
WHERE p_search.document @@ to_tsquery('english', 'Endangered & Species')
ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) DESC;

在上面這個查詢中，我們給予不同的tsvector不同的權重，A權重的post.title比B權重的post.content更加重要。依此類推。這意味著如果我們查一個詞Alice。Adocument在標題中有Alice，Bdocument在內容中有Alice。那A文檔會在前。
ts_rank函數會回傳tsquery與tsvector的相關度，用一個小數來表示。

SELECT ts_rank(to_tsvector('This is an example of document'), 
               to_tsquery('example | document')) as relevancy;
 relevancy
-----------
 0.0607927
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'), 
               to_tsquery('example ')) as relevancy;
 relevancy
-----------
 0.0607927
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'), 
               to_tsquery('example | unkown')) as relevancy;
 relevancy
-----------
 0.0303964
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'),
               to_tsquery('example & document')) as relevancy;
 relevancy
-----------
 0.0985009
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'), 
               to_tsquery('example & unknown')) as relevancy;
 relevancy
-----------
 1e-20
(1 row)

但是，相關度這個概念實際上有點模糊且與實際APP需求緊密連接的。不同的APP需要不同的排名方式。你可以自己加上一些參數上去。比如你加一個根據作者年齡的排序，你可以ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) * author.age DESC。最後怎麼算還是由你自己決定。

(Optimization and Indexing)優化和索引

優化搜索非常簡單明瞭。因為PostgreSQL支持基於Index的函數。所以你可以創建一個GIN index作用於tsvector函數。

CREATE INDEX idx_fts_post ON post 
USING gin((setweight(to_tsvector(language::regconfig, title),'A') || 
       setweight(to_tsvector(language::regconfig, content), 'B')));

-- If this throws an IMMUTABLE error then you can use this workaround

CREATE OR REPLACE FUNCTION gin_fts_fct(title text, content text, language text) 
  RETURNS tsvector
AS
$BODY$
    SELECT setweight(to_tsvector($3::regconfig, $1), 'A') || setweight(to_tsvector($3::regconfig, $1), 'B');
$BODY$
LANGUAGE sql
IMMUTABLE;

CREATE INDEX idx_fts_post ON post  USING gin(gin_fts_fct(title, content, language));

選擇GIN還是GiST索引？這要根據你的讀寫量以及數據量來決定

As a rule of thumb, GIN indexes are best for static data because lookups are faster. For dynamic data, GiST indexes are faster to update. Specifically, GiST indexes are very good for dynamic data and fast if the number of unique words (lexemes) is under 100,000 while GIN indexes will handle 100,000+ lexemes better but are slower to update.
-- Postgres doc : Chap 12 Full Text Search

在這個例子中，我們將採用GIN。但是你可以根據你的情況決定使用什麼數據。在document中，我們還有一個問題。document是跨表的，且各表欄位擁有不同權重。為了得到更好的性能，我們通過triggers或者materialized view的方式進行數據重組。不過您不需要總是重組數據。在一些情況下，您可以加一些基於index的函數。還可以通過tsvector_update_trigger(...)或者tsvector_update_trigger_column(...)來重組數據。查看文檔來獲取更多細節。另外如果可以接受短暫延遲的話。用Materialized View是一個不錯的選擇。

CREATE MATERIALIZED VIEW search_index AS 
SELECT post.id,
       post.title,
       setweight(to_tsvector(post.language::regconfig, post.title), 'A') || 
       setweight(to_tsvector(post.language::regconfig, post.content), 'B') ||
       setweight(to_tsvector('simple', author.name), 'C') ||
       setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'A') as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id

然後可以通過REFRESH MATERIALIZED VIEW search_index;的方式來重建索引。
首先我們建立索引

CREATE INDEX idx_fts_search ON search_index USING gin(document);

然後查詢也會變得更加簡單

SELECT id as post_id, title
FROM search_index
WHERE document @@ to_tsquery('english', 'Endangered & Species')
ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) DESC;

Mispelling

英文沒啥用， 中文的分詞不知道實現了pg_trgm沒有。。

總結

Posgres全文搜索功能還是相當棒和快的。有了它，你就不用裝其他的搜索引擎了。Posgres像不像一顆幸福的子彈。。。也許不是，如果您的業務圍著搜索轉啊轉的話。
有一些功能我沒講，但是您基本上也用不到。以我個人而言，我希望Posgres的全文搜索還有一些功能能夠加進來。

  • 更多的內置語言
  • 和Lucene搞基
  • 更好的排名和權重分配解決方案
  • 模糊查詢再叼一點
    總的來說，Posgres再全文搜索上肯定是沒有ElasticSearch和SOLR那麼先進。。。

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ubuntu下MySQL的安裝與卸載
    Ubuntu下多次重裝MySQL，可以完全卸載後再安裝，本文將結束MySQL的安裝與卸載方法。 ...
  • 【轉載】Docker+Kubernetes 乾貨文章精選
    主要涉及到以下關鍵字： K8S、Docker、微服務、安裝、教程、網路、日誌、存儲、安全、工具、CI/CD、分散式、實踐、架構等； 以下盤點2018年一些精選優質文章！ 漫畫形式： 漫畫：小黃人學 Service Mesh 之 Istio漫畫：Kubernetes中的ConfigMap和Secret ...
  • mysql存儲之int
    開始之前給大家出個問題，資料庫表test中兩個欄位 a int(2),b int(3)，現在想執行下麵的插入語句 思考是否可以插入？ 答案是能插入 再看下麵的語句 思考能不能插入？註意第一個數字多了一位變成了11位 資料庫會報如下錯誤 “Out of range value for column ' ...
  • 轉換CLOB欄位類型為VARCHAR2, lob類型不支持的sql語句
    轉自：https://blog.csdn.net/e_wsq/article/details/7561209 步驟： 1.建立一個臨時varchar2欄位用來保存數據 2.將clob的內容截取後更新到varchar2欄位中 update table_wonder set tempcolumn = d ...
  • 命令行操作mysql 未完待續......
    複製數據表 刪除表中某個欄位 查看表結構 未完待續...... ...
  • SQL Server 查找統計信息的相關採樣信息
    SQL Server 查找統計信息的相關採樣信息 有時候我們會遇到，由於統計信息不准確導致優化器生成了一個錯誤的執行計劃（或者這樣表達：一個較差的執行計劃），從而引起了系統性能問題。那麼如果我們懷疑這個錯誤的執行計劃是由於統計信息不准確引起的。那麼我們如何判斷統計信息不准確呢？當然首先得去查看實際執... ...
  • 不一樣的 SQL Server 日期格式化
    最近統計一些數據，需要按天/按小時/按分鐘來統計，涉及到一些日期的格式化，網上看了一些文章大部分都是使用 `CONVERT` 來轉換的，SQL Server 從 2012 開始增加了 `FORMAT` 方法，可以使用 `FORMAT` 來格式化日期，更標準化，更具可定製性，而且和 C# 里的日期格式... ...
  • Postgres 優雅存儲樹形數據
    碰到一個樹形數據需要存儲再數據控制，碰到以下兩個問題： 在PG資料庫中如何表達樹形數據 如何有效率的查詢以任意節點為Root的子樹 測試數據 為了更加簡單一些，我們將使用一下數據 簡單的自引用 當設計自引用表(有時候自己join自己)。最簡單明瞭的就是有一個 欄位。 然後插入一些樣例數據，用 來關聯 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類