Postgresql排序與limit組合場(chǎng)景性能極限優(yōu)化詳解

1 構(gòu)造測(cè)試數(shù)據(jù)

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索引

測(cè)試場(chǎng)景的限制GIN索引查詢速度是很快的， 在實(shí)際生產(chǎn)中，可能出現(xiàn)使用gin索引后，查詢速度依然很高的情況，特點(diǎn)就是執(zhí)行計(jì)劃中Bitmap Heap Scan占用了大量時(shí)間，Bitmap Index Scan大部分標(biāo)記的塊都被過濾掉了。

這種情況是很常見的，一般的btree索引可以cluster來重組數(shù)據(jù)，但是gin索引是不支持cluster的，一般的gin索引列都是數(shù)組類型。所以當(dāng)出現(xiàn)數(shù)據(jù)非常分散的情況時(shí)，bitmap index scan會(huì)標(biāo)記大量的塊，后面recheck的成本非常高，導(dǎo)致gin索引查詢慢。

我們接著來看這個(gè)例子

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

可以看到當(dāng)前執(zhí)行計(jì)劃是依賴gin索引掃描的，但gin索引出現(xiàn)性能問題時(shí)我們?nèi)绾蝸韮?yōu)化呢？

3 排序limit組合場(chǎng)景優(yōu)化

SQL中的排序與limit組合是一個(gè)很典型的索引優(yōu)化創(chuàng)景。我們知道btree索引在內(nèi)存中是有序的，通過遍歷btree索引可以直接拿到sort后的結(jié)果，這里組合使用limit后，只需要遍歷btree的一部分節(jié)點(diǎn)然后按照其他條件recheck就ok了。

我們來看一下優(yōu)化方法：

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)

本例的測(cè)試場(chǎng)景構(gòu)造可能沒有最大程度的體現(xiàn)問題，不過可以看出cluster后走btree索引可以很穩(wěn)定的達(dá)到34ms左右。

在gin性能存在問題的時(shí)候，這類limit + order by的SQL語(yǔ)句不妨常識(shí)強(qiáng)制（pg_hint_plan）走一下btree索引，可能有意想不到的效果。

4 高并發(fā)場(chǎng)景下的gin索引查詢性能下降

GIN索引為PostgreSQL數(shù)據(jù)庫(kù)多值類型的倒排索引，一條記錄可能涉及到多個(gè)GIN索引中的KEY，所以如果寫入時(shí)實(shí)時(shí)合并索引，會(huì)導(dǎo)致IO急劇增加，寫入RT必然增加。為了提高寫入吞吐，PG允許用戶開啟GIN索引的延遲合并技術(shù)，開啟后，數(shù)據(jù)會(huì)先寫入pending list，并不是直接寫入索引頁(yè)，當(dāng)pending list達(dá)到一定大小，或者autovacuum 對(duì)應(yīng)表時(shí)，會(huì)觸發(fā)pending list合并到索引的動(dòng)作。

查詢時(shí)，如果有未合并到索引中的PENDING LIST，那么會(huì)查詢pending list，同時(shí)查詢索引也的信息。

如果寫入量很多，pending list非常巨大，合并(autovacuum worker做的)速度跟不上時(shí)，會(huì)導(dǎo)致通過GIN索引查詢時(shí)查詢性能下降。

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

-- 如果很多條記錄在pending list中，查詢性能會(huì)下降明顯。
-- vacuum table，強(qiáng)制合并pending list
vacuum tbl; 

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

補(bǔ)充：PostgreSQL -- 性能優(yōu)化的小方法

一、回收磁盤空間

在PostgreSQL中，使用delete和update語(yǔ)句刪除或更新的數(shù)據(jù)行并沒有被實(shí)際刪除，而只是在舊版本數(shù)據(jù)行的物理地址上將該行的狀態(tài)置為已刪除或已過期。因此當(dāng)數(shù)據(jù)表中的數(shù)據(jù)變化極為頻繁時(shí)，那么在一段時(shí)間之后該表所占用的空間將會(huì)變得很大，然而數(shù)據(jù)量卻可能變化不大。要解決該問題，需要定期對(duì)數(shù)據(jù)變化頻繁的數(shù)據(jù)表執(zhí)行VACUUM操作?，F(xiàn)在新版PostgreSQL是自動(dòng)執(zhí)行VACUUM的

使用VACUUM和VACUUM FULL命令回收磁盤空間

postgres=# vacuum arr_test;

postgres=# vacuum full arr_test;

創(chuàng)建測(cè)試數(shù)據(jù)：

postgres=# create table arr (id serial, value int, age int) #創(chuàng)建測(cè)試表
postgres=# insert into arr (value, age) select generate_series(1, 1000000) as value, (random()*(10^2))::integer; #插入100W測(cè)試數(shù)據(jù)
postgres=# select pg_relation_size('arr'); #查看表大小
 pg_relation_size 
------------------
   44285952
(1 row)
postgres=# delete from arr where id300000; #刪除299999條數(shù)據(jù)
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 id600000; #更新30W條數(shù)據(jù)
UPDATE 300000
postgres=# select pg_relation_size('arr'); #查看表大小，明顯再次增大
 pg_relation_size 
------------------
   44285952
(1 row)

二、重建索引

在PostgreSQL中，為數(shù)據(jù)更新頻繁的數(shù)據(jù)表定期重建索引(REINDEX INDEX)是非常有必要的。

對(duì)于B-Tree索引，只有那些已經(jīng)完全清空的索引頁(yè)才會(huì)得到重復(fù)使用，對(duì)于那些僅部分空間可用的索引頁(yè)將不會(huì)得到重用，如果一個(gè)頁(yè)面中大多數(shù)索引鍵值都被刪除，只留下很少的一部分，那么該頁(yè)將不會(huì)被釋放并重用。

在這種極端的情況下，由于每個(gè)索引頁(yè)面的利用率極低，一旦數(shù)據(jù)量顯著增加，將會(huì)導(dǎo)致索引文件變得極為龐大，不僅降低了查詢效率，而且還存在整個(gè)磁盤空間被完全填滿的危險(xiǎn)。

對(duì)于重建后的索引還存在另外一個(gè)性能上的優(yōu)勢(shì)，因?yàn)樵谛陆⒌乃饕?，邏輯上相互連接的頁(yè)面在物理上往往也是連在一起的，這樣可以提高磁盤頁(yè)面被連續(xù)讀取的幾率，從而提高整個(gè)操作的IO效率

postgres=# REINDEX INDEX testtable_idx;

三、重新收集統(tǒng)計(jì)信息

PostgreSQL查詢規(guī)劃器在選擇最優(yōu)路徑時(shí)，需要參照相關(guān)數(shù)據(jù)表的統(tǒng)計(jì)信息用以為查詢生成最合理的規(guī)劃。這些統(tǒng)計(jì)是通過ANALYZE命令獲得的，你可以直接調(diào)用該命令，或者把它當(dāng)做VACUUM命令里的一個(gè)可選步驟來調(diào)用，如VACUUM ANAYLYZE table_name，該命令將會(huì)先執(zhí)行VACUUM再執(zhí)行ANALYZE。與回收空間(VACUUM)一樣，對(duì)數(shù)據(jù)更新頻繁的表保持一定頻度的ANALYZE，從而使該表的統(tǒng)計(jì)信息始終處于相對(duì)較新的狀態(tài)，這樣對(duì)于基于該表的查詢優(yōu)化將是極為有利的。然而對(duì)于更新并不頻繁的數(shù)據(jù)表，則不需要執(zhí)行該操作。

我們可以為特定的表，甚至是表中特定的字段運(yùn)行ANALYZE命令，這樣我們就可以根據(jù)實(shí)際情況，只對(duì)更新比較頻繁的部分信息執(zhí)行ANALYZE操作，這樣不僅可以節(jié)省統(tǒng)計(jì)信息所占用的空間，也可以提高本次ANALYZE操作的執(zhí)行效率。

這里需要額外說明的是，ANALYZE是一項(xiàng)相當(dāng)快的操作，即使是在數(shù)據(jù)量較大的表上也是如此，因?yàn)樗褂昧私y(tǒng)計(jì)學(xué)上的隨機(jī)采樣的方法進(jìn)行行采樣，而不是把每一行數(shù)據(jù)都讀取進(jìn)來并進(jìn)行分析。因此，可以考慮定期對(duì)整個(gè)數(shù)據(jù)庫(kù)執(zhí)行該命令。

事實(shí)上，我們甚至可以通過下面的命令來調(diào)整指定字段的抽樣率

如：

ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200

注意：該值的取值范圍是0--1000，其中值越低采樣比例就越低，分析結(jié)果的準(zhǔn)確性也就越低，但是ANALYZE命令執(zhí)行的速度卻更快。如果將該值設(shè)置為-1，那么該字段的采樣比率將恢復(fù)到系統(tǒng)當(dāng)前默認(rèn)的采樣值，我們可以通過下面的命令獲取當(dāng)前系統(tǒng)的缺省采樣值。

postgres=# show default_statistics_target;
  default_statistics_target
 ---------------------------
  100
 (1 row)

從上面的結(jié)果可以看出，該數(shù)據(jù)庫(kù)的缺省采樣值為100(10%)。

postgresql 性能優(yōu)化

一、排序:

1. 盡量避免

2. 排序的數(shù)據(jù)量盡量少，并保證在內(nèi)存里完成排序。

（至于具體什么數(shù)據(jù)量能在內(nèi)存中完成排序，不同數(shù)據(jù)庫(kù)有不同的配置：

oracle是sort_area_size；

postgresql是work_mem (integer)，單位是KB，默認(rèn)值是4MB。

mysql是sort_buffer_size 注意：該參數(shù)對(duì)應(yīng)的分配內(nèi)存是每連接獨(dú)占！

）

二、索引：

1. 過濾的數(shù)據(jù)量比較少，一般來說20%,應(yīng)該走索引。20%-40% 可能走索引也可能不走索引。> 40% ，基本不走索引(會(huì)全表掃描)

2. 保證值的數(shù)據(jù)類型和字段數(shù)據(jù)類型要一致。

3. 對(duì)索引的字段進(jìn)行計(jì)算時(shí)，必須在運(yùn)算符右側(cè)進(jìn)行計(jì)算。也就是 to_char(oc.create_date, ‘yyyyMMdd')是沒用的

4. 表字段之間關(guān)聯(lián)，盡量給相關(guān)字段上添加索引。

5. 復(fù)合索引，遵從最左前綴的原則,即最左優(yōu)先。（單獨(dú)右側(cè)字段查詢沒有索引的）

三、連接查詢方式：

1、hash join

放內(nèi)存里進(jìn)行關(guān)聯(lián)。

適用于結(jié)果集比較大的情況。

比如都是200000數(shù)據(jù)

2、nest loop

從結(jié)果1 逐行取出，然后與結(jié)果集2進(jìn)行匹配。

適用于兩個(gè)結(jié)果集，其中一個(gè)數(shù)據(jù)量遠(yuǎn)大于另外一個(gè)時(shí)。

結(jié)果集一：1000

結(jié)果集二：1000000

四、多表聯(lián)查時(shí)：

在多表聯(lián)查時(shí)，需要考慮連接順序問題。

1、當(dāng)postgresql中進(jìn)行查詢時(shí)，如果多表是通過逗號(hào)，而不是join連接，那么連接順序是多表的笛卡爾積中取最優(yōu)的。如果有太多輸入的表， PostgreSQL規(guī)劃器將從窮舉搜索切換為基因概率搜索，以減少可能性數(shù)目(樣本空間)。基因搜索花的時(shí)間少， 但是并不一定能找到最好的規(guī)劃。

2、對(duì)于JOIN，LEFT JOIN / RIGHT JOIN 會(huì)一定程度上指定連接順序，但是還是會(huì)在某種程度上重新排列：FULL JOIN 完全強(qiáng)制連接順序。如果要強(qiáng)制規(guī)劃器遵循準(zhǔn)確的JOIN連接順序，我們可以把運(yùn)行時(shí)參數(shù)join_collapse_limit設(shè)置為 1

五、PostgreSQL提供了一些性能調(diào)優(yōu)的功能：

優(yōu)化思路：

0、為每個(gè)表執(zhí)行 ANALYZE

。然后分析 EXPLAIN (ANALYZE，BUFFERS) sql。

1、對(duì)于多表查詢，查看每張表數(shù)據(jù)，然后改進(jìn)連接順序。

2、先查找那部分是重點(diǎn)語(yǔ)句，比如上面SQL，外面的嵌套層對(duì)于優(yōu)化來說沒有意義，可以去掉。

3、查看語(yǔ)句中，where等條件子句，每個(gè)字段能過濾的效率。找出可優(yōu)化處。

比如oc.order_id = oo.order_id是關(guān)聯(lián)條件，需要加索引

oc.op_type = 3 能過濾出1/20的數(shù)據(jù)，

oo.event_type IN (…) 能過濾出1/10的數(shù)據(jù)，

這兩個(gè)是優(yōu)化的重點(diǎn)，也就是實(shí)現(xiàn)確保op_type與event_type已經(jīng)加了索引，其次確保索引用到了。

優(yōu)化方案：

a) 整體優(yōu)化：

1、使用EXPLAIN

EXPLAIN命令可以查看執(zhí)行計(jì)劃，這個(gè)方法是我們最主要的調(diào)試工具。

2、及時(shí)更新執(zhí)行計(jì)劃中使用的統(tǒng)計(jì)信息

由于統(tǒng)計(jì)信息不是每次操作數(shù)據(jù)庫(kù)都進(jìn)行更新的，一般是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL執(zhí)行的時(shí)候會(huì)更新統(tǒng)計(jì)信息，

因此執(zhí)行計(jì)劃所用的統(tǒng)計(jì)信息很有可能比較舊。 這樣執(zhí)行計(jì)劃的分析結(jié)果可能誤差會(huì)變大。

以下是表tenk1的相關(guān)的一部分統(tǒng)計(jì)信息。

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是項(xiàng)目數(shù)；relpages是所占硬盤的塊數(shù)。

估計(jì)成本通過 （磁盤頁(yè)面讀取【relpages】*seq_page_cost）+（行掃描【reltuples】*cpu_tuple_cost）計(jì)算。

默認(rèn)情況下， seq_page_cost是1.0，cpu_tuple_cost是0.01。

3、使用臨時(shí)表（with）

對(duì)于數(shù)據(jù)量大，且無(wú)法有效優(yōu)化時(shí)，可以使用臨時(shí)表來過濾數(shù)據(jù)，降低數(shù)據(jù)數(shù)量級(jí)。

4、對(duì)于會(huì)影響結(jié)果的分析，可以使用 begin;…rollback;來回滾。

b) 查詢優(yōu)化：

1、明確用join來關(guān)聯(lián)表，確保連接順序

一般寫法：SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

如果明確用join的話，執(zhí)行時(shí)候執(zhí)行計(jì)劃相對(duì)容易控制一些。

例子：

SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;

SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

c) 插入更新優(yōu)化

1、關(guān)閉自動(dòng)提交（autocommit=false）

如果有多條數(shù)據(jù)庫(kù)插入或更新等，最好關(guān)閉自動(dòng)提交，這樣能提高效率

2、多次插入數(shù)據(jù)用copy命令更高效

我們有的處理中要對(duì)同一張表執(zhí)行很多次insert操作。這個(gè)時(shí)候我們用copy命令更有效率。因?yàn)閕nsert一次，其相關(guān)的index都要做一次，比較花費(fèi)時(shí)間。

3、臨時(shí)刪除index【具體可以查看Navicat表數(shù)據(jù)生成sql的語(yǔ)句，就是先刪再建的】

有時(shí)候我們?cè)趥浞莺椭匦聦?dǎo)入數(shù)據(jù)的時(shí)候，如果數(shù)據(jù)量很大的話，要好幾個(gè)小時(shí)才能完成。這個(gè)時(shí)候可以先把index刪除掉。導(dǎo)入后再建index。

4、外鍵關(guān)聯(lián)的刪除

如果表的有外鍵的話，每次操作都沒去check外鍵整合性。因此比較慢。數(shù)據(jù)導(dǎo)入后再建立外鍵也是一種選擇。

d) 修改參數(shù)：

介紹幾個(gè)重要的

1、增加maintenance_work_mem參數(shù)大小

增加這個(gè)參數(shù)可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的執(zhí)行效率。

2、增加checkpoint_segments參數(shù)的大小

增加這個(gè)參數(shù)可以提升大量數(shù)據(jù)導(dǎo)入時(shí)候的速度。

3、設(shè)置archive_mode無(wú)效

這個(gè)參數(shù)設(shè)置為無(wú)效的時(shí)候，能夠提升以下的操作的速度

?CREATE TABLE AS SELECT

?CREATE INDEX

?ALTER TABLE SET TABLESPACE

?CLUSTER等。

4、autovacuum相關(guān)參數(shù)

autovacuum：默認(rèn)為on，表示是否開起autovacuum。默認(rèn)開起。特別的，當(dāng)需要凍結(jié)xid時(shí)，盡管此值為off，PG也會(huì)進(jìn)行vacuum。

autovacuum_naptime：下一次vacuum的時(shí)間，默認(rèn)1min。 這個(gè)naptime會(huì)被vacuum launcher分配到每個(gè)DB上。autovacuum_naptime/num of db。

log_autovacuum_min_duration：記錄autovacuum動(dòng)作到日志文件，當(dāng)vacuum動(dòng)作超過此值時(shí)。 “-1”表示不記錄?！?”表示每次都記錄。

autovacuum_max_workers：最大同時(shí)運(yùn)行的worker數(shù)量，不包含launcher本身。

autovacuum_work_mem ：每個(gè)worker可使用的最大內(nèi)存數(shù)。

autovacuum_vacuum_threshold ：默認(rèn)50。與autovacuum_vacuum_scale_factor配合使用， autovacuum_vacuum_scale_factor默認(rèn)值為20%。當(dāng)update,delete的tuples數(shù)量超過autovacuum_vacuum_scale_factor *table_size+autovacuum_vacuum_threshold時(shí)，進(jìn)行vacuum。如果要使vacuum工作勤奮點(diǎn)，則將此值改小。

autovacuum_analyze_threshold ：默認(rèn)50。與autovacuum_analyze_scale_factor配合使用。

autovacuum_analyze_scale_factor ：默認(rèn)10%。當(dāng)update,insert,delete的tuples數(shù)量超過autovacuum_analyze_scale_factor *table_size+autovacuum_analyze_threshold時(shí)，進(jìn)行analyze。

autovacuum_freeze_max_age：200 million。離下一次進(jìn)行xid凍結(jié)的最大事務(wù)數(shù)。

autovacuum_multixact_freeze_max_age：400 million。離下一次進(jìn)行xid凍結(jié)的最大事務(wù)數(shù)。

autovacuum_vacuum_cost_delay ：如果為-1，取vacuum_cost_delay值。

autovacuum_vacuum_cost_limit ：如果為-1，到vacuum_cost_limit的值，這個(gè)值是所有worker的累加值。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。