PostgreSQL全文检索简介

PostgreSQL自带有一个简易的全文检索引擎，可以实现小规模数据量的全文检索功能。本文我们将引导介绍一下这个功能，对于小数据量的搜索这个功能是足够使用的，而无需搭建额外的ES等重量级的全文检索服务器。

PostgreSQL的全文检索入门

PG的全文检索操作符是@@，当一个tsvector(文档)和tsquery(条件)匹配时返回true，并且前后顺序无影响:

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
 ?column?
----------
 t

SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
 ?column?
----------
 f

PostgreSQL 10开始，jsonb也支持全文检索了。

和普通的SQL查询一样，只要在WHERE条件中使用这个符号就代表使用全文检索条件筛选文档了。如:

SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');

@@操作符支持隐式转换，对于text类型可以无需强类型转换(::tsvector或to_tsvector(config_name, text))，所以这个操作符实际支持的参数类型是这样的:

tsvector @@ tsquery
tsquery  @@ tsvector
text @@ tsquery
text @@ text

关于tsquery

此外，PostgreSQL还提供了两个相对简化的版本plainto_tsquery和phraseto_tsquery。

plainto_tsquery(plainto_tsquery([ config regconfig, ] querytext text) returns tsquery)用户将未格式化的text经过分词之后，插入&符号转为tsquery:

SELECT plainto_tsquery('english', 'The Fat Rats');
 plainto_tsquery 
-----------------
 'fat' & 'rat'

phraseto_tsquery(phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery)行为和plainto_tsquery行为类似，但是分词之后不是插入&而是<->(FOLLOWED BY):

SELECT phraseto_tsquery('english', 'The Fat Rats');
 phraseto_tsquery
------------------
 'fat' <-> 'rat'

关于索引

CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector('english', body));

也可以是一个连接列:

CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector('english', title || ' ' || body));

还可以单独创建一个tsvector列，为这个列创建索引:

ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
UPDATE pgweb SET textsearchable_index_col =
     to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));

CREATE INDEX textsearch_idx ON pgweb USING GIN(textsearchable_index_col);

NOTE:

创建一个基于 GIN（通用倒排索引）的索引，column必须是tsvector类型。所以需要对列进行显式类型转换。

使用了to_tsvector()函数的双参数版本指定了全文检索配置，因此必须使用to_tsvector()相同全文检索配置才能命中索引。比即，WHERE to_tsvector('english', body) @@ 'a & b' 可以使用该索引，但WHERE to_tsvector(body) @@ 'a & b'不能。

在使用一个单独的列来存储tsvector表示时，有必要创建一个触发器在title或body改变时保证tsvector列为当前值。。

GIN 索引只存储 tsvector值的词（词位），并且不存储它们的权重标签。因此，在使用涉及权重的查询时需要一次在表行上的重新检查。

关于排序

除了普通的ORDER BY条件之外，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 title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;
                     title                     |   rank
-----------------------------------------------+----------
 Neutrinos in the Sun                          |      3.1
 The Sudbury Neutrino Detector                 |      2.4
 A MACHO View of Galactic Dark Matter          |  2.01317
 Hot Gas and Dark Matter                       |  1.91171
 The Virgo Cluster: Hot Plasma and Dark Matter |  1.90953
 Rafting for Solar Neutrinos                   |      1.9
 NGC 4650A: Strange Galaxy and Dark Matter     |  1.85774
 Hot Gas and Dark Matter                       |   1.6123
 Ice Fishing for Cosmic Neutrinos              |      1.6
 Weak Lensing Distorts the Universe            | 0.818218

PostgreSQL中文全文检索

在CREATE EXTENSION之后，必须配置分词参数才能正确进行分词和查找，否则什么都查不到。官方文档提供的一个配置策略是:

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

n,v,a,i,e,l这几个字母分别表示一种token策略，只启用了这几种token mapping，其余则被屏蔽。具体支持的参数和含义可以用\dFp+ zhparser显示:

postgres=# \dFp+ zhparser 
      Text search parser "public.zhparser"
     Method      |    Function     | Description 
-----------------+-----------------+-------------
 Start parse     | zhprs_start     | 
 Get next token  | zhprs_getlexeme | 
 End parse       | zhprs_end       | 
 Get headline    | prsd_headline   | (internal)
 Get token types | zhprs_lextype   | 

Token types for parser "public.zhparser"
 Token name |      Description       
------------+------------------------
 a          | adjective,形容词
 b          | differentiation,区别词
 c          | conjunction,连词
 d          | adverb,副词
 e          | exclamation,感叹词
 f          | position,方位词
 g          | root,词根
 h          | head,前连接成分
 i          | idiom,成语
 j          | abbreviation,简称
 k          | tail,后连接成分
 l          | tmp,习用语
 m          | numeral,数词
 n          | noun,名词
 o          | onomatopoeia,拟声词
 p          | prepositional,介词
 q          | quantity,量词
 r          | pronoun,代词
 s          | space,处所词
 t          | time,时语素
 u          | auxiliary,助词
 v          | verb,动词
 w          | punctuation,标点符号
 x          | unknown,未知词
 y          | modal,语气词
 z          | status,状态词
(26 rows)

WITH simple表示词典使用的是内置的simple词典，即仅做小写转换。根据需要可以灵活定义词典和token映射，以实现屏蔽词和同义词归并等功能。

比如我们看下面这个例子:

-- 以下这个全文检索配置来源于zhparser文档的范例
ALTER TEXT SEARCH CONFIGURATION zhparser ADD MAPPING FOR n,v,a,i,e,l WITH simple;
postgres=# SELECT to_tsquery('zhparser', '江淮杜伏威');
     to_tsquery     
--------------------
 '杜' & '伏' & '威'
(1 row)

可以看到江淮这个词组在查询的时候被忽略了，我们启用j(abbreviation,简称)再看看结果:

postgres=# ALTER TEXT SEARCH CONFIGURATION zhparser ADD MAPPING FOR j WITH simple;
ALTER TEXT SEARCH CONFIGURATION
postgres=# SELECT to_tsquery('zhparser', '江淮杜伏威');
         to_tsquery          
-----------------------------
 '江淮' & '杜' & '伏' & '威'
(1 row)