如何使用RDS的模糊查詢 - ApsaraDB RDS

pg_bigm是阿里雲產品RDS Postgresql的一款外掛程式，該外掛程式提供了全文本搜尋能力，允許建立一個二元文法（2-gram）的GIN索引來加速搜尋過程。

前提條件

執行個體為RDS PostgreSQL 10或以上版本。
說明
暫不支援RDS PostgreSQL 17。
執行個體核心小版本為20230830或以上。
重要
20230830核心小版本之前已支援此外掛程式，但為了規範外掛程式管理，提升RDS PostgreSQL在外掛程式側的安全防護，RDS計劃在核心版本迭代中陸續對部分存在安全風險的外掛程式進行最佳化，部分外掛程式在低核心小版本無法建立，更多資訊，請參見【產品/功能變更】RDS PostgreSQL限制建立外掛程式說明。
- 如果您的執行個體核心小版本低於20230830，且已經使用了此外掛程式，則不影響使用。
- 如果您首次建立或重新建立此外掛程式，請升級核心小版本到最新。
使用該外掛程式前，需要將pg_bigm加入到shared_preload_libraries參數中。
您可以使用RDS PostgreSQL參數設定功能，為shared_preload_libraries參數添加pg_bigm。具體操作，請參見設定執行個體參數。

與pg_trgm異同

pg_trgm是RDS Postgresql的另一款外掛程式，使用3-gram的模型來實現全文本搜尋。pg_bigm外掛程式是在pg_trgm基礎上繼續開發的，兩者的區別如下。

功能和特性	pg_trgm	pg_bigm
全文檢索搜尋的短語匹配方法	3-gram	2-gram
支援的索引類型	GIN和GIST	GIN
支援的全文本搜尋操作符號	`LIKE`、`ILIKE`、`~`、`~*`	`LIKE`
非字母語言的全文本搜尋	不支援	支援
帶有1~2個字元的關鍵字的全文本搜尋	慢	快
相似性搜尋	支援	支援
最大可以索引的列大小	238,609,291位元組（約228 MB）	107,374,180位元組（約102 MB）

注意事項

建立GIN索引的列的長度不可以超過107,374,180位元組（約102 MB）。
如果資料庫中儲存的內容語言是非ASCII，則建議將資料庫的編碼方式改為UTF8。
說明
查詢當前資料庫的編碼方式命令為select pg_encoding_to_char(encoding) from pg_database where datname = current_database();。

基本操作

建立外掛程式

postgres=> create extension pg_bigm;
CREATE EXTENSION

建立索引

postgres=> CREATE TABLE pg_tools (tool text, description text);
CREATE TABLE
postgres=> INSERT INTO pg_tools VALUES ('pg_hint_plan', 'Tool that allows a user to specify an optimizer HINT to PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'Tool that allows a user to stabilize planner statistics in PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_bigm', 'Tool that provides 2-gram full text search capability in PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_trgm', 'Tool that provides 3-gram full text search capability in PostgreSQL');
INSERT 0 1
postgres=> CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);
CREATE INDEX
postgres=> CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);
CREATE INDEX

執行全文本搜尋

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)

卸載外掛程式

postgres=> drop extension pg_bigm;
DROP EXTENSION

外掛程式常用函數

likequery函數

作用：產生可以被LIKE關鍵字識別的字串。
參數：1個請求參數，類型為字串。
傳回值：可以被LIKE關鍵字識別的搜尋字串。
實現原理：
- 在關鍵詞前後添加%符號。
- 使用\來自動轉義符號%。

樣本如下：

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函數
- 作用：返回給定字串的所有2-gram元素的集合。
- 參數：1個請求參數，類型為字串。
- 傳回值：數組，包含所有的2-gram元素。
- 實現原理：
  - 在字串前後添加空白字元。
  - 計算所有的2-gram子串。
- 樣本如下：
```
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函數
- 作用：計算兩個字串的相似性。
- 參數：2個請求參數，類型為字串。
- 傳回值：浮點數，表示相似性。
- 實現原理：
  - 統計兩個字串共有的2-gram元素。
  - 相似性範圍是[0, 1]，0代表兩個字串完全不一樣，1代表兩個字串一樣。
  說明
  - 由於計算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函數
- 作用：返回GIN索引的pending list中頁面和元組的個數。
- 參數：1個，GIN索引的名字或者OID。
- 傳回值：2個，pending list中頁面的數量和元組的數量。
  說明
  如果GIN索引建立時，指定參數FASTUPDATE為False，則該GIN索引不存在pending list，即返回結果為0。
- 樣本如下：
```
postgres=> 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）以保證結果正確性。

樣本如下：

postgres=> CREATE TABLE tbl (doc text);
CREATE TABLE
postgres=> INSERT INTO tbl VALUES('He is awaiting trial');
INSERT 0 1
postgres=> INSERT INTO tbl VALUES('It was a trivial mistake');
INSERT 0 1
postgres=> CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);
CREATE INDEX
postgres=> SET enable_seqscan TO off;
SET
postgres=> 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)

postgres=>
postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('trial');
         doc
----------------------
 He is awaiting trial
(1 row)

postgres=> SET pg_bigm.enable_recheck = off;
SET
postgres=> 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
設定相似性閾值，相似性超過這個閾值的元組會做為相似性搜尋的結果。