postgresql关于like%xxx%的优化

任何一个关系型数据库关于模糊匹配(like)的优化都是一件痛苦的事,相对而言,诸如like 'abc%'之类的还好一点,可以通过创建索引来优化,但对于like 'c%'之类的,真的就没有办法了。


        这里介绍一种postgresql关于like 'c%'的优化方法,是基于全文检索的特性来实现的。
        测试数据准备(环境centos6.5 + postgresql 9.6.1)。
        postgres=# create table ts(id int,name text);
        CREATE TABLE
        postgres=# \d ts
              Table "public.ts"
         Column |  Type   | Modifiers
        --------+---------+-----------
         id     | integer |
         name   | text    |
postgres=# insert into ts select n,n||'_pjy' from generate_series(1,2000) n;
INSERT 0 2000
postgres=# insert into ts select n,n||'_mdh' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_lmm' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_syf' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_wbd' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_hhh' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_sjw' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_jjs' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_ymd' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_biu' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# insert into ts select n,n||'_dfl' from generate_series(1,2000000) n;
INSERT 0 2000000
postgres=# select count(*) from ts;
  count  
----------
 20002000
(1 row)
开始测试:
postgres=# explain analyze select * from ts where name like '%pjy%';
                                                QUERY PLAN                                                
-----------------------------------------------------------------------------------------------------------
 Seq Scan on ts  (cost=0.00..358144.05 rows=2000 width=15) (actual time=0.006..1877.087 rows=2000 loops=1)
   Filter: (name ~~ '%pjy%'::text)
   Rows Removed by Filter: 20000000
 Planning time: 0.031 ms
 Execution time: 1877.178 ms
(5 rows)
关键一步:
postgres=# create index idx_name on ts using gin (to_tsvector('english',name));
CREATE INDEX
postgres=# vacuum analyze ts;
VACUUM
postgres=# \d ts
      Table "public.ts"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 name   | text    |
Indexes:
    "idx_name" gin (to_tsvector('english'::regconfig, name))
postgres=# explain analyze select * from ts where to_tsvector('english',name) @@ to_tsquery('pjy');
                                                     QUERY PLAN                                                     
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ts  (cost=39.75..8187.70 rows=2000 width=15) (actual time=0.016..0.016 rows=0 loops=1)
   Recheck Cond: (to_tsvector('english'::regconfig, name) @@ to_tsquery('pjy'::text))
   ->  Bitmap Index Scan on idx_name  (cost=0.00..39.25 rows=2000 width=0) (actual time=0.016..0.016 rows=0 loops=1)
         Index Cond: (to_tsvector('english'::regconfig, name) @@ to_tsquery('pjy'::text))
 Planning time: 0.094 ms
 Execution time: 0.036 ms
(6 rows)
大家可以看到,执行时间从2秒下降到了0.04毫秒!!!
关于pg的全文检索,tsvector和tsquery,这里就不详细介绍了,大家可以自己查阅手册。

发布了21 篇原创文章 · 获赞 6 · 访问量 6074
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览