当时数量量比较庞大的时候，做模糊查询效率很慢，为了优化查询效率，尝试如下方法做效率对比。

一、对比情况说明：

1、数据量100w条数据

2、执行sql

二、对比结果

explain analyze SELECT
 c_patent,
 c_applyissno,
 d_applyissdate,
 d_applydate,
 c_patenttype_dimn,
 c_newlawstatus,
 c_abstract
FROM
 public.t_knowl_patent_zlxx_temp
WHERE
 c_applicant LIKE '%本溪满族自治县连山关镇安平安养殖场%';

1、未建索时执行计划：

"Gather (cost=1000.00..83803.53 rows=92 width=1278) (actual time=217.264..217.264 rows=0 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 -> Parallel Seq Scan on t_knowl_patent_zlxx (cost=0.00..82794.33 rows=38 width=1278) (actual time=212.355..212.355 rows=0 loops=3)
  Filter: ((c_applicant)::text ~~ '%本溪满族自治县连山关镇安平安养殖场%'::text)
  Rows Removed by Filter: 333333
Planning time: 0.272 ms
Execution time: 228.116 ms"

2、btree索引

建索引语句

1CREATE INDEX idx_public_t_knowl_patent_zlxx_applicant ON public.t_knowl_patent_zlxx(c_applicant varchar_pattern_ops);

执行计划

"Gather (cost=1000.00..83803.53 rows=92 width=1278) (actual time=208.253..208.253 rows=0 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 -> Parallel Seq Scan on t_knowl_patent_zlxx (cost=0.00..82794.33 rows=38 width=1278) (actual time=203.573..203.573 rows=0 loops=3)
  Filter: ((c_applicant)::text ~~ '%本溪满族自治县连山关镇安平安养殖场%'::text)
  Rows Removed by Filter: 333333
Planning time: 0.116 ms
Execution time: 218.189 ms"

但是如果将查询sql稍微改动一下，把like查询中的前置%去掉是这样的

Index Scan using idx_public_t_knowl_patent_zlxx_applicant on t_knowl_patent_zlxx_temp (cost=0.55..8.57 rows=92 width=1278) (actual time=0.292..0.292 rows=0 loops=1)
 Index Cond: (((c_applicant)::text ~>=~ '本溪满族自治县连山关镇安平安养殖场'::text) AND ((c_applicant)::text ~<~ '本溪满族自治县连山关镇安平安养殖圻'::text))
 Filter: ((c_applicant)::text ~~ '本溪满族自治县连山关镇安平安养殖场%'::text)
Planning time: 0.710 ms
Execution time: 0.378 ms

3、gin索引

创建索引语句(postgresql要求在9.6版本及以上)

create extension pg_trgm;CREATE INDEX idx_public_t_knowl_patent_zlxx_applicant ON public.t_knowl_patent_zlxx USING gin (c_applicant gin_trgm_ops);

执行计划

Bitmap Heap Scan on t_knowl_patent_zlxx (cost=244.71..600.42 rows=91 width=1268) (actual time=0.649..0.649 rows=0 loops=1)
 Recheck Cond: ((c_applicant)::text ~~ '%本溪满族自治县连山关镇安平安养殖场%'::text)
 -> Bitmap Index Scan on idx_public_t_knowl_patent_zlxx_applicant (cost=0.00..244.69 rows=91 width=0) (actual time=0.647..0.647 rows=0 loops=1)