PostgreSQL 8.3.1 全文檢索
在postgreSQL 8.3自帶支持全文檢索功能，在之前的版本中需要安裝配置tsearch2才能使用，安轉(zhuǎn)配置tsearch2就不再多說了，主要介紹一下8.3中自帶全文檢索功能。
全文檢索類型(Text Search Types)
postgreSQL設(shè)計支持全文檢索，提供兩個數(shù)據(jù)類型（tsvector,tsquery），并且通過動態(tài)檢索自然語言文檔的集合，定位到最匹配的查詢結(jié)果。
tsvector
一個tsvector的值是唯一分詞的分類列表，把一話一句詞格式化為不同的詞條，在進(jìn)行分詞處理的時候
tsvector會自動去掉分詞中重復(fù)的詞條，按照一定的順序裝入。例如
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把一個字符串按照空格進(jìn)行分詞，分詞的順序是按照長短和字母來排序的。但是某些時候，我們?yōu)榱俗屧~條中包含空格或者符號，就需要對其使用引號。
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
這個位置信息通常就是當(dāng)前文檔中單詞所處的位置，這個位置信息用于關(guān)注度的體現(xiàn)。位置信息常量的值的范圍為1 到 16383。分詞后，會把相同詞條的位置記錄到一個詞條中。（如上所示）。
詞條通過權(quán)重可以使其所在位置促進(jìn)它的標(biāo)記。權(quán)重分為A,B,C,D,D為默認(rèn)值可以不顯示.
權(quán)重用于關(guān)系,體現(xiàn)文檔結(jié)構(gòu)是很有特色地.例如,通俗一點,就是相同的詞條,但是詞條所在位置的權(quán)重不一樣,在一個文檔中,標(biāo)題和文本內(nèi)容,在做全文檢索排序功能時需要分配給這兩個詞不同的優(yōu)先權(quán),不同的權(quán)重標(biāo)記.
理解tsvector類型是很重要的,不能只關(guān)注標(biāo)準(zhǔn)的應(yīng)用.例如
select 'The Fat Rats'::tsvector;
tsvector
--------------------
'Fat' 'The' 'Rats'
但是對于英文全文檢索應(yīng)用來說,上面的句子就是非標(biāo)準(zhǔn)化的,但是tsvector是不會知道的,為處理加工的文本應(yīng)該通過使用to_tsvector函數(shù)來是之規(guī)格化,標(biāo)注化的應(yīng)用于搜索.
SELECT to_tsvector('english', 'The Fat Rats');
to_tsvector
-----------------
'fat':2 'rat':3
tsquery
顧名思義,tsquery,表示的應(yīng)該是查詢相關(guān)的.tsquery是存儲用于檢索的詞條.并且可以聯(lián)合使用boolean 操作符來連接, & (AND), | (OR), and ! (NOT). 使用括號(),可以強(qiáng)制分為一組.
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 在做搜索的時候,也可以使用權(quán)重,并且每個詞都可以使用一個或者多個權(quán)重標(biāo)記,這樣在檢索的時候,會匹配相同權(quán)重的信息.
跟上面的tsvector ,相同tsquery也有一個to_tsquery函數(shù).
全文檢索的 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
我們在處理索引的時候還是要使用他們的函數(shù)如,
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
上面的前兩種我們已經(jīng)使用過了,但是后兩種,
text @@ tsquery 等同于 to_tsvector(x) @@ y.
而 text @@ text 等同于 to_tsvector(x) @@ plainto_tsquery(y).
表和索引
前面介紹了如何在簡單文本中進(jìn)行全文檢索匹配.下面部分將介紹如何檢索表數(shù)據(jù)和使用索引.
檢索一個表
在全文檢索中不使用索引也是可以進(jìn)行檢索的,例如下面的簡單例子,查詢出title 從所有body中包含friend的行.
SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
復(fù)雜一點的例子:
檢索出最近的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;
建立索引
我們可以通過創(chuàng)建gin索引來加速檢索速度.例如
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body));
創(chuàng)建索引可以有多種方式.索引的創(chuàng)建甚至可以連接兩個列:
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', title || body));
另外的一種方式是創(chuàng)建一個單獨(dú)的 tsvector列,然后使用to_tsvector函數(shù)把需要索引字段的數(shù)據(jù)聯(lián)合在一起，比如列title和body，并且使用函數(shù)coalesce來確保字段為NULL的可以建立索引。
如下：
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
UPDATE pgweb SET textsearchable_index_col =
to_tsvector('english', coalesce(title,'') || coalesce(body,''));
然后，我們就可以創(chuàng)建倒排的索引
CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
索引創(chuàng)建完畢，我們就可以使用全文檢索了。
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC LIMIT 10;
控制全文檢索(Controlling Text Search)
為了實現(xiàn)全文檢索，我們需要把一個文檔創(chuàng)建一個tsvector 格式，并且通過tsquery實現(xiàn)用戶的查詢。
因此，在查詢中我們返回一個按照重要性排序的查詢結(jié)果。
分析文檔(Parsing Documents)
postgresql 中提供了to_tsvector函數(shù)把文檔處理成tsvector數(shù)據(jù)類型。（前面已經(jīng)介紹過了）
這個函數(shù)會將文本文檔，分解成唯一的詞條，并且還包括詞條所在文本中的位置。（這里to_tsvector函數(shù)就不再多做介紹了）。
其實在做分詞是很重要的步驟，分詞的操作直接關(guān)系到你的后來檢索的結(jié)果。（后面再重要描述）
在postgreSQL中默認(rèn)的to_tsvector（'english',）配置默認(rèn)的是英語。
postgre 中還有一個函數(shù) setweight ，要使用這個函數(shù)我們要引入一個概念，這個概念就是權(quán)重weight，什么是權(quán)重呢，字面上解釋就是權(quán)衡一下哪個更重要，也就是說哪個更側(cè)重一些。我們可以通過函數(shù)setweight來設(shè)置權(quán)重，postgre提供了四個A，B，C，D來分別表示不同權(quán)重級別，這個級別類型用來標(biāo)記他們來自于文檔中的不同部分，例如title和body。查詢結(jié)果的關(guān)注度可以使用這個權(quán)重級別。如：
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函數(shù)和plainto_tsquery函數(shù)，來處理分析搜索語句。
SELECT to_tsquery('english', 'The & Fat & Rats');
to_tsquery
---------------
'fat' & 'rat'
在搜索中tsquery中可以使用權(quán)重（weight）,在搜索詞條中可以附加權(quán)重，并且匹配出來的查詢結(jié)果也是必須在這個這個權(quán)重范圍的。
SELECT to_tsquery('english', 'Fat | Rats:AB');
to_tsquery
------------------
'fat' | 'rat':AB
從上面的例子可以看出，to_tsquery函數(shù)在處理查詢文本的時候，查詢文本的單個詞之間要使用邏輯操作符（& (AND), | (OR) and ! (NOT)）連接（或者使用括號）。例如 跟上面的例子相似
SELECT to_tsquery('english', 'Fat Rats');
如果要使執(zhí)行上面的操作，就會報語法錯誤。
然而plainto_tsquery函數(shù)卻可以提供一個標(biāo)準(zhǔn)的tsquery，如上面的例子，plainto_tsquery會自動加上邏輯&操作符。
SELECT plainto_tsquery('english', 'Fat Rats');
plainto_tsquery
-----------------
'fat' & 'rat'
但是plainto_tsquery函數(shù)不能夠識別邏輯操作符和權(quán)重標(biāo)記。
SELECT plainto_tsquery('english', 'The Fat & Rats:C');
plainto_tsquery
---------------------
'fat' & 'rat' & 'c'
查詢結(jié)果關(guān)注度(Ranking Search Results)
相關(guān)度，就是試圖測試衡量哪一個文檔是檢索中最關(guān)注的。因此我們把最匹配的文檔現(xiàn)在在最前面。這樣才能真正達(dá)到檢索的準(zhǔn)確度，postgresql提供兩個相關(guān)的函數(shù)，ts_rank和ts_rank_cd.
這兩個函數(shù)的語法是
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
兩個函數(shù)的第一個參數(shù)都是 權(quán)重(weight)，在前面已經(jīng)講了權(quán)重的概念。
參數(shù)的格式為 {D-weight, C-weight, B-weight, A-weight} ，在使用函數(shù)的時候沒有指定這個參數(shù)，postgre會默認(rèn)指定參數(shù)為：{0.1, 0.2, 0.4, 1.0}
這個參數(shù)應(yīng)該理解為：單前檢索的關(guān)鍵詞（詞條）在當(dāng)前這個檢索文檔中的位置，如果這個詞條在這個當(dāng)前的位置權(quán)重高，那么他的相關(guān)度的值也會高。
另外函數(shù)的最好一個參數(shù)是一個整型，這個參數(shù)是表示，這個參數(shù)指定文檔大小影響相關(guān)度的程度。
你可以指定一個或者多個例如（2|4）。
這些參數(shù)的定義
0 (the default) ignores the document length
表示跟長度大小沒有關(guān)系
1 divides the rank by 1 + the logarithm of the document length
表示參數(shù) 關(guān)注度（rank）除以 文檔長度的對數(shù)+1
2 divides the rank by the document length
表示 關(guān)注度 除以 文檔的長度
4 divides the rank by the mean harmonic distance between extents (this is implemented only by ts_rank_cd)
表示 關(guān)注度 除以 文檔長度的平均值，只能使用函數(shù)ts_rank_cd.
8 divides the rank by the number of unique words in document
表示 關(guān)注度 除以 文檔中 唯一分詞的數(shù)量
16 divides the rank by 1 + the logarithm of the number of unique words in document
表示關(guān)注度 除以 唯一分詞數(shù)量的對數(shù)+1
32 divides the rank by itself + 1
表示 關(guān)注度 除以 本身+1
其他的一些特性
length(vector tsvector) returns integer
這個函數(shù)返回當(dāng)前索引字段的分詞長度，就是分詞的個數(shù)
strip(vector tsvector) retzitor
這個函數(shù)返回當(dāng)前索引字段的數(shù)據(jù)不包括詞的位置
自動更新處理的觸發(fā)器(Triggers for Automatic Updates)
在我們作全文檢索的時候，當(dāng)我們使用單獨(dú)的列來存儲索引文件的時候，我們一定需要創(chuàng)建一個觸發(fā)器，當(dāng)涉及索引字段的其他列的內(nèi)容改變的時候，索引文件也要相應(yīng)的改變，postgre提供兩個觸發(fā)器可以實現(xiàn)該功能，也可以自定義觸發(fā)器。
這兩個觸發(fā)器的為：
tsvector_update_trigger(tsvector_column_name, config_name, text_column_name [, ... ])
tsvector_update_trigger_column(tsvector_column_name, config_column_name, text_column_name [, ... ])
這個函數(shù)可以自動把一個或者多個文本字段，計算生成索引字段。例如
我們創(chuàng)建一個表
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);
創(chuàng)建觸發(fā)器，字段title和body都是文本字段，他們索引后的字段為tsv,這里使用postgre自帶的分詞規(guī)則pg_catalog.english。
向表中插入一條數(shù)據(jù)。
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字段自動加入的數(shù)據(jù)，并且經(jīng)過tsvector處理。
接下來我們測試一下檢索
SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body');
上面這個查詢，查不到結(jié)果。
SELECT title, body FROM messages WHERE tsv @@ to_tsquery('english','title & body');
title | body
------------+-----------------------
title here | the body text is here
注意: 看上面的查詢條件，在進(jìn)行全文檢索的時候，在創(chuàng)建索引時，使用的分詞方式，與檢索時使用的分詞方式一定要想同，否則查詢的結(jié)果就會有問題。
創(chuàng)建觸發(fā)器后，不論title或者body那個字段改變，都會自動反射給tsv,索引會自動更新。
上面這中自帶的觸發(fā)器很有局限性，比如說，在建立索引的時候title和body要有不同的權(quán)重，上面的觸發(fā)器就能達(dá)到我們想要的效果，下面是一個 pl/pgsql 觸發(fā)器。
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();
索引統(tǒng)計函數(shù)
ts_stat(sqlquery text, [ weights text, ] OUT word text, OUT ndoc integer, OUT nentry integer)
返回的是統(tǒng)計的紀(jì)錄
word text — 索引中的詞條
ndoc integer — 詞條在索引中出現(xiàn)的次數(shù)
nentry integer — 詞條在文檔中出現(xiàn)的總次數(shù)
例如：
SELECT * FROM ts_stat('SELECT tsv FROM messages')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
查詢的結(jié)果為
word ndoc nentry
test 2 3
title 2 2
test 1 2
body 1 1
上面可以看到，通過ts_stat函數(shù)就可以看到索引列中的分詞的情況。
詞典(Dictionaries)
詞典就是被用來過濾掉一些不被關(guān)注的詞（在檢索的時候），并且對詞句規(guī)格化，是為了相同的詞在不同的來源的文檔中可以被匹配。一個成功分詞（格式化的）的詞稱為詞條。除了能提高檢索的質(zhì)量外，分詞的規(guī)格化，過濾詞，還能夠減少文檔索引的大小，這樣可以提高性能。標(biāo)準(zhǔn)化的詞也不能總是符合語言學(xué)意義，并且總是依賴于應(yīng)用所在的環(huán)境。
簡單舉例：
例如一些顏色名稱，將會被替換成其相對應(yīng)的16進(jìn)制的值，如 red,green,blue ->FF0000, 00FF00, 0000FF 等。
如果要制定小數(shù)，我們可以去掉一些小數(shù)的位數(shù)，來減少范圍。如 3.14159265359 ,3.1415926,這兩個小數(shù)如果是保留小數(shù)點后兩位小數(shù)，那么格式化后他們的值將都是 3.14。
postgresql提供了一些預(yù)定義的詞典面向多種語言的，并且還有幾個預(yù)定義的模版，可以根據(jù)用戶的需要自定義詞典。
屏蔽詞(Stop Words)
stop words 是一個很普遍并且在每個文檔中幾乎都能出現(xiàn)的的詞，并且這個詞沒有實際的意義，因此在全文檢索的文檔中他么將被忽略。例如 英文文本內(nèi)容中單詞 像 a 和like，他們不需要存儲在索引中，但是他會影響詞所在文檔的位置。
SELECT to_tsvector('english','in the list of stop words');
to_tsvector
----------------------------
'list':3 'stop':5 'word':6
并且相關(guān)度的計算與是否存在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的安裝目錄下.現(xiàn)在我們使用一下新建的詞典.如:
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
這里不再詳細(xì)介紹了.
全文檢索的測試和調(diào)試
ts_debug函數(shù)用來調(diào)試全文檢索的
這個函數(shù)顯示的是文檔的每個詞條通過基本詞典的分析和處理的信息。
這個函數(shù)返回的信息為：
alias text — short name of the token type
文本別名-詞的類型名稱
description text — description of the token type
描述-描述詞的類型
token text — text of the token
詞內(nèi)容-詞的文本內(nèi)容
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函數(shù)分析文檔并且返回一串記錄，每一個解析后的詞都有一個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)
這兩種索引都能用在提高全文檢索的速度，注意全文檢索不一定非要使用索引，但是萬一當(dāng)一個字段被固定規(guī)律搜索時，使用索引將會有很好的效果。
CREATE INDEX name ON table USING gist(column);
創(chuàng)建索引 gist 索引字段的類型可以是 tsvector 或者 tsquery.
CREATE INDEX name ON table USING gin(column);
創(chuàng)建索引 gin 索引字段的類型必須是 tsvector;