您现在的位置是:首页 > 博客网站制作 > 个人博客搭建个人博客搭建

postgresql模糊匹配好用利器

杨青青2022-02-20【个人博客搭建】人已围观

简介ArteryBase-模糊匹配大杀器问题背景随着pg越来越强大,abase目前已经升级到5.0(postgresql10.4),目前abase5.0继承了全文检索插件(zhparser),使用全文检索越来越方便。本文会对a

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大,且创建耗费时间

可以看出前模糊匹配使用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


--查询'北京华宇'
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索引即可。

Tags:

很赞哦! ()

文章评论

站点信息

  • 建站时间:2011年01月12日
  • 网站程序:帝国CMS7.5
  • 主题模板《今夕何夕》
  • 文章统计625篇文章
  • 标签管理标签云
  • 微信公众号:扫描二维码,关注我们