** PostgreSQL 8.3.1 全文检索**
在postgreSQL 8.3自带支持全文检索功能，在之前的版本中需要安装配置tsearch2才能使用，安转配置tsearch2就不再多说了，主要介绍一下8.3中自带全文检索功能。
**全文检索类型(Text Search Types)**
postgreSQL设计支持全文检索，提供两个数据类型（tsvector,tsquery），并且通过动态检索自然语言文档的集合，定位到最匹配的查询结果。
**tsvector**
一个tsvector的值是唯一分词的分类列表，把一话一句词格式化为不同的词条，在进行分词处理的时候
tsvector会自动去掉分词中重复的词条，按照一定的顺序装入。例如
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
tsvector
----------------------------------------------------
'a' 'on' 'and' 'ate' 'cat' 'fat' 'mat' 'rat' 'sat'
从上面的例子可以看出 ，通过tsvector把一个字符串按照空格进行分词，分词的顺序是按照长短和字母来排序的。但是某些时候，我们为了让词条中包含空格或者符号，就需要对其使用引号。
SELECT $$the lexeme ' ' contains spaces$$::tsvector;
tsvector
-------------------------------------------
'the' ' ' 'lexeme' 'spaces' 'contains'
为了使用引号，我们可以使用双$$符号来避免混淆。
并且词条位置常量可以附属于每个词条,例如：
SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;
tsvector
-------------------------------------------------------------------------------
'a':1,6,10 'on':5 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
这个位置信息通常就是当前文档中单词所处的位置，这个位置信息用于关注度的体现。位置信息常量的值的范围为1 到 16383。分词后，会把相同词条的位置记录到一个词条中。（如上所示）。
词条通过权重可以使其所在位置促进它的标记。权重分为A,B,C,D,D为默认值可以不显示.
权重用于关系,体现文档结构是很有特色地.例如,通俗一点,就是相同的词条,但是词条所在位置的权重不一样,在一个文档中,标题和文本内容,在做全文检索排序功能时需要分配给这两个词不同的优先权,不同的权重标记.
理解tsvector类型是很重要的,不能只关注标准的应用.例如
select 'The Fat Rats'::tsvector;
tsvector
--------------------
'Fat' 'The' 'Rats'
但是对于英文全文检索应用来说,上面的句子就是非标准化的,但是tsvector是不会知道的,为处理加工的文本应该通过使用to_tsvector函数来是之规格化,标注化的应用于搜索.
SELECT to_tsvector('english', 'The Fat Rats');
to_tsvector
-----------------
'fat':2 'rat':3
**tsquery**
顾名思义,tsquery,表示的应该是查询相关的.tsquery是存储用于检索的词条.并且可以联合使用boolean 操作符来连接, & (AND), | (OR), and ! (NOT). 使用括号(),可以强制分为一组.
SELECT 'fat & rat'::tsquery;
tsquery
---------------
'fat' & 'rat'
SELECT 'fat & (rat | cat)'::tsquery;
tsquery
---------------------------
'fat' & ( 'rat' | 'cat' )
SELECT 'fat & rat & ! cat'::tsquery;
tsquery
------------------------
'fat' & 'rat' & !'cat'
同时,tsquery 在做搜索的时候,也可以使用权重,并且每个词都可以使用一个或者多个权重标记,这样在检索的时候,会匹配相同权重的信息.
跟上面的tsvector ,相同tsquery也有一个to_tsquery函数.
**全文检索的 document**
document就是全文检索的搜索单元,在postgresql中全文检索匹配操作使用@@ 操作符,如果一个
tsvector(document) 匹配到 tsquery(query)则返回true.
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
?column?
----------
t
我们在处理索引的时候还是要使用他们的函数如,
SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
?column?
----------
t
并且操作符 @@ 可以使用text作为tsvector和tsquery.如下操作符可以使使用的方法
tsvector @@ tsquery
tsquery @@ tsvector
text @@ tsquery
text @@ text
上面的前两种我们已经使用过了,但是后两种,
text @@ tsquery 等同于 to_tsvector(x) @@ y.
而 text @@ text 等同于 to_tsvector(x) @@ plainto_tsquery(y).
**表和索引
**
前面介绍了如何在简单文本中进行全文检索匹配.下面部分将介绍如何检索表数据和使用索引.
检索一个表
在全文检索中不使用索引也是可以进行检索的,例如下面的简单例子,查询出title 从所有body中包含friend的行.
SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
复杂一点的例子:
检索出最近的10个文档,在表中的title 和 body字段中包含 creat和table的titile.
SELECT title
FROM pgweb
WHERE to_tsvector(title || body) @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC LIMIT 10;
**建立索引**
我们可以通过创建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列,然后使用to_tsvector函数把需要索引字段的数据联合在一起，比如列title和body，并且使用函数coalesce来确保字段为NULL的可以建立索引。
如下：
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);
索引创建完毕，我们就可以使用全文检索了。
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC LIMIT 10;
**控制全文检索(Controlling Text Search)
**
为了实现全文检索，我们需要把一个文档创建一个tsvector 格式，并且通过tsquery实现用户的查询。
因此，在查询中我们返回一个按照重要性排序的查询结果。
**分析文档(Parsing Documents)**
postgresql 中提供了to_tsvector函数把文档处理成tsvector数据类型。（前面已经介绍过了）
这个函数会将文本文档，分解成唯一的词条，并且还包括词条所在文本中的位置。（这里to_tsvector函数就不再多做介绍了）。
其实在做分词是很重要的步骤，分词的操作直接关系到你的后来检索的结果。（后面再重要描述）
在postgreSQL中默认的to_tsvector（'english',）配置默认的是英语。
postgre 中还有一个函数 setweight ，要使用这个函数我们要引入一个概念，这个概念就是权重weight，什么是权重呢，字面上解释就是权衡一下哪个更重要，也就是说哪个更侧重一些。我们可以通过函数setweight来设置权重，postgre提供了四个A，B，C，D来分别表示不同权重级别，这个级别类型用来标记他们来自于文档中的不同部分，例如title和body。查询结果的关注度可以使用这个权重级别。如：
UPDATE tt SET ti =
setweight(to_tsvector(coalesce(title,'')), 'A') ||
setweight(to_tsvector(coalesce(keyword,'')), 'B') ||
setweight(to_tsvector(coalesce(abstract,'')), 'C') ||
setweight(to_tsvector(coalesce(body,'')), 'D');
**搜索分析(Parsing Queries)**
postgreSQL中提供了to_tsquery函数和plainto_tsquery函数，来处理分析搜索语句。
SELECT to_tsquery('english', 'The & Fat & Rats');
to_tsquery
---------------
'fat' & 'rat'
在搜索中tsquery中可以使用权重（weight）,在搜索词条中可以附加权重，并且匹配出来的查询结果也是必须在这个这个权重范围的。
SELECT to_tsquery('english', 'Fat | Rats:AB');
to_tsquery
------------------
'fat' | 'rat':AB
从上面的例子可以看出，to_tsquery函数在处理查询文本的时候，查询文本的单个词之间要使用逻辑操作符（& (AND), | (OR) and ! (NOT)）连接（或者使用括号）。例如 跟上面的例子相似
SELECT to_tsquery('english', 'Fat Rats');
如果要使执行上面的操作，就会报语法错误。
然而plainto_tsquery函数却可以提供一个标准的tsquery，如上面的例子，plainto_tsquery会自动加上逻辑&操作符。
SELECT plainto_tsquery('english', 'Fat Rats');
plainto_tsquery
-----------------
'fat' & 'rat'
但是plainto_tsquery函数不能够识别逻辑操作符和权重标记。
SELECT plainto_tsquery('english', 'The Fat & Rats:C');
plainto_tsquery
---------------------
'fat' & 'rat' & 'c'
**查询结果关注度(Ranking Search Results)**
相关度，就是试图测试衡量哪一个文档是检索中最关注的。因此我们把最匹配的文档现在在最前面。这样才能真正达到检索的准确度，postgresql提供两个相关的函数，ts_rank和ts_rank_cd.
这两个函数的语法是
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
两个函数的第一个参数都是 权重(weight)，在前面已经讲了权重的概念。
参数的格式为 {D-weight, C-weight, B-weight, A-weight} ，在使用函数的时候没有指定这个参数，postgre会默认指定参数为：{0.1, 0.2, 0.4, 1.0}
这个参数应该理解为：单前检索的关键词（词条）在当前这个检索文档中的位置，如果这个词条在这个当前的位置权重高，那么他的相关度的值也会高。
另外函数的最好一个参数是一个整型，这个参数是表示，这个参数指定文档大小影响相关度的程度。
你可以指定一个或者多个例如（2|4）。
这些参数的定义
0 (the default) ignores the document length
表示跟长度大小没有关系
1 divides the rank by 1 + the logarithm of the document length
表示参数 关注度（rank）除以 文档长度的对数+1
2 divides the rank by the document length
表示 关注度 除以 文档的长度
4 divides the rank by the mean harmonic distance between extents (this is implemented only by ts_rank_cd)
表示 关注度 除以 文档长度的平均值，只能使用函数ts_rank_cd.
8 divides the rank by the number of unique words in document
表示 关注度 除以 文档中 唯一分词的数量
16 divides the rank by 1 + the logarithm of the number of unique words in document
表示关注度 除以 唯一分词数量的对数+1
32 divides the rank by itself + 1
表示 关注度 除以 本身+1
**其他的一些特性**
length(vector tsvector) returns integer
这个函数返回当前索引字段的分词长度，就是分词的个数
strip(vector tsvector) retzitor
这个函数返回当前索引字段的数据不包括词的位置
**自动更新处理的触发器(Triggers for Automatic Updates)**
在我们作全文检索的时候，当我们使用单独的列来存储索引文件的时候，我们一定需要创建一个触发器，当涉及索引字段的其他列的内容改变的时候，索引文件也要相应的改变，postgre提供两个触发器可以实现该功能，也可以自定义触发器。
这两个触发器的为：
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 TABLE messages (
title text,
body text,
tsv tsvector//索引字段
);
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);
创建触发器，字段title和body都是文本字段，他们索引后的字段为tsv,这里使用postgre自带的分词规则pg_catalog.english。
向表中插入一条数据。
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
可以看到tsv字段自动加入的数据，并且经过tsvector处理。
接下来我们测试一下检索
SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body');
上面这个查询，查不到结果。
SELECT title, body FROM messages WHERE tsv @@ to_tsquery('english','title & body');
title | body
------------+-----------------------
title here | the body text is here
注意: 看上面的查询条件，在进行全文检索的时候，在创建索引时，使用的分词方式，与检索时使用的分词方式一定要想同，否则查询的结果就会有问题。
创建触发器后，不论title或者body那个字段改变，都会自动反射给tsv,索引会自动更新。
上面这中自带的触发器很有局限性，比如说，在建立索引的时候title和body要有不同的权重，上面的触发器就能达到我们想要的效果，下面是一个 pl/pgsql 触发器。
CREATE FUNCTION messages_trigger() RETURNS trigger AS $$
begin
new.tsv :=
setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'D');
return new;
end
$$ LANGUAGE plpgsql;
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE PROCEDURE messages_trigger();
**索引统计函数
**
ts_stat(sqlquery text, [ weights text, ] OUT word text, OUT ndoc integer, OUT nentry integer)
返回的是统计的纪录
word text ― 索引中的词条
ndoc integer ― 词条在索引中出现的次数
nentry integer ― 词条在文档中出现的总次数
例如：
SELECT * FROM ts_stat('SELECT tsv FROM messages')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
查询的结果为
word ndoc nentry
test 2 3
title 2 2
test 1 2
body 1 1
上面可以看到，通过ts_stat函数就可以看到索引列中的分词的情况。
**词典(Dictionaries)**
词典就是被用来过滤掉一些不被关注的词（在检索的时候），并且对词句规格化，是为了相同的词在不同的来源的文档中可以被匹配。一个成功分词（格式化的）的词称为词条。除了能提高检索的质量外，分词的规格化，过滤词，还能够减少文档索引的大小，这样可以提高性能。标准化的词也不能总是符合语言学意义，并且总是依赖于应用所在的环境。
简单举例：
例如一些颜色名称，将会被替换成其相对应的16进制的值，如 red,green,blue ->FF0000, 00FF00, 0000FF 等。
如果要制定小数，我们可以去掉一些小数的位数，来减少范围。如 3.14159265359 ,3.1415926,这两个小数如果是保留小数点后两位小数，那么格式化后他们的值将都是 3.14。
postgresql提供了一些预定义的词典面向多种语言的，并且还有几个预定义的模版，可以根据用户的需要自定义词典。
**屏蔽词(Stop Words)**
stop words 是一个很普遍并且在每个文档中几乎都能出现的的词，并且这个词没有实际的意义，因此在全文检索的文档中他么将被忽略。例如 英文文本内容中单词 像 a 和like，他们不需要存储在索引中，但是他会影响词所在文档的位置。
SELECT to_tsvector('english','in the list of stop words');
to_tsvector
----------------------------
'list':3 'stop':5 'word':6
并且相关度的计算与是否存在stop words是十分不同的,如:
SELECT ts_rank_cd (to_tsvector('english','in the list of stop words'), to_tsquery('list & stop'));
ts_rank_cd
------------
0.05
SELECT ts_rank_cd (to_tsvector('english','list stop words'), to_tsquery('list & stop'));
ts_rank_cd
------------
0.1
**简单词典
**
使用简单词典,自定义词典,如:
CREATE TEXT SEARCH DICTIONARY public.simple_dict (
TEMPLATE = pg_catalog.simple,
STOPWORDS = english
);
上面例子中的 english是表示的stop words的名字,这个stop words的全名因该是$sharedir/tsearch_data/english.stop,$sharedir也就是postgresql的安装目录下.现在我们使用一下新建的词典.如:
SELECT ts_lexize('public.simple_dict','YeS');
ts_lexize
-----------
{yes}
SELECT ts_lexize('public.simple_dict','The');
ts_lexize
-----------
{}
还有几个postgresql中自带的词典, Simple Dictionary,Synonym Dictionary
,Thesaurus Dictionary
,Ispell Dictionary
,Snowball Dictionary
这里不再详细介绍了.
**全文检索的测试和调试
**
**ts_debug函数用来调试全文检索的
**这个函数显示的是文档的每个词条通过基本词典的分析和处理的信息。
这个函数返回的信息为：
alias text ― short name of the token type
文本别名-词的类型名称
description text ― description of the token type
描述-描述词的类型
token text ― text of the token
词内容-词的文本内容
dictionaries regdictionary[] ― the dictionaries selected by the configuration for this token type
词典-词的配置所选择的词典
dictionary regdictionary ― the dictionary that recognized the token, or NULL if none did
词典
lexemes text[] ― the lexeme(s) produced by the dictionary that recognized the token, or NULL if none did; an empty array ({}) means it was recognized as a stop word
处理后的词条
Here is a simple example:
SELECT * FROM ts_debug('english','a fat cat sat on a mat - it ate a fat rats');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+-------+----------------+--------------+---------
asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | cat | {english_stem} | english_stem | {cat}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | sat | {english_stem} | english_stem | {sat}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | on | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | mat | {english_stem} | english_stem | {mat}
blank | Space symbols | | {} | |
blank | Space symbols | - | {} | |
asciiword | Word, all ASCII | it | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | ate | {english_stem} | english_stem | {ate}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | rats | {english_stem} | english_stem | {rat}
**分析器测试(ts_parse)**
ts_parse函数分析文档并且返回一串记录，每一个解析后的词都有一个tokid，和的分词。如
SELECT * FROM ts_parse('default', '123 - a number');
tokid | token
-------+--------
22 | 123
12 |
12 | -
1 | a
12 |
1 | number
**gist 和 gin的索引类型(GiST and GIN Index Types)**
这两种索引都能用在提高全文检索的速度，注意全文检索不一定非要使用索引，但是万一当一个字段被固定规律搜索时，使用索引将会有很好的效果。
CREATE INDEX name ON table USING gist(column);
创建索引 gist 索引字段的类型可以是 tsvector 或者 tsquery.
CREATE INDEX name ON table USING gin(column);
创建索引 gin 索引字段的类型必须是 tsvector;
- 数据表
- 模式Schema
- 表的继承和分区
- 常用数据类型
- 函数和操作符-一
- 函数和操作符-二
- 函数和操作符-三
- 索引
- 事物隔离
- 性能提升技巧
- 服务器配置
- 角色和权限
- 数据库管理
- 数据库维护
- 系统表
- 系统视图
- SQL语言函数
- PL-pgSQL过程语言
- PostgreSQL 序列（SEQUENCE）
- PostgreSQL的时间-日期函数使用
- PostgreSQL 查看数据库，索引，表，表空间大小
- 用以查询某表的详细 包含表字段的注释信息
- PostgreSQL 系统表查看系统信息
- postgre存储过程简单实用方法
- PostgreSQL实用日常维护SQL
- PostgreSQL的时间函数使用整理
- 命令
- pg_ctl控制服务器
- initdb 初始化数据库簇
- createdb创建数据库
- dropdb 删除数据库
- createuser创建用户
- dropuser 删除用户
- psql交互式工具
- psql命令手册
- pg_dump 数据库转储
- pg_restore恢复数据库
- vacuumdb 清理优化数据库
- reindexdb 数据库重创索引
- createlang 安装过程语言
- droplang 删除过程语言
- pg_upgrade 升级数据库簇
- 调试存储过程
- 客户端命令-一
- 客户端命令-二
- 使用技巧
- PostgreSQL删除重复数据
- postgresql 小技巧
- PostgreSQL的10进制与16进制互转
- PostgreSQL的汉字转拼音
- Postgres重复数据的更新一例
- PostgreSQL使用with一例
- PostgreSQL在函数内返回returning
- PostgreSQL中的group_concat使用
- PostgreSQL数据库切割和组合字段函数
- postgresql重复数据的删除
- PostgreSQL的递归查询(with recursive)
- PostgreSQL函数如何返回数据集
- PostgreSQL分区表（Table Partitioning）应用 - David_Tang - 博客园
- PostgreSQL: function 返回结果集多列和单列的例子
- 利用pgAgent创建定时任务
- 浅谈 PostgreSQL 类型转换类似Oracle
- postgresql在windows(包括win7)下的安装配置
- PostgreSQL简介、安装、用户管理、启动关闭、创建删除数据库 (2010-11-08 12-52-51)转载▼标签： 杂谈分类： PostgreSQL
- PostgreSQL的generate_series函数应用
- PostgreSQL 8.3.1 全文检索(Full Text Search)
- postgresql record 使用
- 备份恢复
- PostgreSQL基于时间点恢复(PITR)
- Postgresql基于时间点恢复PITR案例(二)
- Postgres逻辑备份脚本
- Postgres invalid command \N数据恢复处理