PostgreSQL之全文搜索

简述　　

　　全文搜索（或者文本搜索）提供了确定满足一个查询的自然语言文档的能力，并可以选择将它们按照与查询的相关度排序。

　　全文索引允许文档被预处理并且保存一个索引用于以后快速的搜索。预处理包括：

• 将文档解析成记号。标识出多种类型的记号是有所帮助的，例如数字、词、复杂的词、电子邮件地址，这样它们可以被以不同的方式处理
• 将记号转换成词位。和一个记号一样，一个词位是一个字符串，但是它已经被正规化，这样同一个词的不同形式被变成一样。例如，正规化几乎总是包括将大写字母转换成小写形式，并且经常涉及移除后缀（例如英语中的s或es）。这允许搜索找到同一个词的变体形式，而不需要冗长地输入所有可能的变体。此外，这个步骤通常会消除停用词，它们是那些太普通的词，它们对于搜索是无用的。
• 为搜索优化存储预处理好的文档。例如，每一个文档可以被表示为正规化的词位的一个有序数组。与词位一起，通常还想要存储用于近似排名的位置信息，这样一个包含查询词更“密集”区域的文档要比那些包含分散的查询词的文档有更高的排名。

文档

　　一个document是在一个全文搜索系统中进行搜索的单元，例如，一篇杂志文章或电子邮件消息。文本搜索引擎必须能够解析文档并存储词位（关键词）与它们的父文档之间的关联。随后，这些关联会被用来搜索包含查询词的文档。

　　对于PostgreSQL中的搜索，一个文档通常是一个数据库表中一行内的一个文本形式的域，或者可能是这类域的一个组合（连接），这些域可能存储在多个表或者是动态获取。换句话说，一个文档可能从用于索引的不同部分构建，并且它可能被作为一个整体存储在某个地方。例如：

SELECT title || ' ' ||  author || ' ' ||  abstract || ' ' || body AS document
FROM messages
WHERE mid = 12;

文档搜索

　　参照 PostgreSQL之数据类型(二)文本搜索、UUID、XML、JSON、数组 中的文本搜索.

索引

　　创建一个单独的tsvector列来保存to_tsvector的输出。若要使此列与其源数据保持自动更新，用存储生成的列。这个例子是title和body的连接，使用coalesce来保证当其他域为NULL时一个域仍然能留在索引中：

ALTER TABLE pgweb
    ADD COLUMN textsearchable_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

　　然后创建一个GIN索引来加速搜索：

CREATE INDEX textsearch_idx ON pgweb USING GIN(textsearchable_index_col);

　　准备好执行一个快速的全文搜索：

SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

配置

　　文件检索配置指定将文档转换为tsvector所需的所有选项：用于将文本解析为token的解析器，然后将token转换为词位的词典。

　　下面的SQL用来查下Postgre默认的配置信息：

SELECT name,setting FROM pg_settings where name = 'config_file';
-------------
config_file    /etc/postgresql/11/main/postgresql.conf

　　默认配置查询展示：

SELECT login_name,real_name
from t_user limit 2;
-----------
tt    帅帅
bailing    百羚管理员


SELECT to_tsvector('english',login_name || real_name)
from t_user limit 2;
-----------
'tt帅帅':1
'bailing百羚管理员':1

　 增加中文分配配置：

CREATE EXTENSION IF NOT EXISTS zhparser;
DROP TEXT SEARCH CONFIGURATION IF EXISTS testzhcfg;
CREATE TEXT SEARCH CONFIGURATION testzhcfg (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;

　 中文查询效果展示：

SELECT to_tsvector('testzhcfg',login_name || real_name)
from t_user limit 2;
------------
'tt':1 '帅':2,3
'bailing':1 '管理员':2

搜索排名

　　排名处理尝试度量文档和一个特定查询的接近程度，这样当有很多匹配时最相关的那些可以被先显示。PostgreSQL提供了两种预定义的排名函数，它们考虑词法、临近性和结构信息；即，它们考虑查询词在文档中出现得有多频繁，文档中的词有多接近，以及词出现的文档部分有多重要。

ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 
基于向量的匹配词位的频率来排名向量。

ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4
为给定文档向量和查询计算覆盖密度排名

select tmp.* 
from (
SELECT login_name , real_name, ts_rank_cd (to_tsvector('testzhcfg',login_name || real_name), to_tsquery('bailing | 管理员')) as rank 
from t_user) as tmp
order by tmp.rank desc;
------------------
yc_test_1023    应用系统管理员    0.1
add             应用系统管理员    0.1
youke           百羚游客         0

 加亮结果

　　要表示搜索结果，理想的方式是显示每一个文档的一个部分并且显示它是怎样与查询相关的。通常，搜索引擎显示文档片段时会对其中的搜索术语进行标记。PostgreSQL提供了一个函数ts_headline来实现这个功能。

ts_headline([ config regconfig, ] document text, query tsquery [, options text ]) returns text

　　ts_headline接受一个文档和一个查询，并且从该文档返回一个引用，在其中来自查询的术语会被加亮。被用来解析该文档的配置可以用config指定；如果config被忽略，将会使用default_text_search_config配置。

SELECT ts_headline('english','The most common type of search is to find all documents containing given query terms
and return them in order of their similarity to the query.',to_tsquery('query & similarity'));
                        ts_headline                         
------------------------------------------------------------
 containing given <b>query</b> terms and return them in order of their <b>similarity</b> to the <b>query</b>.

用于自动更新的触发器

　　当使用一个单独的列来存储你的文档的tsvector表示时，有必要创建一个触发器在文档内容列改变时更新tsvector列。

tsvector_update_trigger(tsvector_column_name, config_name, text_column_name [, ... ])
tsvector_update_trigger_column(tsvector_column_name, config_column_name, text_column_name [, ... ])

　　这些触发器函数在CREATE TRIGGER命令中指定的参数控制下，自动从一个或多个文本列计算一个tsvector列。

CREATE TABLE messages (
    title       text,
    body        text,
    tsv         tsvector
);

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON messages FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

INSERT INTO messages VALUES('title here', 'the body text is here');

SELECT * FROM messages;
   title    |         body          |            tsv             
------------+-----------------------+----------------------------
 title here | the body text is here | 'bodi':4 'text':5 'titl':1

SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body');
   title    |         body          
------------+-----------------------
 title here | the body text is here

 词典

　　词典被用来消除不被搜索考虑的词（stop words）、并被用来正规化词这样同一个词的不同派生形式将会匹配。一个被成功地正规化的词被称为一个词位。除了提高搜索质量，正规化和移除停用词减小了文档的tsvector表示的尺寸，因而提高了性能。正规化不会总是有语言上的意义并且通常依赖于应用的语义。

限制　

• 每一个词位的长度必须小于 2K 字节
• 一个tsvector（词位 + 位置）的长度必须小于 1 兆字节
• 词位的数量必须小于 264
• tsvector中的位置值必须大于 0 并且小于 16,383
• <N>（FOLLOWED BY）tsquery操作符中的匹配距离不能超过 16,384
• 每个词位不超过 256 个位置
• 一个tsquery中结点（词位 + 操作符）的个数必须小于 32,768