目录

• PostgreSQL LIKE的那些事
• “太长不看”版
• 模式匹配的相关语法
• 模式匹配的相关优化措施
• 并行查询
• like上的索引查询
• like与前缀B树索引
• like与pg_trgm组件
• like与全文索引

PostgreSQL LIKE的那些事

笔者前段时间在写一个个人自用的项目时候，遇到一个需求，要从一个6百多万数量的表中，使用like ‘%keyword%’这种语法来找到需要的结果,虽然默认情况下，也不是不能接受（SSD存储+并行查询让这个事情实际的执行效率比想象中高得多），但毕竟对资源的瞬间消耗很大，让家里的小NAS有点不堪重负，因此对PostgreSQL对like的处理上，进行了诸多调查，来让这个事情的处理，可以看起来更漂亮些。

“太长不看”版

如果能确保like后面’%xxx%‘至少是三个字符，那么可以直接考虑trgm的gin索引。

不纠结于非得like语法的，可以考虑具备良好分词器的全文索引。

另外增补一个在下文中没有找到地方写的Tip：trgm的gin索引支持正则表达式方式的查询。

模式匹配的相关语法

PostgreSQL中，like这个语法的周边，是很丰富的。

注：以下说明主要来源为官方文档 Pattern Matching章节。

首先当然是like本身。

like这个语法，是用通配符，来匹配字符串（或者从别的类型转换来的字符串）的，仅支持两个通配符，‘_’代表任意单个字符，‘%’代表任意0到多个字符，官方文档有个很好的例子：

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

对于需要匹配字符中’_’,’%'这俩字符的需求，可以使用LIKE ‘’ ESCAPE ‘’语法，在ESCAPE中指定转义字符，比如!为转义字符，就可以在LIKE中用来转义，举例如下：

select tablename from pg_tables where tablename like 'tbl!_%'ESCAPE '!';

这里还有一个衍生需求，就是like匹配的时候，有时候需要忽略大小写，在PostgreSQL中，有个特殊的ILIKE关键字，专门用来做忽略大小写的LIKE，这个也是我项目中自用的方式。

另外，PostgreSQL中有两个简写： ‘’ 这个符号对应LIKE关键字，‘!’对应NOT LIKE，‘*’这个符号对应ILIKE，‘!*’对应NOT ILIKE，比方前面的SQL，还可以这么写（个人观点来说，我反对各种语法糖,这样会导致后期交接维护困难，这也是我早些年不使用Ruby语言的主要缘由之一）：

'abc' ~~ '_b_'    true
'abc' ~~ '_B_'    false
'abc' ~~* '_B_'   true

除此之外，如果仅仅是想要做前缀匹配（比方like 'abc%'这种逻辑），PostgreSQL还有另外两个办法可以解决,^@语法，以及starts_with函数：

select 'abc' ^@ 'ab' true
select 'abc' ^@ 'b' false
select starts_with('abc','a') true
select starts_with('abc','b') false

在LIKE之外，字符匹配当然不止这一个需求，还需要一个重要需求：正则表达式。

对于正则表达式的语法我这里就不做过多介绍，单只讨论正则表达式在PostgreSQL中的使用。

语法上，PostgreSQL采用SIMILAR TO 关键字，也就是这种用法：

'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false

另外一个正则表达式的用途，从字符串提取需要的字符，则是通过函数 substring(string, pattern, escape-character)/substring(string from pattern for escape-character) 实现,第三个参数，是指定转义字符，方便匹配正则表达式的符号。

substring('foobar' , '%#"o_b#"%' , '#')   oob
substring('foobar' , '#"o_b#"%' , '#')    NULL
substring('foobar' from '%#"o_b#"%' for '#')   oob
substring('foobar' from '#"o_b#"%' for '#')    NULL

前面我们提到LIKE有ILIKE进行忽略大小写的匹配，正则表达式这边，则是这种方式：

举例如下：

'abc' ~ 'abc'    true
'abc' ~ '^a'     true
'abc' ~ '(b|d)'  true
'abc' ~ '^(b|c)' false

注：PostgreSQL中，函数使用的是POSIX正则表达式匹配，而SQL中实现的是SQL正则表达式，这两个有些细微的区别，下文讨论的，仅适用于POSIX正则表达式

如果有使用正则表达式比较多的人，应该知道正则表达式中的‘()’表达式，可以用来截取匹配到的字符中仅仅自己需要的字符内容。PostgreSQL也是通过substring做到的：

substring('foobar' from 'o.b')     oob
substring('foobar' from 'o(.)b')   o

除了substring之外，PostgreSQL还有很多专用于正则表达式匹配的函数，这里仅做简单介绍，避免文章拖延过长：

以上，就是语法层面的一些讨论了，接下来，让我们看看实际使用的时候，具体的优化如何处理。

模式匹配的相关优化措施

如果是小数据量的情况，优化也许没那么重要，但如果是生产环境中使用，一个性能糟糕的选择，可能就是隐患了，这一部分，就是针对前面提到的匹配方式的一些具体的优化措施。

并行查询

自从PostgreSQL实现并行查询之后，针对不频繁的大扫描/计算量的数据处理上，已经是不需要特别多余的关注了。并行查询并非后文重点讨论，这里仅介绍下其原理，以及相关的一些调整参数：

并行查询来说，到现在的PG 12为止，包括一般查询，子查询，表连接等诸多地方都有已经实现，详细可以参考德哥的文章PostgreSQL 并行计算解说 汇总.

其原理是，当解析到SQL可以并行化处理的时候，设置一个任务分发器（Gather），拆解单个执行任务为多个可以并行的任务，发送到并行查询的执行器（Worker），当Worker执行完成后，结果再回到Gather进行下一步处理。

相关的参数主要有：

实际生产环境中，建议max_parallel_workers，max_parallel_workers_per_gather根据实际情况进行适当设置以提升性能。

like上的索引查询

现在让我们来看看，索引对like查询的影响。

注1：以下相关测试均为同一测试环境（4核CPU，8G内存，SSD，pg版本12.1），另外，为了避免并行查询对测试结果的影响，这里设置了’set max_parallel_workers_per_gather=0’。

注2：测试数据集为我个人的nas数据集，单表数据量为6518418，每次select均反复执行多次，避免io缓存等方面的影响，计时方式为psql打开\timing输出的结果。

表结构为：

                         Table "public.item_test"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 id         | integer                     |           |          |
 name       | character varying(512)      |           |          |
 cover      | integer                     |           |          |
 path       | text                        |           |          |
 parent     | integer                     |           |          |
 library_id | integer                     |           |          |
 version    | integer                     |           |          |
 order_id   | integer                     |           |          |
 item_type  | character varying(128)      |           |          |
 ctime      | timestamp without time zone |           |          |
 utime      | timestamp without time zone |           |          |
 file_type  | character varying(128)      |           |          |

like与前缀B树索引

对于没有索引的前缀查询，其结果如下：

mynas=> explain select count(*) from item_test where name like '(C96%';
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Aggregate  (cost=10000270364.64..10000270364.65 rows=1 width=8)
   ->  Seq Scan on item_test  (cost=10000000000.00..10000270363.23 rows=567 width=0)
         Filter: (name ~~ '(C96%'::text)
        
mynas=> select count(1) from item_test where name like '(C96%';
 count
-------
   536
(1 row)

Time: 745.689 ms

如果加上普通索引：

mynas=> create index idx_itemtest on item_test(name varchar_pattern_ops);
CREATE INDEX
Time: 25311.067 ms (00:25.311)
mynas=> explain select count(1) from item_test where name like '(C96%';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Aggregate  (cost=85608.37..85608.38 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=978.63..85526.89 rows=32592 width=0)
         Filter: ((name)::text ~~ '(C96%'::text)
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..970.48 rows=32592 width=0)
               Index Cond: (((name)::text ~>=~ '(C96'::text) AND ((name)::text ~<~ '(C97'::text))
(5 rows)
mynas=> select count(1) from item_test where name like '(C96%';
 count
-------
   536
(1 row)

Time: 4.852 ms

从单纯前缀查询看，可以看到B树索引效果显著，但对于后缀查询（‘%C96’），以及模糊查询（‘%C96%’），就没有用处了.

mynas=> select count(1) from item_test where name like '%C96';
 count
-------
     1
(1 row)

Time: 989.560 ms
mynas=> select count(1) from item_test where name like '%C96%';
 count
-------
   922
(1 row)

Time: 922.812 ms

实际上，对于后缀查询，有个变通的方式，就是后缀变前缀，比方我要查询’%C96’后缀，可以创建name字段的反向（从字符串最后一个字符倒着创建）的B树索引，以支持”后缀“查询。

mynas=#  create index idx_itemtest on item_test(reverse(name) varchar_pattern_ops);
CREATE INDEX
mynas=> select count(1) from item_test where name like '%C96';
 count
-------
     1
(1 row)

Time: 1051.475 ms (00:01.051)

mynas=> select count(1) from item_test where reverse(name) like '69C%';
 count
-------
     1
(1 row)

Time: 3.034 ms

顺带一提，对于ilike的情况如下：

mynas=> explain select count(1) from item_test where name ilike '(C96%';

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate  (cost=85522.33..85522.34 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=970.80..85519.07 rows=1304 width=0)
         Filter: ((name)::text ~~* '(C96%'::text)
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..970.48 rows=32592 width=0)
               Index Cond: (((name)::text ~>=~ '('::text) AND ((name)::text ~<~ ')'::text))
mynas=> select count(1) from item_test where name ilike '(C96%';
 count
-------
   536
(1 row)

Time: 63.164 ms

# 对于没有索引的情况

mynas=> drop index idx_itemtest;
DROP INDEX
mynas=> select count(1) from item_test where name ilike '(C96%';
 count
-------
   536
(1 row)

Time: 3946.207 ms (00:03.946)

like与pg_trgm组件

如前文所述，普通的B树索引，仅能用于like前缀查询，而实际的业务场景中，‘%xxx%’这种模糊查询才是使用最广泛的，在PostgreSQL中，恰好有针对这种情况的优化，就是trgm组件，结合gin/gist索引，达到实现模糊搜索的目的。

注1：pg_trgm仅支持like/ilike条件，如果有‘=’等其他需求，需要另外创建B树索引。

注2：下面描述主要来源为官方文档pg_trgm节。

先来说下pg_trgm。

pg_trgm的主要设计目的，是支持字符串的相似度检查，以及针对字符串的相似查询。其原理，是把字符串处理为三个字符长度的组（认为每个字符串自带两个前缀空格，和一个后缀空格）：比如’foobar’会被切割（select show_trgm(‘foobar’)）为{" f"," fo","ar ",bar,foo,oba,oob}

而gin/gist索引，就是在这样的三元组之上创建，可以明显认识到，这将会是个庞大的索引。

下一个问题是，当创建索引的时候，是选择gin（倒排索引）还是选择gist（范围索引）？

抛开原理不谈，让我们先测试下。

mynas=> create extension pg_trgm;
mynas=> create index idx_itemtest on item_test using gist(name gist_trgm_ops);
CREATE INDEX
mynas=> explain select count(1) from item_test where name like '%C96%';
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=218395.90..218395.91 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=15665.13..217744.06 rows=260737 width=0)
         Recheck Cond: ((name)::text ~~ '%C96%'::text)
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..15599.94 rows=260737 width=0)
               Index Cond: ((name)::text ~~ '%C96%'::text)
(5 rows)

mynas=> select count(1) from item_test where name like '%C96%';
 count
-------
   922
(1 row)

Time: 557.345 ms
mynas=> drop index idx_itemtest;
DROP INDEX
mynas=> create index idx_itemtest on item_test using gin(name gin_trgm_ops);
CREATE INDEX

mynas=> explain select count(1) from item_test where name like '%C96%';
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=205151.49..205151.50 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=2420.71..204499.64 rows=260737 width=0)
         Recheck Cond: ((name)::text ~~ '%C96%'::text)
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..2355.53 rows=260737 width=0)
               Index Cond: ((name)::text ~~ '%C96%'::text)
(5 rows)
mynas=> select count(1) from item_test where name like '%C96%';
 count
-------
   922
(1 row)

Time: 6.019 ms

数据上看，gin索引的sql响应时间，明显强于gist，那是不是说gin就绝对可取了呢？

但实际上，gin有个”小“致命的问题：因为trgm切割出来的都是3长度的组，对于1，2这种长度的like内字符串，是没办法进行匹配的!

mynas=> explain select count(1) from item_test where name like '%C9%';
                                QUERY PLAN
--------------------------------------------------------------------------
 Aggregate  (cost=273622.43..273622.45 rows=1 width=8)
   ->  Seq Scan on item_test  (cost=0.00..270363.22 rows=1303684 width=0)
         Filter: ((name)::text ~~ '%C9%'::text)
(3 rows)
mynas=> select count(1) from item_test where name like '%C9%';
 count
-------
 10039
(1 row)

Time: 1033.843 ms (00:01.034)

实际上这个限制的说法更专业点是：

1. 有前缀的模糊查询，例如a%，至少需要提供1个字符。( 搜索的是token=’ a’ )
2. 有后缀的模糊查询，例如%ab，至少需要提供2个字符。( 搜索的是token='ab ’ )
3. 前后模糊查询，例如%abcd%，至少需要提供3个字符。( 这个使用数组搜索，搜索的是token(s) 包含 {" a"," ab",abc,bcd,"cd "} )

解决方案也很简单（至少看起来）：自己手动切字符串而非依赖trgm德哥的博客/PostgreSQL模糊搜索的最佳实践，建立基于”单字符“的gin索引。

mynas=> create or replace function split001(text) returns text[] as $$
mynas$> declare
mynas$>   res text[];
mynas$> begin
mynas$>   select regexp_split_to_array($1,'') into res;
mynas$>   for i in 1..length($1)-1 loop
mynas$>     res := array_append(res, substring($1,i,2));
mynas$>   end loop;
mynas$>   return res;
mynas$> end;
mynas$> $$ language plpgsql strict immutable;
CREATE FUNCTION

mynas=> create index idx_itemtest on item_test using gin(split001(name) );

mynas=> explain select count(1) from item_test where split001(name) @> array['C9'];
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Aggregate  (cost=93090.33..93090.34 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=312.59..93008.85 rows=32592 width=0)
         Recheck Cond: (split001((name)::text) @> '{C9}'::text[])
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..304.44 rows=32592 width=0)
               Index Cond: (split001((name)::text) @> '{C9}'::text[])
(5 rows)

mynas=> select count(1) from item_test where split001(name) @> array['C9'];
 count
-------
 10039
(1 row)

Time: 15.190 ms

效果显著。

但总的来说，这个索引的维护工作量非常大，创建，以及后续的新数据插入时间代价都很大，除非确实认为单双字符检索很重要，一般不建议这种玩法。

like与全文索引

trgm这种办法，是另外建立索引，而如果不纠结于非得用LIKE这个关键字，而是使用PostgreSQL自带的全文索引功能，问题解决的办法就有更多的玩法了。

但既然使用了全文索引，那么表结构就得变一下了，首先，是新增一个tsvector列，在其上建立一个gin索引。

mynas=> alter table item_test add column tk tsvector;
mynas=> update item_test set tk=to_tsvector(name) ;
mynas=> create index idx_idx_itemtest on item_test using gin (tk);
CREATE INDEX
Time: 19366.479 ms (00:19.366)
mynas=> explain select count(1) from item_test where tk @@ to_tsquery('%C96%');

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=4545.10..4545.11 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=32.76..4542.35 rows=1099 width=0)
         Recheck Cond: (tk @@ to_tsquery('%C96%'::text))
         ->  Bitmap Index Scan on idx_idx_itemtest  (cost=0.00..32.49 rows=1099 width=0)
               Index Cond: (tk @@ to_tsquery('%C96%'::text))
(5 rows)

mynas=> select count(1) from item_test where tk @@ to_tsquery('C96');
 count
-------
   786
(1 row)

Time: 4.172 ms
mynas=> select count(1) from item_test where name like '%C96%';
 count
-------
   922
(1 row)

Time: 1610.243 ms (00:01.610)

如果不考虑精确性（实际上有922而查询出来只有786），全文搜索效率是高于前面提到的trgm处理的gin索引，而这个“精确性”的主要缘由，和分词有关，默认的to_tsvector分词，是根据分隔符切分的，比如(C96)[xxx]yyy这种格式，可以分词出来，而04_C96_004.jpg这种名字，就不会被识别到。

由于我这边的个人需求，单独处理分词器并不划算（以我的需求来说需要CJK三合一的分词器），因此后文中针对全文索引进一步的优化，我这边项目中并没有实际应用。

从技术上来说，这里的全文索引，看起来和前文提到的trgm/gin形式的索引，区别只是在是否单独处理拆分出来的词，但是，既然单独拆分出来了，就可以有更独立的方式处理了。

PostgreSQL从9.6开始，新出来一个在gin基础上，进一步优化了全文搜索功能的插件，RUM插件，postgrespro/rum，感兴趣的可以参考RUM索引接口(潘多拉魔盒)。