pg_bigm是PolarDB PostgreSQL版(兼容Oracle)的一款插件,该插件提供了全文本搜索能力,允许创建一个二元语法(2-gram)的GIN索引来加速搜索过程。
前提条件
支持的PolarDB PostgreSQL版(兼容Oracle)版本如下:
Oracle语法兼容 2.0(内核小版本2.0.14.2.0及以上)
Oracle语法兼容 1.0(内核小版本1.1.28及以上)
您可通过如下语句查看PolarDB PostgreSQL版(兼容Oracle)的内核小版本号:
SHOW polar_version;
与pg_trgm异同
pg_trgm是PolarDB PostgreSQL版(兼容Oracle)的另一款插件,使用3-gram的模型来实现全文本搜索。pg_bigm插件是在pg_trgm基础上继续开发的,两者的区别如下。
功能和特性 | pg_trgm | pg_bigm |
全文搜索的短语匹配方法 | 3-gram | 2-gram |
支持的索引类型 | GIN和GIST | GIN |
支持的全文本搜索操作符号 |
|
|
非字母语言的全文本搜索 | 不支持 | 支持 |
带有1~2个字符的关键字的全文本搜索 | 慢 | 快 |
相似性搜索 | 支持 | 支持 |
最大可以索引的列大小 | 238,609,291字节(约228 MB) | 107,374,180字节(约102 MB) |
注意事项
建立GIN索引的列的长度不可以超过107,374,180字节(约102 MB)。示例如下:
CREATE TABLE t1 (description text); CREATE INDEX t1_idx ON t1 USING gin (description gin_bigm_ops); INSERT INTO t1 SELECT repeat('A', 107374181);
如果数据库中存储的内容语言是非ASCII,则建议将数据库的编码方式改为UTF8。查询当前数据库编码方式的命令如下:
SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database();
基本操作
创建插件。
CREATE EXTENSION pg_bigm;
创建GIN索引时需要指定
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);
执行全文本搜索。
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)
使用
=%
操作符执行相似性搜索。SELECT tool FROM pg_tools WHERE tool =% 'bigm';
返回结果如下:
tool --------- pg_bigm (1 row)
卸载插件
DROP EXTENSION pg_bigm;
插件常用函数
likequery函数
作用:生成可以被LIKE关键字识别的字符串。
参数:1个请求参数,类型为字符串。
返回值:可以被LIKE关键字识别的搜索字符串。
实现原理:
在关键词前后添加
%
符号。使用
\
来自动转义符号%
。
示例如下:
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)
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函数
作用:返回给定字符串的所有2-gram元素的集合。
参数:1个请求参数,类型为字符串。
返回值:数组,包含所有的2-gram元素。
实现原理:
在字符串前后添加空格字符。
计算所有的2-gram子串。
示例如下:
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函数
作用:计算两个字符串的相似度。
参数:2个请求参数,类型为字符串。
返回值:浮点数,表示相似度。
实现原理:
统计两个字符串共有的2-gram元素。
相似度范围是[0, 1],0代表两个字符串完全不一样,1代表两个字符串一样。
说明由于计算2-gram时,会在字符串前后添加空格,于是
ABC
和B
的相似度为0,ABC
和A
的相似度为0.25。bigm_similarity函数是大小写敏感的,例如,
ABC
和abc
的相似度为0。
示例如下:
SELECT bigm_similarity('full text search', 'text similarity search');
返回结果如下:
bigm_similarity ----------------- 0.571429 (1 row)
SELECT bigm_similarity('ABC', 'A');
返回结果如下:
bigm_similarity ----------------- 0.25 (1 row)
SELECT bigm_similarity('ABC', 'B');
返回结果如下:
bigm_similarity ----------------- 0 (1 row)
SELECT bigm_similarity('ABC', 'abc');
返回结果如下:
bigm_similarity ----------------- 0 (1 row)
pg_gin_pending_stats函数
作用:返回GIN索引的pending list中页面和元组的个数。
参数:1个,GIN索引的名字或者OID。
返回值:2个,pending list中页面的数量和元组的数量。
说明如果GIN索引创建时,指定参数FASTUPDATE为False,则该GIN索引不存在pending list,即返回结果为0。
示例如下:
SELECT * FROM pg_gin_pending_stats('pg_tools_idx');
显示结果如下:
pages | tuples -------+-------- 0 | 0 (1 row)
插件行为控制参数
pg_bigm.enable_recheck
决定是否进行recheck。
说明建议您保持默认值(ON)以保证结果正确性。
示例如下:
准备测试数据。
CREATE TABLE tbl (doc text); INSERT INTO tbl VALUES('He is awaiting trial'); INSERT INTO tbl VALUES('It was a trivial mistake'); CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);
执行如下查询语句。
pg_bigm.enable_recheck为on时,即进行recheck:
SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT * FROM tbl WHERE doc LIKE likequery('trial');
返回结果如下:
QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tbl (cost=20.00..24.01 rows=1 width=32) (actual time=0.020..0.021 rows=1 loops=1) Recheck Cond: (doc ~~ '%trial%'::text) Rows Removed by Index Recheck: 1 Heap Blocks: exact=1 -> Bitmap Index Scan on tbl_idx (cost=0.00..20.00 rows=1 width=0) (actual time=0.013..0.013 rows=2 loops=1) Index Cond: (doc ~~ '%trial%'::text) Planning Time: 0.117 ms Execution Time: 0.043 ms (8 rows)
执行如下查询语句:
SELECT * FROM tbl WHERE doc LIKE likequery('trial');
返回结果如下:
doc ---------------------- He is awaiting trial (1 row)
pg_bigm.enable_recheck为off时,即不进行recheck:
SET pg_bigm.enable_recheck = off; SELECT * FROM tbl WHERE doc LIKE likequery('trial');
返回结果如下:
doc -------------------------- He is awaiting trial It was a trivial mistake (2 rows)
pg_bigm.gin_key_limit
限制用于全文本搜索的2-gram元素的最大个数,默认为0,0代表使用所有的2-gram元素。
说明如果发现使用所有的2-gram元素导致性能下降,可以调整该参数值,限制2-gram元素的个数来提高性能。
pg_bigm.similarity_limit
设置相似度阈值,相似度超过这个阈值的元组会做为相似性搜索的结果。