pgsql全文索引

pgsql 全文索引

先给个实际的效果，在hotel数据库，进入poi_detail数据表，执行指令

select name from poi_detail where to_tsvector('senaean_word'::regconfig,lower(name)) @@ plainto_tsquery('senaean_word'::regconfig,'长城');

实现了模糊查询。

下面开始具体介绍，这句pgsql用法：

首先在数据库创建了索引 ：CREATE INDEX index_name ON 表名（field1，[field2...]） using gin（to_tevector('senaean_word'::regconfig,lower(name::text))）;

顺便说句如何删除索引： DROP INDEX index_name;

在创建索引的时候可能会出现问题 senaean_word没这东西， senaean_word是

第三方的分词，默认的分词则是‘english’。如果想创建中文分词的索引，前提是创建数据表的主机装有分词配置等文件如：’senaean_word‘ ，才可以创建。附上网上自带中文分词安装方法详解（没试过）http://blog.163.com/digoal@126/blog/static/163877040201252141010693/

1.如果没有带’english‘ 如下例子：

select 'The Fat Rats'::tsvector;

tsvector
--------------------
'Fat' 'The' 'Rats'

输出结果则不是按照英文的语义输出。

2.当含有 ‘english’分词则效果如下：

SELECT to_tsvector('english', 'The Fat Rats');
to_tsvector
-----------------
'fat':2 'rat':3

对于英文全文检索应用来说,1.中句子就是非标准化的,但是tsvector是不会知道的,为处理加工的文本应该通过使用to_tsvector函数来是之规格化,标注化的应用于搜索. 英文全文检索的时候，会自动剔除没用的单词比如‘the’，‘and’， 就如中文分词对于’的‘ ’是‘这类的单词都会自动剔除掉，这种单词在一篇文档中出现的频率很高，但是用户查询频率低些，所以处理的时候剔除这类词。

下面简单介绍下 字段功能

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函数.to_tsquery函数上文已经有过介绍了——to_tsvector函数来使之规格化,标注化的，应用于搜索。

对于tsquery字段，主要是你查询的信息。

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'

下面简单介绍下表的和索引以及建立简单的索引：

表和索引

前面介绍了如何在简单文本中进行全文检索匹配.下面部分将介绍如何检索表数据和使用索引.

检索一个表

在全文检索中不使用索引也是可以进行检索的,例如下面的简单例子,查询出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;

最后再介绍下四种类型索引：

PostgreSQL提供了多　　种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。

1. B-Tree:

CREATE TABLE test1 (
id integer,
content varchar
);
CREATE INDEX test1_id_index ON test1 (id);

B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" <、<=、=、>=和>"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。

2. Hash：

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。
这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。

3. GiST：

GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。

4. GIN：

GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如：< @、@>、=、&&等。