标签
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倍+加速示例 (含，单值+多值列合成)》
小结
本文提供了一种简单的方法，将包括若干普通字段、数组字段的adhoc查询简化，不需要字典化，就可以利用RUM索引，进行非常高效率的精准检索。
参考
《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》
《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 3 rum, smlar应用场景分析》
《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》
