• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
启东1 显示全部楼层 发表于 2021-10-26 13:00:09 |阅读模式 打印 上一主题 下一主题
1 构造测试数据
  1. create table tbl(id int, num int, arr int[]);
  2. create index idx_tbl_arr on tbl using gin (arr);
  3. create or replace function gen_rand_arr() returns int[] as $$
  4. select array(select (1000*random())::int from generate_series(1,64));
  5. $$ language sql strict;
  6. insert into tbl select generate_series(1,3000000),(10000*random())::int, gen_rand_arr();
  7. 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索引查询慢。
我们接着来看这个例子
  1. explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 20;
  2.                 QUERY PLAN
  3. ---------------------------------------------------------------------------------------------------------------------------------------
  4. Limit (cost=2152.02..2152.03 rows=1 width=40) (actual time=57.665..57.668 rows=20 loops=1)
  5. -> Sort (cost=2152.02..2152.03 rows=1 width=40) (actual time=57.664..57.665 rows=20 loops=1)
  6.    Sort Key: num
  7.    Sort Method: top-N heapsort Memory: 27kB
  8.    -> Bitmap Heap Scan on tbl (cost=2148.00..2152.01 rows=1 width=40) (actual time=57.308..57.581 rows=505 loops=1)
  9.     Recheck Cond: (arr @> '{350,514,213,219,528,753,270}'::integer[])
  10.     Heap Blocks: exact=493
  11.     -> 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)
  12.       Index Cond: (arr @> '{350,514,213,219,528,753,270}'::integer[])
  13. Planning time: 0.050 ms
  14. Execution time: 57.710 ms
复制代码
可以看到当前执行计划是依靠gin索引扫描的,但gin索引出现性能题目时我们如何来优化呢?
3 排序limit组合场景优化

SQL中的排序与limit组合是一个很典型的索引优化创景。我们知道btree索引在内存中是有序的,通过遍历btree索引可以直接拿到sort后的结果,这里组合使用limit后,只必要遍历btree的一部门节点然后按照其他条件recheck就ok了。
我们来看一下优化方法:
  1. create index idx_tbl_num on tbl(num);
  2. analyze tbl;
  3. set enable_seqscan = off;
  4. set enable_bitmapscan = off;
  5. postgres=# explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 10;
  6.                 QUERY PLAN
  7. ------------------------------------------------------------------------------------------------------------------------------------------
  8. Limit (cost=0.43..571469.93 rows=1 width=287) (actual time=6.300..173.949 rows=10 loops=1)
  9. -> 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)
  10.    Filter: (arr @> '{350,514,213,219,528,753,270}'::integer[])
  11.    Rows Removed by Filter: 38399
  12. Planning time: 0.125 ms
  13. Execution time: 173.972 ms
  14. (6 rows)
  15. Time: 174.615 ms
  16. postgres=# cluster tbl using idx_tbl_num;
  17. CLUSTER
  18. Time: 124340.276 ms
  19. postgres=# explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 10;
  20.                 QUERY PLAN
  21. -----------------------------------------------------------------------------------------------------------------------------------------
  22. Limit (cost=0.43..563539.77 rows=1 width=287) (actual time=1.145..34.602 rows=10 loops=1)
  23. -> 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)
  24.    Filter: (arr @> '{350,514,213,219,528,753,270}'::integer[])
  25.    Rows Removed by Filter: 38399
  26. Planning time: 0.206 ms
  27. Execution time: 34.627 ms
  28. (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索引查询时查询性能降落。
  1. create extension pageinspect ;
  2. SELECT * FROM gin_metapage_info(get_raw_page('idx_tbl_arr', 0));
  3. -- 如果很多条记录在pending list中,查询性能会下降明显。
  4. -- vacuum table,强制合并pending list
  5. vacuum tbl;
复制代码
第4部门引用https://github.com/digoal/blog/blob/master/201809/20180919_02.md
补充:PostgreSQL -- 性能优化的小方法
一、接纳磁盘空间

在PostgreSQL中,使用delete和update语句删除或更新的数据行并没有被现实删除,而只是在旧版本数据行的物理所在大将该行的状态置为已删除或已逾期。因此当数据表中的数据变化极为频仍时,那么在一段时间之后该表所占用的空间将会变得很大,然而数据量却大概变化不大。要办理该题目,必要定期对数据变化频仍的数据表执行VACUUM操纵。如今新版PostgreSQL是自动执行VACUUM的
使用VACUUM和VACUUM FULL下令接纳磁盘空间
  1. postgres=# vacuum arr_test;
  2. postgres=# vacuum full arr_test;
复制代码
创建测试数据:
  1. postgres=# create table arr (id serial, value int, age int) #创建测试表
  2. postgres=# insert into arr (value, age) select generate_series(1, 1000000) as value, (random()*(10^2))::integer; #插入100W测试数据
  3. postgres=# select pg_relation_size('arr'); #查看表大小
  4. pg_relation_size
  5. ------------------
  6.    44285952
  7. (1 row)
  8. postgres=# delete from arr where id<300000; #删除299999条数据
  9. DELETE 299999
  10. postgres=# select pg_relation_size('arr'); #再次查看表大小,没有变化
  11. pg_relation_size
  12. ------------------
  13.    44285952
  14. (1 row)
  15. postgres=# vacuum full arr; #vacuum表,再次查看表大小,明显变小了
  16. VACUUM
  17. postgres=# select pg_relation_size('arr');
  18. pg_relation_size
  19. ------------------
  20.    30998528
  21. (1 row)
  22. postgres=# update arr set age=10000 where id>=300000 and id<600000; #更新30W条数据
  23. UPDATE 300000
  24. postgres=# select pg_relation_size('arr'); #查看表大小,明显再次增大
  25. pg_relation_size
  26. ------------------
  27.    44285952
  28. (1 row)
复制代码
二、重建索引

在PostgreSQL中,为数据更新频仍的数据表定期重建索引(REINDEX INDEX)黑白常有必要的。
对于B-Tree索引,只有那些已经完全清空的索引页才会得到重复使用,对于那些仅部门空间可用的索引页将不会得到重用,假如一个页面中大多数索引键值都被删除,只留下很少的一部门,那么该页将不会被开释并重用。
在这种非常的环境下,由于每个索引页面的使用率极低,一旦数据量明显增加,将会导致索引文件变得极为巨大,不仅低沉了查询效率,而且还存在整个磁盘空间被完全填满的危险。
对于重建后的索引还存在别的一个性能上的上风,因为在新创建的索引上,逻辑上相互毗连的页面在物理上往往也是连在一起的,如许可以提高磁盘页面被一连读取的几率,从而提高整个操纵的IO效率
  1. postgres=# REINDEX INDEX testtable_idx;
复制代码
三、重新网络统计信息

PostgreSQL查询规划器在选择最优路径时,必要参照相关数据表的统计信息用以为查询生成最公道的规划。这些统计是通过ANALYZE下令得到的,你可以直接调用该下令,大概把它当做VACUUM下令里的一个可选步调来调用,如VACUUM ANAYLYZE table_name,该下令将会先执行VACUUM再执行ANALYZE。与接纳空间(VACUUM)一样,对数据更新频仍的表保持肯定频度的ANALYZE,从而使该表的统计信息始终处于相对较新的状态,如许对于基于该表的查询优化将是极为有利的。然而对于更新并不频仍的数据表,则不必要执行该操纵。
我们可以为特定的表,甚至是表中特定的字段运行ANALYZE下令,如许我们就可以根据现实环境,只对更新比较频仍的部门信息执行ANALYZE操纵,如许不仅可以节省统计信息所占用的空间,也可以提高本次ANALYZE操纵的执行效率。
这里必要额外说明的是,ANALYZE是一项相称快的操纵,即使是在数据量较大的表上也是云云,因为它使用了统计学上的随机采样的方法进行行采样,而不是把每一行数据都读取进来并进行分析。因此,可以考虑定期对整个数据库执行该下令。
毕竟上,我们甚至可以通过下面的下令来调解指定字段的抽样率
如:
  1. ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200
复制代码
留意:该值的取值范围是0--1000,其中值越低采样比例就越低,分析结果的准确性也就越低,但是ANALYZE下令执行的速率却更快。假如将该值设置为-1,那么该字段的采样比率将规复到体系当前默认的采样值,我们可以通过下面的下令获取当前体系的缺省采样值。
  1. postgres=# show default_statistics_target;
  2.   default_statistics_target
  3. ---------------------------
  4.   100
  5. (1 row)
复制代码
从上面的结果可以看出,该数据库的缺省采样值为100(10%)。
postgresql 性能优化
一、排序:

1. 尽量避免
2. 排序的数据量尽量少,并保证在内存里完成排序。
(至于详细什么数据量能在内存中完成排序,不同数据库有不同的配置:
oracle是sort_area_size;
postgresql是work_mem (integer),单位是KB,默认值是4MB。
mysql是sort_buffer_size 留意:该参数对应的分配内存是每毗连独占!

二、索引:

1. 过滤的数据量比较少,一样平常来说<20%,应该走索引。20%-40% 大概走索引也大概不走索引。> 40% ,基本不走索引(会全表扫描)
2. 保证值的数据范例和字段数据范例要同等。
3. 对索引的字段进行计算时,必须在运算符右侧进行计算。也就是 to_char(oc.create_date, ‘yyyyMMdd')是没用的
4. 表字段之间关联,尽量给相关字段上添加索引。
5. 复合索引,遵从最左前缀的原则,即最左优先。(单独右侧字段查询没有索引的)
三、毗连查询方式:

1、hash join
放内存里进行关联。
实用于结果集比较大的环境。
好比都是200000数据
2、nest loop
从结果1 逐行取出,然后与结果集2进行匹配。
实用于两个结果集,其中一个数据量宏大于别的一个时。
结果集一:1000
结果集二:1000000
四、多表联查时:

在多表联查时,必要考虑毗连序次题目。
1、当postgresql中进行查询时,假如多表是通过逗号,而不是join毗连,那么毗连序次是多表的笛卡尔积中取最优的。假如有太多输入的表, PostgreSQL规划器将从穷举搜索切换为基因概率搜索,以镌汰大概性数量(样本空间)。基因搜索花的时间少, 但是并不肯定能找到最好的规划。
2、对于JOIN,LEFT JOIN / RIGHT JOIN 会肯定水平上指定毗连序次,但是照旧会在某种水平上重新分列:FULL JOIN 完全逼迫毗连序次。假如要逼迫规划器遵照准确的JOIN毗连序次,我们可以把运行时参数join_collapse_limit设置为 1
五、PostgreSQL提供了一些性能调优的功能:

优化思路:
0、为每个表执行 ANALYZE
。然后分析 EXPLAIN (ANALYZE,BUFFERS) sql。
1、对于多表查询,检察每张表数据,然后改进毗连序次。
2、先查找那部门是重点语句,好比上面SQL,表面的嵌套层对于优化来说没故意义,可以去掉。
3、检察语句中,where等条件子句,每个字段能过滤的效率。找出可优化处。
好比oc.order_id = oo.order_id是关联条件,必要加索引
oc.op_type = 3 能过滤出1/20的数据,
oo.event_type IN (…) 能过滤出1/10的数据,
这两个是优化的重点,也就是实现确保op_type与event_type已经加了索引,其次确保索引用到了。
优化方案:
a) 团体优化:
1、使用EXPLAIN
EXPLAIN下令可以检察执行计划,这个方法是我们最主要的调试工具。
2、及时更新执行计划中使用的统计信息
由于统计信息不是每次操纵数据库都进行更新的,一样平常是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL执行的时间会更新统计信息,
因此执行计划所用的统计信息很有大概比较旧。 如许执行计划的分析结果大概误差会变大。
以下是表tenk1的相关的一部门统计信息。
  1. SELECT relname, relkind, reltuples, relpages
  2. FROM pg_class
  3. WHERE relname LIKE 'tenk1%';
  4. relname | relkind | reltuples | relpages
  5. ----------------------+---------+-----------+----------
  6. tenk1 | r | 10000 | 358
  7. tenk1_hundred | i | 10000 | 30
  8. tenk1_thous_tenthous | i | 10000 | 30
  9. tenk1_unique1 | i | 10000 | 30
  10. tenk1_unique2 | i | 10000 | 30
  11. (5 rows)
复制代码
其中 relkind是范例,r是自身表,i是索引index;reltuples是项目数;relpages是所占硬盘的块数。
估计本钱通过 (磁盘页面读取【relpages】*seq_page_cost)+(行扫描【reltuples】*cpu_tuple_cost)计算。
默认环境下, seq_page_cost是1.0,cpu_tuple_cost是0.01。

3、使用暂时表(with)
对于数据量大,且无法有用优化时,可以使用暂时表来过滤数据,低沉数据数量级。
4、对于会影响结果的分析,可以使用 begin;…rollback;往返滚。

b) 查询优化:
1、明确用join来关联表,确保毗连序次
一样平常写法:SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
假如明确用join的话,执行时间执行计划相对容易控制一些。
例子:
  1. SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
  2. SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
复制代码
c) 插入更新优化
1、关闭自动提交(autocommit=false)
假如有多条数据库插入或更新等,最好关闭自动提交,如许能提高效率
2、多次插入数据用copy下令更高效
我们有的处理中要对同一张表执行许多次insert操纵。这个时间我们用copy下令更有用率。因为insert一次,其相关的index都要做一次,比较耗费时间。
3、暂时删除index【详细可以检察Navicat表数据生成sql的语句,就是先删再建的】
有时间我们在备份和重新导入数据的时间,假如数据量很大的话,要好几个小时才气完成。这个时间可以先把index删撤消。导入后再建index。
4、外键关联的删除
假如表的有外键的话,每次操纵都没去check外键整合性。因此比较慢。数据导入后再创建外键也是一种选择。
d) 修改参数:

先容几个告急的
1、增加maintenance_work_mem参数大小
增加这个参数可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的执行效率。
2、增加checkpoint_segments参数的大小
增加这个参数可以提升大量数据导入时间的速率。
3、设置archive_mode无效
这个参数设置为无效的时间,可以大概提升以下的操纵的速率
?CREATE TABLE AS SELECT
?CREATE INDEX
?ALTER TABLE SET TABLESPACE
?CLUSTER等。
4、autovacuum相关参数
autovacuum:默认为on,表示是否开起autovacuum。默认开起。特殊的,当必要冻结xid时,只管此值为off,PG也会进行vacuum。
autovacuum_naptime:下一次vacuum的时间,默认1min。 这个naptime会被vacuum launcher分配到每个DB上。autovacuum_naptime/num of db。
log_autovacuum_min_duration:记录autovacuum动作到日志文件,当vacuum动作凌驾此值时。 “-1”表示不记录。“0”表示每次都记录。
autovacuum_max_workers:最大同时运行的worker数量,不包罗launcher本身。
autovacuum_work_mem :每个worker可使用的最大内存数。
autovacuum_vacuum_threshold :默认50。与autovacuum_vacuum_scale_factor共同使用, autovacuum_vacuum_scale_factor默认值为20%。当update,delete的tuples数量凌驾autovacuum_vacuum_scale_factor *table_size+autovacuum_vacuum_threshold时,进行vacuum。假如要使vacuum工作勤劳点,则将此值改小。
autovacuum_analyze_threshold :默认50。与autovacuum_analyze_scale_factor共同使用。
autovacuum_analyze_scale_factor :默认10%。当update,insert,delete的tuples数量凌驾autovacuum_analyze_scale_factor *table_size+autovacuum_analyze_threshold时,进行analyze。
autovacuum_freeze_max_age:200 million。离下一次进行xid冻结的最大事务数。
autovacuum_multixact_freeze_max_age:400 million。离下一次进行xid冻结的最大事务数。
autovacuum_vacuum_cost_delay :假如为-1,取vacuum_cost_delay值。
autovacuum_vacuum_cost_limit :假如为-1,到vacuum_cost_limit的值,这个值是所有worker的累加值。
以上为个人经验,希望能给各人一个参考,也希望各人多多支持脚本之家。如有错误或未考虑完全的地方,望不吝赐教。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作