AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

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

2016-10-19 18393
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
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
关系型数据库
索引
PostgreSQL
C语言
搜索推荐
关键词：
PostgreSQL索引
PostgreSQL接口
PostgreSQL全文检索
云数据库 RDS PostgreSQL 版全文检索
云数据库 RDS PostgreSQL 版索引
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
sunrr
|
3月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略？
【8月更文挑战第26天】PostgreSQL的索引优化策略？
sunrr
94 1
土木林森
|
3月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL：主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异，为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统，均使用SQL作为查询语言，但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同，帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言，但每个系统都有独特的语法规则，了解这些差异有助于提升开发效率和项目成功率。
土木林森
360 0
pbeskoyq7ffm4
|
3月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
pbeskoyq7ffm4
282 0
游客ep32quzlpfvj4
|
5月前
|
SQL 关系型数据库 数据库
nacos 2.2.3版本 查看配置文件的历史版本的接口 是针对MySQL数据库的sql 改成postgresql后 sql语句报错 该怎么解决
在Nacos 2.2.3中切换到PostgreSQL后，执行配置文件历史版本分页查询出错，因`LIMIT 0, 10`语法不被PostgreSQL支持，需改为`LIMIT 10 OFFSET 0`。仅当存在历史版本时报错。解决方案是调整查询SQL以兼容PostgreSQL语法。
游客ep32quzlpfvj4
189 4
你都不懂
|
6月前
|
自然语言处理 算法 关系型数据库
postgresql 全文检索 SCWS和zhparser部署
UPDATE report_content SET content_tsv = to_tsvector('testzhcfg',content);
你都不懂
127 8
你都不懂
|
6月前
|
人工智能 自然语言处理 关系型数据库
【PostgreSQL支持中文的全文检索插件(zhparser)】
| Execution Time: 0.146 ms
你都不懂
239 7
t-25772328213-6-45193
|
6月前
|
SQL 存储 缓存
PostgreSQL函数管理接口
学习PostgreSQL服务端开发必须要对函数管理接口有比较深入的了解
t-25772328213-6-45193
206 0
猫头虎
|
关系型数据库 Go 数据库
《提高查询速度：PostgreSQL索引实用指南》
《提高查询速度：PostgreSQL索引实用指南》
猫头虎
585 0
带你读小助手
|
关系型数据库 分布式数据库 数据库
PolarDB for PostgreSQL 14：全局索引
PolarDB for PostgreSQL 14 相较于 PostgreSQL 14，提供了更多企业级数据库的特性。本实验将体验其中的全局索引功能。
带你读小助手
820 0
惜元
|
6月前
|
SQL 关系型数据库 数据库
RDS PostgreSQL索引推荐原理及最佳实践
前言很多开发人员都知道索引对于数据库的查询性能至关重要，一个好的索引能使数据库的性能提升成千上万倍。但给数据库加索引是一项相对专业的工作，需要对数据库的运行原理有一定了解。同时，加了索引有没有性能提升、性能提升了多少，这些都是加索引前就想知道的。这项繁杂的工作有没有更好的方案呢？有！就是今天重磅推出...
惜元
116 1
RDS PostgreSQL索引推荐原理及最佳实践
数据库

关系型数据库

热门文章

最新文章

  • 1
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 2
    什么场景应该用 MongoDB ？
  • 3
    PostgreSQL 如何实现批量更新、删除、插入
  • 4
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 5
    Linux 性能诊断 perf使用指南
  • 6
    PostgreSQL 9种索引的原理和应用场景
  • 7
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 8
    Jedis常见异常汇总
  • 9
    PostgreSQL 9.5+ 高效分区表实现 - pg_pathman
  • 10
    PostgreSQL 百亿数据 秒级响应 正则及模糊查询
  • 1
    【赵渝强老师】MySQL的全量日志文件
    13
  • 2
    【赵渝强老师】MySQL的慢查询日志
    20
  • 3
    Pgloader极简教程
    23
  • 4
    【赵渝强老师】MySQL的binlog日志文件
    23
  • 5
    【赵渝强老师】MySQL的撤销日志文件和错误日志文件
    11
  • 6
    【赵渝强老师】MySQL InnoDB的段、区和页
    27
  • 7
    【赵渝强老师】MySQL InnoDB的表空间
    29
  • 8
    【赵渝强老师】MySQL的基准测试与sysbench
    28
  • 9
    【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
    113
  • 10
    【赵渝强老师】Oracle的参数文件与告警日志文件
    23

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情
  • 云数据库 RDS PostgreSQL 版
    文档详情 产品详情

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • PostgreSQL 物联网六脉神剑
  • PostgreSQL在哈啰的实践-周飞
  • PostgreSQL高并发数据库应用数据

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于OpenSearch向量检索版和MaxCompute快速搭建图搜服务
  • Lindorm AIGC：十分钟搞定智能问答 + 多模态检索

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • clickhouse
    • 下一篇
    阿里云OSS设置跨域访问