伏龙阁资源网

postgresql模糊匹配大杀器(推荐)
站长资源 2024/11/23 佚名
2 0

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

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
www.wwsws.com 伏龙阁资源网
39,976影音资源
44,792技术资源
21,817软件资源
651,128站长资源
一句话新闻
苹果官宣WWDC 2024！预计会有大批AI功能 - 2024/11/23

3月27日消息，苹果宣布2024年全球开发者大会（WWDC）将于6月10日至6月14日举行，巧合的是，这次大会与端午假期重合。

苹果官方表示：

在线参加 Apple 每年规模最大的开发者盛会。亲眼见证 Apple 最新平台、技术和工具的发布。了解如何创建和改进你的 App 和游戏。与 Apple 设计师和工程师互动交流，与全球开发者社区建立联系。以上活动均免费在线举行。

探索各种新的工具、框架和功能，助力你打造出理想的 App 和游戏。通过视频讲座学习新技能，与 Apple 专家进行一对一会面，以推进你的项目，完善你的构思。

Swift Student Challenge 旨在支持和鼓舞下一代开发者、创作者和企业家。太平洋时间 3 月 28 日，我们将公布今年的获奖者名单。获奖者将有资格参加在 Apple Park 举办的特别活动。我们还会选出 50 名杰出获胜者，他们将受邀前往库比提诺，获得为期三天的非凡体验，包括参加 Apple Park 的特别活动。

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。

首次推出的GDDR7内存模块密度为16GB，每个模块容量为2GB。其速度预设为32 Gbps（PAM3），但也可以降至28 Gbps，以提高产量和初始阶段的整体性能和成本效益。

据三星表示，GDDR7内存的能效将提高20%，同时工作电压仅为1.1V，低于标准的1.2V。通过采用更新的封装材料和优化的电路设计，使得在高速运行时的发热量降低，GDDR7的热阻比GDDR6降低了70%。

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接