pg_bigm 是云数据库 PostgreSQL 版提供的一款插件，该插件提供了全文本搜索能力，允许创建一个二元语法（2-gram）的 GIN 索引来加速搜索过程。
建立 GIN 索引的列的大小不可以超过 107,374,180字节（约 102MB）。任何尝试输入大于此限制的数据会报错。
如果数据库中存储的内容语言未采用 ASCII 编码，则建议将数据库的编码方式改为 UTF8。
使用该插件前，需要将 pg_bigm 加入到 shared_preload_libraries 参数中。关于如何修改参数，请参见修改参数。
注意
不将 pg_bigm 添加到 shared_preload_libraries 参数也能成功创建插件，但是插件不能正常工作。插件没有 shared_preload_libraries 检查，添加到 shared_preload_libraries 参数只是将 pg_bigm 作为 reserved_class_prefix，防止用户使用 pg_bigm 作为自定义变量前缀。不添加到 shared_preload_libraries 参数时，自定义 pg_bigm.xxx 的变量会成功，但是这个变量并不属于 pg_bigm 类；添加到 shared_preload_libraries 参数时，由于 reserved_class_prefix 中存在 pg_bigm，所以自定义 pg_bigm.xxx 的变量会失败。
创建插件
CREATE EXTENSION pg_bigm;
删除插件
DROP EXTENSION pg_bigm;
testdb=> CREATE TABLE books ( id SERIAL PRIMARY KEY, title TEXT, description TEXT); testdb=> INSERT INTO books (title, description) VALUES ('Python 编程入门', '一本适合初学者的 Python 编程书籍。'), ('C 编程入门', '一本适合初学者的 C 编程书籍。'), ('数据库原理与应用', '介绍数据库的基本原理和实际应用。'), ('数据结构与算法', '深入讲解数据结构和算法知识。'); testdb=> CREATE INDEX idx_books_title ON books USING GIN (title gin_bigm_ops);
在这个示例中，通过 LIKE 执行全文搜索匹配，命中关键词 “Python 编程” 的结果将被返回。
testdb=> SELECT * FROM books WHERE title LIKE '%Python 编程%'; id | title | description ----+-----------------+------------------------------------ 1 | Python 编程入门 | 一本适合初学者的 Python 编程书籍。 (1 row)
=% 操作符执行相似性搜索=% 操作符用于计算两个字符串之间的相似度，返回一个浮点数，值越接近 1 表示相似度越高。
testdb=> SET pg_bigm.similarity_limit TO 0.1; testdb=> SELECT *FROM books WHERE title =% 'C 编程'; id | title | description ----+-----------------+------------------------------------ 1 | Python 编程入门 | 一本适合初学者的 Python 编程书籍。 2 | C 编程入门 | 一本适合初学者的 C 编程书籍。 (2 rows) testdb=> SET pg_bigm.similarity_limit TO 0.2; testdb=> SELECT *FROM books WHERE title =% 'C 编程'; id | title | description ----+------------+------------------------------- 2 | C 编程入门 | 一本适合初学者的 C 编程书籍。 (1 row)
pg_bigm 插件常用的函数有 likequery、show_bigm、bigm_similarity 和 pg_gin_pending_stats。这些函数的基本信息如下：
| 函数 | 功能 | 参数 | 返回值 | 实现原理 |
|---|---|---|---|---|
likequery | 生成可以被 LIKE 关键字识别的字符串。 | 1 个请求参数，类型为字符串。 | 可以被 LIKE 关键字识别的搜索字符串。 |
|
show_bigm | 返回给定字符串的所有 2-gram 元素的集合。 | 1 个请求参数，类型为字符串。 | 数组，包含所有的 2-gram 元素。 |
|
bigm_similarity | 计算两个字符串的相似度。 | 2 个请求参数，类型都为字符串。 | 表示相似度的浮点数。 |
说明
|
| pg_gin_pending_stats | 返回 GIN 索引的 pending list 中页面和元组的个数。 | 1 个，GIN 索引的名字或者 OID。 | 2 个，pending list 中页面的数量和元组的数量。 | 如果 GIN 索引创建时，指定参数 FASTUPDATE 为 False，则该 GIN 索引不存在 pending list，返回结果为 0。 |
likequery
testdb=> SELECT * FROM books WHERE title LIKE likequery('数据'); id | title | description ----+------------------+---------------------------------- 3 | 数据库原理与应用 | 介绍数据库的基本原理和实际应用。 4 | 数据结构与算法 | 深入讲解数据结构和算法知识。 (2 rows)
show_bigm
testdb=> 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) testdb=> SELECT show_bigm('Python 编程'); show_bigm --------------------------------------------- {"程 ",编程," 编"," P",Py,ho,"n ",on,th,yt} (1 row)
bigm_similarity
bigm_similarity 函数计算两个字符串之间的相似度，与 =% 操作符类似，但以函数形式调用。
testdb=> SELECT bigm_similarity('ABC', 'A'); bigm_similarity ----------------- 0.25 (1 row) testdb=> SELECT bigm_similarity('ABC', 'B'); bigm_similarity ----------------- 0 (1 row) testdb=> SELECT bigm_similarity('ABC', 'abc'); bigm_similarity ----------------- 0 (1 row) testdb=> SELECT id, title, bigm_similarity(title, 'Python 编程') AS similarity FROM books WHERE bigm_similarity(title, 'Python 编程') > 0.2; id | title | similarity ----+-----------------+------------ 1 | Python 编程入门 | 0.75 2 | C 编程入门 | 0.2 (2 rows)
pg_gin_pending_stats
testdb=> SELECT * FROM pg_gin_pending_stats('idx_books_title'); pages | tuples -------+-------- 0 | 0 (1 row)
| 参数 | 类型 | 默认值 | 取值范围 | 描述 |
|---|---|---|---|---|
| pg_bigm.last_update | String | 无 | 只读参数，无法修改。 | 该插件的最后更新日期。 |
pg_bigm.enable_recheck | Boolean | on | on，off | 决定是否进行 recheck（是全文搜索的内部过程）。
说明 建议您保持默认值（on）以保证结果正确性。 |
pg_bigm.gin_key_limit | Integer | 0 | 0 ~ 2147483647 | 限制用于全文本搜索的 2-gram 元素的最大个数。默认值 0 表示使用所有的 2-gram 元素。如果发现使用所有的 2-gram 元素导致性能下降，可以调整该参数值，限制 2-gram 元素的个数来提高性能。 |
| pg_bigm.similarity_limit | Real | 0.3 | 0~1 | 设置相似度阈值，相似度超过这个阈值的元组会做为相似性搜索的结果。 |
部分参数的使用示例如下：
pg_bigm.last_update
testdb=> SHOW pg_bigm.last_update; pg_bigm.last_update --------------------- 2024.06.06 (1 row)
pg_bigm.enable_recheck
testdb=> CREATE TABLE tbl (doc text); CREATE TABLE testdb=> INSERT INTO tbl VALUES('He is awaiting trial'); INSERT 0 1 testdb=> INSERT INTO tbl VALUES('It was a trivial mistake'); INSERT 0 1 testdb=> CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops); CREATE INDEX testdb=> SET enable_seqscan TO off; SET testdb=> EXPLAIN ANALYZE SELECT * FROM tbl WHERE doc LIKE likequery('trial'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tbl (cost=5.50..6.61 rows=1 width=32) (actual time=0.014..0.015 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..5.50 rows=1 width=0) (actual time=0.008..0.008 rows=2 loops=1) Index Cond: (doc ~~ '%trial%'::text) Planning Time: 0.221 ms Execution Time: 0.037 ms (8 rows) testdb=> SELECT * FROM tbl WHERE doc LIKE likequery('trial'); doc ---------------------- He is awaiting trial (1 row) testdb=> SET pg_bigm.enable_recheck = off; SET testdb=> SELECT * FROM tbl WHERE doc LIKE likequery('trial'); doc -------------------------- He is awaiting trial It was a trivial mistake (2 rows) testdb=>
说明
在此示例中，可看到 Bitmap Index Scan 从全文搜索索引中检索了两行，但是在重新检查过程之后仅返回了一行。
pg_trgm 与 pg_bigm 有何异同
pg_trgm 是云数据库 PostgreSQL 版提供的另一款插件，使用 3-gram 的模型来实现全文本搜索。pg_bigm 插件是在基于 pg_trgm 进行的再次开发。pg_bigm 与 pg_trgm 两者的区别如下：
| 功能和特性 | pg_trgm | pg_bigm |
|---|---|---|
| 全文搜索的短语匹配方法 | 3-gram | 2-gram |
| 支持的索引类型 | GIN 和 GIST | GIN Only |
| 支持的全文本搜索操作符号 | LIKE (~~), ILIKE (~~*), ~, ~* | LIKE Only |
| 非字母语言的全文本搜索（比如：日语） | 不支持 | 支持 |
| 带有 1~2 个字符的关键字的全文本搜索 | 慢 | 快 |
| 相似性搜索 | 支持 | 支持 |
| 最大可以索引的列大小 | 238,609,291 字节（约 228MB） | 107,374,180 字节（约 102MB） |
更多信息，请参见官方文档。