PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化,普通、数组等组合字段生成新数组

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介:

标签

PostgreSQL , rum 索引 , adhoc查询 , 非字典化 , 数组 , 普通字段 , 任意字段组合搜索


背景

《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》

这篇文档介绍了PostgreSQL AdHOC加速的原理,利用rum索引,精准搜索任意字段组合。10亿级数据,任意组合查询,RT低至毫秒级,TPS达到万级。文章提到了数据字典化,将多个字段转换为一个大数组,然后利用RUM索引加速的方法。

如果你嫌麻烦,可以有更加简单的方法,比如我们的表字段中本身就有普通字段,还有数组字段,多种组合,并且你不想字典化,然后有有任意字段组合的包含,相交,等值组合查询,(更复杂的ADhoc查询) 怎么办呢?

利用UDF,把要参与ADHOC(等值、包含、相交等组合条件)搜索的字段们组合起来,创建表达式RUM索引即可。

例子

1、测试表结构

create table test (    
  column1 varchar,    
  column2 int[],    
  column3 int[],    
  column4 text    
);    

目标

将数据转换为这样的数组

[column1_val, column2_val1, column2_val2,..., column3_val1, column3_val2,..., column4_val]    

然后针对这个数组来组合查询(利用rum索引精准搜索)。

2、创建一个函数,给数组添加前缀,返回一个TEXT数组

postgres=# create or replace function f_array_prefix(text, anyarray) returns text[] as $$    
  select array(select $1||unnest($2));              
$$ language sql strict immutable;    

效果

postgres=# select f_array_prefix('abc_', array[1,2,3]);    
   f_array_prefix        
---------------------    
 {abc_1,abc_2,abc_3}    
(1 row)    
    
postgres=# select f_array_prefix('abc_', array['a','b','c']);    
   f_array_prefix        
---------------------    
 {abc_a,abc_b,abc_c}    
(1 row)    

2.1、扩展知识,解读一个带前缀的数组的指定前缀的后缀。

postgres=# create or replace function get_suffix(text[], text) returns text[] as $$
  select array_remove(array(select substring(unnest, $2||'(.*)') from unnest($1)),null);
$$ language sql strict immutable;
CREATE FUNCTION

postgres=# select get_suffix(array['abc_1','abc_2','t_2','t_123'], 't_');
 get_suffix 
------------
 {2,123}
(1 row)

3、创建表达式函数,将test表参与ADHoc查询的字段们组合成一个新的TEXT数组

create or replace function f_search(varchar, int[], int[], text) returns text[] as $$    
  select 
  array_remove(    
    array_append(    
      array_cat(    
        array_cat(    
          array_append(array[]::text[], 'column1_'||$1), -- 初始数组为空     
          f_array_prefix('column2_', $2)    
        ),    
        f_array_prefix('column3_', $3)    
      ),    
      'column4_'||$4     
    ),
  null);    
$$ language sql CALLED ON NULL INPUT immutable;    

效果

postgres=# select f_search('abcde', array[1,2,3], array[5,6,7], 'hello');    
                                         f_search                                              
-------------------------------------------------------------------------------------------    
 {column1_abcde,column2_1,column2_2,column2_3,column3_5,column3_6,column3_7,column4_hello}    
(1 row)    

4、创建一个函数,生成随机数组:1万个取值空间, 20个随机值.

create or replace function gen_rand() returns int[] as $$    
  select array(select (10000*random())::int from generate_series(1,20));    
$$ language sql strict volatile;    

效果

postgres=# select gen_rand();    
                                              gen_rand                                                  
----------------------------------------------------------------------------------------------------    
 {6714,935,1593,8801,4097,5959,2059,3306,8710,4663,8671,7999,9122,4405,8874,236,822,6524,8093,8368}    
(1 row)    
    
postgres=# select gen_rand();    
                                               gen_rand                                                    
-------------------------------------------------------------------------------------------------------    
 {3640,5125,5307,4672,1943,9987,6141,8813,6347,6007,9652,3061,6942,1245,1862,1039,7204,3921,4345,5914}    
(1 row)    

5、生成100万测试数据

insert into test select md5(random()::text), gen_rand(), gen_rand(), md5(random()::text) from generate_series(1,1000000);    

6、创建表达式rum索引

create index idx_test_1 on test using rum (f_search(column1, column2, column3, column4) rum_anyarray_ops);    

7、表和索引大小如下

postgres=# \dt+ test    
                    List of relations    
 Schema | Name | Type  |  Owner   |  Size  | Description     
--------+------+-------+----------+--------+-------------    
 public | test | table | postgres | 287 MB |     
(1 row)    
    
postgres=# \di+ idx_test_1     
                           List of relations    
 Schema |    Name    | Type  |  Owner   | Table |  Size  | Description     
--------+------------+-------+----------+-------+--------+-------------    
 public | idx_test_1 | index | postgres | test  | 917 MB |     
(1 row)    

8、对等查询例子

explain select * from test where f_search(column1, column2, column3, column4) @> array['column2_1', 'column3_5'];    
    
等价于    
    
explain select * from test where column2 @> array[1] and column3 @> array[5];    
explain select * from test where f_search(column1, column2, column3, column4) && array['column2_1', 'column3_5'];    
    
等价于    
    
explain select * from test where column2 @> array[1] or column3 @> array[5];    
explain select * from test     
where     
f_search(column1, column2, column3, column4) @> array['column2_1', 'column3_5', 'column1_abc']    
or    
f_search(column1, column2, column3, column4) @> array['column2_2', 'column3_5', 'column1_abc']    
or    
f_search(column1, column2, column3, column4) @> array['column2_3', 'column3_5', 'column1_abc'];    
    
等价于    
    
explain select * from test where column2 && array[1,2,3] and column3 @> array[5] and column1='abc';    

9、例子

任意字段组合, 精准检索,1毫秒内响应。

postgres=# explain (analyze,verbose,timing,costs,buffers)   
select * from test   
where   -- 表达式查询  
f_search(column1, column2, column3, column4) @> array['column2_1', 'column3_5'];    
  
                                                          QUERY PLAN                                                              
------------------------------------------------------------------------------------------------------------------------------    
 Index Scan using idx_test_1 on public.test  (cost=12.00..43.64 rows=25 width=268) (actual time=0.511..0.530 rows=10 loops=1)    
   Output: column1, column2, column3, column4    
   Index Cond: (f_search(test.column1, test.column2, test.column3, test.column4) @> '{column2_1,column3_5}'::text[])    
   Buffers: shared hit=20    
 Planning Time: 0.245 ms    
 Execution Time: 0.548 ms    
(6 rows)    

深挖优化 - schema less\分区

如果你的数据中,有一些字段很稀疏,并且这些字段也参与了任意字段组合搜索,那么有没有更深入的优化方法呢?

稀疏列的查看

analyze tablename;
select attname, n_distinct from pg_stats where tablename='tablename' and n_distinct>=0 order by 2;

我们有两种方法可以使得性能更加精进:

1、对这些稀疏字段采用schema less\表分区,就是说通过UDF或者数据库的多级分区表,将数据按稀疏字段打散,每个分区一个RUM索引(分区字段不作为RUM的索引字段),当输入分区字段作为组合查询条件时,会自动根据分区规则过滤到对应的一个或多个分区进行查询。

2、对RUM索引使用partial index或(分区索引),与方法1类似,只不过是一张表,但是通过partial index手段对索引进行分区,实现索引本身的切片,当任意字段搜索中输入了partial index的where 字段条件时,自动选择对应的RUM partial索引。

但是,请注意:

如果用户没有输入任何分区字段作为条件,那么需要扫描所有分区,每个分区走对应的分区RUM索引。

方法1例子:

《时间、空间、对象多维属性 海量数据任意多维 高效检索 - 阿里云RDS PostgreSQL最佳实践》

《PostgreSQL 在铁老大订单系统中的schemaless设计和性能压测》

《PostgreSQL schemaless 的实现(类mongodb collection)》

《PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践》

深挖优化 - partial index\分区索引

方法2例子:

《PostgreSQL 店铺运营实践 - JSON[]数组 内部标签数据等值、范围检索100倍+加速示例 (含,单值+多值列合成)》

《PostgreSQL UDF实现tsvector(全文检索), array(数组)多值字段与scalar(单值字段)类型的整合索引(类分区索引) - 单值与多值类型复合查询性能提速100倍+ 案例 (含,单值+多值列合成)》

小结

本文提供了一种简单的方法,将包括若干普通字段、数组字段的adhoc查询简化,不需要字典化,就可以利用RUM索引,进行非常高效率的精准检索。

参考

《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》

《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 3 rum, smlar应用场景分析》

《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》

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

《PostgreSQL 文本数据分析实践之 - 相似度分析》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
7天前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
41 1
|
3月前
|
缓存 关系型数据库 数据库
PostgreSQL 查询性能
【8月更文挑战第5天】PostgreSQL 查询性能
72 8
|
4月前
|
Java 关系型数据库 API
使用Spring Boot和PostgreSQL构建高级查询
使用Spring Boot和PostgreSQL构建高级查询
|
5月前
|
SQL 关系型数据库 数据库
Python查询PostgreSQL数据库
木头左教你如何用Python连接PostgreSQL数据库:安装`psycopg2`库,建立连接,执行SQL脚本如创建表、插入数据,同时掌握错误处理和事务管理。别忘了性能优化,利用索引、批量操作提升效率。下期更精彩!💡 csvfile
Python查询PostgreSQL数据库
|
关系型数据库 分布式数据库 PolarDB
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
359 0
|
存储 缓存 关系型数据库
|
存储 SQL 并行计算
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(中)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
415 0
|
存储 算法 安全
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(下)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
377 0
|
关系型数据库 分布式数据库 开发工具

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版