当前位置 博文首页 > 月图灵:postgres模糊匹配大杀器

    月图灵:postgres模糊匹配大杀器

    作者:月图灵 时间:2021-01-22 16:35

    postgres 模糊匹配 like 全模糊匹配

    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)