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

 --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

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

 --查看安装扩展 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) @@ '''北京'' & ''华宇'' & ''华'
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：PostgreSQL 实现子查询返回多行的案例_PostgreSQL_
                                                                
下一篇：PostgreSQL忘记postgres账号密码的解决方法_PostgreSQL_