Postgresql排序与limit组合场景性能极限优化_posterger limit

1 构造测试数据

create table tbl(id int, num int, arr int[]); 
create index idx_tbl_arr on tbl using gin (arr); 
create or replace function gen_rand_arr() returns int[] as $$  
  select array(select (1000*random())::int from generate_series(1,64));  
$$ language sql strict;


insert into tbl select generate_series(1,3000000),(10000*random())::int, gen_rand_arr();

insert into tbl select generate_series(1,500), (10000*random())::int, array[350,514,213,219,528,753,270,321,413,424,524,435,546,765,234,345,131,345,351];
1
2
3
4
5
6
7
8
9
10

2 查询走GIN索引

测试场景的限制GIN索引查询速度是很快的， 在实际生产中，可能出现使用gin索引后，查询速度依然很高的情况，特点就是执行计划中Bitmap Heap Scan占用了大量时间，Bitmap Index Scan大部分标记的块都被过滤掉了。

这种情况是很常见的，一般的btree索引可以cluster来重组数据，但是gin索引是不支持cluster的，一般的gin索引列都是数组类型。所以当出现数据非常分散的情况时，bitmap index scan会标记大量的块，后面recheck的成本非常高，导致gin索引查询慢。

我们接着来看这个例子

explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 20;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2152.02..2152.03 rows=1 width=40) (actual time=57.665..57.668 rows=20 loops=1)
   ->  Sort  (cost=2152.02..2152.03 rows=1 width=40) (actual time=57.664..57.665 rows=20 loops=1)
         Sort Key: num
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Bitmap Heap Scan on tbl  (cost=2148.00..2152.01 rows=1 width=40) (actual time=57.308..57.581 rows=505 loops=1)
               Recheck Cond: (arr @> '{350,514,213,219,528,753,270}'::integer[])
               Heap Blocks: exact=493
               ->  Bitmap Index Scan on idx_tbl_arr  (cost=0.00..2148.00 rows=1 width=0) (actual time=57.248..57.248 rows=505 loops=1)
                     Index Cond: (arr @> '{350,514,213,219,528,753,270}'::integer[])
 Planning time: 0.050 ms
 Execution time: 57.710 ms
1
2
3
4
5
6
7
8
9
10
11
12
13
14

可以看到当前执行计划是依赖gin索引扫描的，但gin索引出现性能问题时我们如何来优化呢？

3 排序limit组合场景优化

SQL中的排序与limit组合是一个很典型的索引优化创景。我们知道btree索引在内存中是有序的，通过遍历btree索引可以直接拿到sort后的结果，这里组合使用limit后，只需要遍历btree的一部分节点然后按照其他条件recheck就ok了。

我们来看一下优化方法：

create index idx_tbl_num on tbl(num);
analyze tbl;

set enable_seqscan = off;
set enable_bitmapscan = off;


postgres=# explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 10;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..571469.93 rows=1 width=287) (actual time=6.300..173.949 rows=10 loops=1)
   ->  Index Scan Backward using idx_tbl_num on tbl  (cost=0.43..571469.93 rows=1 width=287) (actual time=6.299..173.943 rows=10 loops=1)
         Filter: (arr @> '{350,514,213,219,528,753,270}'::integer[])
         Rows Removed by Filter: 38399
 Planning time: 0.125 ms
 Execution time: 173.972 ms
(6 rows)

Time: 174.615 ms
postgres=# cluster tbl using idx_tbl_num;
CLUSTER
Time: 124340.276 ms
postgres=# explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 10;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..563539.77 rows=1 width=287) (actual time=1.145..34.602 rows=10 loops=1)
   ->  Index Scan Backward using idx_tbl_num on tbl  (cost=0.43..563539.77 rows=1 width=287) (actual time=1.144..34.601 rows=10 loops=1)
         Filter: (arr @> '{350,514,213,219,528,753,270}'::integer[])
         Rows Removed by Filter: 38399
 Planning time: 0.206 ms
 Execution time: 34.627 ms
(6 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

本例的测试场景构造可能没有最大程度的体现问题，不过可以看出cluster后走btree索引可以很稳定的达到34ms左右。

在gin性能存在问题的时候，这类limit + order by的SQL语句不妨常识强制（pg_hint_plan）走一下btree索引，可能有意想不到的效果。

4 高并发场景下的gin索引查询性能下降

GIN索引为PostgreSQL数据库多值类型的倒排索引，一条记录可能涉及到多个GIN索引中的KEY，所以如果写入时实时合并索引，会导致IO急剧增加，写入RT必然增加。为了提高写入吞吐，PG允许用户开启GIN索引的延迟合并技术，开启后，数据会先写入pending list，并不是直接写入索引页，当pending list达到一定大小，或者autovacuum 对应表时，会触发pending list合并到索引的动作。

查询时，如果有未合并到索引中的PENDING LIST，那么会查询pending list，同时查询索引也的信息。

如果写入量很多，pending list非常巨大，合并(autovacuum worker做的)速度跟不上时，会导致通过GIN索引查询时查询性能下降。

create extension pageinspect ; 
SELECT * FROM gin_metapage_info(get_raw_page('idx_tbl_arr', 0));  

-- 如果很多条记录在pending list中，查询性能会下降明显。
-- vacuum table，强制合并pending list
vacuum tbl; 
1
2
3
4
5
6

第4部分引用https://github.com/digoal/blog/blob/master/201809/20180919_02.md