[TOC] ## 概述 - 前模糊，后模糊，前后模糊，正則匹配都屬于文本搜索領域常見的需求。 - PostgreSQL在文本搜索領域除了全文檢索，還有trgm是一般數據庫沒有的，可能很多人沒有聽說過。 - 對于前模糊和后模糊，PG則與其他數據庫一樣，可以使用btree來加速。后模糊可以使用反轉函數的函數索引來加速。 - 對于前后模糊和正則匹配，則可以使用trgm，TRGM是一個非常強的插件，對這類文本搜索場景性能提升非常有效，100萬左右的數據量，性能提升有500倍以上 - pg_trgm 也支持正則搜索 - **中文支持** PostgreSQL 9.3開始，pg_trgm支持wchar 原理 - pg_trgm 擴展模塊使用 trigram 技術來實現。Trigrams 是將字符串分解為長度為 3 的、互不重疊的連續字母序列的過程。例如，“hello”會被分解成“hel”，“ell”和“llo”。pg_trgm 使用這些 trigrams 來計算字符串之間的相似度，并將相似度高的字符串歸為一類。 ## 符號 | 符號 | 關鍵字 | | :-- | :-- | | ~ | LIKE | | ~* | ILIKE | | !~ | NOT LIKE | | !~* | NOT ILIKE | > like都比較熟悉的大小寫敏感匹配，ilike操作是大小寫不敏感模糊匹配。 ## 快速入門 ``` create extension pg_trgm; > create table tbl (id int, info text); > insert into tbl select generate_series(1,1000000), md5(random()::text); > create index idx_tbl_1 on tbl using gin(info gin_trgm_ops); > select * from tbl limit 10; id | info ----+---------------------------------- 1 | dc369f84738f7fa4dc38c364cef817d0 2 | 4912b0b16670c4f2390d44ae790b9809 3 | eb442b00bf3b5bc6863d004a2c8fa3bb 4 | 0b4b8a8ad0cdf2e6870afbb94813eba4 5 | 661e895ee982ec4d9f944b10adffb897 6 | 09c4e7476d4bdfc1ccbdfe92ba0fdbdf 7 | 8b6e442faed938d066dda5e552100277 8 | e5cdeca599d5068a8d3bb6ce9f370827 9 | ddbbfbeaa9199219b7c909fb395d9a69 10 | 96f254f64df1ec43bb0cb4801222c919 (10 rows) > select * from tbl where info ~ '670c4f2'; id | info ----+---------------------------------- 2 | 4912b0b16670c4f2390d44ae790b9809 ``` 不使用TRGM優化的情況下，需要1657毫秒. ``` > set enable_bitmapscan=off; SET Time: 0.272 ms > select * from tbl where info ~ 'e770044a'; id | info ----+---------------------------------- 6 | 776c3cdf5fa818a324ef3e770044a488 (1 row) ```