任何一个关系型数据库关于模糊匹配（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

注：如想直接使用to_tsvector(text)函数，需把函数改为immutable，如下：

\c postgres postgres

alter function to_tsvector(text) immutable;

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，这里就不详细介绍了，大家可以自己查阅手册。