HighgoDB (PostgreSQL)利用pg_trgm的gin索引优化前后模糊查询

by

在数据库中文本的模糊查询是ES等数据库的强项，但在关系型数据库中也有一些手段，如后缀%普通索引就可以使用，前缀可以创建reverse反转索引，但是前后模糊的话，在oracle中可以创建索引使用index full scan+加回表查询，今天发现在PostgreSQL中还有pg_trgm扩展，配合GIN索引有不错的性能表现。

highgo=# create table tb(info text);
CREATE TABLE
highgo=# insert into tb select substring(md5(random()::text),1,8) from generate_series(1,100000);
ERROR:  temporary file size exceeds temp_file_limit (1kB)

highgo=# set temp_file_limit='1GB';
SET
highgo=# insert into tb select substring(md5(random()::text),1,8) from generate_series(1,100000);
INSERT 0 100000

highgo=# create table tb(info text);
CREATE TABLE

highgo=# create index idx_tb1 on tb(reverse(info));
CREATE INDEX

highgo=# explain select * from tb where info ~ '5821a';
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on tb  (cost=0.00..1791.00 rows=10 width=9)
   Filter: (info ~ '5821a'::text)
(2 rows)

Note:
还是使用seq scan.

pg_trgm

pg_trgm插件提供了文本相似度查询函数和操作符，快速搜索相似字符串的索引操作符，可以基于数据库开发文本搜索工具，或结合索引加速文本模糊查询。

pg_trgm插件引入了Trigram概念，一个Trigram是从一个字符串中取出的由三个连续字符组成的文本组。在pg_trgm插件中，从文本提取的Trigram长度为3，对于长度小于3的Trigram，将以空格前后缀填充得到最终的Trigram，且默认只能包含两个空格前缀和一个空格后缀。

函数

函数

返回值

描述

similarity(text, text)

real

返回一个数值表示两个参数的相似程度。该结果范围为0~1。

  • 0：两个字符串完全不相似。

  • 1：两个字符串完全一样。

show_trgm(text)

text[]

返回一个给定字符串中所有Trigram组成的一个数组。实际更多用于调试。

word_similarity(text, text)

real

返回一个数值，表示第一个字符串和第二个字符串中最相似的词的相似度。该函数在第二个字符串中是搜索最相似的词而不是最相似的子串。该结果范围为0~1。

  • 0：两个字符串完全不相似。

  • 1：第一个字符串和第二个字符串中的某个词相同。

索引操作符

操作符

描述

gist_trgm_ops

将文本数据转换成Trigram，并使用GIST索引结构保存Trigram。

gin_trgm_ops

将文本数据转换成Trigram，并使用GIN索引结构保存Trigram。

创建Gin grgm索引

highgo=# create extension pg_trgm;
CREATE EXTENSION

highgo=# SELECT show_trgm('abc');
        show_trgm
-------------------------
 {"  a"," ab",abc,"bc "}
(1 row)

highgo=# SELECT show_trgm('abcd');
          show_trgm
-----------------------------
 {"  a"," ab",abc,bcd,"cd "}
(1 row)

highgo=# select similarity('1abc1','1abc1');
 similarity
------------
          1
(1 row)

highgo=# select similarity('1abcd1','1abc1');
 similarity
------------
 0.44444445
(1 row)

highgo=# select similarity('1abcd1','1abcd');
 similarity
------------
      0.625
(1 row)


highgo=# create index idx_tb2 on tb using GIN( info gin_trgm_ops);
CREATE INDEX

highgo=# explain (verbose,analyze,buffers,costs,timing)    select * from tb where info ~ '5821a';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.tb  (cost=10.58..25.02 rows=10 width=9) (actual time=0.085..0.091 rows=2 loops=1)
   Output: info
   Recheck Cond: (tb.info ~ '5821a'::text)
   Heap Blocks: exact=2
   Buffers: shared hit=9
   ->  Bitmap Index Scan on idx_tb2  (cost=0.00..10.57 rows=10 width=0) (actual time=0.072..0.072 rows=2 loops=1)
         Index Cond: (tb.info ~ '5821a'::text)
         Buffers: shared hit=7
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.210 ms
 Execution Time: 0.118 ms
(12 rows)

Note:
使用上了Gin索引。

— OVER —