Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

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

2 minute read

背景

《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 文本数据分析实践之 - 相似度分析》

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...