[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) ```