postgresql模糊匹配大殺器(推薦)
ArteryBase-模糊匹配大殺器
問題背景
隨著pg越來越強大,abase目前已經升級到5.0(postgresql10.4),目前abase5.0繼承瞭全文檢索插件(zhparser),使用全文檢索越來越方便。本文會對abase支持的like模糊匹配,全文檢索,創建何種索引,如何使用進行說明。針對於各種模糊匹配均可走索引
前模糊匹配(%xxx),後模糊匹配(xxx%)
使用場景:如果簡單的前模糊匹配或者後模糊匹配則可以建一個簡單的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以信息結尾的記錄,使用反轉函數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) --查詢以張三結尾的信息 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
前模糊匹配的原理是將數據反轉存儲,查詢時字段需要反轉,輸入的值也需要反轉。 原理和前模糊匹配一樣。
全模糊匹配(%xxx%)-三元組匹配pg_trgm
使用場景:pg_trgm支持前模糊匹配,後模糊匹配以及全模糊匹配,但是全模糊匹配至少要三個字符才會走索引,在全模糊匹配不少於三個字符的場景才生效(abase一個漢字為一個字符),也就是like ‘%xxx%’不能少於三個漢字。
pg_trgm的擴展abase也是自帶的,如果不能使用可以嘗試先刪除擴展,然後在創建擴展 --查看安裝擴展 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) --如果沒有則可以創建擴展: create extension pg_trgm; --刪除擴展 drop extension pg_trgm; --c_ajmc創建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 ---------------- 測試洞庭湖數據 (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,默認會走btree因為代價比gin低,(需要註意的是pg_trgm的後模糊匹配至少需要提供一個字符才會走,前模糊匹配需要提供兩個字符) 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的反轉函數 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大,且創建耗費時間
pg_trgm擴展的前模糊匹配和後模糊匹配也均可走索引,後模糊匹配btree的效率比gin要高。
全文檢索-zhparser
使用場景:單個字段全文檢索,多字段全文檢索,行級全文檢索
目前abase5.0自帶瞭全文檢索支持,使用select * from pg_extension可以看到zhparser的擴展。在abase5.0以前需要手動安裝
--查看安裝擴展 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) --如果沒有則可以創建擴展: db_15fb=# create extension zhparser; CREATE EXTENSION --創建使用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),動詞(v),形容詞(a),成語(i),嘆詞(e)和習用語(l)6種,這6種以外的token全部被屏蔽。詞典使用的是內置的simple詞典,即僅做小寫轉換。根據需要可以靈活定義詞典和token映射,以實現屏蔽詞和同義詞歸並等功能。 --分詞效果 db_15fb=# select to_tsvector('testzhcfg','南京市長江大橋'); to_tsvector ---------------------------------------------------------------------------------------- '南京':2 '南京市':1 '大':9 '大橋':6 '市':3 '橋':10 '江':8 '長':7 '長江':5 '長江大橋':4 (1 row)
全文檢索查詢
--c_ajmc創建索引,可以看出創建gin索引相比btree是比較耗時的 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 ---------------+---------------------- 華宇 | 北京決定和華宇 測試案號 | 測試北京與華宇xx糾紛 北京五環之歌 | 北京和華宇信息 (2018)xxxxxx1 | 北京出席華宇科技 測試案號華宇 | 北京華宇 (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 ---------------+---------------------- 華宇 | 北京決定和華宇 測試案號 | 測試北京與華宇xx糾紛 北京五環之歌 | 北京和華宇信息 (2018)xxxxxx1 | 北京出席華宇科技 測試案號華宇 | 北京華宇 (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 ---------------------------+-------------- 北京科技園 | 華宇信息 華宇 | 北京決定和華宇 測試案號 | 測試北京與華宇xx糾紛 北京五環之歌 | 北京和華宇信息 (2017)xx民初xx號 | 華宇 (2017)xx民初xx號 | 北京 ... 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 這裡查詢的結果包含瞭北京和華宇,如果想讓隻查詢包含'北京'和'華宇'中間不包含其他名詞或動詞等,可使用phraseto_tsquery,此處不管是'北京華宇','北京|華宇','北京&華宇'結果都一樣。 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 --------------+---------------------- 測試案號 | 測試北京與華宇xx糾紛 北京五環之歌 | 北京和華宇信息 測試案號華宇 | 北京華宇 (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) 但是結果中包含瞭'測試北京與華宇xx糾紛','北京和華宇信息',原因是token映射中忽略瞭名詞(n),動詞(v),形容詞(a),成語(i),嘆詞(e)和習用語(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) --'北京'和'華宇'中間不包含任何詞,結果包含'北京華宇' 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號 | 測試北京華宇信息技術 (2017)川0191民初9022號 | 測試北京華宇xxx (2 rows) Time: 1.347 ms
多字段全文檢索
前面是單個字段的全文檢索,如果我需要對多個字段做全文檢索如何做呢?
比如我需要對案號,案件名稱等字段 查詢其中包含‘北京’的行。由於pg_trgm是三元組匹配,所以這個地方就不能實現。
ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; --針對多個字段的函數索引 創建函數索引,需要將函數修改為穩定狀態(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 --創建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 ---------------+---------------------- 北京科技園 | 華宇信息 華宇 | 北京決定和華宇 測試案號 | 測試北京與華宇xx糾紛 北京五環之歌 | 北京和華宇信息 (2018)xxxxxx1 | 北京出席華宇科技 測試案號華宇 | 北京華宇 (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 ---------------------------+-------------------- 北京科技園 | 華宇信息 華宇 | 北京決定和華宇 測試案號 | 測試北京與華宇xx糾紛 北京五環之歌 | 北京和華宇信息 北京奧運 | 之歌 (2017)xxxxx民初xxxx號 | 華宇 (2017)xxxx1民初xxxx號 | 北京 ...未顯示完全 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號 | 測試北京華宇信息技術 (2017)川0191民初9022號 | 測試北京華宇xxx (2 rows) Time: 1.786 ms
行級全文檢索[
比如需要在所有列中找到匹配'北京'的值 使用t_msys::text可以將行轉成一個大文本。 --創建行級全文檢索 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信息技術有限公司 | 北京科技園 | 華宇信息 南京xx信息技術有限公司 | 華宇 | 北京決定和華宇 南京xx信息技術有限公司 | 測試案號 | 測試北京與華宇xx糾紛 南京xx信息技術有限公司 | 北京五環之歌 | 北京和華宇信息 南京xx信息技術有限公司 | 北京奧運 | 之歌 北京華宇信息技術有限公司 | 測試數據 | 測試數據 測試北京信息技術 | 測試數據 | 測試數據 ... 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
權重排序
查詢術語在文檔中出現的頻率,術語在文檔中的接近程度,以及文檔中出現的部分的重要性 --c_ajmc根據權重排序 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 測試北京與華宇xx糾紛 | 0.463622 北京和華宇信息 | 0.463622 北京華宇 | 0.463622 北京決定和華宇 | 0.457134 北京出席華宇科技 | 0.457134 (6 rows) Time: 2.179 ms --c_baah,c_ajmc多字段權重排序 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 測試案號華宇 | 北京華宇 | 0.186813 華宇 | 北京決定和華宇 | 0.185238 北京五環之歌 | 北京和華宇信息 | 0.181526 測試案號 | 測試北京與華宇xx糾紛 | 0.0991032 (2018)xxxxxx1 | 北京出席華宇科技 | 0.0973585 北京科技園 | 華宇信息 | 0.095243 (7 rows) Time: 2.038 ms --查詢離婚信息,返回結果26610條,耗時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條數據,耗時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)
可以看出當查詢的結果集大的時候排序是非常耗時的,因為它要求查詢每一個匹配文檔的tsvector,如果一行較大,可能存儲在toast表中,這樣就涉及到大量的隨機訪問
磁盤io會升高。不幸的是,這幾乎不可能避免,因為實際查詢常常導致巨大數目的匹配。
表大小: db_15fb=# select pg_size_pretty(pg_relation_size('t_msys')); pg_size_pretty ---------------- 131 MB (1 row) Time: 0.858 ms --索引使用 | 索引類型 | 索引名稱 | 索引大小 | 創建耗時 | 場景 | | :----------- | ----------------------- | -------- | -------------|--------------| | 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|行級全文檢索 | 行級全文檢索占用空間接近表達小,創建也比較耗時。
結語
1.後模糊匹配(xxx%),可使用btree創建索引,效率比gin索引高,using btree(c_ajmc text_pattern_ops).
2.前模糊匹配(%xxx),btree和gin的效率相差不大,但是gin創建耗費時間,且gin比btree索引大。所以推薦使用btree reverse函數創建索引。using btree(reverse(c_ajmc))
3.全模糊匹配(%xxx%),可使用gin創建索引,但是pg_trgm支持最少三個字符。using gin(c_ajmc gin_trgm_ops)
4.如果需要對多個字段的全文檢索,比如查詢案號,或者案件名稱這兩個字段中包含‘北京’的值,或者案件名稱中包含‘北京’和‘華宇’可以使用全文檢索,具體的創建參考上面的例子。
5.需要註意的是當全文檢索返回的結果集很大時,按照權重排序效率會很低!!!
6.需要註意的是使用全模糊匹配,查詢的字符太少返回的結果多,會影響查詢效率!!!
7.如果該字段僅需要後模糊匹配隻需要建索引:using btree(c_ajmc text_pattern_ops)。如果該字段僅需要前模糊匹配則建索引using btree(reverse(c_ajmc))。如果字段有全模糊匹配也有前後模糊匹配就隻需要建一個gin索引即可。
7.pg_trgm
8.zhparser
參考資料
參考資料
ts_rank
到此這篇關於postgresql模糊匹配大殺器的文章就介紹到這瞭,更多相關postgresql模糊匹配內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- postgresql 中的 like 查詢優化方案
- postgresql insert into select無法使用並行查詢的解決
- postgresql 刪除重復數據的幾種方法小結
- postgresql如何找到表中重復數據的行並刪除
- 解決postgresql insert into select無法使用並行查詢的問題