服务器之家

服务器之家 > 正文

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

时间：2021-04-02 21:49     来源/作者：月图灵

ArteryBase-模糊匹配大杀器

问题背景

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
--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%'不能少于三个汉字。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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以前需要手动安装

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--查看安装扩展
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)

全文检索查询

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
--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是三元组匹配，所以这个地方就不能实现。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
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

行级全文检索[

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
比如需要在所有列中找到匹配'北京'的值
使用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

权重排序

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
查询术语在文档中出现的频率，术语在文档中的接近程度，以及文档中出现的部分的重要性
--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会升高。不幸的是，这几乎不可能避免，因为实际查询常常导致巨大数目的匹配。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
表大小：
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模糊匹配内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/zhangfx01/p/postgres.html

标签：
PostgreSQL 模糊 匹配 

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
Intellij idea2020永久破解，亲测可用！！！
Intellij idea2020永久破解，亲测可用！！！ 2020-07-29
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
返回顶部