pg_bigm是云原生数据仓库 AnalyticDB PostgreSQL 版支持的一款开源第三方插件,该插件提供了全文本搜索能力,允许用户创建一个二元语法(2-gram)的GIN索引来加速搜索过程。

在pg_bigm插件中,从文本提取的Trigram长度为2,对于长度小于2的Trigram,将以空格前后缀填充得到最终的Trigram,并且默认只能包含一个空格前缀和一个空格后缀。示例如下:

postgres=> SELECT show_bigm('full text search');
show_bigm                            
------------------------------------------------------------------
 {" f"," s"," t",ar,ch,ea,ex,fu,"h ","l ",ll,rc,se,"t ",te,ul,xt}
(1 row)

注意事项

  • pg_bigm插件仅支持存储弹性模式实例,并且内核版本须满足以下条件:

    • AnalyticDB PostgreSQL 6.0版实例并且版本为v6.6.2.1及以上。

    • AnalyticDB PostgreSQL 7.0版实例并且版本为v7.0.6.1及以上。

  • 建立GIN索引的列长度不可以超过107,374,180字节(约102 MB),示例如下:

    postgres=> CREATE TABLE t1 (description text);
    CREATE TABLE
    
    postgres=> CREATE INDEX t1_idx ON t1 USING gin (description gin_bigm_ops);
    CREATE INDEX
    
    postgres=> INSERT INTO t1 SELECT repeat('A', 107374181);
    ERROR:OUT OF memory

    如果云原生数据仓库 AnalyticDB PostgreSQL 版数据库中存储内容的语言不是ASCII,建议您将数据库的编码方式改为UTF-8。查询当前数据库编码方式的命令如下:

    SELECT pg_encoding_to_char(encoding)
    FROM pg_database
    WHERE datname = current_database();

安装插件

请您在云原生数据仓库 AnalyticDB PostgreSQL 版实例插件管理中安装pg_bigm插件。具体操作,请参见安装、升级与卸载插件

使用方法

创建索引

示例如下:

CREATE TABLE pg_tools (tool text, description text);

INSERT INTO pg_tools VALUES ('pg_hint_plan', 'Tool that allows a user to specify an optimizer HINT to PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'Tool that allows a user to stabilize planner statistics in PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_bigm', 'Tool that provides 2-gram full text search capability in PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_trgm', 'Tool that provides 3-gram full text search capability in PostgreSQL');

CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);
CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);

执行全文本搜索

您可以使用pg_bigm插件执行全文本搜索,示例如下:

postgres=> SELECT * FROM pg_tools WHERE description LIKE '%search%';
 tool   |  description
---------+---------------------------------------------------------------------
 pg_bigm | Tool that provides 2-gram FULL text SEARCH capability IN PostgreSQL
 pg_trgm | Tool that provides 3-gram FULL text SEARCH capability IN PostgreSQL
(2 ROWS)

相似性搜索

您可以使用=%操作符进行相似性搜索。示例如下:

postgres=> SET pg_bigm.similarity_limit TO 0.2;
SET
postgres=> SELECT tool FROM pg_tools WHERE tool =% 'bigm';
tool
---------
 pg_bigm
 pg_trgm
(2 ROWS)

插件常用函数

likequery函数

使用pg_bigm插件中的slikequery函数及%操作符生成可以被LIKE关键字识别的字符串。likequery函数的更多信息,请参见附录。示例如下:

postgres=> SELECT likequery('pg_bigm has improved the full text search performance by 200%');
likequery
-------------------------------------------------------------------
 %pg\_bigm has improved the FULL text SEARCH performance BY 200\%%
(1 row)

postgres=> SELECT * FROM pg_tools WHERE description LIKE likequery('search');
 tool   | description
---------+---------------------------------------------------------------------
 pg_bigm | Tool that provides 2-gram FULL text SEARCH capability IN PostgreSQL
 pg_trgm | Tool that provides 3-gram FULL text SEARCH capability IN PostgreSQL
(2 ROWS)

show_bigm函数

使用pg_bigm插件中的show_bigm函数返回给定字符串的所有2-gram元素的集合。show_bigm函数的更多信息,请参见附录。示例如下:

postgres=> SELECT show_bigm('full text search');
show_bigm
------------------------------------------------------------------
 {" f"," s"," t",ar,ch,ea,ex,fu,"h ","l ",ll,rc,se,"t ",te,ul,xt}
(1 ROW)

bigm_similarity函数

使用pg_bigm插件中的bigm_similarity函数计算两个字符串的相似度。

重要
  • 计算 2-gram 时,会在字符串前后添加空格,因此需要注意以下内容:

    • ‘ABC’和 ‘B’的相似度为0。

    • ‘ABC' 和 ‘A’的相似度为0.25。

  • bigm_similarity函数大小写敏感,例如 ‘ABC’和 ‘abc’的相似度为0。

示例如下:

postgres=> SELECT bigm_similarity('full text search', 'text similarity search');
 bigm_similarity
-----------------
 0.5714286
(1 ROW)

postgres=> SELECT bigm_similarity('ABC', 'A');
 bigm_similarity
-----------------
 0.25
(1 ROW)

postgres=> SELECT bigm_similarity('ABC', 'B');
 bigm_similarity
-----------------
 0
(1 ROW)

postgres=> SELECT bigm_similarity('ABC', 'abc');
 bigm_similarity
-----------------
 0
(1 ROW)

pg_gin_pending_stats 函数

使用pg_gin_pending_stats函数返回GIN索引待处理列表的页面和元组的个数。示例如下:

postgres=> SELECT * FROM pg_gin_pending_stats('pg_tools_idx');
 pages | tuples
-------+--------
     0 |      0
(1 ROW)

在创建GIN索引时,您可以指定参数fastupdateoff,该GIN 索引不存在待处理列表,返回结果为0,示例如下:

CREATE INDEX ON your_table_name (your_column_name gin_trgm_ops) WITH (fastupdate = off);

附录

GUC参数

pg_bigm.last_update:该插件的最后更新日期,只读参数。

pg_bigm.enable_recheck:决定是否进行 recheck,默认为on,用户可以设置,建议保持默认值以保证结果正确性。

pg_bigm.gin_key_limit:限制用于全文本搜索的2-gram元素的最大个数,用户可以设置,默认为0,代表使用所有的2-gram元素。如果使用所有的2-gram元素导致性能下降,可以设置成较小的正数,限制2-gram元素的个数提升性能。

pg_bigm.similarity_limit:设置相似度阈值,相似度超过这个阈值的元组会作为相似度搜索的结果。

函数

函数

返回值

描述

likequery(text)

string

返回一个可以被LIKE关键字识别的搜索字符串。该结果的范围是0~1(0指两个字符串完全不相似,1指两个字符串完全相同)。

show_bigm(text)

text[ ]

返回一个给定字符串中的所有2-gram元素的集合的数组。

bigm_similarity(text, text)

real

返回一个浮点数,计算两个字符串的相似度。该函数在第二个字符串中搜索并统计两个字符串共有的2-gram元素。结果的范围是0~1(0指两个字符串完全不相似,1指第一个字符串和第二个字符串中的一个词相同)。

操作符

操作符

返回值

描述

text % text

boolean

检查左侧的文本是否与右侧的文本相似,类似于SQL的LIKEILIKE操作符,通过 bigram 索引进行优化以加速匹配。

text =% text

boolean

如果函数的第一个参数包含一个与第二个参数中的词相似的词,且这两词的相似度等于由pg_bigm.similarity_limit设定的阈值,这个函数返回true。

索引操作符

操作符

描述

gin_bigm_ops

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