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

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

标签

PostgreSQL , 单值列 , 多值列 , GIN倒排索引 , 多值列变异 , 分区索引 , 分区表 , 变异索引


背景

在应用程序中,特别是使用PostgreSQL多值列(数组、全文检索、JSON)类型的业务中,除了多值列有查询需求,单值列也有查询需求。

那么当两种查询并行存在时,数据库会根据COST选择单列或多列组合索引。但是性能并不能达到极致,只是简化用户的索引设计而已。

例如

create table tbl(gid int, c1 int[]);  
  
  
insert into tbl select random()*99, gen_randarr(999, 10) from generate_series(1,10000000);  

gid有100个值,c1有10个值(取值范围1000以内)。用户可能按gid查询,也可能按c1查询,还可能按两个字段组合查询。

当按两个字段组合查询时,现有达到方法并不高效。包括btree_gin在内。

如何提速?

demo

1、创建一个产生随机数的函数

CREATE OR REPLACE FUNCTION public.gen_randarr(integer, integer)  
 RETURNS integer[]  
 LANGUAGE sql  
 STRICT  
AS $function$  
  select array(select (random()*$1)::int from generate_series(1,$2));  
$function$;  

2、创建测试表,包含一个单值列,和一个多值列

create table tbl(gid int, c1 int[]);  

3、写入1000万数据

insert into tbl select random()*99, gen_randarr(999, 10) from generate_series(1,10000000);  

传统加速方法1

建立 单值列+多值列 的复合索引

create extension btree_gin;  
set maintenance_work_mem ='8GB';  
create index idx_tbl_1 on tbl using gin (gid, c1);  

复合查询性能如何?

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where gid=1 and c1 @> array[1,2,3];  
                                                     QUERY PLAN                                                        
---------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl  (cost=96.00..97.02 rows=1 width=65) (actual time=12.810..12.810 rows=0 loops=1)  
   Output: gid, c1  
   Recheck Cond: ((tbl.gid = 1) AND (tbl.c1 @> '{1,2,3}'::integer[]))  
   Buffers: shared hit=184  
   ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..96.00 rows=1 width=0) (actual time=12.807..12.807 rows=0 loops=1)  
         Index Cond: ((tbl.gid = 1) AND (tbl.c1 @> '{1,2,3}'::integer[]))  
         Buffers: shared hit=184  
 Planning time: 0.154 ms  
 Execution time: 12.838 ms  
(9 rows)  
postgres=# explain (analyze,verbose,timing,costs,buffers)  select * from tbl where gid=1 and c1 && array[1,2,3];  
                                                         QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl  (cost=129.80..3433.25 rows=3297 width=65) (actual time=17.453..22.486 rows=2932 loops=1)  
   Output: gid, c1  
   Recheck Cond: ((tbl.gid = 1) AND (tbl.c1 && '{1,2,3}'::integer[]))  
   Heap Blocks: exact=2906  
   Buffers: shared hit=3089  
   ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..128.97 rows=3297 width=0) (actual time=17.121..17.121 rows=2932 loops=1)  
         Index Cond: ((tbl.gid = 1) AND (tbl.c1 && '{1,2,3}'::integer[]))  
         Buffers: shared hit=183  
 Planning time: 0.223 ms  
 Execution time: 22.761 ms  
(10 rows)  

如果你没有继续优化的动力,你会发现这个性能好像也蛮OK的。索引条件也用了多个。

实际上这个索引是内部将两个索引合并后做的BITMAP SCAN。

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

传统加速方法2

实际上就是将 单值, 多值列 索引分开建。

postgres=# drop index idx_tbl_1;  
DROP INDEX  
  
postgres=# create index idx_tbl_1 on tbl (gid);  
CREATE INDEX  
  
postgres=# create index idx_tbl_2 on tbl using gin (c1);  

实际效果比GIN的复合索引差一些

explain (analyze,verbose,timing,costs,buffers) select * from tbl where gid=1 and c1 @> array[1,2,3];  
  
explain (analyze,verbose,timing,costs,buffers)  select * from tbl where gid=1 and c1 && array[1,2,3];  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where gid=1 and c1 @> array[1,2,3];  
                                                      QUERY PLAN                                                         
-----------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl  (cost=72.09..83.25 rows=1 width=65) (actual time=12.848..12.848 rows=0 loops=1)  
   Output: gid, c1  
   Recheck Cond: (tbl.c1 @> '{1,2,3}'::integer[])  
   Filter: (tbl.gid = 1)  
   Rows Removed by Filter: 13  
   Heap Blocks: exact=13  
   Buffers: shared hit=131  
   ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..72.09 rows=11 width=0) (actual time=12.810..12.810 rows=13 loops=1)  
         Index Cond: (tbl.c1 @> '{1,2,3}'::integer[])  
         Buffers: shared hit=118  
 Planning time: 0.254 ms  
 Execution time: 12.874 ms  
(12 rows)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers)  select * from tbl where gid=1 and c1 && array[1,2,3];  
                                                              QUERY PLAN                                                                 
---------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl  (cost=3534.41..6837.86 rows=3297 width=65) (actual time=69.636..74.613 rows=2932 loops=1)  
   Output: gid, c1  
   Recheck Cond: ((tbl.gid = 1) AND (tbl.c1 && '{1,2,3}'::integer[]))  
   Heap Blocks: exact=2906  
   Buffers: shared hit=2982 read=279  
   ->  BitmapAnd  (cost=3534.41..3534.41 rows=3297 width=0) (actual time=69.002..69.002 rows=0 loops=1)  
         Buffers: shared hit=76 read=279  
         ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..1089.93 rows=106333 width=0) (actual time=13.538..13.538 rows=100704 loops=1)  
               Index Cond: (tbl.gid = 1)  
               Buffers: shared read=279  
         ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..2442.58 rows=310077 width=0) (actual time=50.878..50.878 rows=296887 loops=1)  
               Index Cond: (tbl.c1 && '{1,2,3}'::integer[])  
               Buffers: shared hit=76  
 Planning time: 0.147 ms  
 Execution time: 74.886 ms  
(15 rows)  

本文100倍性能提升加速方法

本文提到的方法,适合于单值列与多值列混合查询的场景,实际上我们用到了PostgreSQL的UDF和表达式索引的功能。

UDF将单值列和多值列合并,变成一个新的多值列。

表达式索引,针对这个UDF来构建。

目的是将内部的BITMAP合并这个部分化解掉,仅使用一颗倒排树,并且这颗倒排树包含了 单值列和多值列的值。

1、创建一个UDF,将本例的gid与c1的值合并。

create or replace function gen_newarr(int, anyarray) returns text[] as $$  
declare  
  res text[] := '{}';  
  x int;  
begin  
  foreach x in array $2 loop  
    res := array_append(res, $1||'_'||x);  
  end loop;  
  return res;  
end;  
$$ language plpgsql strict immutable;  
  
  
  
  
postgres=# select gen_newarr(123,array[1,2,3,4]);  
-[ RECORD 1 ]-------------------------  
gen_newarr | {123_1,123_2,123_3,123_4}  

2、创建表达式索引

set maintenance_work_mem ='8GB';  
create index idx_tbl_2 on tbl using gin (gen_newarr(gid, c1));  

3、查询时,使用表达式查询,所以需要更改SQL语句

postgres=# explain (analyze,verbose,timing,costs,buffers)  select * from tbl where gen_newarr(gid, c1) @> array['1_1','1_2','1_3'];  
                                                    QUERY PLAN                                                       
-------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl  (cost=12.01..13.27 rows=1 width=65) (actual time=0.146..0.146 rows=0 loops=1)  
   Output: gid, c1  
   Recheck Cond: (gen_newarr(tbl.gid, tbl.c1) @> '{1_1,1_2,1_3}'::text[])  
   Buffers: shared hit=14  
   ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..12.01 rows=1 width=0) (actual time=0.144..0.144 rows=0 loops=1)  
         Index Cond: (gen_newarr(tbl.gid, tbl.c1) @> '{1_1,1_2,1_3}'::text[])  
         Buffers: shared hit=14  
 Planning time: 0.092 ms  
 Execution time: 0.174 ms  
(9 rows)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers)  select * from tbl where gen_newarr(gid, c1) && array['1_1','1_2','1_3'];  
                                                         QUERY PLAN                                                            
-----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl  (cost=1220.70..133422.08 rows=149251 width=65) (actual time=1.020..6.034 rows=2932 loops=1)  
   Output: gid, c1  
   Recheck Cond: (gen_newarr(tbl.gid, tbl.c1) && '{1_1,1_2,1_3}'::text[])  
   Heap Blocks: exact=2906  
   Buffers: shared hit=2919  
   ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..1183.38 rows=149251 width=0) (actual time=0.640..0.640 rows=2932 loops=1)  
         Index Cond: (gen_newarr(tbl.gid, tbl.c1) && '{1_1,1_2,1_3}'::text[])  
         Buffers: shared hit=13  
 Planning time: 0.102 ms  
 Execution time: 6.348 ms  
(10 rows)  

查询如下

select * from tbl where gen_newarr(gid, c1) && array['1_1','1_2','1_3'];  
  
 gid |                   c1                      
-----+-----------------------------------------  
   1 | {62,904,204,618,917,227,388,352,167,1}  
   1 | {825,126,174,409,340,285,231,942,3,136}  
   1 | {222,418,799,881,728,582,558,2,368,196}  
   1 | {847,197,690,1,288,468,179,521,799,196}  
   1 | {867,316,447,747,953,998,370,360,558,3}  
   1 | {249,963,669,929,534,945,388,816,1,601}  
   1 | {925,609,108,981,712,681,906,832,3,275}  
   1 | {3,354,253,947,588,598,401,89,246,968}  
   1 | {323,121,22,3,7,714,80,619,178,439}  
   1 | {866,1,185,704,932,882,496,324,264,882}  
......  

性能显著提升。

单值列+全文检索 复合查询加速

原理类似,将单值列与全文检索列合并

create table tbl123(gid int, ts tsvector);  
  
insert into tbl123 select random()*99, array_to_tsvector(gen_randarr(999, 10)::text[]) from generate_series(1,10000000);  
create index idx_tbl123_1 on tbl123 using gin ( array_to_tsvector(gen_newarr(gid, tsvector_to_array(ts))) );  
explain (analyze,verbose,timing,costs,buffers)  select * from tbl123 where array_to_tsvector(gen_newarr(gid, tsvector_to_array(ts))) @@ tsquery '1_1 & 1_2 & 1_3';  
  
  
explain (analyze,verbose,timing,costs,buffers)  select * from tbl123 where array_to_tsvector(gen_newarr(gid, tsvector_to_array(ts))) @@ tsquery '1_1 | 1_2 | 1_3';  
  
  
postgres=# explain (analyze,verbose,timing,costs,buffers)  select * from tbl123 where array_to_tsvector(gen_newarr(gid, tsvector_to_array(ts))) @@ tsquery '1_1 & 1_2 & 1_3';  
                                                               QUERY PLAN                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl123  (cost=12.01..13.28 rows=1 width=77) (actual time=0.207..0.207 rows=0 loops=1)  
   Output: gid, ts  
   Recheck Cond: (array_to_tsvector(gen_newarr(tbl123.gid, tsvector_to_array(tbl123.ts))) @@ '''1_1'' & ''1_2'' & ''1_3'''::tsquery)  
   Buffers: shared hit=14  
   ->  Bitmap Index Scan on idx_tbl123_1  (cost=0.00..12.01 rows=1 width=0) (actual time=0.204..0.204 rows=0 loops=1)  
         Index Cond: (array_to_tsvector(gen_newarr(tbl123.gid, tsvector_to_array(tbl123.ts))) @@ '''1_1'' & ''1_2'' & ''1_3'''::tsquery)  
         Buffers: shared hit=14  
 Planning time: 0.080 ms  
 Execution time: 0.238 ms  
(9 rows)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers)  select * from tbl123 where array_to_tsvector(gen_newarr(gid, tsvector_to_array(ts))) @@ tsquery '1_1 | 1_2 | 1_3';  
                                                               QUERY PLAN                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl123  (cost=1220.70..136709.34 rows=149251 width=77) (actual time=0.971..5.988 rows=2970 loops=1)  
   Output: gid, ts  
   Recheck Cond: (array_to_tsvector(gen_newarr(tbl123.gid, tsvector_to_array(tbl123.ts))) @@ '''1_1'' | ''1_2'' | ''1_3'''::tsquery)  
   Heap Blocks: exact=2937  
   Buffers: shared hit=2950  
   ->  Bitmap Index Scan on idx_tbl123_1  (cost=0.00..1183.38 rows=149251 width=0) (actual time=0.612..0.612 rows=2970 loops=1)  
         Index Cond: (array_to_tsvector(gen_newarr(tbl123.gid, tsvector_to_array(tbl123.ts))) @@ '''1_1'' | ''1_2'' | ''1_3'''::tsquery)  
         Buffers: shared hit=13  
 Planning time: 0.029 ms  
 Execution time: 6.284 ms  
(10 rows)  
  
postgres=# select * from tbl123 where array_to_tsvector(gen_newarr(gid, tsvector_to_array(ts))) @@ tsquery '1_1 | 1_2 | 1_3';  
 gid |                            ts                               
-----+-----------------------------------------------------------  
   1 | '180' '219' '253' '262' '282' '3' '633' '657' '807' '809'  
   1 | '1' '166' '261' '670' '807' '860' '897' '922' '93' '964'  
   1 | '1' '174' '211' '319' '322' '532' '84' '849' '869' '993'  
......  

性能提升也非常明显。

小结

1、分区表,每个分区定义对应的索引。但是当单值类型本身包含的值非常多时,分区就要很多很多,可能也不是很好。

2、分区索引,目前PG还不支持对单个表创建多颗树的复合索引(一颗树以单值列构建,VALUE指向另一个颗树。另一颗数以多值列构建的GIN倒排树。)

3、也就是本例提到的方法,使用UDF,将单值列与多值列合并,类似变异,变成另一个多值列,在另一个多值列中包含这个单值列的属性,从而达到分区表或分区索引同样的效果。

性能提升非常明显。

当多值列本身就携带单值列属性时,我们就没有必要建立 单值列+多值列 的复合索引,仅仅构建多值列索引即可。

当多值列中没有携带单值列属性时,那么我们如果有 单值列+多值列 的复合查询条件,可以采用UDF,将 单值列+多值列 合并成一个新的多值列,对多值列构建GIN倒排索引,提速非常明显。本例提速超过100倍

参考

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
1月前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
212 1
|
2月前
|
人工智能 前端开发 JavaScript
拿下奇怪的前端报错(一):报错信息是一个看不懂的数字数组Buffer(475) [Uint8Array],让AI大模型帮忙解析
本文介绍了前端开发中遇到的奇怪报错问题,特别是当错误信息不明确时的处理方法。作者分享了自己通过还原代码、试错等方式解决问题的经验,并以一个Vue3+TypeScript项目的构建失败为例,详细解析了如何从错误信息中定位问题,最终通过解读错误信息中的ASCII码找到了具体的错误文件。文章强调了基础知识的重要性,并鼓励读者遇到类似问题时不要慌张,耐心分析。
|
2月前
|
存储 Java
Java“(array) <X> Not Initialized” (数组未初始化)错误解决
在Java中,遇到“(array) &lt;X&gt; Not Initialized”(数组未初始化)错误时,表示数组变量已被声明但尚未初始化。解决方法是在使用数组之前,通过指定数组的大小和类型来初始化数组,例如:`int[] arr = new int[5];` 或 `String[] strArr = new String[10];`。
84 2
|
2月前
|
存储 JavaScript 前端开发
JavaScript Array(数组) 对象
JavaScript Array(数组) 对象
27 3
|
2月前
|
数据采集 JavaScript 前端开发
JavaScript中通过array.filter()实现数组的数据筛选、数据清洗和链式调用,JS中数组过滤器的使用详解(附实际应用代码)
JavaScript中通过array.filter()实现数组的数据筛选、数据清洗和链式调用,JS中数组过滤器的使用详解(附实际应用代码)
|
3月前
|
Go
Golang语言之数组(array)快速入门篇
这篇文章是关于Go语言中数组的详细教程,包括数组的定义、遍历、注意事项、多维数组的使用以及相关练习题。
38 5
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
368 0
|
存储 缓存 关系型数据库
|
存储 SQL 并行计算
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(中)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
428 0
|
存储 算法 安全
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(下)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
386 0

相关产品

  • 云原生数据库 PolarDB