Postgresql排序与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];
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;
补充：PostgreSQL -- 性能优化的⼩⽅法
⼀、回收磁盘空间
在PostgreSQL中，使⽤delete和update语句删除或更新的数据⾏并没有被实际删除，⽽只是在旧版本
数据⾏的物理地址上将该⾏的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在⼀段时间之后该表所占⽤的空间将会变得很⼤，然⽽数据量却可能变化不⼤。要解决该问题，需要定期对数据变化频繁的数据表执⾏VACUUM操作。现在新版PostgreSQL是⾃动执⾏VACUUM的
使⽤VACUUM和VACUUM FULL命令回收磁盘空间
postgres=# vacuum arr_test;
postgres=# vacuum full arr_test;
创建测试数据：
postgres=# create table arr (id serial, value int, age int) #创建测试表
postgres=# insert into arr (value, age) select generate_series(1, 1000000) as value, (random()*(10^2))::integer; #插⼊100W测试数据
postgres=# select pg_relation_size('arr'); #查看表⼤⼩
pg_relation_size
-
-----------------
44285952
(1 row)
postgres=# delete from arr where id<300000; #删除299999条数据
DELETE 299999
postgres=# select pg_relation_size('arr'); #再次查看表⼤⼩，没有变化
pg_relation_size
------------------
44285952
(1 row)
postgres=# vacuum full arr; #vacuum表，再次查看表⼤⼩，明显变⼩了
VACUUM
postgres=# select pg_relation_size('arr');
pg_relation_size
------------------
30998528
(1 row)
postgres=# update arr set age=10000 where id>=300000 and id<600000; #更新30W条数据
UPDATE 300000
postgres=# select pg_relation_size('arr'); #查看表⼤⼩，明显再次增⼤
pg_relation_size
------------------
44285952
(1 row)
⼆、重建索引
在PostgreSQL中，为数据更新频繁的数据表定期重建索引(REINDEX INDEX)是⾮常有必要的。
对于B-Tree索引，只有那些已经完全清空的索引页才会得到重复使⽤，对于那些仅部分空间可⽤的索引页将不会得到重⽤，如果⼀个页⾯中⼤多数索引键值都被删除，只留下很少的⼀部分，那么该页将不会被释放并重⽤。
在这种极端的情况下，由于每个索引页⾯的利⽤率极低，⼀旦数据量显著增加，将会导致索引⽂件变得极为庞⼤，不仅降低了查询效率，⽽且还存在整个磁盘空间被完全填满的危险。
对于重建后的索引还存在另外⼀个性能上的优势，因为在新建⽴的索引上，逻辑上相互连接的页⾯在物理上往往也是连在⼀起的，这样可以提⾼磁盘页⾯被连续读取的⼏率，从⽽提⾼整个操作的IO效率
postgres=# REINDEX INDEX testtable_idx;
三、重新收集统计信息
PostgreSQL查询规划器在选择最优路径时，需要参照相关数据表的统计信息⽤以为查询⽣成最合理的规划。这些统计是通过ANALYZE命令获得的，你可以直接调⽤该命令，或者把它当做VACUUM命令⾥的⼀个可选步骤来调⽤，如VACUUM ANAYLYZE table_name，该命令将会先执⾏VACUUM再执⾏ANALYZE。与回收空间(VACUUM)⼀样，对数据更新频繁的表保持⼀定频度的ANALYZE，从⽽使该表的统计信息始终处于相对较新的状态，这样对于基于该表的查询优化将是极为有利的。然⽽对于更新并不频繁的数据表，则不需要执⾏该操作。
我们可以为特定的表，甚⾄是表中特定的字段运⾏ANALYZE命令，这样我们就可以根据实际情况，只对更新⽐较频繁的部分信息执⾏ANALYZE操作，这样不仅可以节省统计信息所占⽤的空间，也可以提⾼本次ANALYZE操作的执⾏效率。
这⾥需要额外说明的是，ANALYZE是⼀项相当快的操作，即使是在数据量较⼤的表上也是如此，因为它使⽤了统计学上的随机采样的⽅法进⾏⾏采样，⽽不是把每⼀⾏数据都读取进来并进⾏分析。因此，可以考虑定期对整个数据库执⾏该命令。
事实上，我们甚⾄可以通过下⾯的命令来调整指定字段的抽样率
如：
ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200
注意：该值的取值范围是0--1000，其中值越低采样⽐例就越低，分析结果的准确性也就越低，但是ANALYZE命令执⾏的速度却更快。如果将该值设置为-1，那么该字段的采样⽐率将恢复到系统当前默认的采样值，我们可以通过下⾯的命令获取当前系统的缺省采样值。
postgres=# show default_statistics_target;
default_statistics_target
---------------------------
100
(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_ate_date, ‘yyyyMMdd')是没⽤的
4. 表字段之间关联，尽量给相关字段上添加索引。
5. 复合索引，遵从最左前缀的原则，即最左优先。（单独右侧字段查询没有索引的）
三、连接查询⽅式：insert语句字段顺序
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的相关的⼀部分统计信息。
SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';
relname | relkind | reltuples | relpages
----------------------+---------+-----------+----------
tenk1 | r | 10000 | 358
tenk1_hundred | i | 10000 | 30
tenk1_thous_tenthous | i | 10000 | 30
tenk1_unique1 | i | 10000 | 30
tenk1_unique2 | i | 10000 | 30
(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 f = c.id;
如果明确⽤join的话，执⾏时候执⾏计划相对容易控制⼀些。
例⼦：
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id f = c.id;
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的累加值。
以上为个⼈经验，希望能给⼤家⼀个参考，也希望⼤家多多⽀持。如有错误或未考虑完全的地⽅，望不吝赐教。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。