当前位置:   article > 正文

Postgresql模糊匹配案例(包括中文前后模糊)_bpchar_pattern_ops

bpchar_pattern_ops

经常被问到为什么模糊匹配不走索引,验证几种情况(Pg9.4.18)

构造测试数据

create table test(id int, info text);
insert into test select generate_series(1,1000000),md5(random()::text);
create index idx on test(info collate "C");
  • 1
  • 2
  • 3

前模糊

规则

  1. 索引、查询条件的collate必须一致才能使用索引。

  2. collate<>"C"的话,使用默认的索引ops是不能走索引的需要制定对应类型的ops,例如

    text_pattern_ops

    varchar_pattern_ops

    bpchar_pattern_ops

test01 走索引

create table testc(id int, info text collate "C");
insert into testc select generate_series(1,1000000),md5(random()::text);
create index idxc on testc(info collate "C");
explain (analyze,verbose,timing,costs,buffers) select * from testc where info like 'abcd%' collate "C";
  • 1
  • 2
  • 3
  • 4

test02 不走索引

create table testcz(id int, info text);
insert into testcz select generate_series(1,1000000),md5(random()::text);
create index idxcz on testcz(info collate "zh_CN");
explain (analyze,verbose,timing,costs,buffers) select * from testcz where info like 'abcd%' collate "zh_CN";
  • 1
  • 2
  • 3
  • 4

test03 使用对应的pattern ops走索引

create table testcp (id int, info text);
insert into testcp select generate_series(1,1000000),md5(random()::text);
create index indexcp on testcp(info text_pattern_ops);
explain (analyze,verbose,timing,costs,buffers) select * from testcp where info like 'abcd%';
  • 1
  • 2
  • 3
  • 4

test 04 使用对应的pattern ops走索引在zh_CN也是列时也走索引

create table testcc (id int, info text collate "zh_CN");
insert into testcc select generate_series(1,1000000),md5(random()::text);
create index idxcc on testcc(info text_pattern_ops);
explain (analyze,verbose,timing,costs,buffers) select * from testcc where info like 'abcd%';
  • 1
  • 2
  • 3
  • 4

test05 lc_collate<>C时不走索引,需要走索引的话使用对应的pattern ops

create table testcn (id int, info text collate "zh_CN");
insert into testcn select generate_series(1,1000000),md5(random()::text);
create index idxcn on testcn(info collate "zh_CN");
explain (analyze,verbose,timing,costs,buffers) select * from testcn where info like 'abcd%';
  • 1
  • 2
  • 3
  • 4

后模糊

reverse()后同前模糊

test01 collate<>“C” 使用对应的text_pattern_ops可以搞定

drop table test;
create table test(id int, info text);
create index idx on test(info collate "C");
create index idx1 on test(reverse(info) collate "C");
insert into test select generate_series(1,1000000),md5(random()::text);
explain (analyze,verbose,timing,costs,buffers) select * from test where reverse(info) like '4152%' collate "C";

create table testz(id int, info text collate "zh_CN");
create index idxz on testz(reverse(info) text_pattern_ops);
insert into testz select generate_series(1,1000000),md5(random()::text);
explain (analyze,verbose,timing,costs,buffers) select * from testz where reverse(info) like '4152%';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

前后模糊

test01 走索引

drop table test;
create extension pg_trgm;
create table test(id int, info text);
create index idx_test_text on test using gin (info gin_trgm_ops);
insert into test select generate_series(1,1000000),md5(random()::text);

explain (analyze,verbose,timing,costs,buffers) select * from test where info like '%4152%';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

前后模糊中文

规则

  1. 对于中文,lc_ctype不能为"C",只有TOKEN分割正确效果才是OK的
  2. 在"C"的库中使用有严重性能问题

test01

DB : test06 | pg9002311 | UTF8 | zh_CN.utf8 | zh_CN.utf8

create or replace function gen_hanzi(int) returns text as $$                  
declare        
  res text;        
begin        
  if $1 >=1 then        
    select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);        
    return res;        
  end if;        
  return null;        
end;        
$$ language plpgsql strict;

create extension pg_trgm;
create table test001(c1 text); 
insert into test001 select gen_hanzi(20) from generate_series(1,100000);
create index idx_test001_1 on test001 using gin (c1 gin_trgm_ops);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

走索引:性能没问题

explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '你%';      --0.083 ms
explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%恧顤';    --0.039 ms
explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%燋邢賀%'; --0.042 ms
  • 1
  • 2
  • 3

走索引:有性能问题!

-- DB : postgres  | pg9002311 | UTF8     | C          | C
explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '你%';      --45.050 ms
explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%恧顤';    --61.116 ms
explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%燋邢賀%'; --60.768 ms

-- "C" 建表的时候制定collate "zh_CN"
explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '你%';      --46.050 ms
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

总结

  1. 如果只有前模糊查询需求(字符串 like ‘xx%’),使用collate "C"的b-tree索引;当collate不为"C"时,
    可以使用类型对应的pattern ops(例如text_pattern_ops)建立b-tree索引。
  2. 如果只有后模糊的查询需求(字符串 like ‘%abc’ 等价于 reverse(字符串) like ‘cba%’),
    使用collate "C"的reverse()表达式的b-tree索引;当collate不为"C"时,
    可以使用类型对应的pattern ops(例如text_pattern_ops)建立b-tree索引。
  3. 如果有前后模糊查询需求,并且包含中文,请使用lc_ctype <> "C"的数据库,同时使用pg_trgm插件的gin索引。
    (只有TOKEN分割正确效果才是OK的。(lc_ctype设置正确,才能够正确的逐一分割多字节字符串中的文字:
    Character classification (What is a letter? Its upper-case equivalent?))。)
  4. 如果有前后模糊查询需求,并且不包含中文,请使用pg_trgm插件的gin索引。
  5. 如果有正则表达式查询需求,请使用pg_trgm插件的gin索引。
  6. 如果有输入条件少于3个字符的模糊查询需求,可以使用GIN表达式索引,通过数组包含的方式进行搜索,性能一样非常好。

参考德哥文章,原文地址
https://github.com/digoal/blog/blob/master/201704/20170426_01.md

附:

   测试这样的数据库
      zfba=# \l
                                    List of databases
       Name    |  Owner  | Encoding |  Collation  |    Ctype    |  Access privileges  
      -----------+---------+----------+-------------+-------------+---------------------
       zfba      | zfba    | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |    
   
   create database test06 with encoding 'UTF-8' lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8' template template0;
   
   test06=# \l+
                      List of databases
      Name    |   Owner   | Encoding | Collation  |   Ctype    |
   -----------+-----------+----------+------------+------------+
    test06    | pg9002311 | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
   (5 rows)
   
   create table testcn (id int, info text);
   insert into testcn select generate_series(1,1000000),md5(random()::text);
   create index idxcn on testcn(info);
   
   -- 不走索引!
   explain (analyze,verbose,timing,costs,buffers) select * from testcn where info like 'abcd%';
   
   -- 走索引!
   create index idxcx on testcn(info text_pattern_ops);
   explain (analyze,verbose,timing,costs,buffers) select * from testcn where info like 'abcd%';
  • 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
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/笔触狂放9/article/detail/135174
推荐阅读
相关标签
  

闽ICP备14008679号