前模糊，后模糊，前后模糊，正则匹配都属于文本搜索领域常见的需求。
PostgreSQL在文本搜索领域除了全文检索，还有trgm是一般数据库没有的，甚至可能很多人没有听说过。
对于前模糊和后模糊，PG则与其他数据库一样，可以使用btree来加速，后模糊可以使用反转函数的函数索引来加速。
对于前后模糊和正则匹配，则可以使用trgm，TRGM是一个非常强的插件，对这类文本搜索场景性能提升非常有效，100万左右的数据量，性能提升有500倍以上。
例子：
生成100万数据
postgres=# create table tbl (id int, info text);
CREATE TABLE
postgres=# insert into tbl select generate_series(1,1000000), md5(random()::text);
INSERT 0 1000000
postgres=# create index idx_tbl_1 on tbl using gin(info gin_trgm_ops);
CREATE INDEX
postgres=# 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)
postgres=# select * from tbl where info ~ '670c4f2';
id | info
----+----------------------------------
2 | 4912b0b16670c4f2390d44ae790b9809
(1 row)
Time: 2.668 ms
postgres=# explain analyze select * from tbl where info ~ '670c4f2';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=28.27..138.43 rows=100 width=37) (actual time=1.957..1.958 rows=1 loops=1)
Recheck Cond: (info ~ '670c4f2'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_tbl_1 (cost=0.00..28.25 rows=100 width=0) (actual time=1.939..1.939 rows=1 loops=1)
Index Cond: (info ~ '670c4f2'::text)
Planning time: 0.342 ms
Execution time: 1.989 ms
(7 rows)
不使用TRGM优化，需要1657毫秒.
postgres=# set enable_bitmapscan=off;
SET
Time: 0.272 ms
postgres=# select * from tbl where info ~ 'e770044a';
id | info
----+----------------------------------
6 | 776c3cdf5fa818a324ef3e770044a488
(1 row)
Time: 1657.231 ms对于ascii字符，性能提升非常明显。
因为trgm不支持wchar，所以需要转换一下。
中文：
postgres=# explain analyze select * from tbl where info ~ '中国';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=149.62..151.82 rows=2 width=37) (actual time=8.624..8.624 rows=0 loops=1)
Recheck Cond: (info ~ '中国'::text)
Rows Removed by Index Recheck: 10103
Heap Blocks: exact=156
-> Bitmap Index Scan on idx_tbl_1 (cost=0.00..149.61 rows=2 width=0) (actual time=1.167..1.167 rows=10103 loops=1)
Index Cond: (info ~ '中国'::text)
Planning time: 0.244 ms
Execution time: 8.657 ms
(8 rows)
Time: 9.388 ms中文虽然走索引，但是它是没有正确的使用token的，所以都放到recheck了。
还不如全表扫描
postgres=# set enable_bitmapscan=off;
SET
postgres=# explain analyze select * from tbl where info ~ '中国';
QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on tbl (cost=0.00..399.75 rows=2 width=37) (actual time=6.899..6.899 rows=0 loops=1)
Filter: (info ~ '中国'::text)
Rows Removed by Filter: 10103
Planning time: 0.213 ms
Execution time: 6.921 ms
(5 rows)
Time: 7.593 ms但是你可以用PostgreSQL的函数索引和bytea化(转换成ascii码)来实现这块的功能
例如
postgres=# select text(textsend(info)) from tbl limit 10;
text
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\xe7abbde69b8ce7b5a4e8b197e5afa9e58c88e991a6e7b18ce5b495e8a79fe7ae8ee882bce7a283e7af9de8a086e7ac8de59e81e5a6bae9bcb6e6ba9fe981bbe4bda8e7928de98ab0e5a18de697b5e79fabe9b0a5e9b0a5
\xe5aa8ee69ab5e58996e892b0e89484e587b0e8bcbce69f80e79eb8e89390e7baa8e79f93e582b6e98f81e9a18ee9b48ee9ba8ce784a6e8b5a2e5a797e9a3b5e5a4aee986b1e9919de6b19ce9bdb9e6bbb6e8b5bde8b5bd
\xe7b4a4e5b2b3e7ac96e79481e78dbce5b28ae6b9b6e88dafe5aebce4bcbde8a3a3e4be98e78e93e5848ae4b888e5b0b5e5aeaee9aeb2e99982e59a98e6b0b2e583b3e9b799e893a5e5ba89e8949fe7868ee78cbde78cbd
\xe797a3e4b991e8baaee9ae88e69db5e78c99e9a8abe9bd80e7bd98e8b3bae89cb5e799bbe78d89e990a7e5b989e6a484e6a1a1e6939ce9b490e890b4e9a5abe6b392e58a9be5adaae9b895e89985e8a79ee8b889e8b889
\xe687a4e9b795e58094e9b0a6e6a58ee4bd80e6898ae6bdbee7828de788bde79897e8be83e59b93e7908ae9879be7b093e89eaae6a3bce792bee59e9ae8b5abe7a89fe9b6aae99bbae9a18fe6b3abe7b7aae89282e89282
\xe996b8e5a4b7e6b2b7e8a397e6a898e58a94e6a4a5e586b3e9b8b5e5ba98e99ba4e99c90e6be90e88d94e99dade89892e594abe59d98e5a7afe592a0e58c9be59590e8a299e7bb86e9abace7a5bee881bde793a7e793a7
\xe795aee7bba4e4bc86e7b29ae780b2e7bd9fe8a9bee8bf97e68486e5a4bde8a79ee6bf8be98cb8e8b6bfe4bb8ae88ba3e8ba98e6acb8e6aa94e59ab5e697bfe78b96e6859be7afb9e9bb85e799a7e798a3e6a982e6a982
\xe98987e7828be585ace9808ce5959be6b4a0e582ade59fbfe7b18ee792b9e8bd87e8849ce89d98e4b8b4e7af9ce6abb3e98a8ce89490e897bde59ea7e8a5a8e98a94e7848be59abae5bb9be890b6e58188e6acb8e6acb8
\xe7898de88880e89abfe99dbfe5bab9e5b387e8b3a7e8a0bfe9a4a7e5aa9be6a18ee68ca7e9b2b2e58b8de6a088e6a4abe5a481e58297e4bb90e5b780e786b4e6958de58bb4e78884e9ae98e9909ae8b19be984a8e984a8
\xe6b4a8e8b99ee6b789e8bfb9e9b69de9b0a6e9b7bde59fbae6a886e793a1e691ace9a185e5bba1e699a5e9bcace78598e9adaee9b199e59eb5e897b6e88f92e69caee8b9ade8beade4bdbae5b3b6e599b9e7bea1e7bea1
(10 rows)
Time: 0.457 ms对bytea文本创建gin索引
postgres=# create or replace function textsend_i (text) returns bytea as
$$
select textsend($1);
$$
language sql strict immutable;
CREATE FUNCTION
postgres=# drop index idx_tbl_1 ;
DROP INDEX
Time: 10.179 ms
postgres=# create index idx_tbl_1 on tbl using gin(text(textsend_i(info)) gin_trgm_ops);
CREATE INDEX使用了bytea的gin索引后，性能提升非常明显，数据量越多，性能表现越好。
postgres=# set enable_bitmapscan=on;
postgres=# explain analyze select * from tbl where text(textsend_i(info)) ~ ltrim(text(textsend_i('中国')), '\x');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=369.28..504.93 rows=100 width=37) (actual time=0.099..0.099 rows=0 loops=1)
Recheck Cond: ((textsend_i(info))::text ~ 'e4b8ade59bbd'::text)
-> Bitmap Index Scan on idx_tbl_1 (cost=0.00..369.25 rows=100 width=0) (actual time=0.097..0.097 rows=0 loops=1)
Index Cond: ((textsend_i(info))::text ~ 'e4b8ade59bbd'::text)
Planning time: 0.494 ms
Execution time: 0.128 ms
(6 rows)
postgres=# select * from tbl limit 10;
id | info
----+------------------------------------------------------------
1 | 竽曌絤豗審匈鑦籌崕觟箎肼碃篝蠆笍垁妺鼶溟遻佨璍銰塍旵矫鰥鰥
2 | 媎暵剖蒰蔄凰輼柀瞸蓐纨矓傶鏁顎鴎麌焦赢姗飵央醱鑝汜齹滶赽赽
3 | 紤岳笖甁獼岊湶药宼伽裣侘玓儊丈尵宮鮲陂嚘氲僳鷙蓥庉蔟熎猽猽
4 | 痣乑躮鮈杵猙騫齀罘賺蜵登獉鐧幉椄桡擜鴐萴饫泒力孪鸕虅觞踉踉
5 | 懤鷕倔鰦楎佀扊潾炍爽瘗较囓琊釛簓螪棼璾垚赫稟鶪雺顏泫緪蒂蒂
6 | 閸夷沷裗樘劔椥决鸵庘雤霐澐荔靭蘒唫坘姯咠匛啐袙细髬祾聽瓧瓧
7 | 畮绤伆粚瀲罟詾迗愆夽觞濋錸趿今苣躘欸檔嚵旿狖慛篹黅癧瘣橂橂
8 | 鉇炋公逌啛洠傭埿籎璹轇脜蝘临篜櫳銌蔐藽垧襨銔焋嚺廛萶偈欸欸
9 | 牍舀蚿靿庹峇賧蠿餧媛桎挧鲲勍栈椫夁傗仐巀熴敍勴爄鮘鐚豛鄨鄨
10 | 洨蹞淉迹鶝鰦鷽基樆瓡摬顅廡晥鼬煘魮鱙垵藶菒朮蹭辭佺島噹羡羡
(10 rows)
postgres=# explain analyze select * from tbl where text(textsend_i(info)) ~ ltrim(text(textsend_i('坘')), '\x');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=149.88..574.79 rows=320 width=37) (actual time=0.063..0.063 rows=0 loops=1)
Recheck Cond: ((textsend_i(info))::text ~ 'e59d98'::text)
-> Bitmap Index Scan on idx_tbl_1 (cost=0.00..149.80 rows=320 width=0) (actual time=0.061..0.061 rows=0 loops=1)
Index Cond: ((textsend_i(info))::text ~ 'e59d98'::text)
Planning time: 0.303 ms
Execution time: 0.087 ms
(6 rows)
postgres=# select * from tbl where text(textsend_i(info)) ~ ltrim(text(textsend_i('坘')), '\x');
id | info
------+------------------------------------------------------------
6 | 閸夷沷裗樘劔椥决鸵庘雤霐澐荔靭蘒唫坘姯咠匛啐袙细髬祾聽瓧瓧
432 | 飒莭鮊鍥?笩妳琈笈慻儘轴轧坘碠郎蚿呙偓鍹脆鼺蹔谕蚱畨縫鱳鱳
934 | 咓僨復圼峷奁扉羰滵樞韴迬猰優鰸獤溅躐瓜抵権纀懶粯坘蚲纾鴁鴁
3135 | 倣稽蛯巭瘄皮蓈睫柨苧眱賴髄猍乱歖痐坘恋顎东趥谓鰪棩剔烱茟茟
3969 | 崴坘螏顓碴鵰邰欴苄蛨簰瘰膪菷栱镘衟齘觊诀忮繈憘痴峣撋梆澝澝
4688 | 围豁啖顫诬呅尥腥缾郸熛枵焐篯坘僇矟銘隨譼鎶舰肳礞婛轲蠟慕慕
6121 | 窳研稼旅唣疚褣鬾韨赑躽坘浒攁舑遬鳴滴抓嗠捒铗牜欘質丛姤騖騖
6904 | 飘稘輔鬄枠舶婬儁噈坘裎姖爙炃苖隽斓堯鈶摙蚼疁兗快鐕鎒墩譭譭
8854 | 叒鐲唬鞩泍糕懜坘戚靥鎿鋂炿尟汜阢甌鲖埁顔胳邉謾宱肦劰責戆戆
9104 | 鵬篱爯俌坘柉誵孀漴纞錀澁摫螭芄餜爹綅俆逨哒猈珢輿廄陲欗缷缷
9404 | 民坘謤齏隽紽峐荟頩胯頴傳蠂枯滦榦陠帡疃鈶遽艌瘧蒭嗍龞瓈嚍嚍
9727 | 夃坘慫逹壪泵偉鸶揺雠倴矸虠覾芽齏遬儂錞鐴焑劽疁擯蛛倞瑫菰菰
(12 rows)有兴趣还可以再参考以下文章。
如何用PostgreSQL解决一个人工智能语义去重的小问题
https://yq.aliyun.com/articles/25899
PostgreSQL 百亿数据 秒级响应 正则及模糊查询
https://yq.aliyun.com/articles/7444
PostgreSQL 1000亿数据量 正则匹配 速度与激情
https://yq.aliyun.com/articles/7549
