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索引时,您可以指定参数fastupdate
为off
,该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:设置相似度阈值,相似度超过这个阈值的元组会作为相似度搜索的结果。
函数
函数 | 返回值 | 描述 |
| string | 返回一个可以被 |
| text[ ] | 返回一个给定字符串中的所有2-gram元素的集合的数组。 |
| real | 返回一个浮点数,计算两个字符串的相似度。该函数在第二个字符串中搜索并统计两个字符串共有的2-gram元素。结果的范围是0~1(0指两个字符串完全不相似,1指第一个字符串和第二个字符串中的一个词相同)。 |
操作符
操作符 | 返回值 | 描述 |
text % text | boolean | 检查左侧的文本是否与右侧的文本相似,类似于SQL的 |
text =% text | boolean | 如果函数的第一个参数包含一个与第二个参数中的词相似的词,且这两词的相似度等于由 |
索引操作符
操作符 | 描述 |
gin_bigm_ops | 将文本数据转换成 trigram,并使用 GIN 索引结构保存 trigram。 |