PostgreSQL資料庫效能調優的注意點以及pg資料庫效能優化方式

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已經加了索引，其次確保索參照到了。

一、排序

• 儘量避免
• 排序的資料量儘量少，並保證在記憶體裡完成排序。

（至於具體什麼資料量能在記憶體中完成排序，不同資料庫有不同的設定：oracle是sort_area_size；postgresql是work_mem (integer)，單位是KB，預設值是4MB。mysql是sort_buffer_size 注意：該引數對應的分配記憶體是每連線獨佔！）

二、索引

• 過濾的資料量比較少，一般來說<20%,應該走索引。20%-40% 可能走索引也可能不走索引。> 40% ，基本不走索引(會全表掃描)
• 保證值的資料型別和欄位資料型別要一直。
• 對索引的欄位進行計算時，必須在運運算元右側進行計算。也就是 to_char(oc.create_date, ‘yyyyMMdd’)是沒用的
• 表欄位之間關聯，儘量給相關欄位上新增索引。
• 複合索引，遵從最左字首的原則,即最左優先。（單獨右側欄位查詢沒有索引的）

三、連線查詢方式

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提供了一些效能調優的功能

主要有如下幾個方面。

1.使用EXPLAIN

EXPLAIN命令可以檢視執行計劃，這個方法是我們最主要的偵錯工具。

2.及時更新執行計劃中使用的統計資訊

由於統計資訊不是每次運算元據庫都進行更新的，一般是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL執行的時候會更新統計資訊，

因此執行計劃所用的統計資訊很有可能比較舊。 這樣執行計劃的分析結果可能誤差會變大。

以下是表tenk1的相關的一部分統計資訊。

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%';

(5 rows)

其中 relkind是型別，r是自身表，i是索引index；reltuples是專案數；relpages是所佔硬碟的塊數。

3.明確用join來關聯表

一般寫法：

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

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

例子：

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

4.關閉自動提交

（autocommit=false）

5.多次插入資料用copy命令更高效

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

6.臨時刪除index

有時候我們在備份和重新匯入資料的時候，如果資料量很大的話，要很幾個小時才能完成。這個時候可以先把index刪除掉。匯入在建index。

7.外來鍵關聯的刪除

如果表的有外來鍵的話，每次操作都沒去check外來鍵整合性。因此比較慢。資料匯入後在建立外來鍵也是一種選擇。

8.增加maintenance_work_mem引數大小

增加這個引數可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的執行效率。

9.增加checkpoint_segments引數的大小

增加這個引數可以提升大量資料匯入時候的速度。

10.設定archive_mode無效

這個引數設定為無效的時候，能夠提升以下的操作的速度

• CREATE TABLE AS SELECT
• CREATE INDEX
• ALTER TABLE SET TABLESPACE
• CLUSTER等。

11.最後執行VACUUM ANALYZE

表中資料大量變化的時候建議執行VACUUM ANALYZE。

對生產執行的資料庫要用定時任務crontb執行如下操作：

psql -U username -d databasename -c "vacuum verbose analyze tablename;"

PostgreSQL 引數設定

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的累加值。

pg中效能相關常調引數

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。