pg_bigm

与pg_trgm异同

pg_trgm是PolarDB PostgreSQL版（兼容Oracle）的另一款插件，使用3-gram的模型来实现全文本搜索。pg_bigm插件是在pg_trgm基础上继续开发的，两者的区别如下。

注意事项

• 建立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;

• 加载插件到内存

  LOAD '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)

• 使用=%操作符执行相似性搜索

  SET pg_bigm.similarity_limit TO 0.2;
  # 显示结果如下：
  SET
  
  SELECT tool FROM pg_tools WHERE tool =% 'bigm';
  # 显示结果如下：
    tool
  ---------
   pg_bigm
   pg_trgm
  (2 rows)

• 卸载插件

  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.5714286
    (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.last_update

  该插件的最后更新日期，只读参数，无法修改。

  示例如下：

  SHOW pg_bigm.last_update;

• pg_bigm.enable_recheck

  决定是否进行recheck。

  说明 建议您保持默认值（ON）以保证结果正确性。

  示例如下：

  CREATE TABLE tbl (doc text);
  # 显示结果如下：
  CREATE TABLE
  
  INSERT INTO tbl VALUES('He is awaiting trial');
  # 显示结果如下：
  INSERT 0 1
  
  INSERT INTO tbl VALUES('It was a trivial mistake');
  # 显示结果如下：
  INSERT 0 1
  
  CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);
  # 显示结果如下：
  CREATE INDEX
  
  SET enable_seqscan TO off;
  # 显示结果如下：
  SET
  
  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)
  
  SET pg_bigm.enable_recheck = off;
  # 显示结果如下：
  SET
  
  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

  设置相似度阈值，相似度超过这个阈值的元组会做为相似性搜索的结果。