pg_bigm

功能描述

Vastbase 支持 pg_bigm 插件，该插件提供了全文搜索功能，并允许创建二元语法（2-gram）的 GIN 索引，以加速搜索过程，从而提高了 Vastbase 的全文搜索性能。

• 全文搜索：全文搜索是指在文本数据中进行关键词的搜索过程，可以识别文件中的关键词、短语或模式。
• 2-gram：将文本中所有相邻的两个字符作为索引项，这样前一个索引项的后一个字符与下一个索引项的前一个字符是相同的。例如对于字符串 ABCDE ，由它生成的索引项分别为 AB、BC、CD、DE。

pg_bigm插件会在字符串的前端添加两个空格，末尾添加一个空格，以便捕捉到字符串开头和结尾附近的模式，例如，对于字符串 "ABCDE"，处理后会变成 " ABCDE "，那么其生成的索引项为：" A"，AB，BC，CD，DE，"E "。

注意事项

• 建立 GIN 索引的列的长度不可以超过107,374,180字节（约102 MB）。如下示例会产生报错：ERROR: out of memory。

  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();
  

使用方法

• 在使用 pg_bigm 插件之前，需要在对应数据库中执行如下命令安装 pg_bigm 插件。

  CREATE EXTENSION pg_bigm;
  

• 查看数据库当前插件。

  \dx
  

• 卸载 pg_bigm 插件。

  卸载命令中的 CASCADE 选项用于删除所有依赖于pg_bigm的数据库对象，例如 pg_bigm 全文搜索索引。

  DROP EXTENSION pg_bigm CASCADE;
  

插件行为控制参数

pg_bigm 的部分行为可以通过如下 GUC 参数去控制，详细介绍请参考插件控制参数。

常用函数

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');
SELECT bigm_similarity('ABC', 'A');
SELECT bigm_similarity('ABC', 'abc');

返回结果依次为：

 bigm_similarity
-----------------
         .571429
(1 row)

 bigm_similarity
-----------------
             .25
(1 row)

 bigm_similarity
-----------------
               0
(1 row)

pg_gin_pending_stats

描述：返回 GIN 索引的 pending list 中页面和元组的个数。

参数：1个 GIN 索引的名字或者 OID。

返回值：pending list 中页面的数量和元组的数量。

如果 GIN 索引创建时，指定参数 FASTUPDATE 为 False，则该 GIN 索引不存在 pending list，即返回结果为0。

示例：

1、创建索引。

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);

2、调用函数。

SELECT * FROM pg_gin_pending_stats('pg_tools_idx');

返回结果为：

 pages | tuples
-------+--------
     0 |      0
(1 row)

likequery

描述：生成可以被 LIKE 关键字识别的字符串。

参数：1个请求参数，字符串类型。

返回值：可以被 LIKE 关键字识别的搜索字符串。

实现原理：

• 在关键词前后添加%符号。
• 使用\来自动转义符号%。

示例：

1、调用 likequery 函数。

SELECT likequery('pg_bigm has improved the full text search performance by 200%');
SELECT * FROM pg_tools WHERE description LIKE likequery('search');

返回结果为：

                             likequery
-------------------------------------------------------------------
 %pg\_bigm has improved the full text search performance by 200\%%
(1 row)

  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)

2、清理测试数据。

DROP TABLE pg_tools cascade;

示例

1、创建插件。

CREATE EXTENSION pg_bigm;

2、创建测试表并插入数据。

CREATE TABLE tbl (doc text);
INSERT INTO tbl VALUES('He is awaiting trial');
INSERT INTO tbl VALUES('It was a trivial mistake');

3、创建索引。

CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);

4、关闭全表扫描（可以在少量数据下体现索引效果）。

SET enable_seqscan=off;

5、查看执行计划。

EXPLAIN(COSTS OFF) SELECT * FROM tbl WHERE doc LIKE likequery('trial');

返回结果为：

                  QUERY PLAN
----------------------------------------------
 Bitmap Heap Scan on tbl
   Recheck Cond: (doc ~~ '%trial%'::text)
   ->  Bitmap Index Scan on tbl_idx
         Index Cond: (doc ~~ '%trial%'::text)
(4 rows)

6、调用函数，查询可以识别到trial关键字的字符串。

SELECT * FROM tbl WHERE doc LIKE likequery('trial');

返回结果为：

         doc
----------------------
 He is awaiting trial
(1 row)

7、 设置参数 pg_bigm.enable_recheck 为off，关闭索引扫描时进行重新检查的功能。

SET pg_bigm.enable_recheck = off;

8、再次调用函数，查询可以识别到 trial 关键字的字符串。

SELECT * FROM tbl WHERE doc LIKE likequery('trial');

返回结果为：

           doc
--------------------------
 He is awaiting trial
 It was a trivial mistake
(2 rows)

9、删除测试数据，恢复环境。

DROP TABLE tbl cascade;
DROP EXTENSION pg_bigm cascade;
RESET enable_seqscan;
RESET pg_bigm.enable_recheck;