标签
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》
