1 構(gòu)造測試數(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索引

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

這種情況是很常見的，一般的btree索引可以cluster來重組數(shù)據(jù)，但是gin索引是不支持cluster的，一般的gin索引列都是數(shù)組類型。所以當出現(xiàn)數(shù)據(jù)非常分散的情況時，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

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

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

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

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

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

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

GIN索引為PostgreSQL數(shù)據(jù)庫多值類型的倒排索引，一條記錄可能涉及到多個GIN索引中的KEY，所以如果寫入時實時合并索引，會導致IO急劇增加，寫入RT必然增加。為了提高寫入吞吐，PG允許用戶開啟GIN索引的延遲合并技術，開啟后，數(shù)據(jù)會先寫入pending list，并不是直接寫入索引頁，當pending list達到一定大小，或者autovacuum 對應表時，會觸發(fā)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

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

一、回收磁盤空間

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

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

postgres=# vacuum arr_test;

postgres=# vacuum full arr_test;

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

postgres=# create table arr (id serial, value int, age int) #創(chuàng)建測試表
postgres=# insert into arr (value, age) select generate_series(1, 1000000) as value, (random()*(10^2))::integer; #插入100W測試數(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)是非常有必要的。

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

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

對于重建后的索引還存在另外一個性能上的優(yōu)勢，因為在新建立的索引上，邏輯上相互連接的頁面在物理上往往也是連在一起的，這樣可以提高磁盤頁面被連續(xù)讀取的幾率，從而提高整個操作的IO效率

postgres=# REINDEX INDEX testtable_idx;

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

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

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

這里需要額外說明的是，ANALYZE是一項相當快的操作，即使是在數(shù)據(jù)量較大的表上也是如此，因為它使用了統(tǒng)計學上的隨機采樣的方法進行行采樣，而不是把每一行數(shù)據(jù)都讀取進來并進行分析。因此，可以考慮定期對整個數(shù)據(jù)庫執(zhí)行該命令。

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

如：

ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200

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

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

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

postgresql 性能優(yōu)化

一、排序:

1. 盡量避免

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

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

oracle是sort_area_size；

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

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

）

二、索引：

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

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

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

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

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

三、連接查詢方式：

1、hash join

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

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

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

2、nest loop

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

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

結(jié)果集一：1000

結(jié)果集二：1000000

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

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

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

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

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

優(yōu)化思路：

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

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

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

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

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

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

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

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

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

優(yōu)化方案：

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

1、使用EXPLAIN

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

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

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

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

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

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

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

默認情況下， seq_page_cost是1.0，cpu_tuple_cost是0.01。

3、使用臨時表（with）

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

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

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

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

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

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

例子：

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、關閉自動提交（autocommit=false）

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

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

我們有的處理中要對同一張表執(zhí)行很多次insert操作。這個時候我們用copy命令更有效率。因為insert一次，其相關的index都要做一次，比較花費時間。

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

有時候我們在備份和重新導入數(shù)據(jù)的時候，如果數(shù)據(jù)量很大的話，要好幾個小時才能完成。這個時候可以先把index刪除掉。導入后再建index。

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

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

d) 修改參數(shù)：

介紹幾個重要的

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

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

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

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

3、設置archive_mode無效

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

?CREATE TABLE AS SELECT

?CREATE INDEX

?ALTER TABLE SET TABLESPACE

?CLUSTER等。

4、autovacuum相關參數(shù)

autovacuum：默認為on，表示是否開起autovacuum。默認開起。特別的，當需要凍結(jié)xid時，盡管此值為off，PG也會進行vacuum。

autovacuum_naptime：下一次vacuum的時間，默認1min。 這個naptime會被vacuum launcher分配到每個DB上。autovacuum_naptime/num of db。

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

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

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

autovacuum_vacuum_threshold ：默認50。與autovacuum_vacuum_scale_factor配合使用， autovacuum_vacuum_scale_factor默認值為20%。當update,delete的tuples數(shù)量超過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數(shù)量超過autovacuum_analyze_scale_factor *table_size+autovacuum_analyze_threshold時，進行analyze。

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

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

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

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

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