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

详细的全文检索功能请参见。感谢PostgreSQL中文社区的

PostgreSQL的全文检索入门

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

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

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

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

SELECT titleFROM pgwebWHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');

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

tsvector @@ tsquerytsquery  @@ tsvectortext @@ tsquerytext @@ text

NOTE: 实际使用中，建议使用to_tsvector(config_name, text)和to_tsquery(config_name, text)这种显式强类型转换的形式。因为如果使用隐式转换或::tsvector这种默认参数转换，将使用配置分词，该配置默认是pg_catalog.simple，可能对于绝大多数查询场景并不适用。因此推荐使用to_tsvector()和to_tsquery()函数显式强类型转换，并且指明分词字典，已实现更精确的查询需求。

关于tsquery

查询条件并不是简单的正则，而是一组搜索术语，使用并且使用布尔操作符&（AND）、|（OR）和!（NOT）来组合它们，还有短语搜索操作符<->（FOLLOWED BY）。更详细的语法参见。

此外，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'

关于索引

使用索引可以加快全文检索的速度。对于全文检索来说，可选的索引类型是GIN(通用倒排索引)和GIST(通用搜索树)，官方文档更推荐使用。创建一个GIN索引的范例:

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 rankFROM apod, to_tsquery('neutrino|(dark & matter)') queryWHERE query @@ textsearchORDER BY rank DESCLIMIT 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 9.6以上的版本还可以使用排序。(注意，这个是扩展，默认不包含)。

参考文档:

PostgreSQL中文全文检索

PostgreSQL默认的分词字典中并不包含中文分词字典，因此我们必须手工引入。目前一个比较好的项目是，同时这个插件也是阿里云的RDS默认包含的。安装和启用没什么好说的。值得一提的是分词配置参数。

在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 CONFIGURATIONpostgres=# SELECT to_tsquery('zhparser', '江淮杜伏威');         to_tsquery          ----------------------------- '江淮' & '杜' & '伏' & '威'(1 row)

所以实际使用中要设置合理的token types，过少将导致搜索结果不准确，过多将导致性能下降。此外，还有一些诸如短词复合: zhparser.multi_short = f这一类的控制分词结果的选项，根据实际使用酌情开启。