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

2023-11-08 13:01| 来源: 网络整理| 查看: 265

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]; 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

可以看到当前执行计划是依赖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)

本例的测试场景构造可能没有最大程度的体现问题，不过可以看出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;

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

【本文地址】

公司简介

联系我们