【搜索引擎】 PostgreSQL 10 实时全文检索和分词、相似搜索、模糊匹配实现类似Google搜索自动提示

需求分析

要通过PostgreSQL实现类似Google搜索自动提示的功能,例如要实现一个查询海量数据中的商品名字，每次输入就提示用户各种相关搜索选项，例如淘宝、京东等电商查询

思路

这个功能可以用 PostgreSQL的实时全文检索和分词、相似搜索、前模糊匹配等特性实现。具体策略是，定义一个搜索提示的最大数量。首先通过前模糊匹配查询获取数据，如果未满最大数量，则进行全文检索分词查询补偿，如果全文检索补偿查询的数据未满最大提示数量，最后就加入相识查询的结果。当然这里是一个简单的思路，复杂的还得根据实际需求实现。

构造数据

新建一张商品表，插入一千万条数据，name就是商品名字。

create table goods(id int, name varchar);

insert into goods select generate_series(1,10000000),md5(random()::varchar);

一、前模糊匹配及优化

实现SQL，每次输入就作为前缀模糊查询：

select * from goods where name like '123%' ;

这个简单的前模糊匹配SQL，可以使用B-Tree来加速优化模糊查询。

未建立索引时查询"123%"的商品名字，执行计划显示耗时大约为575ms：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' ;
========================================
Gather  (cost=1000.00..136516.59 rows=1000 width=37) (actual time=1.390..572.857 rows=2364 loops=1)
  Output: id, name
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=83334
  ->  Parallel Seq Scan on public.goods  (cost=0.00..135416.59 rows=417 width=37) (actual time=0.750..528.116 rows=788 loops=3)
        Output: id, name
        Filter: ((goods.name)::text ~~ '123%'::text)
        Rows Removed by Filter: 3332545
        Buffers: shared hit=83334
        Worker 0: actual time=1.032..511.776 rows=676 loops=1
          Buffers: shared hit=24201
        Worker 1: actual time=0.145..511.737 rows=755 loops=1
          Buffers: shared hit=26101
Planning time: 0.065 ms
Execution time: 573.157 ms

优化1，建立索引（lc_collate方式）

通过lc_collate方式建立索引、也就是B-Tree索引。

lc_collate (string) 是指报告文本数据排序使用的区域
lc_collate (string) 是指报告文本数据排序使用的区域

建立索引脚本如下

create index idx_c on goods(name collate "C");

执行计划显示耗时为10ms以内：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' collate "C";

优化2，建立索引（操作符类varchar_pattern_ops方式）

建立索引脚本如下

create index idx_varchar on goods(name varchar_pattern_ops);

执行计划显示耗时为5ms以内：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' collate "C";

======================================
Bitmap Heap Scan on public.goods  (cost=86.60..7681.10 rows=1000 width=37) (actual time=0.740..4.628 rows=2364 loops=1)
  Output: id, name
  Filter: ((goods.name)::text ~~ '123%'::text)
  Heap Blocks: exact=2330
  Buffers: shared hit=2351
  ->  Bitmap Index Scan on idx_varchar  (cost=0.00..86.35 rows=2179 width=0) (actual time=0.487..0.487 rows=2364 loops=1)
        Index Cond: (((goods.name)::text ~>=~ '123'::text) AND ((goods.name)::text ~<~ '124'::text))
        Buffers: shared hit=21
Planning time: 0.139 ms
Execution time: 4.891 ms

二、全文检索和分词（通过gin索引优化加速）

注意：全文检索和下面的相识搜索都需要pg_trgm插件。所以先要执行：

create extension pg_trgm;

具体SQL如下，每次输入空格用&符号代替，最后接:*表示模糊检索。to_tsvector ，to_tsquery参阅postgresql全文检索文档。

SELECT name FROM goods WHERE to_tsvector('English',name) @@  to_tsquery('English','aaa&bbb&cc:*')

通过执行计划查看速度：接近8秒

。。。。。。。
Planning time: 0.129 ms
Execution time: 7986.176 ms

通过gin索引来优化加速，这里to_tsvector('English',name)就是一个表达式索引。

CREATE INDEX name_idx ON goods USING GIN(to_tsvector('English',name));

优化后后的执行计划，速度为13毫秒左右：

explain (analyze,verbose,timing,costs,buffers) SELECT name FROM goods WHERE to_tsvector('English',name) @@  to_tsquery('English','aaa&bbb&cc:*')
=================================================
Bitmap Heap Scan on public.goods  (cost=88.04..109.24 rows=5 width=33) (actual time=17.343..17.353 rows=4 loops=1)
  Output: name
  Recheck Cond: (to_tsvector('english'::regconfig, (goods.name)::text) @@ '''aaa'' & ''bbb'' & ''cc'':*'::tsquery)
  Heap Blocks: exact=1
  Buffers: shared hit=473
  ->  Bitmap Index Scan on name_idx  (cost=0.00..88.04 rows=5 width=0) (actual time=17.334..17.334 rows=4 loops=1)
        Index Cond: (to_tsvector('english'::regconfig, (goods.name)::text) @@ '''aaa'' & ''bbb'' & ''cc'':*'::tsquery)
        Buffers: shared hit=472
Planning time: 0.222 ms
Execution time: 13.381 ms

三、相似搜索

具体实现SQL，通过查询结果可以看到越相似，相似度越小，可以看到，在搜索aaa bbb的时候搜索出了aaa b6b，这就是相似搜索。

SELECT name ,name <-> 'aaa bbb' FROM goods WHERE name <-> 'aaa bbb' < 0.7 LIMIT 10

aaa bbb             0
aaa bbb ccc         0.333333
aaa ccc bbb         0.333333
aaa bbb ccc ddd     0.5
aaa b6b ccc         0.666667
aaa bbb ccsdsd      0.466667
aaa                 0.5

PostgreSQL扩展知识

在第一种模糊查询中，可以使用关键字ILIKE替换LIKE， ILIKE表示字符串匹配时与大小写无关。这是一个PostgreSQL扩展、并不是标准SQL语法。

参考

postgresql索引官方文档

monkjavaer