postgresql模糊匹配大杀器(推荐)

2023-07-06 10:37:03 73

ArteryBase-模糊匹配大杀器

问题背景

随着pg越来越强大，abase目前已经升级到5.0(postgresql10.4)，目前abase5.0继承了全文检索插件(zhparser)，使用全文检索越来越方便。本文会对abase支持的like模糊匹配，全文检索，创建何种索引，如何使用进行说明。针对于各种模糊匹配均可走索引

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

使用场景：如果简单的前模糊匹配或者后模糊匹配则可以建一个简单的btree索引。

--1.后模糊匹配(xxx%)
createindexi_t_msys_btrre_c_ajmcondb_msys.t_msysusingbtree(c_ajmctext_pattern_ops);
CREATEINDEX
Time:4189.886ms(00:04.190)
db_15fb=#selectc_ajmcfromdb_msys.t_msyswherec_ajmclike'北京%';
	c_ajmc
------------------------
北京决定和华宇
北京和华宇信息
北京
北京华宇，北京华宇
、、、
db_15fb=#explainanalyzeselectc_ajmcfromdb_msys.t_msyswherec_ajmclike'北京%';
QUERYPLAN
-------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=43.92..2177.91rows=4204width=80)(actualtime=0.570..2.667rows=1570loops=1)
Filter:((c_ajmc)::text~~'北京%'::text)
HeapBlocks:exact=500
->BitmapIndexScanoni_t_msys_btrre_c_ajmc(cost=0.00..42.87rows=632width=0)(actualtime=0.477..0.477rows=1570loops=1)
IndexCond:(((c_ajmc)::text~>=~'北京'::text)AND((c_ajmc)::text~<~'北亭'::text))
Planningtime:0.956ms
Executiontime:2.841ms
(7rows)

Time:4.848ms
--2.前模糊匹配(%xxx),查询以c_ajmc以信息结尾的记录，使用反转函数reverse
db_15fb=#createindexi_t_msys_reverse_c_ajmcondb_msys.t_msysusingbtree(reverse(c_ajmc)text_pattern_ops);
CREATEINDEX
Time:4011.131ms(00:04.011)
--查询以张三结尾的信息
db_15fb=#selectc_ajmcfromdb_msys.t_msyswherereverse(c_ajmc)likereverse('%张三');
c_ajmc
----------
华宇张三
北京张三
(2rows)

Time:0.910ms

--前模糊匹配也可走索引
db_15fb=#explainanalyzeselectc_ajmcfromdb_msys.t_msyswherereverse(c_ajmc)likereverse('%张三');
QUERYPLAN

-------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=114.86..5312.88rows=1787width=80)(actualtime=0.064..0.066rows=2loops=1)
Filter:(reverse((c_ajmc)::text)~~'三张%'::text)
HeapBlocks:exact=1
->BitmapIndexScanoni_t_msys_reverse_c_ajmc(cost=0.00..114.42rows=1787width=0)(actualtime=0.042..0.042rows=2loops=1)
IndexCond:((reverse((c_ajmc)::text)~>=~'三张'::text)AND(reverse((c_ajmc)::text)~<~'三弡'::text))
Planningtime:0.236ms
Executiontime:0.148ms
(7rows)

Time:1.211ms

--或者使用like'三张%'等效于reverse('%张三')
db_15fb=#explainanalyzeselectc_ajmcfromdb_msys.t_msyswherereverse(c_ajmc)like'三张%';
QUERYPLAN

-------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=114.86..5312.88rows=1787width=80)(actualtime=0.056..0.058rows=2loops=1)
Filter:(reverse((c_ajmc)::text)~~'三张%'::text)
HeapBlocks:exact=1
->BitmapIndexScanoni_t_msys_reverse_c_ajmc(cost=0.00..114.42rows=1787width=0)(actualtime=0.036..0.036rows=2loops=1)
IndexCond:((reverse((c_ajmc)::text)~>=~'三张'::text)AND(reverse((c_ajmc)::text)~<~'三弡'::text))
Planningtime:0.259ms
Executiontime:0.108ms
(7rows)

Time:1.119ms

前模糊匹配的原理是将数据反转存储，查询时字段需要反转，输入的值也需要反转。原理和前模糊匹配一样。

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

使用场景：pg_trgm支持前模糊匹配，后模糊匹配以及全模糊匹配，但是全模糊匹配至少要三个字符才会走索引，在全模糊匹配不少于三个字符的场景才生效(abase一个汉字为一个字符)，也就是like'%xxx%'不能少于三个汉字。

pg_trgm的扩展abase也是自带的，如果不能使用可以尝试先删除扩展，然后在创建扩展
--查看安装扩展
db_sqlfx=#select*frompg_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||
(9rows)
--如果没有则可以创建扩展：
createextensionpg_trgm;
--删除扩展
dropextensionpg_trgm;
--c_ajmc创建gin索引
db_15fb=#createindexi_t_msys_gin_c_ajmcondb_msys.t_msysusinggin(c_ajmcgin_trgm_ops);
CREATEINDEX
Time:25013.192ms(00:25.013)
--查询'洞庭湖'
db_15fb=#selectc_ajmcfromdb_msys.t_msyswherec_ajmclike('%洞庭湖%');
c_ajmc
----------------
测试洞庭湖数据
(1row)
Time:1.005ms
--全模糊匹配可走索引
db_15fb=#explainanalyzeselectc_ajmcfromdb_msys.t_msyswherec_ajmclike('%洞庭湖%');
QUERYPLAN
-------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=24.27..159.92rows=35width=80)(actualtime=0.088..0.088rows=1loops=1)
RecheckCond:((c_ajmc)::text~~'%洞庭湖%'::text)
HeapBlocks:exact=1
->BitmapIndexScanoni_t_msys_gin_c_ajmc(cost=0.00..24.27rows=35width=0)(actualtime=0.069..0.069rows=1loops=1)
IndexCond:((c_ajmc)::text~~'%洞庭湖%'::text)
Planningtime:0.404ms
Executiontime:0.152ms
(7rows)

Time:1.263ms
--后模糊匹配，需要先删除前面的btree，默认会走btree因为代价比gin低,(需要注意的是pg_trgm的后模糊匹配至少需要提供一个字符才会走，前模糊匹配需要提供两个字符)
dropindexi_t_msys_btrre_c_ajmc;
db_15fb=#explainanalyzeselectc_ajmcfromdb_msys.t_msyswherec_ajmclike('北京%');
QUERYPLAN
-------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=72.58..9791.59rows=4204width=80)(actualtime=1.058..4.993rows=1570loo
ps=1)
RecheckCond:((c_ajmc)::text~~'北京%'::text)
RowsRemovedbyIndexRecheck:855
HeapBlocks:exact=989
->BitmapIndexScanoni_t_msys_gin_c_ajmc(cost=0.00..71.53rows=4204width=0)(actualtime=0.869..0.8
69rows=2425loops=1)
IndexCond:((c_ajmc)::text~~'北京%'::text)
Planningtime:0.589ms
Executiontime:5.160ms
(8rows)
Time:6.658ms
--使用gin索引前模糊匹配
db_15fb=#explainanalyzeselectc_ajmcfromdb_msys.t_msyswherec_ajmclike('%合同纠纷');
QUERYPLAN
-------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=1220.09..19633.34rows=126980width=80)(actualtime=62.980..298.705rows=166872loops=1)
RecheckCond:((c_ajmc)::text~~'%合同纠纷'::text)
RowsRemovedbyIndexRecheck:12
HeapBlocks:exact=16654
->BitmapIndexScanoni_t_msys_gin_c_ajmc(cost=0.00..1188.35rows=126980width=0)(actualtime=58.905..58.905rows=166886loops=1)
IndexCond:((c_ajmc)::text~~'%合同纠纷'::text)
Planningtime:0.623ms
Executiontime:309.385ms
(8rows)
Time:311.072ms
--使用btree的反转函数
db_15fb=#explainanalyzeselectc_ajmcfromdb_msys.t_msyswherereverse(c_ajmc)likereverse('%合同纠纷');QUERYPLAN
-------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=114.86..5312.88rows=1787width=80)(actualtime=51.135..289.537rows=166872loops=1)
Filter:(reverse((c_ajmc)::text)~~'纷纠同合%'::text)
HeapBlocks:exact=16654
->BitmapIndexScanoni_t_msys_reverse_c_ajmc(cost=0.00..114.42rows=1787width=0)(actualtime=46.970..46.970rows=166874loops=1)
IndexCond:((reverse((c_ajmc)::text)~>=~'纷纠同合'::text)AND(reverse((c_ajmc)::text)~<~'纷纠吉'::text))
Planningtime:0.268ms
Executiontime:301.174ms
(7rows)

Time:302.413ms

可以看出前模糊匹配使用gin和btree都可以走索引，gin和btree的效率相差不大，但是gin索引大小比btree大，且创建耗费时间

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

全文检索-zhparser

使用场景：单个字段全文检索，多字段全文检索，行级全文检索

目前abase5.0自带了全文检索支持，使用select*frompg_extension可以看到zhparser的扩展。在abase5.0以前需要手动安装

--查看安装扩展
db_sqlfx=#select*frompg_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||
(9rows)
--如果没有则可以创建扩展：
db_15fb=#createextensionzhparser;
CREATEEXTENSION
--创建使用zhparser作为解析器的全文搜索的配置
db_15fb=#createtextsearchconfigurationtestzhcfg(parser=zhparser);
CREATETEXTSEARCHCONFIGURATION
--往全文搜索配置中增加token映射
db_15fb=#altertextsearchconfigurationtestzhcfgaddmappingforn,v,a,i,e,lwithsimple;
ALTERTEXTSEARCHCONFIGURATION
上面的token映射只映射了名词(n)，动词(v)，形容词(a)，成语(i),叹词(e)和习用语(l)6种，这6种以外的token全部被屏蔽。词典使用的是内置的simple词典，即仅做小写转换。根据需要可以灵活定义词典和token映射，以实现屏蔽词和同义词归并等功能。
--分词效果
db_15fb=#selectto_tsvector('testzhcfg','南京市长江大桥');
to_tsvector
----------------------------------------------------------------------------------------
'南京':2'南京市':1'大':9'大桥':6'市':3'桥':10'江':8'长':7'长江':5'长江大桥':4
(1row)

全文检索查询

--c_ajmc创建索引，可以看出创建gin索引相比btree是比较耗时的
db_15fb=#createindexi_t_msys_c_ajmcondb_msys.t_msysusinggin(to_tsvector('testzhcfg',c_ajmc));
CREATEINDEX
Time:32601.072ms(00:32.601)
--查询c_ajmc包含北京华宇，to_tsquery('testzhcfg','北京华宇')
db_15fb=#selectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京华宇');
c_baah|c_ajmc
---------------+----------------------
华宇|北京决定和华宇
测试案号|测试北京与华宇xx纠纷
北京五环之歌|北京和华宇信息
(2018)xxxxxx1|北京出席华宇科技
测试案号华宇|北京华宇
(5rows)

Time:1.927ms
db_15fb=#explainanalyzeselectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京华宇');
QUERYPLAN
-------------------------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=56.00..60.26rows=1width=106)(actualtime=0.989..1.004rows=3loops=1)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text)@@'''北京''&''华宇''&''华''&''宇'''::tsquery)
HeapBlocks:exact=5
->BitmapIndexScanoni_t_msys_c_ajmc(cost=0.00..56.00rows=1width=0)(actualtime=0.971..0.971rows=13loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text)@@'''北京''&''华宇''&''华''&''宇'''::tsquery)
Planningtime:0.275ms
Executiontime:1.055ms
(7rows)

Time:2.290ms
--to_tsquery('testzhcfg','北京华宇')等效于to_tsquery('testzhcfg','北京&华宇')
db_15fb=#selectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京&华宇');
c_baah|c_ajmc
---------------+----------------------
华宇|北京决定和华宇
测试案号|测试北京与华宇xx纠纷
北京五环之歌|北京和华宇信息
(2018)xxxxxx1|北京出席华宇科技
测试案号华宇|北京华宇
(5rows)

Time:2.037ms
db_15fb=#explainanalyzeselectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京&华宇');
QUERYPLAN

---------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=56.00..60.26rows=1width=106)(actualtime=0.941..0.958rows=5loops=1)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text)@@'''北京''&''华宇''&''华''&''宇'''::tsquery)
HeapBlocks:exact=5
->BitmapIndexScanoni_t_msys_c_ajmc(cost=0.00..56.00rows=1width=0)(actualtime=0.921..0.921rows=15loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text)@@'''北京''&''华宇''&''华''&
''宇'''::tsquery)
Planningtime:0.295ms
Executiontime:1.008ms
(7rows)

Time:2.070ms

--包含'北京'或者'华宇'的：to_tsquery('testzhcfg','北京|华宇')
db_15fb=#selectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京|华宇');
c_baah|c_ajmc

---------------------------+--------------
北京科技园|华宇信息
华宇|北京决定和华宇
测试案号|测试北京与华宇xx纠纷
北京五环之歌|北京和华宇信息
(2017)xx民初xx号|华宇
(2017)xx民初xx号	|北京
...
Time:10.426ms

db_15fb=#explainanalyzeselectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京|华宇');
QUERYPLAN
-------------------------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=69.85..5710.15rows=1787width=106)(actualtime=2.269..7.338rows=2941loops=1)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text)@@'''北京''|''华宇''&''华''&''宇'''::tsquery)
HeapBlocks:exact=1355
->BitmapIndexScanoni_t_msys_c_ajmc(cost=0.00..69.41rows=1787width=0)(actualtime=2.034..2.034rows=2954loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text)@@'''北京''|''华宇''&''华''&''宇'''::tsquery)
Planningtime:0.268ms
Executiontime:7.565ms
(7rows)

Time:8.655ms

这里查询的结果包含了北京和华宇，如果想让只查询包含'北京'和'华宇'中间不包含其他名词或动词等，可使用phraseto_tsquery,此处不管是'北京华宇','北京|华宇','北京&华宇'结果都一样。
db_15fb=#selectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@phraseto_tsquery('testzhcfg','北京华宇');
c_baah|c_ajmc
--------------+----------------------
测试案号|测试北京与华宇xx纠纷
北京五环之歌|北京和华宇信息
测试案号华宇|北京华宇
(3rows)

Time:2.203ms

db_15fb=#explainanalyzeselectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@phraseto_tsquery('testzhcfg','北京华宇');
QUERYPLAN
------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=56.00..60.26rows=1width=106)(actualtime=1.147..1.258rows=3loops=1)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text)@@'''北京''<->''华宇''<->''华''<->''宇'''::tsquery)
RowsRemovedbyIndexRecheck:2
HeapBlocks:exact=5
->BitmapIndexScanoni_t_msys_c_ajmc(cost=0.00..56.00rows=1width=0)(actualtime=1.016..1.016rows=15loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text)@@'''北京''<->''华宇''<->''华''<->''宇'''::tsquery)
Planningtime:0.333ms
Executiontime:1.307ms
(8rows)


但是结果中包含了'测试北京与华宇xx纠纷','北京和华宇信息',原因是token映射中忽略了名词(n)，动词(v)，形容词(a)，成语(i),叹词(e)和习用语(l)6种以外的词。如果需要可以加入其中那样就可以更加精确匹配出'北京华宇'
--不包含'与'
ALTERTEXTSEARCHCONFIGURATIONtestzhcfgADDMAPPINGFORn,v,a,i,e,lWITHsimple;
db_sqlfx=#selectto_tsvector('testzhcfg','北京与华宇');
to_tsvector
---------------------------------
'北京':1'华':3'华宇':2'宇':4
(1row)
--将所有词性全部影射出后就就包含'与'
ALTERTEXTSEARCHCONFIGURATIONtestzhcfgADDMAPPINGFORa,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,zWITHsimple;
db_sqlfx=#selectto_tsvector('testzhcfg','北京与华宇')
;
to_tsvector
------------------------------------------------------
'与':4'京':3'北':2'北京':1'华':6'华宇':5'宇':7
(1row)

--'北京'和'华宇'中间不包含任何词，结果包含'北京华宇'
ALTERTEXTSEARCHCONFIGURATIONtestzhcfgalterMAPPINGFORa,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,zWITHsimple;
db_15fb=#selectc_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@phraseto_tsquery('testzhcfg','北京华宇');
c_baah|c_ajmc
------------------------+----------------------
(2017)川0191民初3198号|测试北京华宇信息技术
(2017)川0191民初9022号|测试北京华宇xxx
(2rows)
Time:1.347ms

多字段全文检索

前面是单个字段的全文检索，如果我需要对多个字段做全文检索如何做呢？
比如我需要对案号，案件名称等字段查询其中包含‘北京'的行。由于pg_trgm是三元组匹配，所以这个地方就不能实现。

ALTERTEXTSEARCHCONFIGURATIONtestzhcfgADDMAPPINGFORn,v,a,i,e,lWITHsimple;
--针对多个字段的函数索引
创建函数索引，需要将函数修改为稳定状态(immutable)
db_15fb=#createorreplacefunctionf1(regconfig,text)returnstsvectoras$$
selectto_tsvector($1,$2);
$$languagesqlimmutablestrict;
CREATEFUNCTION
Time:13.580ms

db_15fb=#createorreplacefunctionf1(text)returnstsvectoras$$
selectto_tsvector($1);
$$languagesqlimmutablestrict;
CREATEFUNCTION

Time:17.822ms
db_15fb=#alterfunctionrecord_out(record)immutable;
ALTERFUNCTION
Time:20.823ms

db_15fb=#alterfunctiontextin(cstring)immutable;
ALTERFUNCTION
Time:15.078ms

--创建c_baah,c_ajmc字段索引
db_15fb=#createindexi_t_msys_ah_ajmcondb_msys.t_msysusinggin(f1('testzhcfg'::regconfig,c_baah||c_ajmc::text));
CREATEINDEX
Time:38587.146ms(00:38.587)
--查询c_baah,c_ajmc包含'北京'和'华宇'的记录，等效于to_tsquery('testzhcfg','北京华宇');to_tsquery('北京&华宇')效率稍高

db_15fb=#selectc_baah,c_ajmcfromdb_msys.t_msyswheref1('testzhcfg'::regconfig,c_baah||c_ajmc::text)@@to_tsquery('北京&华宇');
c_baah|c_ajmc
---------------+----------------------
北京科技园|华宇信息
华宇|北京决定和华宇
测试案号|测试北京与华宇xx纠纷
北京五环之歌|北京和华宇信息
(2018)xxxxxx1|北京出席华宇科技
测试案号华宇|北京华宇
(6rows)

Time:1.222ms
db_15fb=#explainanalyzeselectc_baah,c_ajmcfromdb_msys.t_msyswheref1('testzhcfg'::regconfig,c_baah||c_ajmc::text)@@to_tsquery('北京&华宇');
QUERYPLAN
---------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=32.32..72.33rows=9width=106)(actualtime=0.184..0.197rows=6loops=1)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,((c_baah)::text||(c_ajmc)::text))@@to_tsquery('北京&华宇'::text))
HeapBlocks:exact=3
->BitmapIndexScanoni_t_msys_ah_ajmc(cost=0.00..32.32rows=9width=0)(actualtime=0.163..0.163rows=7loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,((c_baah)::text||(c_ajmc)::text))@@to_tsquery('北京&华宇'::text))
Planningtime:0.329ms
Executiontime:0.251ms
(7rows)

Time:1.396ms

----查询c_baah,c_ajmc包含'北京'或'华宇'的记录
db_15fb=#selectc_baah,c_ajmcfromdb_msys.t_msyswheref1('testzhcfg'::regconfig,c_baah||c_ajmc::text)@@to_tsquery('北京|华宇');
c_baah|c_ajmc

---------------------------+--------------------
北京科技园|华宇信息
华宇|北京决定和华宇
测试案号|测试北京与华宇xx纠纷
北京五环之歌|北京和华宇信息
北京奥运|之歌
(2017)xxxxx民初xxxx号|华宇
(2017)xxxx1民初xxxx号|北京

...未显示完全
Time:9.965ms
db_15fb=#explainanalyzeselectc_baah,c_ajmcfromdb_msys.t_msyswheref1('testzhcfg'::regconfig,c_baah||c_ajmc::text)@@to_tsquery('北京|华宇');
QUERYPLAN
--------------------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=63.89..10564.67rows=3566width=106)(actualtime=1.104..6.190rows=2942loops=1)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,((c_baah)::text||(c_ajmc)::text))@@to_tsquery('北京|华宇'::text))
HeapBlocks:exact=1353
->BitmapIndexScanoni_t_msys_ah_ajmc(cost=0.00..62.99rows=3566width=0)(actualtime=0.853..0.853rows=2944loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,((c_baah)::text||(c_ajmc)::text))@@to_tsquery('北京|华宇'::text))
Planningtime:0.285ms
Executiontime:6.429ms
(7rows)

Time:7.670ms


--查询'北京华宇'
db_15fb=#selectc_baah,c_ajmcfromdb_msys.t_msyswheref1('testzhcfg'::regconfig,c_baah||c_ajmc::text)@@phraseto_tsquery('testzhcfg','北京华宇');
c_baah|c_ajmc
------------------------+----------------------
(2017)川0191民初3198号|测试北京华宇信息技术
(2017)川0191民初9022号|测试北京华宇xxx
(2rows)

Time:1.786ms

行级全文检索[

比如需要在所有列中找到匹配'北京'的值
使用t_msys::text可以将行转成一个大文本。
--创建行级全文检索
db_15fb=#createindexi_t_msys_allondb_msys.t_msysusinggin(f1('testzhcfg'::regconfig,t_msys::text));
CREATEINDEX
Time:128538.026ms(02:08.538)
--查询所有字段包含'北京'的情况
db_15fb=#selectc_jksxcsmc,c_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg'::regconfig,t_msys::text)@@to_tsquery('北京');
c_jksxcsmc|c_baah|c_ajmc

----------------------------+---------------------------+-------------------------------------------------------------------------
南京xx信息技术有限公司|北京科技园|华宇信息
南京xx信息技术有限公司|华宇|北京决定和华宇
南京xx信息技术有限公司|测试案号|测试北京与华宇xx纠纷
南京xx信息技术有限公司|北京五环之歌|北京和华宇信息
南京xx信息技术有限公司|北京奥运|之歌
北京华宇信息技术有限公司|测试数据|测试数据
测试北京信息技术|测试数据|测试数据
...
Time:10.382ms
db_15fb=#explainanalyzeselectc_jksxcsmc,c_baah,c_ajmcfromdb_msys.t_msyswhereto_tsvector('testzhcfg'::regconfig,t_msys::text)@@to_tsquery('北京');
QUERYPLAN
----------------------------------------------------------------------------------------------------------------------------
BitmapHeapScanont_msys(cost=38.10..6134.09rows=1787width=146)(actualtime=1.014..6.792rows=2841loops=1)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,(t_msys.*)::text)@@to_tsquery('北京'::text))
HeapBlocks:exact=1281
->BitmapIndexScanoni_t_msys_all(cost=0.00..37.66rows=1787width=0)(actualtime=0.788..0.788rows=2843loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,(t_msys.*)::text)@@to_tsquery('北京'::text))
Planningtime:0.312ms
Executiontime:7.056ms
(7rows)

Time:8.364ms

权重排序

查询术语在文档中出现的频率，术语在文档中的接近程度，以及文档中出现的部分的重要性
--c_ajmc根据权重排序
db_15fb=#selectc_ajmc,ts_rank(to_tsvector('testzhcfg',c_ajmc),to_tsquery('testzhcfg','北京华宇'))rank
fromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','北京华宇')
orderbyrankdesc
;
c_ajmc|rank
----------------------+----------
北京华宇，北京华宇|0.910206
测试北京与华宇xx纠纷|0.463622
北京和华宇信息|0.463622
北京华宇|0.463622
北京决定和华宇|0.457134
北京出席华宇科技|0.457134
(6rows)

Time:2.179ms

--c_baah,c_ajmc多字段权重排序
db_15fb=#selectc_baah,c_ajmc,ts_rank(f1('testzhcfg'::regconfig,c_baah||c_ajmc::text),to_tsquery('北京&华宇'))rank
db_15fb-#fromdb_msys.t_msyswheref1('testzhcfg'::regconfig,c_baah||c_ajmc::text)@@to_tsquery('北京&华宇')
db_15fb-#orderbyrankdesc;
c_baah|c_ajmc|rank
-------------------+----------------------+-----------
北京华宇,北京华宇|北京华宇，北京华宇|0.733734
测试案号华宇|北京华宇|0.186813
华宇|北京决定和华宇|0.185238
北京五环之歌|北京和华宇信息|0.181526
测试案号|测试北京与华宇xx纠纷|0.0991032
(2018)xxxxxx1|北京出席华宇科技|0.0973585
北京科技园|华宇信息|0.095243
(7rows)
Time:2.038ms

--查询离婚信息，返回结果26610条，耗时849ms
db_15fb=#explain(analyze,verbose,buffers)selectc_ajmc,ts_rank(to_tsvector('testzhcfg',c_ajmc),to_tsquery('testzhcfg','离婚'))rank
db_15fb-#fromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','离婚')orderbyrankdesclimit10
db_15fb-#;
QUERYPLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Limit(cost=48.53..48.53rows=1width=84)(actualtime=849.020..849.023rows=10loops=1)
Output:c_ajmc,(ts_rank(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text),'''离婚''&''离''&''婚'''::tsquery))
Buffers:sharedhit=11372
->Sort(cost=48.53..48.53rows=1width=84)(actualtime=849.017..849.018rows=10loops=1)
Output:c_ajmc,(ts_rank(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text),'''离婚''&''离''&''婚'''::tsquery))
SortKey:(ts_rank(to_tsvector('testzhcfg'::regconfig,(t_msys.c_ajmc)::text),'''离婚''&''离''&''婚'''::tsquery))DESC
SortMethod:top-NheapsortMemory:25kB
Buffers:sharedhit=11372
->BitmapHeapScanondb_msys.t_msys(cost=44.00..48.52rows=1width=84)(actualtime=14.057..825.193rows=26610loops=1)
Output:c_ajmc,ts_rank(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text),'''离婚''&''离''&''婚'''::tsquery)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,(t_msys.c_ajmc)::text)@@'''离婚''&''离''&''婚'''::tsquery)
HeapBlocks:exact=11336
Buffers:sharedhit=11372
->BitmapIndexScanoni_t_msys_c_ajmc(cost=0.00..44.00rows=1width=0)(actualtime=11.260..11.260rows=26610loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,(t_msys.c_ajmc)::text)@@'''离婚''&''离''&''婚'''::tsquery)
Buffers:sharedhit=36
Planningtime:0.384ms
Executiontime:849.099ms
(18rows)

Time:850.649ms

--查询合同|纠纷，返回179308条数据，耗时10s
db_15fb=#explain(analyze,verbose,buffers)selectc_ajmc,ts_rank(to_tsvector('testzhcfg',c_ajmc),to_tsquery('testzhcfg','合同|纠纷'))rank
fromdb_msys.t_msyswhereto_tsvector('testzhcfg',c_ajmc)@@to_tsquery('testzhcfg','合同|纠纷')
;
QUERYPLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
BitmapHeapScanondb_msys.t_msys(cost=80.00..84.52rows=1width=84)(actualtime=148.596..10658.341rows=179308loops=1)
Output:c_ajmc,ts_rank(to_tsvector('testzhcfg'::regconfig,(c_ajmc)::text),'''合同''&''合''&''同''|''纠纷''&''纠''&''纷'''::tsquery)
RecheckCond:(to_tsvector('testzhcfg'::regconfig,(t_msys.c_ajmc)::text)@@'''合同''&''合''&''同''|''纠纷''&''纠''&''纷'''::tsquery)
HeapBlocks:exact=16632
Buffers:sharedhit=16811
->BitmapIndexScanoni_t_msys_c_ajmc(cost=0.00..80.00rows=1width=0)(actualtime=144.298..144.298rows=179310loops=1)
IndexCond:(to_tsvector('testzhcfg'::regconfig,(t_msys.c_ajmc)::text)@@'''合同''&''合''&''同''|''纠纷''&''纠''&''纷'''::tsquery)
Buffers:sharedhit=179
Planningtime:0.373ms
Executiontime:10695.288ms
(10rows)

可以看出当查询的结果集大的时候排序是非常耗时的，因为它要求查询每一个匹配文档的tsvector，如果一行较大，可能存储在toast表中，这样就涉及到大量的随机访问

磁盘io会升高。不幸的是，这几乎不可能避免，因为实际查询常常导致巨大数目的匹配。

表大小：
db_15fb=#selectpg_size_pretty(pg_relation_size('t_msys'));
pg_size_pretty
----------------
131MB
(1row)

Time:0.858ms
--索引使用

|索引类型|索引名称|索引大小|创建耗时|场景|
|:-----------|-----------------------|--------|-------------|--------------|
|btree|i_t_msys_btrre_c_ajmc|37MB|4189.886ms|前模糊匹配|
|btree-reverse|i_t_msys_reverse_c_ajmc|37MB|4011.131ms|后模糊匹配|
|gin-pg_trgm|i_t_msys_gin_c_ajmc|67MB|25013.192ms|全模糊匹配三元组|
|gin-zhparser|i_t_msys_c_ajmc|21MB		|32601.072ms|单字段全文检索|
|gin-zhparser|i_t_msys_ah_ajmc|25MB		|38587.146ms|多字段全文检索|
|gin-zhparser|i_t_msys_all|106MB	|128538.026ms|行级全文检索|
行级全文检索占用空间接近表达小，创建也比较耗时。

结语

1.后模糊匹配(xxx%)，可使用btree创建索引，效率比gin索引高，usingbtree(c_ajmctext_pattern_ops).

2.前模糊匹配(%xxx)，btree和gin的效率相差不大，但是gin创建耗费时间，且gin比btree索引大。所以推荐使用btreereverse函数创建索引。usingbtree(reverse(c_ajmc))

3.全模糊匹配(%xxx%),可使用gin创建索引，但是pg_trgm支持最少三个字符。usinggin(c_ajmcgin_trgm_ops)

4.如果需要对多个字段的全文检索，比如查询案号，或者案件名称这两个字段中包含‘北京'的值，或者案件名称中包含‘北京'和‘华宇'可以使用全文检索，具体的创建参考上面的例子。

5.需要注意的是当全文检索返回的结果集很大时，按照权重排序效率会很低！！！

6.需要注意的是使用全模糊匹配，查询的字符太少返回的结果多，会影响查询效率！！！

7.如果该字段仅需要后模糊匹配只需要建索引：usingbtree(c_ajmctext_pattern_ops)。如果该字段仅需要前模糊匹配则建索引usingbtree(reverse(c_ajmc))。如果字段有全模糊匹配也有前后模糊匹配就只需要建一个gin索引即可。

7.pg_trgm

8.zhparser

参考资料

ts_rank

到此这篇关于postgresql模糊匹配大杀器的文章就介绍到这了,更多相关postgresql模糊匹配内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！