中文模糊查询性能优化 by PostgreSQL trgm

  1. 云栖社区>
  2. 博客列表>
  3. 正文

中文模糊查询性能优化 by PostgreSQL trgm

德哥 2016-05-06 13:00:07 浏览7268 评论3

摘要: 前模糊,后模糊,前后模糊,正则匹配都属于文本搜索领域常见的需求。PostgreSQL在文本搜索领域除了全文检索,还有trgm是一般数据库没有的,甚至可能很多人没有听说过。对于前模糊和后模糊,PG则与其他数据库一样,可以使用btree来加速,后模糊可以使用反转函数的函数索引来加速。对于前后模糊和正则.

前模糊,后模糊,前后模糊,正则匹配都属于文本搜索领域常见的需求。
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

用云栖社区APP,舒服~

【云栖快讯】诚邀你用自己的技术能力来用心回答每一个问题,通过回答传承技术知识、经验、心得,问答专家期待你加入!  详情请点击

网友评论

1F
微加

bytea创建gin索引,如果对应字段上有\字符串没有加转移符,例如Unicode中文字符串\u70e8\u848e\u5730\u6931\u6dce\u76d5\u54fd\u6189\u9283\u6c9f\u6c9f ,会报错:ERROR: invalid input syntax for type bytea

需要确保对应字段上值包含\的都加上转移符 ,即:\\, 才能正常的建立索引。

如果在建表之后马上创建了索引,那么插入值的时候就必须先转义掉\,否则也会报上面的错误:

UPDATE tbl  SET info = '这\是个错误的值'  WHERE id = 9727;
ERROR:  invalid input syntax for type bytea
UPDATE tbl  SET info = '这\\是个正确的值'  WHERE id = 9727;
UPDATE 1
德哥 赞同
德哥

已更新本文内容, 通过textsend来转不存在这个问题

评论
2F
微加

@digoal 基于应用逻辑上的考虑,此种索引不适合放在 存储 富文本text类型字段上,适合存储 标题标签VARCHAR上。

富文本text类型字段搜索场景使用bytea,存在的弊端:

  1. 入库之前需要应用层额外增加转移符操作;
  2. 出库之后对应的又需要转移符操作;

因此对 富文本text类型字段搜索,考虑走全文检索方案,不应该选择 bytea 索引方案

德哥

其实换个函数就可以的,不一定要用bytea转, 已更新本文

微加

@digoal 多谢德哥的分享与帮助。

评论
3F
xiahou

$ cd pg_trgm_pro
$ make USE_PGXS=1
$make USE_PGXS=1 install
$ make USE_PGXS=1 installcheck
$ psql DB -c "CREATE EXTENSION pg_trgm;" 无法安装在pg9.6版本 报错如下:[root@pangusv4 pg_trgm]# make USE_PGXS=1
make: pg_config: Command not found
make: * No targets. Stop.
德哥,这个是什么问题?如何正确安装在pg9.6上,跪谢

德哥
文章1883篇 | 关注5355
关注
PostgreSQL被业界誉为“最先进的开源数据库”,面向企业复杂SQL处理的OLTP在线事... 查看详情
全球领先的SaaS性能测试平台,具有强大的分布式压测能力,可模拟海量用户真实的业务场景,让应... 查看详情
提供一种性能卓越、稳定、安全、便捷的计算服务,帮助您快速构建处理能力出色的应用,解放计算给服... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航