postgresql模糊匹配大殺器(推薦)

更新時(shí)間：2021年01月22日 15:55:18 作者：月圖靈

這篇文章主要介紹了postgresql模糊匹配大殺器,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

ArteryBase-模糊匹配大殺器

問題背景

隨著pg越來越強(qiáng)大，abase目前已經(jīng)升級(jí)到5.0(postgresql10.4)，目前abase5.0繼承了全文檢索插件(zhparser)，使用全文檢索越來越方便。本文會(huì)對(duì)abase支持的like模糊匹配，全文檢索，創(chuàng)建何種索引，如何使用進(jìn)行說明。針對(duì)于各種模糊匹配均可走索引

前模糊匹配(%xxx),后模糊匹配(xxx%)

使用場(chǎng)景：如果簡單的前模糊匹配或者后模糊匹配則可以建一個(gè)簡單的btree索引。

--1.后模糊匹配(xxx%)
create index i_t_msys_btrre_c_ajmc on db_msys.t_msys using btree(c_ajmc text_pattern_ops);
CREATE INDEX
Time: 4189.886 ms (00:04.190)
db_15fb=# select c_ajmc from db_msys.t_msys where c_ajmc like '北京%';
	c_ajmc      
------------------------
 北京決定和華宇
 北京和華宇信息
 北京
 北京華宇，北京華宇
、、、
db_15fb=# explain analyze select c_ajmc from db_msys.t_msys where c_ajmc like '北京%';
    QUERY PLAN   
-------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=43.92..2177.91 rows=4204 width=80) (actual time=0.570..2.667 rows=1570 loops=1)
 Filter: ((c_ajmc)::text ~~ '北京%'::text)
 Heap Blocks: exact=500
 -> Bitmap Index Scan on i_t_msys_btrre_c_ajmc (cost=0.00..42.87 rows=632 width=0) (actual time=0.477..0.477 rows=1570 loops=1)
 Index Cond: (((c_ajmc)::text ~>=~ '北京'::text) AND ((c_ajmc)::text ~<~ '北亭'::text))
 Planning time: 0.956 ms
 Execution time: 2.841 ms
(7 rows)

Time: 4.848 ms
--2.前模糊匹配(%xxx),查詢以c_ajmc以信息結(jié)尾的記錄，使用反轉(zhuǎn)函數(shù)reverse
db_15fb=# create index i_t_msys_reverse_c_ajmc on db_msys.t_msys using btree(reverse(c_ajmc) text_pattern_ops);
CREATE INDEX
Time: 4011.131 ms (00:04.011)
--查詢以張三結(jié)尾的信息
db_15fb=# select c_ajmc from db_msys.t_msys where reverse(c_ajmc) like reverse('%張三');
 c_ajmc 
----------
 華宇張三
 北京張三
(2 rows)

Time: 0.910 ms

--前模糊匹配也可走索引
db_15fb=# explain analyze select c_ajmc from db_msys.t_msys where reverse(c_ajmc) like reverse('%張三');
    QUERY PLAN   
  
-------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=114.86..5312.88 rows=1787 width=80) (actual time=0.064..0.066 rows=2 loops=1)
 Filter: (reverse((c_ajmc)::text) ~~ '三張%'::text)
 Heap Blocks: exact=1
 -> Bitmap Index Scan on i_t_msys_reverse_c_ajmc (cost=0.00..114.42 rows=1787 width=0) (actual time=0.042..0.042 rows=2 loops=1)
 Index Cond: ((reverse((c_ajmc)::text) ~>=~ '三張'::text) AND (reverse((c_ajmc)::text) ~<~ '三弡'::text))
 Planning time: 0.236 ms
 Execution time: 0.148 ms
(7 rows)

Time: 1.211 ms

--或者使用like '三張%'等效于 reverse('%張三')
db_15fb=# explain analyze select c_ajmc from db_msys.t_msys where reverse(c_ajmc) like '三張%';
    QUERY PLAN   
  
-------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=114.86..5312.88 rows=1787 width=80) (actual time=0.056..0.058 rows=2 loops=1)
 Filter: (reverse((c_ajmc)::text) ~~ '三張%'::text)
 Heap Blocks: exact=1
 -> Bitmap Index Scan on i_t_msys_reverse_c_ajmc (cost=0.00..114.42 rows=1787 width=0) (actual time=0.036..0.036 rows=2 loops=1)
 Index Cond: ((reverse((c_ajmc)::text) ~>=~ '三張'::text) AND (reverse((c_ajmc)::text) ~<~ '三弡'::text))
 Planning time: 0.259 ms
 Execution time: 0.108 ms
(7 rows)

Time: 1.119 ms

前模糊匹配的原理是將數(shù)據(jù)反轉(zhuǎn)存儲(chǔ)，查詢時(shí)字段需要反轉(zhuǎn)，輸入的值也需要反轉(zhuǎn)。原理和前模糊匹配一樣。

全模糊匹配(%xxx%)-三元組匹配pg_trgm

使用場(chǎng)景：pg_trgm支持前模糊匹配，后模糊匹配以及全模糊匹配，但是全模糊匹配至少要三個(gè)字符才會(huì)走索引，在全模糊匹配不少于三個(gè)字符的場(chǎng)景才生效(abase一個(gè)漢字為一個(gè)字符)，也就是like '%xxx%'不能少于三個(gè)漢字。

pg_trgm的擴(kuò)展abase也是自帶的，如果不能使用可以嘗試先刪除擴(kuò)展，然后在創(chuàng)建擴(kuò)展
--查看安裝擴(kuò)展
db_sqlfx=# select * from pg_extension;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
--------------+----------+--------------+----------------+------------+--
 plpgsql | 10 | 11 | f | 1.0 | | 
 uuid-ossp | 10 | 2200 | t | 1.1 | | 
 adminpack | 10 | 11 | f | 1.1 | | 
 postgres_fdw | 10 | 2200 | t | 1.0 | | 
 file_fdw | 10 | 2200 | t | 1.0 | | 
 pg_prewarm | 10 | 2200 | t | 1.1 | | 
 btree_gin | 10 | 2200 | t | 1.2 | | 
 zhparser | 10 | 2200 | t | 1.0 | | 
 pg_trgm | 10 | 2200 | t | 1.3 | | 
(9 rows)
--如果沒有則可以創(chuàng)建擴(kuò)展：
create extension pg_trgm;
--刪除擴(kuò)展
drop extension pg_trgm;
--c_ajmc創(chuàng)建gin索引
db_15fb=# create index i_t_msys_gin_c_ajmc on db_msys.t_msys using gin(c_ajmc gin_trgm_ops);
CREATE INDEX
Time: 25013.192 ms (00:25.013)
--查詢'洞庭湖'
db_15fb=# select c_ajmc from db_msys.t_msys where c_ajmc like ('%洞庭湖%');
 c_ajmc 
----------------
 測(cè)試洞庭湖數(shù)據(jù)
(1 row)
Time: 1.005 ms
--全模糊匹配可走索引
db_15fb=# explain analyze select c_ajmc from db_msys.t_msys where c_ajmc like ('%洞庭湖%');
    QUERY PLAN    
-------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=24.27..159.92 rows=35 width=80) (actual time=0.088..0.088 rows=1 loops=1)
 Recheck Cond: ((c_ajmc)::text ~~ '%洞庭湖%'::text)
 Heap Blocks: exact=1
 -> Bitmap Index Scan on i_t_msys_gin_c_ajmc (cost=0.00..24.27 rows=35 width=0) (actual time=0.069..0.069rows=1 loops=1)
 Index Cond: ((c_ajmc)::text ~~ '%洞庭湖%'::text)
 Planning time: 0.404 ms
 Execution time: 0.152 ms
(7 rows)

Time: 1.263 ms
--后模糊匹配，需要先刪除前面的btree，默認(rèn)會(huì)走btree因?yàn)榇鷥r(jià)比gin低,(需要注意的是pg_trgm的后模糊匹配至少需要提供一個(gè)字符才會(huì)走，前模糊匹配需要提供兩個(gè)字符)
drop index i_t_msys_btrre_c_ajmc;
db_15fb=# explain analyze select c_ajmc from db_msys.t_msys where c_ajmc like ('北京%');
    QUERY PLAN   
-------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=72.58..9791.59 rows=4204 width=80) (actual time=1.058..4.993 rows=1570 loo
ps=1)
 Recheck Cond: ((c_ajmc)::text ~~ '北京%'::text)
 Rows Removed by Index Recheck: 855
 Heap Blocks: exact=989
 -> Bitmap Index Scan on i_t_msys_gin_c_ajmc (cost=0.00..71.53 rows=4204 width=0) (actual time=0.869..0.8
69 rows=2425 loops=1)
 Index Cond: ((c_ajmc)::text ~~ '北京%'::text)
 Planning time: 0.589 ms
 Execution time: 5.160 ms
(8 rows)
Time: 6.658 ms
--使用gin索引 前模糊匹配
db_15fb=# explain analyze select c_ajmc from db_msys.t_msys where c_ajmc like ('%合同糾紛');
    QUERY PLAN    
-------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=1220.09..19633.34 rows=126980 width=80) (actual time=62.980..298.705 rows=166872 loops=1)
 Recheck Cond: ((c_ajmc)::text ~~ '%合同糾紛'::text)
 Rows Removed by Index Recheck: 12
 Heap Blocks: exact=16654
 -> Bitmap Index Scan on i_t_msys_gin_c_ajmc (cost=0.00..1188.35 rows=126980 width=0) (actual time=58.905..58.905 rows=166886 loops=1)
 Index Cond: ((c_ajmc)::text ~~ '%合同糾紛'::text)
 Planning time: 0.623 ms
 Execution time: 309.385 ms
(8 rows)
Time: 311.072 ms
--使用btree的反轉(zhuǎn)函數(shù) 
db_15fb=# explain analyze select c_ajmc from db_msys.t_msys where reverse(c_ajmc) like reverse('%合同糾紛');     QUERY PLAN    
-------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=114.86..5312.88 rows=1787 width=80) (actual time=51.135..289.537 rows=166872 loops=1)
 Filter: (reverse((c_ajmc)::text) ~~ '紛糾同合%'::text)
 Heap Blocks: exact=16654
 -> Bitmap Index Scan on i_t_msys_reverse_c_ajmc (cost=0.00..114.42 rows=1787 width=0) (actual time=46.970..46.970 rows=166874 loops=1)
 Index Cond: ((reverse((c_ajmc)::text) ~>=~ '紛糾同合'::text) AND (reverse((c_ajmc)::text) ~<~ '紛糾吉'::text))
 Planning time: 0.268 ms
 Execution time: 301.174 ms
(7 rows)

Time: 302.413 ms

可以看出前模糊匹配使用gin和btree都可以走索引，gin和btree的效率相差不大，但是gin索引大小比btree大，且創(chuàng)建耗費(fèi)時(shí)間

pg_trgm擴(kuò)展的前模糊匹配和后模糊匹配也均可走索引，后模糊匹配btree的效率比gin要高。

全文檢索-zhparser

使用場(chǎng)景：單個(gè)字段全文檢索，多字段全文檢索，行級(jí)全文檢索

目前abase5.0自帶了全文檢索支持，使用select * from pg_extension可以看到zhparser的擴(kuò)展。在abase5.0以前需要手動(dòng)安裝

--查看安裝擴(kuò)展
db_sqlfx=# select * from pg_extension;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
--------------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql | 10 | 11 | f | 1.0 | | 
 uuid-ossp | 10 | 2200 | t | 1.1 | | 
 adminpack | 10 | 11 | f | 1.1 | | 
 postgres_fdw | 10 | 2200 | t | 1.0 | | 
 file_fdw | 10 | 2200 | t | 1.0 | | 
 pg_prewarm | 10 | 2200 | t | 1.1 | | 
 btree_gin | 10 | 2200 | t | 1.2 | | 
 zhparser | 10 | 2200 | t | 1.0 | | 
 pg_trgm | 10 | 2200 | t | 1.3 | | 
(9 rows)
--如果沒有則可以創(chuàng)建擴(kuò)展：
db_15fb=# create extension zhparser;
CREATE EXTENSION
--創(chuàng)建使用zhparser作為解析器的全文搜索的配置 
db_15fb=# create text search configuration testzhcfg(parser = zhparser);
CREATE TEXT SEARCH CONFIGURATION
--往全文搜索配置中增加token映射 
db_15fb=# alter text search configuration testzhcfg add mapping for n,v,a,i,e,l with simple;
ALTER TEXT SEARCH CONFIGURATION
上面的token映射只映射了名詞(n)，動(dòng)詞(v)，形容詞(a)，成語(i),嘆詞(e)和習(xí)用語(l)6種，這6種以外的token全部被屏蔽。詞典使用的是內(nèi)置的simple詞典，即僅做小寫轉(zhuǎn)換。根據(jù)需要可以靈活定義詞典和token映射，以實(shí)現(xiàn)屏蔽詞和同義詞歸并等功能。
--分詞效果
db_15fb=# select to_tsvector('testzhcfg','南京市長江大橋');
   to_tsvector   
----------------------------------------------------------------------------------------
 '南京':2 '南京市':1 '大':9 '大橋':6 '市':3 '橋':10 '江':8 '長':7 '長江':5 '長江大橋':4
(1 row)

全文檢索查詢

--c_ajmc創(chuàng)建索引，可以看出創(chuàng)建gin索引相比btree是比較耗時(shí)的
db_15fb=# create index i_t_msys_c_ajmc on db_msys.t_msys using gin(to_tsvector('testzhcfg',c_ajmc));
CREATE INDEX
Time: 32601.072 ms (00:32.601)
--查詢c_ajmc包含北京華宇，to_tsquery('testzhcfg','北京華宇')
db_15fb=# select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京華宇');
 c_baah | c_ajmc 
---------------+----------------------
 華宇 | 北京決定和華宇
 測(cè)試案號(hào) | 測(cè)試北京與華宇xx糾紛
 北京五環(huán)之歌 | 北京和華宇信息
 (2018)xxxxxx1 | 北京出席華宇科技
 測(cè)試案號(hào)華宇 | 北京華宇
(5 rows)

Time: 1.927 ms
db_15fb=# explain analyze select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京華宇');
    QUERY PLAN    
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=56.00..60.26 rows=1 width=106) (actual time=0.989..1.004 rows=3 loops=1)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text) @@ '''北京'' & ''華宇'' & ''華'' & ''宇'''::tsquery)
 Heap Blocks: exact=5
 -> Bitmap Index Scan on i_t_msys_c_ajmc (cost=0.00..56.00 rows=1 width=0) (actual time=0.971..0.971 rows=13 loops=1)
 Index Cond: (to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text) @@ '''北京'' & ''華宇'' & ''華'' & ''宇'''::tsquery)
 Planning time: 0.275 ms
 Execution time: 1.055 ms
(7 rows)

Time: 2.290 ms
--to_tsquery('testzhcfg','北京華宇')等效于to_tsquery('testzhcfg','北京&華宇')
db_15fb=# select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京&華宇');
 c_baah | c_ajmc 
---------------+----------------------
 華宇 | 北京決定和華宇
 測(cè)試案號(hào) | 測(cè)試北京與華宇xx糾紛
 北京五環(huán)之歌 | 北京和華宇信息
 (2018)xxxxxx1 | 北京出席華宇科技
 測(cè)試案號(hào)華宇 | 北京華宇
(5 rows)

Time: 2.037 ms
db_15fb=# explain analyze select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京&華宇');
    QUERY PLAN   
  
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=56.00..60.26 rows=1 width=106) (actual time=0.941..0.958 rows=5 loops=1)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text) @@ '''北京'' & ''華宇'' & ''華'' & ''宇'''::tsquery)
 Heap Blocks: exact=5
 -> Bitmap Index Scan on i_t_msys_c_ajmc (cost=0.00..56.00 rows=1 width=0) (actual time=0.921..0.921 rows=15 loops=1)
 Index Cond: (to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text) @@ '''北京'' & ''華宇'' & ''華'' & 
''宇'''::tsquery)
 Planning time: 0.295 ms
 Execution time: 1.008 ms
(7 rows)

Time: 2.070 ms

--包含'北京'或者'華宇'的：to_tsquery('testzhcfg','北京|華宇')
db_15fb=# select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京|華宇');
 c_baah | c_ajmc   
     
---------------------------+--------------
 北京科技園 | 華宇信息
 華宇  | 北京決定和華宇
 測(cè)試案號(hào)  | 測(cè)試北京與華宇xx糾紛
 北京五環(huán)之歌 | 北京和華宇信息
 (2017)xx民初xx號(hào) | 華宇
 (2017)xx民初xx號(hào) 	 | 北京
...
Time: 10.426 ms

db_15fb=# explain analyze select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京|華宇');
    QUERY PLAN    
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=69.85..5710.15 rows=1787 width=106) (actual time=2.269..7.338 rows=2941 loops=1)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text) @@ '''北京'' | ''華宇'' & ''華'' & ''宇'''::tsquery)
 Heap Blocks: exact=1355
 -> Bitmap Index Scan on i_t_msys_c_ajmc (cost=0.00..69.41 rows=1787 width=0) (actual time=2.034..2.034 rows=2954 loops=1)
 Index Cond: (to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text) @@ '''北京'' | ''華宇'' & ''華'' & ''宇'''::tsquery)
 Planning time: 0.268 ms
 Execution time: 7.565 ms
(7 rows)

Time: 8.655 ms

這里查詢的結(jié)果包含了北京和華宇，如果想讓只查詢包含'北京'和'華宇'中間不包含其他名詞或動(dòng)詞等，可使用phraseto_tsquery,此處不管是'北京華宇','北京|華宇','北京&華宇'結(jié)果都一樣。
db_15fb=# select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@phraseto_tsquery('testzhcfg','北京華宇');
 c_baah | c_ajmc 
--------------+----------------------
 測(cè)試案號(hào) | 測(cè)試北京與華宇xx糾紛
 北京五環(huán)之歌 | 北京和華宇信息
 測(cè)試案號(hào)華宇 | 北京華宇
(3 rows)

Time: 2.203 ms

db_15fb=# explain analyze select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@phraseto_tsquery('testzhcfg','北京華宇');
    QUERY PLAN    
------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=56.00..60.26 rows=1 width=106) (actual time=1.147..1.258 rows=3 loops=1)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text) @@ '''北京'' <-> ''華宇'' <-> ''華'' <-> ''宇'''::tsquery)
 Rows Removed by Index Recheck: 2
 Heap Blocks: exact=5
 -> Bitmap Index Scan on i_t_msys_c_ajmc (cost=0.00..56.00 rows=1 width=0) (actual time=1.016..1.016 rows=15 loops=1)
 Index Cond: (to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text) @@ '''北京'' <-> ''華宇'' <-> ''華'' <-> ''宇'''::tsquery)
 Planning time: 0.333 ms
 Execution time: 1.307 ms
(8 rows)


但是結(jié)果中包含了'測(cè)試北京與華宇xx糾紛','北京和華宇信息',原因是token映射中忽略了名詞(n)，動(dòng)詞(v)，形容詞(a)，成語(i),嘆詞(e)和習(xí)用語(l)6種以外的詞。如果需要可以加入其中那樣就可以更加精確匹配出'北京華宇'
--不包含'與'
ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;
db_sqlfx=# select to_tsvector('testzhcfg','北京與華宇');
 to_tsvector 
---------------------------------
 '北京':1 '華':3 '華宇':2 '宇':4
(1 row)
--將所有詞性全部影射出后就就包含'與'
ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z WITH simple;
db_sqlfx=# select to_tsvector('testzhcfg','北京與華宇')
;
  to_tsvector  
------------------------------------------------------
 '與':4 '京':3 '北':2 '北京':1 '華':6 '華宇':5 '宇':7
(1 row)

--'北京'和'華宇'中間不包含任何詞，結(jié)果包含'北京華宇'
ALTER TEXT SEARCH CONFIGURATION testzhcfg alter MAPPING FOR a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z WITH simple;
db_15fb=# select c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc)@@phraseto_tsquery('testzhcfg','北京華宇');
 c_baah | c_ajmc 
------------------------+----------------------
 (2017)川0191民初3198號(hào) | 測(cè)試北京華宇信息技術(shù)
 (2017)川0191民初9022號(hào) | 測(cè)試北京華宇xxx
(2 rows)
Time: 1.347 ms

多字段全文檢索

前面是單個(gè)字段的全文檢索，如果我需要對(duì)多個(gè)字段做全文檢索如何做呢？
比如我需要對(duì)案號(hào)，案件名稱等字段查詢其中包含‘北京'的行。由于pg_trgm是三元組匹配，所以這個(gè)地方就不能實(shí)現(xiàn)。

ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;
--針對(duì)多個(gè)字段的函數(shù)索引
創(chuàng)建函數(shù)索引，需要將函數(shù)修改為穩(wěn)定狀態(tài)(immutable)
db_15fb=# create or replace function f1(regconfig,text) returns tsvector as $$ 
 select to_tsvector($1,$2); 
 $$ language sql immutable strict; 
CREATE FUNCTION
Time: 13.580 ms

db_15fb=# create or replace function f1(text) returns tsvector as $$ 
 select to_tsvector($1); 
 $$ language sql immutable strict; 
CREATE FUNCTION

Time: 17.822 ms
db_15fb=# alter function record_out(record) immutable; 
ALTER FUNCTION
Time: 20.823 ms

db_15fb=# alter function textin(cstring) immutable; 
ALTER FUNCTION
Time: 15.078 ms

--創(chuàng)建c_baah,c_ajmc字段索引
db_15fb=# create index i_t_msys_ah_ajmc on db_msys.t_msys using gin(f1('testzhcfg'::regconfig,c_baah||c_ajmc::text));
CREATE INDEX
Time: 38587.146 ms (00:38.587)
--查詢c_baah,c_ajmc包含'北京'和'華宇'的記錄，等效于to_tsquery('testzhcfg','北京華宇') ; to_tsquery('北京&華宇')效率稍高

db_15fb=# select c_baah,c_ajmc from db_msys.t_msys where f1('testzhcfg'::regconfig,c_baah||c_ajmc::text) @@ to_tsquery('北京&華宇') ; 
 c_baah | c_ajmc 
---------------+----------------------
 北京科技園 | 華宇信息
 華宇 | 北京決定和華宇
 測(cè)試案號(hào) | 測(cè)試北京與華宇xx糾紛
 北京五環(huán)之歌 | 北京和華宇信息
 (2018)xxxxxx1 | 北京出席華宇科技
 測(cè)試案號(hào)華宇 | 北京華宇
(6 rows)

Time: 1.222 ms
db_15fb=# explain analyze select c_baah,c_ajmc from db_msys.t_msys where f1('testzhcfg'::regconfig,c_baah||c_ajmc::text) @@ to_tsquery('北京&華宇') ; 
    QUERY PLAN    
---------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=32.32..72.33 rows=9 width=106) (actual time=0.184..0.197 rows=6 loops=1)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, ((c_baah)::text || (c_ajmc)::text)) @@ to_tsquery('北京&華宇'::text))
 Heap Blocks: exact=3
 -> Bitmap Index Scan on i_t_msys_ah_ajmc (cost=0.00..32.32 rows=9 width=0) (actual time=0.163..0.163 rows=7 loops=1)
 Index Cond: (to_tsvector('testzhcfg'::regconfig, ((c_baah)::text || (c_ajmc)::text)) @@ to_tsquery('北京&華宇'::text))
 Planning time: 0.329 ms
 Execution time: 0.251 ms
(7 rows)

Time: 1.396 ms

----查詢c_baah,c_ajmc包含'北京'或'華宇'的記錄
db_15fb=# select c_baah,c_ajmc from db_msys.t_msys where f1('testzhcfg'::regconfig,c_baah||c_ajmc::text) @@ to_tsquery('北京|華宇') ; 
 c_baah | c_ajmc   
     
---------------------------+--------------------
 北京科技園 | 華宇信息
 華宇  | 北京決定和華宇
 測(cè)試案號(hào)  | 測(cè)試北京與華宇xx糾紛
 北京五環(huán)之歌 | 北京和華宇信息
 北京奧運(yùn)  | 之歌
 (2017)xxxxx民初xxxx號(hào) | 華宇
 (2017)xxxx1民初xxxx號(hào) | 北京

...未顯示完全
Time: 9.965 ms
db_15fb=# explain analyze select c_baah,c_ajmc from db_msys.t_msys where f1('testzhcfg'::regconfig,c_baah||c_ajmc::text) @@ to_tsquery('北京|華宇') ; 
    QUERY PLAN    
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=63.89..10564.67 rows=3566 width=106) (actual time=1.104..6.190 rows=2942 loops=1)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, ((c_baah)::text || (c_ajmc)::text)) @@ to_tsquery('北京|華宇'::text))
 Heap Blocks: exact=1353
 -> Bitmap Index Scan on i_t_msys_ah_ajmc (cost=0.00..62.99 rows=3566 width=0) (actual time=0.853..0.853 rows=2944 loops=1)
 Index Cond: (to_tsvector('testzhcfg'::regconfig, ((c_baah)::text || (c_ajmc)::text)) @@ to_tsquery('北京|華宇'::text))
 Planning time: 0.285 ms
 Execution time: 6.429 ms
(7 rows)

Time: 7.670 ms


--查詢'北京華宇'
db_15fb=# select c_baah,c_ajmc from db_msys.t_msys where f1('testzhcfg'::regconfig,c_baah||c_ajmc::text) @@ phraseto_tsquery('testzhcfg','北京華宇') ; 
 c_baah | c_ajmc 
------------------------+----------------------
 (2017)川0191民初3198號(hào) | 測(cè)試北京華宇信息技術(shù)
 (2017)川0191民初9022號(hào) | 測(cè)試北京華宇xxx
(2 rows)

Time: 1.786 ms

行級(jí)全文檢索[

比如需要在所有列中找到匹配'北京'的值
使用t_msys::text可以將行轉(zhuǎn)成一個(gè)大文本。
--創(chuàng)建行級(jí)全文檢索
db_15fb=# create index i_t_msys_all on db_msys.t_msys using gin(f1('testzhcfg'::regconfig,t_msys::text));
CREATE INDEX
Time: 128538.026 ms (02:08.538)
--查詢所有字段包含'北京'的情況
db_15fb=# select c_jksxcsmc,c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg'::regconfig,t_msys::text)@@to_tsquery('北京');
 c_jksxcsmc | c_baah | c_ajmc 
       
----------------------------+---------------------------+-------------------------------------------------------------------------
 南京xx信息技術(shù)有限公司 | 北京科技園 | 華宇信息
 南京xx信息技術(shù)有限公司 | 華宇  | 北京決定和華宇
 南京xx信息技術(shù)有限公司 | 測(cè)試案號(hào)  | 測(cè)試北京與華宇xx糾紛
 南京xx信息技術(shù)有限公司 | 北京五環(huán)之歌 | 北京和華宇信息
 南京xx信息技術(shù)有限公司 | 北京奧運(yùn)  | 之歌
 北京華宇信息技術(shù)有限公司 | 測(cè)試數(shù)據(jù) | 測(cè)試數(shù)據(jù)
 測(cè)試北京信息技術(shù) | 測(cè)試數(shù)據(jù) | 測(cè)試數(shù)據(jù)
...
Time: 10.382 ms
db_15fb=# explain analyze select c_jksxcsmc,c_baah,c_ajmc from db_msys.t_msys where to_tsvector('testzhcfg'::regconfig,t_msys::text)@@to_tsquery('北京');
    QUERY PLAN    
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_msys (cost=38.10..6134.09 rows=1787 width=146) (actual time=1.014..6.792 rows=2841 loops=1)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, (t_msys.*)::text) @@ to_tsquery('北京'::text))
 Heap Blocks: exact=1281
 -> Bitmap Index Scan on i_t_msys_all (cost=0.00..37.66 rows=1787 width=0) (actual time=0.788..0.788 rows=2843 loops=1)
 Index Cond: (to_tsvector('testzhcfg'::regconfig, (t_msys.*)::text) @@ to_tsquery('北京'::text))
 Planning time: 0.312 ms
 Execution time: 7.056 ms
(7 rows)

Time: 8.364 ms

權(quán)重排序

查詢術(shù)語在文檔中出現(xiàn)的頻率，術(shù)語在文檔中的接近程度，以及文檔中出現(xiàn)的部分的重要性
--c_ajmc根據(jù)權(quán)重排序
db_15fb=# select c_ajmc,ts_rank(to_tsvector('testzhcfg',c_ajmc),to_tsquery('testzhcfg','北京華宇')) rank 
from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc) @@to_tsquery('testzhcfg','北京華宇') 
order by rank desc
;
 c_ajmc | rank 
----------------------+----------
 北京華宇，北京華宇 | 0.910206
 測(cè)試北京與華宇xx糾紛 | 0.463622
 北京和華宇信息 | 0.463622
 北京華宇 | 0.463622
 北京決定和華宇 | 0.457134
 北京出席華宇科技 | 0.457134
(6 rows)

Time: 2.179 ms

--c_baah,c_ajmc多字段權(quán)重排序
db_15fb=# select c_baah,c_ajmc,ts_rank(f1('testzhcfg'::regconfig,c_baah||c_ajmc::text),to_tsquery('北京&華宇')) rank 
db_15fb-# from db_msys.t_msys where f1('testzhcfg'::regconfig,c_baah||c_ajmc::text) @@to_tsquery('北京&華宇') 
db_15fb-# order by rank desc;
 c_baah | c_ajmc | rank 
-------------------+----------------------+-----------
 北京華宇,北京華宇 | 北京華宇，北京華宇 | 0.733734
 測(cè)試案號(hào)華宇 | 北京華宇 | 0.186813
 華宇 | 北京決定和華宇 | 0.185238
 北京五環(huán)之歌 | 北京和華宇信息 | 0.181526
 測(cè)試案號(hào) | 測(cè)試北京與華宇xx糾紛 | 0.0991032
 (2018)xxxxxx1 | 北京出席華宇科技 | 0.0973585
 北京科技園 | 華宇信息 | 0.095243
(7 rows)
Time: 2.038 ms

--查詢離婚信息，返回結(jié)果26610條，耗時(shí)849ms
db_15fb=# explain (analyze,verbose,buffers) select c_ajmc,ts_rank(to_tsvector('testzhcfg',c_ajmc),to_tsquery('testzhcfg','離婚')) rank 
db_15fb-# from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc) @@to_tsquery('testzhcfg','離婚') order by rank desc limit 10
db_15fb-# ;
    QUERY PLAN     
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=48.53..48.53 rows=1 width=84) (actual time=849.020..849.023 rows=10 loops=1)
 Output: c_ajmc, (ts_rank(to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text), '''離婚'' & ''離'' & ''婚'''::tsquery))
 Buffers: shared hit=11372
 -> Sort (cost=48.53..48.53 rows=1 width=84) (actual time=849.017..849.018 rows=10 loops=1)
 Output: c_ajmc, (ts_rank(to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text), '''離婚'' & ''離'' & ''婚'''::tsquery))
 Sort Key: (ts_rank(to_tsvector('testzhcfg'::regconfig, (t_msys.c_ajmc)::text), '''離婚'' & ''離'' & ''婚'''::tsquery)) DESC
 Sort Method: top-N heapsort Memory: 25kB
 Buffers: shared hit=11372
 -> Bitmap Heap Scan on db_msys.t_msys (cost=44.00..48.52 rows=1 width=84) (actual time=14.057..825.193 rows=26610 loops=1)
 Output: c_ajmc, ts_rank(to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text), '''離婚'' & ''離'' & ''婚'''::tsquery)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, (t_msys.c_ajmc)::text) @@ '''離婚'' & ''離'' & ''婚'''::tsquery)
 Heap Blocks: exact=11336
 Buffers: shared hit=11372
 -> Bitmap Index Scan on i_t_msys_c_ajmc (cost=0.00..44.00 rows=1 width=0) (actual time=11.260..11.260 rows=26610 loops=1)
  Index Cond: (to_tsvector('testzhcfg'::regconfig, (t_msys.c_ajmc)::text) @@ '''離婚'' & ''離'' & ''婚'''::tsquery)
  Buffers: shared hit=36
 Planning time: 0.384 ms
 Execution time: 849.099 ms
(18 rows)

Time: 850.649 ms

--查詢合同|糾紛，返回179308條數(shù)據(jù)，耗時(shí)10s
db_15fb=# explain (analyze,verbose,buffers) select c_ajmc,ts_rank(to_tsvector('testzhcfg',c_ajmc),to_tsquery('testzhcfg','合同|糾紛')) rank 
from db_msys.t_msys where to_tsvector('testzhcfg',c_ajmc) @@to_tsquery('testzhcfg','合同|糾紛') 
;
     QUERY PLAN     
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on db_msys.t_msys (cost=80.00..84.52 rows=1 width=84) (actual time=148.596..10658.341 rows=179308 loops=1)
 Output: c_ajmc, ts_rank(to_tsvector('testzhcfg'::regconfig, (c_ajmc)::text), '''合同'' & ''合'' & ''同'' | ''糾紛'' & ''糾'' & ''紛'''::tsquery)
 Recheck Cond: (to_tsvector('testzhcfg'::regconfig, (t_msys.c_ajmc)::text) @@ '''合同'' & ''合'' & ''同'' | ''糾紛'' & ''糾'' & ''紛'''::tsquery)
 Heap Blocks: exact=16632
 Buffers: shared hit=16811
 -> Bitmap Index Scan on i_t_msys_c_ajmc (cost=0.00..80.00 rows=1 width=0) (actual time=144.298..144.298 rows=179310 loops=1)
 Index Cond: (to_tsvector('testzhcfg'::regconfig, (t_msys.c_ajmc)::text) @@ '''合同'' & ''合'' & ''同'' | ''糾紛'' & ''糾'' & ''紛'''::tsquery)
 Buffers: shared hit=179
 Planning time: 0.373 ms
 Execution time: 10695.288 ms
(10 rows)

可以看出當(dāng)查詢的結(jié)果集大的時(shí)候排序是非常耗時(shí)的，因?yàn)樗蟛樵兠恳粋€(gè)匹配文檔的tsvector，如果一行較大，可能存儲(chǔ)在toast表中，這樣就涉及到大量的隨機(jī)訪問

磁盤io會(huì)升高。不幸的是，這幾乎不可能避免，因?yàn)閷?shí)際查詢常常導(dǎo)致巨大數(shù)目的匹配。

表大?。?
db_15fb=# select pg_size_pretty(pg_relation_size('t_msys'));
 pg_size_pretty 
----------------
 131 MB
(1 row)

Time: 0.858 ms
--索引使用

| 索引類型 | 索引名稱  | 索引大小 | 創(chuàng)建耗時(shí) | 場(chǎng)景 |
| :----------- | ----------------------- | -------- | -------------|--------------|
| btree | i_t_msys_btrre_c_ajmc | 37MB | 4189.886 ms |前模糊匹配 |
| btree-reverse| i_t_msys_reverse_c_ajmc | 37MB | 4011.131 ms |后模糊匹配 |
| gin-pg_trgm |i_t_msys_gin_c_ajmc | 67MB | 25013.192 ms |全模糊匹配三元組|
| gin-zhparser |i_t_msys_c_ajmc | 21MB		| 32601.072 ms |單字段全文檢索 |
| gin-zhparser |i_t_msys_ah_ajmc | 25MB		| 38587.146 ms |多字段全文檢索 |
| gin-zhparser |i_t_msys_all | 106MB	| 128538.026 ms|行級(jí)全文檢索 |
行級(jí)全文檢索占用空間接近表達(dá)小，創(chuàng)建也比較耗時(shí)。

結(jié)語

1.后模糊匹配(xxx%)，可使用btree創(chuàng)建索引，效率比gin索引高，using btree(c_ajmc text_pattern_ops).

2.前模糊匹配(%xxx)，btree和gin的效率相差不大，但是gin創(chuàng)建耗費(fèi)時(shí)間，且gin比btree索引大。所以推薦使用btree reverse函數(shù)創(chuàng)建索引。using btree(reverse(c_ajmc))

3.全模糊匹配(%xxx%),可使用gin創(chuàng)建索引，但是pg_trgm支持最少三個(gè)字符。using gin(c_ajmc gin_trgm_ops)

4.如果需要對(duì)多個(gè)字段的全文檢索，比如查詢案號(hào)，或者案件名稱這兩個(gè)字段中包含‘北京'的值，或者案件名稱中包含‘北京'和‘華宇'可以使用全文檢索，具體的創(chuàng)建參考上面的例子。

5.需要注意的是當(dāng)全文檢索返回的結(jié)果集很大時(shí)，按照權(quán)重排序效率會(huì)很低?。。?/p>

6.需要注意的是使用全模糊匹配，查詢的字符太少返回的結(jié)果多，會(huì)影響查詢效率?。?！

7.如果該字段僅需要后模糊匹配只需要建索引：using btree(c_ajmc text_pattern_ops)。如果該字段僅需要前模糊匹配則建索引using btree(reverse(c_ajmc))。如果字段有全模糊匹配也有前后模糊匹配就只需要建一個(gè)gin索引即可。

7.pg_trgm

8.zhparser

參考資料

ts_rank