PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)

作者

digoal

日期

2016-10-19

标签

PostgreSQL , RUM , GIN , full text search , 全文检索 , bitmap scan


背景

全文检索,模糊查询在现实的应用中用得非常多,特别是搜索引擎。

通常我们会想到使用搜索引擎来解决,但是需要考虑数据同步到搜索引擎,以及同步延迟,更新,一致性的问题。

并且使用搜索引擎我们还得多维护一个组件。

那么有没有更好的办法呢?

答案是有的,在PostgreSQL中,有内置的全文检索数据类型,以及全模糊查询的索引支持。

效率当然也是杠杠的,比如10亿的TOKEN检索,可以在毫秒级返回。

PostgreSQL 9.6在全文检索这块还做了更多的增强,比如RUM插件,被Oleg称为打开了潘多拉魔盒,在检索效率方面比GIN有极大的提升。

场景描述

我碰到过很多用户这样使用,用逗号将需要检索的元素分割开,当成字符串存储在数据库中,然后使用模糊查询的方法对数据进行检索。

create table test(c1 text);
insert into test values ('1,100,2331,344,502,.........');
insert ............
.....

比如1000万条这样的记录,然后要根据元素组合进行查询。

select * from test where c1 like '%1%' or c1 like '%502%' and c1 like '%2331%';  

这种查询效率非常低下,如果要做到毫秒级的返回,几乎不可想象。

PostgreSQL 数组类型

其实以上场景,在PostgreSQL中,可以使用数组类型来满足。

create table arr_test(c1 int[]);

create index idx_arr_test on arr_test using gin(c1);

insert into arr_test values(array[1,100,2331,344,502,......]);
......

PostgreSQL 数组支持GIN索引,可以实现快速的检索。

例如在1000万记录中检索包含1或2的记录。

postgres=# explain analyze select * from arr_test where c1 && array[1,2] order by c1 offset 19000 limit 100;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=112837.69..112837.94 rows=100 width=424) (actual time=91.440..91.475 rows=100 loops=1)
   ->  Sort  (cost=112790.19..113039.57 rows=99750 width=424) (actual time=82.915..90.477 rows=19100 loops=1)
         Sort Key: c1
         Sort Method: external merge  Disk: 8440kB
         ->  Bitmap Heap Scan on arr_test  (cost=816.06..93595.94 rows=99750 width=424) (actual time=9.180..37.380 rows=19925 loops=1)
               Recheck Cond: (c1 && '{1,2}'::integer[])
               Heap Blocks: exact=19605
               ->  Bitmap Index Scan on idx_arr_test  (cost=0.00..791.12 rows=99750 width=0) (actual time=5.196..5.196 rows=19925 loops=1)
                     Index Cond: (c1 && '{1,2}'::integer[])
 Planning time: 0.131 ms
 Execution time: 93.929 ms
(11 rows)

PostgreSQL 全文检索类型

除了使用数组,PostgreSQL还支持全文检索类型,你可以存储为tsvector,使用tsquery进行查询。

postgres=# create table gin_test(c1 tsvector);
CREATE TABLE

postgres=# create index idx_gin_test on gin_test using gin (c1) ;
CREATE INDEX

全文检索类型同样支持索引,可以加速查询。

例如在1000万记录中检索包含1或2的记录。


潘多拉魔盒RUM

我们看到使用GIN索引时,扫描方式为BITMAP,所以有一个SORT的动作,这个在很大的LIST中是比较耗时的。

9.6的一个插件RUM索引接口,对全文检索的支持更加强大,不需要SORT,直接走INDEX SCAN的接口,也就是说RUM同时还实现了<=>即文本相似度的属性检索。

Oleg说RUM打开了潘多拉魔盒,除此之外9.6在全文检索方面还有极大的提升,9.6的release notes里也有重点说明,这使得PostgreSQL在文本检索能力方面又更加强大了。

忘掉搜索引擎吧,使用PostgreSQL。

测试RUM

https://yq.aliyun.com/articles/59212

postgres=# create table rum_test(c1 tsvector);
CREATE TABLE

postgres=# CREATE INDEX rumidx ON rum_test USING rum (c1 rum_tsvector_ops);
CREATE INDEX

性能指标 : 数组 对比 全文检索类型(GIN对比RUM索引)

下面对比一下数组GIN索引,全文检索类型GIN索引,全文检索类型RUM索引

表结构

postgres=# create table rum_test(c1 tsvector);
CREATE TABLE

postgres=# create table gin_test(c1 tsvector);
CREATE TABLE

postgres=# create table arr_test(c1 int[]);
CREATE TABLE

插入1000万记录,每个字段100个随机值,相当于在10亿随机值中匹配。

$ vi test.sql
insert into rum_test select to_tsvector(string_agg(c1::text,',')) from  (select (100000*random())::int from generate_series(1,100)) t(c1);

$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 200000


$ vi test.sql
insert into gin_test select to_tsvector(string_agg(c1::text,',')) from  (select (100000*random())::int from generate_series(1,100)) t(c1);

$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 200000


$ vi test.sql
insert into arr_test select array_agg(c1) from  (select (100000*random())::int from generate_series(1,100)) t(c1);

$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 200000

创建索引

postgres=# set maintenance_work_mem ='64GB';
SET
postgres=# CREATE INDEX rumidx ON rum_test USING rum (c1 rum_tsvector_ops);
CREATE INDEX

postgres=# create index idx_gin_test on gin_test using gin (c1) ;
CREATE INDEX

postgres=# create index idx_arr_test on arr_test using gin (c1) ;
CREATE INDEX

查询效率对比

1. 查询包含1或2的记录

全文检索类型, rum索引
postgres=# explain analyze select * from rum_test where c1 @@ to_tsquery('english','1 | 2');
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using rumidx on rum_test  (cost=16.00..99121.61 rows=99749 width=1387) (actual time=6.403..24.981 rows=19840 loops=1)
   Index Cond: (c1 @@ '''1'' | ''2'''::tsquery)
 Planning time: 0.075 ms
 Execution time: 26.086 ms
(4 rows)

全文检索类型, GIN索引
postgres=# explain analyze select * from gin_test where c1 @@ to_tsquery('english','1 | 2');
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on gin_test  (cost=816.06..99386.94 rows=99750 width=1387) (actual time=9.551..34.121 rows=19847 loops=1)
   Recheck Cond: (c1 @@ '''1'' | ''2'''::tsquery)
   Heap Blocks: exact=19764
   ->  Bitmap Index Scan on idx_gin_test  (cost=0.00..791.12 rows=99750 width=0) (actual time=5.554..5.554 rows=19847 loops=1)
         Index Cond: (c1 @@ '''1'' | ''2'''::tsquery)
 Planning time: 0.113 ms
 Execution time: 35.279 ms
(7 rows)

数组类型, GIN索引
postgres=# explain analyze select * from arr_test where c1 && array[1,2];
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on arr_test  (cost=816.06..93595.94 rows=99750 width=424) (actual time=9.148..31.648 rows=19925 loops=1)
   Recheck Cond: (c1 && '{1,2}'::integer[])
   Heap Blocks: exact=19605
   ->  Bitmap Index Scan on idx_arr_test  (cost=0.00..791.12 rows=99750 width=0) (actual time=5.214..5.214 rows=19925 loops=1)
         Index Cond: (c1 && '{1,2}'::integer[])
 Planning time: 0.095 ms
 Execution time: 32.810 ms
(7 rows)

2. 排序输出

全文检索类型, rum索引
postgres=# explain analyze select * from rum_test where c1 @@ to_tsquery('english','1 | 2') order by c1 <=> to_tsquery('english','1 | 2') offset 19000 limit 100;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=18988.45..19088.30 rows=100 width=1391) (actual time=58.912..59.165 rows=100 loops=1)
   ->  Index Scan using rumidx on rum_test  (cost=16.00..99620.35 rows=99749 width=1391) (actual time=16.426..57.892 rows=19100 loops=1)
         Index Cond: (c1 @@ '''1'' | ''2'''::tsquery)
         Order By: (c1 <=> '''1'' | ''2'''::tsquery)
 Planning time: 0.133 ms
 Execution time: 59.220 ms
(6 rows)

全文检索类型, GIN索引
postgres=# explain analyze select * from gin_test where c1 @@ to_tsquery('english','1 | 2') order by c1 offset 19000 limit 100;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=176684.69..176684.94 rows=100 width=1387) (actual time=117.809..117.865 rows=100 loops=1)
   ->  Sort  (cost=176637.19..176886.57 rows=99750 width=1387) (actual time=94.889..116.929 rows=19100 loops=1)
         Sort Key: c1
         Sort Method: external merge  Disk: 26968kB
         ->  Bitmap Heap Scan on gin_test  (cost=816.06..99386.94 rows=99750 width=1387) (actual time=9.625..38.336 rows=19847 loops=1)
               Recheck Cond: (c1 @@ '''1'' | ''2'''::tsquery)
               Heap Blocks: exact=19764
               ->  Bitmap Index Scan on idx_gin_test  (cost=0.00..791.12 rows=99750 width=0) (actual time=5.610..5.610 rows=19847 loops=1)
                     Index Cond: (c1 @@ '''1'' | ''2'''::tsquery)
 Planning time: 0.134 ms
 Execution time: 126.122 ms
(11 rows)

数组类型, GIN索引
postgres=# explain analyze select * from arr_test where c1 && array[1,2] order by c1 offset 19000 limit 100;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=112837.69..112837.94 rows=100 width=424) (actual time=90.619..90.656 rows=100 loops=1)
   ->  Sort  (cost=112790.19..113039.57 rows=99750 width=424) (actual time=82.067..89.622 rows=19100 loops=1)
         Sort Key: c1
         Sort Method: external merge  Disk: 8440kB
         ->  Bitmap Heap Scan on arr_test  (cost=816.06..93595.94 rows=99750 width=424) (actual time=9.087..36.870 rows=19925 loops=1)
               Recheck Cond: (c1 && '{1,2}'::integer[])
               Heap Blocks: exact=19605
               ->  Bitmap Index Scan on idx_arr_test  (cost=0.00..791.12 rows=99750 width=0) (actual time=5.138..5.138 rows=19925 loops=1)
                     Index Cond: (c1 && '{1,2}'::integer[])
 Planning time: 0.122 ms
 Execution time: 93.057 ms
(11 rows)

RUM 附加能力

rum检索支持近似度排行,这个在搜索应用中太有用了。

通过相似度分值表示文本和检索条件的相似度。

// 分词举例
postgres=#  select * from to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所,后在日本京都大学深造');
                                   to_tsvector                                    
----------------------------------------------------------------------------------
 '中国科学院':5 '小明':1 '日本京都大学':10 '毕业':3 '深造':11 '硕士':2 '计算所':6
(1 row)
// 有相似度
postgres=#  select * from rum_ts_distance(to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所,后在日本京都大学深造') , to_tsquery('计算所'));
 rum_ts_distance 
-----------------
         16.4493
(1 row)
// 没有相似度
postgres=#  select * from rum_ts_distance(to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所,后在日本京都大学深造') , to_tsquery('计算'));
 rum_ts_distance 
-----------------
        Infinity
(1 row)
// 或相似度
postgres=# select * from rum_ts_distance(to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所,后在日本京都大学深造') , to_tsquery('计算所 | 硕士'));
 rum_ts_distance 
-----------------
         8.22467
(1 row)
// 与相似度
postgres=# select * from rum_ts_distance(to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所,后在日本京都大学深造') , to_tsquery('计算所 & 硕士'));
 rum_ts_distance 
-----------------
         32.8987
(1 row)
// 排序
postgres=# create table test15(c1 tsvector);
CREATE TABLE
postgres=# insert into test15 values (to_tsvector('jiebacfg', 'hello china, i''m digoal')), (to_tsvector('jiebacfg', 'hello world, i''m postgresql')), (to_tsvector('jiebacfg', 'how are you, i''m digoal'));
INSERT 0 3
postgres=# select * from test15;
                         c1                          
-----------------------------------------------------
 ' ':2,5,9 'china':3 'digoal':10 'hello':1 'm':8
 ' ':2,5,9 'hello':1 'm':8 'postgresql':10 'world':3
 ' ':2,4,7,11 'digoal':12 'm':10
(3 rows)
postgres=# create index idx_test15 on test15 using rum(c1 rum_tsvector_ops);
CREATE INDEX
postgres=# select *,c1 <=> to_tsquery('hello') from test15;
                         c1                          | ?column? 
-----------------------------------------------------+----------
 ' ':2,5,9 'china':3 'digoal':10 'hello':1 'm':8     |  16.4493
 ' ':2,5,9 'hello':1 'm':8 'postgresql':10 'world':3 |  16.4493
 ' ':2,4,7,11 'digoal':12 'm':10                     | Infinity
(3 rows)
postgres=# explain select *,c1 <=> to_tsquery('postgresql') from test15 order by c1 <=> to_tsquery('postgresql');
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Index Scan using idx_test15 on test15  (cost=3600.25..3609.06 rows=3 width=36)
   Order By: (c1 <=> to_tsquery('postgresql'::text))
(2 rows)

小结

正如Oleg说的,RUM非常强大,支持相似度检索,支持非BITMAP scan,从查询效率来看,已经比GIN以及单纯的数组查询效率高出1倍。

忘掉搜索引擎,使用PostgreSQL全文检索吧。

分词方面,PG支持的中文分词插件也很多,例如结巴分词,ZHPARSER。

https://github.com/postgrespro/rum

Count

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
关系型数据库 分布式数据库 数据库
PolarDB常见问题之加了索引但是查询没有使用如何解决
PolarDB是阿里云推出的下一代关系型数据库,具有高性能、高可用性和弹性伸缩能力,适用于大规模数据处理场景。本汇总囊括了PolarDB使用中用户可能遭遇的一系列常见问题及解答,旨在为数据库管理员和开发者提供全面的问题指导,确保数据库平稳运行和优化使用体验。
|
2月前
|
SQL 存储 缓存
PostgreSQL函数管理接口
学习PostgreSQL服务端开发必须要对函数管理接口有比较深入的了解
142 0
|
2月前
|
SQL 算法 关系型数据库
PolarDB-X的XPlan索引选择
对于数据库来说,正确的选择索引是基本的要求,选错索引轻则导致查询缓慢,重则导致数据库整体不可用。PolarDB-X存在多种不同的索引,局部索引、全局索引、列存索引、归档表索引。本文主要介绍一种CN上的局部索引算法:XPlan索引选择。
125755 13
PolarDB-X的XPlan索引选择
|
3月前
|
关系型数据库 定位技术 索引
在关系型数据库中,常见的索引种类包括哪些
在关系型数据库中,常见的索引种类包括哪些
486 0
|
6月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版重磅推出的列存索引(
PolarDB MySQL版重磅推出的列存索引(
338 1
|
6月前
|
关系型数据库 Go 数据库
《提高查询速度:PostgreSQL索引实用指南》
《提高查询速度:PostgreSQL索引实用指南》
357 0
|
6月前
|
SQL 缓存 关系型数据库
PolarDB-X 混沌测试实践:如何衡量数据库索引选择能力
随着PolarDB分布式版的不断演进,功能不断完善,新的特性不断增多,整体架构扩大的同时带来了测试链路长,出现问题前难发现,出现问题后难排查等等问题。原有的测试框架已经难以支撑实际场景的复杂模拟测试。因此,我们实现了一个基于业务场景面向优化器索引选择的混沌查询实验室,本文之后简称为CEST(complex environment simulation test)。
|
7月前
|
关系型数据库 分布式数据库 数据库
PolarDB for PostgreSQL 14:全局索引
PolarDB for PostgreSQL 14 相较于 PostgreSQL 14,提供了更多企业级数据库的特性。本实验将体验其中的全局索引功能。
754 0
|
13天前
|
关系型数据库 MySQL 数据库
mysql卸载、下载、安装(window版本)
mysql卸载、下载、安装(window版本)
|
1月前
|
关系型数据库 MySQL 数据库
rds安装数据库客户端工具
安装阿里云RDS的数据库客户端涉及在本地安装对应类型(如MySQL、PostgreSQL)的客户端工具。对于MySQL,可选择MySQL Command-Line Client或图形化工具如Navicat,安装后输入RDS实例的连接参数进行连接。对于PostgreSQL,可以使用`psql`命令行工具或图形化客户端如PgAdmin。首先从阿里云控制台获取连接信息,然后按照官方文档安装客户端,最后配置客户端连接以确保遵循安全指引。
82 1

相关产品

  • 云原生数据库 PolarDB