219
技術社區[雲棲]
PostgreSQL SQL 語言：性能提示
本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。
PostgreSQL為每個收到查詢產生一個查詢計劃。 選擇正確的計劃來匹配查詢結構和數據的屬性對於好的性能來說絕對是最關鍵的，因此係統包含了一個複雜的規劃器來嚐試選擇好的計劃。 你可以使用EXPLAIN命令察看規劃器為任何查詢生成的查詢計劃。 閱讀查詢計劃是一門藝術，它要求一些經驗來掌握，但是本節隻試圖覆蓋一些基礎。
本節中的例子都是從 9.3 開發源代碼的回歸測試數據庫中抽取出來的，並且在此之前做過一次VACUUM ANALYZE。你應該能夠在自己嚐試這些例子時得到相似的結果，但是你的估計代價和行計數可能會小幅變化，因為ANALYZE的統計信息是隨機采樣而不是精確值，並且代價也與平台有某種程度的相關性。
這些例子使用EXPLAIN的默認"text"輸出格式，這種格式緊湊並且便於人類閱讀。如果你想把EXPLAIN的輸出交給一個程序做進一步分析，你應該使用它的某種機器可讀的輸出格式（XML、JSON 或 YAML）。
1.1. EXPLAIN基礎
查詢計劃的結構是一個計劃結點的樹。最底層的結點是掃描結點：它們從表中返回未經處理的行。 不同的表訪問模式有不同的掃描結點類型：順序掃描、索引掃描、位圖索引掃描。 也還有不是表的行來源，例如VALUES子句和FROM中返回集合的函數，它們有自己的結點類型。如果查詢需要連接、聚集、排序、或者在未經處理的行上的其它操作，那麼就會在掃描結點之上有其它額外的結點來執行這些操作。 並且，做這些操作通常都有多種方法，因此在這些位置也有可能出現不同的結點類型。 EXPLAIN給計劃樹中每個結點都輸出一行，顯示基本的結點類型和計劃器為該計劃結點的執行所做的開銷估計。 第一行（最上層的結點）是對該計劃的總執行開銷的估計；計劃器試圖最小化的就是這個數字。
這裏是一個簡單的例子，隻是用來顯示輸出看起來是什麼樣的：
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
由於這個查詢沒有WHERE子句，它必須掃描表中的所有行，因此計劃器隻能選擇使用一個簡單的順序掃描計劃。被包含在圓括號中的數字是（從左至右）：
- 估計的啟動開銷。在輸出階段可以開始之前消耗的時間，例如在一個排序結點裏執行排序的時間。
- 估計的總開銷。這個估計值基於的假設是計劃結點會被運行到完成，即所有可用的行都被檢索。不過實際上一個結點的父結點可能很快停止讀所有可用的行（見下麵的LIMIT例子）。
- 這個計劃結點輸出行數的估計值。同樣，也假定該結點能運行到完成。
- 預計這個計劃結點輸出的行平均寬度（以字節計算）。
開銷是用規劃器的開銷參數所決定的捏造單位來衡量的。傳統上以取磁盤頁麵為單位來度量開銷； 也就是seq_page_cost將被按照習慣設為1.0，其它開銷參數將相對於它來設置。 本節的例子都假定這些參數使用默認值。
有一點很重要：一個上層結點的開銷包括它的所有子結點的開銷。還有一點也很重要：這個開銷隻反映規劃器關心的東西。特別是這個開銷沒有考慮結果行傳遞給客戶端所花費的時間，這個時間可能是實際花費時間中的一個重要因素；但是它被規劃器忽略了，因為它無法通過修改計劃來改變（我們相信，每個正確的計劃都將輸出同樣的行集）。
行數值有一些小技巧，因為它不是計劃結點處理或掃描過的行數，而是該結點發出的行數。這通常比被掃描的行數少一些， 因為有些被掃描的行會被應用於此結點上的任意WHERE子句條件過濾掉。 理想中頂層的行估計會接近於查詢實際返回、更新、刪除的行數。
回到我們的例子：
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
這些數字的產生非常直接。如果你執行：
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
你會發現tenk1有358個磁盤頁麵和10000行。 開銷被計算為 （頁麵讀取數*seq_page_cost）+（掃描的行數*cpu_tuple_cost）。默認情況下，seq_page_cost是1.0，cpu_tuple_cost是0.01， 因此估計的開銷是 (358 * 1.0) + (10000 * 0.01) = 458。
現在讓我們修改查詢並增加一個WHERE條件：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7001 width=244)
Filter: (unique1 < 7000)
請注意EXPLAIN輸出顯示WHERE子句被當做一個"過濾器"條件附加到順序掃描計劃結點。 這意味著該計劃結點為它掃描的每一行檢查該條件，並且隻輸出通過該條件的行。因為WHERE子句的存在，估計的輸出行數降低了。不過，掃描仍將必須訪問所有 10000 行，因此開銷沒有被降低；實際上開銷還有所上升（準確來說，上升了 10000 * cpu_operator_cost）以反映檢查WHERE條件所花費的額外 CPU 時間。
這條查詢實際選擇的行數是 7000，但是估計的行數隻是個近似值。如果你嚐試重複這個試驗，那麼你很可能得到略有不同的估計。 此外，這個估計會在每次ANALYZE命令之後改變， 因為ANALYZE生成的統計數據是從該表中隨機采樣計算的。
現在，讓我們把條件變得更嚴格：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=5.07..229.20 rows=101 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
這裏，規劃器決定使用一個兩步的計劃：子計劃結點訪問訪問一個索引來找出匹配索引條件的行的位置，然後上層計劃結點實際地從表中取出那些行。獨立地抓取行比順序地讀取它們的開銷高很多，但是不是所有的表頁麵都被訪問，這麼做實際上仍然比一次順序掃描開銷要少（使用兩層計劃的原因是因為上層規劃結點把索引標識出來的行位置在讀取之前按照物理位置排序，這樣可以最小化單獨抓取的開銷。結點名稱裏麵提到的"位圖"是執行該排序的機製）。
現在讓我們給WHERE子句增加另一個條件：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=5.04..229.43 rows=1 width=244)
Recheck Cond: (unique1 < 100)
Filter: (stringu1 = 'xxx'::name)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
新增的條件stringu1 = 'xxx'減少了估計的輸出行計數， 但是沒有減少開銷，因為我們仍然需要訪問相同的行集合。 請注意，stringu1子句不能被應用為一個索引條件，因為這個索引隻是在unique1列上。 它被用來過濾從索引中檢索出的行。因此開銷實際上略微增加了一些以反映這個額外的檢查。
在某些情況下規劃器將更傾向於一個"simple"索引掃描計劃：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: (unique1 = 42)
在這類計劃中，表行被按照索引順序取得，這使得讀取它們開銷更高，但是其中有一些是對行位置排序的額外開銷。你很多時候將在隻取得一個單一行的查詢中看到這種計劃類型。它也經常被用於擁有匹配索引順序的ORDER BY子句的查詢中，因為那樣就不需要額外的排序步驟來滿足ORDER BY。
如果在WHERE引用的多個行上有獨立的索引，規劃器可能會選擇使用這些索引的一個 AND 或 OR 組合：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.08..60.21 rows=10 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=25.08..25.08 rows=10 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0)
Index Cond: (unique2 > 9000)
但是這要求訪問兩個索引，所以與隻使用一個索引並把其他條件作為過濾器相比，它不一定能勝出。如果你變動涉及到的範圍，你將看到計劃也會相應改變。
下麵是一個例子，它展示了LIMIT的效果：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=0.29..14.48 rows=2 width=244)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..71.27 rows=10 width=244)
Index Cond: (unique2 > 9000)
Filter: (unique1 < 100)
這是和上麵相同的查詢，但是我們增加了一個LIMIT這樣不是所有的行都需要被檢索，並且規劃器改變了它的決定。注意索引掃描結點的總開銷和行計數顯示出好像它會被運行到完成。但是，限製結點在檢索到這些行的五分之一後就會停止，因此它的總開銷隻是索引掃描結點的五分之一，並且這是查詢的實際估計開銷。之所以用這個計劃而不是在之前的計劃上增加一個限製結點是因為限製無法避免在位圖掃描上花費啟動開銷，因此總開銷會是超過那種方法（25個單位）的某個值。
讓我們嚐試連接兩個表，使用我們已經討論過的列：
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.62 rows=10 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
Index Cond: (unique1 < 10)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244)
Index Cond: (unique2 = t1.unique2)
在這個計劃中，我們有一個嵌套循環連接結點，它有兩個表掃描作為輸入或子結點。該結點的摘要行的縮進反映了計劃樹的結構。連接的第一個（或"outer"）子結點是一個與前麵見到的相似的位圖掃描。它的開銷和行計數與我們從SELECT ... WHERE unique1 < 10得到的相同，因為我們將WHERE子句unique1 < 10用在了那個結點上。t1.unique2 = t2.unique2子句現在還不相關，因此它不影響 outer 掃描的行計數。嵌套循環連接結點將為從 outer 子結點得到的每一行運行它的第二個（或"inner"）子結點。當前 outer 行的列值可以被插入 inner 掃描。這裏，來自 outer 行的t1.unique2值是可用的，所以我們得到的計劃和開銷與前麵見到的簡單SELECT ... WHERE t2.unique2 = constant情況相似（估計的開銷實際上比前麵看到的略低，是因為在t2上的重複索引掃描會利用到高速緩存）。循環結點的開銷則被以 outer 掃描的開銷為基礎設置，外加對每一個 outer 行都要進行一次 inner 掃描 （10 * 7.87），再加上用於連接處理一點 CPU 時間。
在這個例子裏，連接的輸出行計數等於兩個掃描的行計數的乘積，但通常並不是所有的情況中都如此， 因為可能有同時提及兩個表的 額外WHERE子句，並且因此它隻能被應用於連接點，而不能影響任何一個輸入掃描。這裏是一個例子：
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;
QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop (cost=4.65..49.46 rows=33 width=488)
Join Filter: (t1.hundred < t2.hundred)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
Index Cond: (unique1 < 10)
-> Materialize (cost=0.29..8.51 rows=10 width=244)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244)
Index Cond: (unique2 < 10)
條件t1.hundred < t2.hundred不能在tenk2_unique2索引中被測試，因此它被應用在連接結點。這縮減了連接結點的估計輸出行計數，但是沒有改變任何輸入掃描。
注意這裏規劃器選擇了"物化"連接的 inner 關係，方法是在它的上方放了一個物化計劃結點。這意味著t2索引掃描將隻被做一次，即使嵌套循環連接結點需要讀取其數據十次（每個來自 outer 關係的行都要讀一次）。物化結點在讀取數據時將它保存在內存中，然後在每一次後續執行時從內存返回數據。
在處理外連接時，你可能會看到連接計劃結點同時附加有"連接過濾器"和普通"過濾器"條件。連接過濾器條件來自於外連接的ON子句，因此一個無法通過連接過濾器條件的行也能夠作為一個空值擴展的行被發出。但是一個普通過濾器條件被應用在外連接條件之後並且因此無條件移除行。在一個內連接中這兩種過濾器類型沒有語義區別。
如果我們把查詢的選擇度改變一點，我們可能得到一個非常不同的連接計劃：
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=230.47..713.98 rows=101 width=488)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
-> Hash (cost=229.20..229.20 rows=101 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
這裏規劃器選擇了使用一個哈希連接，在其中一個表的行被放入一個內存哈希表，在這之後其他表被掃描並且為每一行查找哈希表來尋找匹配。同樣要注意縮進是如何反映計劃結構的：tenk1上的位圖掃描是哈希結點的輸入，哈希結點會構造哈希表。然後哈希表會返回給哈希連接結點，哈希連接結點將從它的 outer 子計劃讀取行，並為每一個行搜索哈希表。
另一種可能的連接類型是一個歸並連接，如下所示：
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Merge Join (cost=198.11..268.19 rows=10 width=488)
Merge Cond: (t1.unique2 = t2.unique2)
-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)
Filter: (unique1 < 100)
-> Sort (cost=197.83..200.33 rows=1000 width=244)
Sort Key: t2.unique2
-> Seq Scan on onek t2 (cost=0.00..148.00 rows=1000 width=244)
歸並連接要求它的輸入數據被按照連接鍵排序。在這個計劃中，tenk1數據被使用一個索引掃描排序，以便能夠按照正確的順序來訪問行。但是對於onek則更傾向於一個順序掃描和排序，因為在那個表中有更多行需要被訪問（對於很多行的排序，順序掃描加排序常常比一個索引掃描好，因為索引掃描需要非順序的磁盤訪問）。
一種查看變體計劃的方法是強製規劃器丟棄它認為開銷最低的任何策略，這可以使用Section 19.7.1中描述的啟用/禁用標誌實現（這是一個野蠻的工具，但是很有用。另見Section 14.3）。例如，如果我們並不認同在前麵的例子中順序掃描加排序是處理表onek的最佳方法，我們可以嚐試：
SET enable_sort = off;
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Merge Join (cost=0.56..292.65 rows=10 width=488)
Merge Cond: (t1.unique2 = t2.unique2)
-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)
Filter: (unique1 < 100)
-> Index Scan using onek_unique2 on onek t2 (cost=0.28..224.79 rows=1000 width=244)
這顯示規劃器認為用索引掃描來排序onek的開銷要比用順序掃描加排序的方式高大約12%。當然，下一個問題是是否真的是這樣。我們可以通過使用EXPLAIN ANALYZE來仔細研究一下，如下文所述。
1.2. EXPLAIN ANALYZE
可以通過使用EXPLAIN的ANALYZE選項來檢查規劃器估計值的準確性。通過使用這個選項，EXPLAIN會實際執行該查詢，然後顯示真實的行計數和在每個計劃結點中累計的真實運行時間，還會有一個普通EXPLAIN顯示的估計值。例如，我們可能得到這樣一個結果：
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)
Index Cond: (unique1 < 10)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)
Index Cond: (unique2 = t1.unique2)
Planning time: 0.181 ms
Execution time: 0.501 ms
注意"actual time"值是以毫秒計的真實時間，而cost估計值被以捏造的單位表示，因此它們不大可能匹配上。在這裏麵要查看的最重要的一點是估計的行計數是否合理地接近實際值。在這個例子中，估計值都是完全正確的，但是在實際中非常少見。
在某些查詢計劃中，可以多次執行一個子計劃結點。例如，inner 索引掃描可能會因為上層嵌套循環計劃中的每一個 outer 行而被執行一次。在這種情況下，loops值報告了執行該結點的總次數，並且 actual time 和行數值是這些執行的平均值。這是為了讓這些數字能夠與開銷估計被顯示的方式有可比性。將這些值乘上loops值可以得到在該結點中實際消耗的總時間。在上麵的例子中，我們在執行tenk2的索引掃描上花費了總共 0.220 毫秒。
在某些情況中，EXPLAIN ANALYZE會顯示計劃結點執行時間和行計數之外的額外執行統計信息。例如，排序和哈希結點提供額外的信息：
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
Sort Key: t1.fivethous
Sort Method: quicksort Memory: 77kB
-> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
-> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
Index Cond: (unique1 < 100)
Planning time: 0.194 ms
Execution time: 8.008 ms
排序結點顯示使用的排序方法（尤其是，排序是在內存中還是磁盤上進行）和需要的內存或磁盤空間量。哈希結點顯示了哈希桶的數量和批數，以及被哈希表所使用的內存量的峰值（如果批數超過一，也將會涉及到磁盤空間使用，但是並沒有被顯示）。
另一種類型的額外信息是被一個過濾器條件移除的行數：
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)
Filter: (ten < 7)
Rows Removed by Filter: 3000
Planning time: 0.083 ms
Execution time: 5.905 ms
這些值對於被應用在連接結點上的過濾器條件特別有價值。隻有在至少有一個被掃描行或者在連接結點中一個可能的連接對被過濾器條件拒絕時，"Rows Removed"行才會出現。
一個與過濾器條件相似的情況出現在"有損"索引掃描中。例如，考慮這個查詢，它搜索包含一個指定點的多邊形：
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on polygon_tbl (cost=0.00..1.05 rows=1 width=32) (actual time=0.044..0.044 rows=0 loops=1)
Filter: (f1 @> '((0.5,2))'::polygon)
Rows Removed by Filter: 4
Planning time: 0.040 ms
Execution time: 0.083 ms
規劃器認為（非常正確）這個采樣表太小不值得勞煩一次索引掃描，因此我們得到了一個普通的順序掃描，其中的所有行都被過濾器條件拒絕。但是如果我們強製使得一次索引掃描可以被使用，我們看到：
SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=32) (actual time=0.062..0.062 rows=0 loops=1)
Index Cond: (f1 @> '((0.5,2))'::polygon)
Rows Removed by Index Recheck: 1
Planning time: 0.034 ms
Execution time: 0.144 ms
這裏我們可以看到索引返回一個候選行，然後它會被索引條件的重新檢查拒絕。這是因為一個 GiST 索引對於多邊形包含測試是 "有損的"：它確實返回覆蓋目標的多邊形的行，然後我們必須在那些行上做精確的包含性測試。
EXPLAIN有一個BUFFERS選項可以和ANALYZE一起使用來得到更多運行時統計信息：
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.08..60.21 rows=10 width=244) (actual time=0.323..0.342 rows=10 loops=1)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
Buffers: shared hit=15
-> BitmapAnd (cost=25.08..25.08 rows=10 width=0) (actual time=0.309..0.309 rows=0 loops=1)
Buffers: shared hit=7
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)
Index Cond: (unique1 < 100)
Buffers: shared hit=2
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.227..0.227 rows=999 loops=1)
Index Cond: (unique2 > 9000)
Buffers: shared hit=5
Planning time: 0.088 ms
Execution time: 0.423 ms
BUFFERS提供的數字幫助我們標識查詢的哪些部分是對 I/O 最敏感的。
記住因為EXPLAIN ANALYZE實際運行查詢，任何副作用都將照常發生，即使查詢可能輸出的任何結果被丟棄來支持打印EXPLAIN數據。如果你想要分析一個數據修改查詢而不想改變你的表，你可以在分析完後回滾命令，例如：
BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Update on tenk1 (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)
-> Bitmap Heap Scan on tenk1 (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)
Index Cond: (unique1 < 100)
Planning time: 0.079 ms
Execution time: 14.727 ms
ROLLBACK;
正如在這個例子中所看到的，當查詢是一個INSERT、UPDATE或DELETE命令時，應用表更改的實際工作由頂層插入、更新或刪除計劃結點完成。這個結點之下的計劃結點執行定位舊行以及/或者計算新數據的工作。因此在上麵，我們看到我們已經見過的位圖表掃描，它的輸出被交給一個更新結點，更新結點會存儲被更新過的行。還有一點值得注意的是，盡管數據修改結點可能要可觀的運行時間（這裏，它消耗最大份額的時間），規劃器當前並沒有對開銷估計增加任何東西來說明這些工作。這是因為這些工作對每一個正確的查詢計劃都得做，所以它不影響計劃的選擇。
當一個UPDATE或者DELETE命令影響繼承層次時， 輸出可能像這樣：
EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;
QUERY PLAN
-----------------------------------------------------------------------------------
Update on parent (cost=0.00..24.53 rows=4 width=14)
Update on parent
Update on child1
Update on child2
Update on child3
-> Seq Scan on parent (cost=0.00..0.00 rows=1 width=14)
Filter: (f1 = 101)
-> Index Scan using child1_f1_key on child1 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)
-> Index Scan using child2_f1_key on child2 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)
-> Index Scan using child3_f1_key on child3 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)
在這個例子中，更新節點需要考慮三個子表以及最初提到的父表。因此有四個輸入 的掃描子計劃，每一個對應於一個表。為清楚起見，在更新節點上標注了將被更新 的相關目標表，顯示的順序與相應的子計劃相同（這些標注是從 PostgreSQL 9.5 開始新增的，在以前的版本中讀者必須通過 觀察子計劃才能知道這些目標表）。
EXPLAIN ANALYZE顯示的 Planning time是從一個已解析的查詢生成查詢計劃並進行優化 所花費的時間，其中不包括解析和重寫。
EXPLAIN ANALYZE顯示的Execution time包括執行器的啟動和關閉時間，以及運行被觸發的任何觸發器的時間，但是它不包括解析、重寫或規劃的時間。如果有花在執行BEFORE執行器的時間，它將被包括在相關的插入、更新或刪除結點的時間內；但是用來執行AFTER 觸發器的時間沒有被計算，因為AFTER觸發器是在整個計劃完成後被觸發的。在每個觸發器（BEFORE或AFTER）也被獨立地顯示。注意延遲約束觸發器將不會被執行，直到事務結束，並且因此根本不會被EXPLAIN ANALYZE考慮。
1.3. 警告
在兩種有效的方法中EXPLAIN ANALYZE所度量的運行時間可能偏離同一個查詢的正常執行。首先，由於不會有輸出行被遞交給客戶端，網絡傳輸開銷和 I/O 轉換開銷沒有被包括在內。其次，由EXPLAIN ANALYZE所增加的度量符合可能會很可觀，特別是在那些gettimeofday()操作係統調用很慢的機器上。你可以使用pg_test_timing工具來度量在你的係統上的計時開銷。
EXPLAIN結果不應該被外推到與你實際測試的非常不同的情況。例如，一個很小的表上的結果不能被假定成適合大型表。規劃器的開銷估計不是線性的，並且因此它可能為一個更大或更小的表選擇一個不同的計劃。一個極端例子是，在一個隻占據一個磁盤頁麵的表上，你將幾乎總是得到一個順序掃描計劃，而不管索引是否可用。規劃器認識到它在任何情況下都將采用一次磁盤頁麵讀取來處理該表，因此用額外的頁麵讀取去查看一個索引是沒有價值的（我們已經在前麵的polygon_tbl例子中見過）。
在一些情況中，實際的值和估計的值不會匹配得很好，但是這並非錯誤。一種這樣的情況發生在計劃結點的執行被LIMIT或類似的效果很快停止。例如，在我們之前用過的LIMIT查詢中：
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..14.71 rows=2 width=244) (actual time=0.177..0.249 rows=2 loops=1)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..72.42 rows=10 width=244) (actual time=0.174..0.244 rows=2 loops=1)
Index Cond: (unique2 > 9000)
Filter: (unique1 < 100)
Rows Removed by Filter: 287
Planning time: 0.096 ms
Execution time: 0.336 ms
索引掃描結點的估計開銷和行計數被顯示成好像它會運行到完成。但是實際上限製結點在得到兩個行之後就停止請求行，因此實際的行計數隻有 2 並且運行時間遠低於開銷估計所建議的時間。這並非估計錯誤，這僅僅一種估計值和實際值顯示方式上的不同。
歸並連接也有類似的現象。如果一個歸並連接用盡了一個輸入並且其中的最後一個鍵值小於另一個輸入中的下一個鍵值，它將停止讀取另一個輸入。在這種情況下，不會有更多的匹配並且因此不需要掃描第二個輸入的剩餘部分。這會導致不讀取一個子結點的所有內容，其結果就像在LIMIT中所提到的。另外，如果 outer （第一個）子結點包含帶有重複鍵值的行，inner（第二個）子結點會被倒退並且被重新掃描來找能匹配那個鍵值的行。EXPLAIN ANALYZE會統計相同 inner 行的重複發出，就好像它們是真實的額外行。當有很多 outer 重複時，對 inner 子計劃結點所報告的實際行計數會顯著地大於實際在 inner 關係中的行數。
由於實現的限製，BitmapAnd 和 BitmapOr 結點總是報告它們的實際行計數為零。
如我們在上一節所見，查詢規劃器需要估計一個查詢要檢索的行數，這樣才能對查詢計劃做出好的選擇。 本節對係統用於這些估計的統計信息進行一個快速的介紹。
統計信息的一個部分就是每個表和索引中的項的總數，以及每個表和索引占用的磁盤塊數。這些信息保存在pg_class表的reltuples和relpages列中。 我們可以用類似下麵的查詢查看這些信息：
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)
這裏我們可以看到tenk1包含 10000 行， 它的索引也有這麼多行，但是索引遠比表小得多（不奇怪）。
出於效率考慮，reltuples和relpages不是實時更新的 ，因此它們通常包含有些過時的值。它們被VACUUM、ANALYZE和幾個 DDL 命令（例如CREATE INDEX）更新。一個不掃描全表的VACUUM或ANALYZE操作（常見情況）將以它掃描的部分為基礎增量更新reltuples計數，這就導致了一個近似值。在任何情況中，規劃器將縮放它在pg_class中找到的值來匹配當前的物理表尺寸，這樣得到一個較緊的近似。
大多數查詢隻是檢索表中行的一部分，因為它們有限製要被檢查的行的WHERE子句。 因此規劃器需要估算WHERE子句的選擇度，即符合WHERE子句中每個條件的行的比例。 用於這個任務的信息存儲在pg_statistic係統目錄中。 在pg_statistic中的項由ANALYZE和VACUUM ANALYZE命令更新， 並且總是近似值（即使剛剛更新完）。
除了直接查看pg_statistic之外， 手工檢查統計信息的時候最好查看它的視圖pg_stats。pg_stats被設計為更容易閱讀。 而且，pg_stats是所有人都可以讀取的，而pg_statistic隻能由超級用戶讀取（這樣可以避免非授權用戶從統計信息中獲取一些其他人的表的內容的信息。pg_stats視圖被限製為隻顯示當前用戶可讀的表）。例如，我們可以：
SELECT attname, inherited, n_distinct,
array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = 'road';
attname | inherited | n_distinct | most_common_vals
---------+-----------+------------+------------------------------------
name | f | -0.363388 | I- 580 Ramp+
| | | I- 880 Ramp+
| | | Sp Railroad +
| | | I- 580 +
| | | I- 680 Ramp
name | t | -0.284859 | I- 880 Ramp+
| | | I- 580 Ramp+
| | | I- 680 Ramp+
| | | I- 580 +
| | | State Hwy 13 Ramp
(2 rows)
注意，這兩行顯示的是相同的列，一個對應開始於road表（inherited=t）的完全繼承層次， 另一個隻包括road表本身（inherited=f）。
ANALYZE在pg_statistic中存儲的信息量（特別是每個列的most_common_vals中的最大項數和histogram_bounds數組）可以用ALTER TABLE SET STATISTICS命令為每一列設置， 或者通過設置配置變量default_statistics_target進行全局設置。 目前的默認限製是 100 個項。提升該限製可能會讓規劃器做出更準確的估計（特別是對那些有不規則數據分布的列）， 其代價是在pg_statistic中消耗了更多空間，並且需要略微多一些的時間來計算估計數值。 相比之下，比較低的限製可能更適合那些數據分布比較簡單的列。
我們可以在一定程度上用顯式JOIN語法控製查詢規劃器。要明白為什麼需要它，我們首先需要一些背景知識。
在一個簡單的連接查詢中，例如：
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
規劃器可以自由地按照任何順序連接給定的表。例如，它可以生成一個使用WHERE條件a.id = b.id連接 A 到 B 的查詢計劃，然後用另外一個WHERE條件把 C 連接到這個連接表。或者它可以先連接 B 和 C 然後再連接 A 得到同樣的結果。 或者也可以連接 A 到 C 然後把結果與 B 連接 — 不過這麼做效率不好，因為必須生成完整的 A 和 C 的迪卡爾積，而在WHERE子句中沒有可用條件來優化該連接（PostgreSQL執行器中的所有連接都發生在兩個輸入表之間， 所以它必須以這些形式之一建立結果）。 重要的一點是這些不同的連接可能性給出在語義等效的結果，但在執行開銷上卻可能有巨大的差別。 因此，規劃器會對它們進行探索並嚐試找出最高效的查詢計劃。
當一個查詢隻涉及兩個或三個表時，那麼不需要考慮很多連接順序。但是可能的連接順序數隨著表數目的增加成指數增長。 當超過十個左右的表以後，實際上根本不可能對所有可能性做一次窮舉搜索，甚至對六七個表都需要相當長的時間進行規劃。 當有太多的輸入表時，PostgreSQL規劃器將從窮舉搜索切換為一種遺傳概率搜索，它隻需要考慮有限數量的可能性（切換的閾值用geqo_threshold運行時參數設置）。遺傳搜索用時更少，但是並不一定會找到最好的計劃。
當查詢涉及外連接時，規劃器比處理普通（內）連接時擁有更小的自由度。例如，考慮：
SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
盡管這個查詢的約束表麵上和前一個非常相似，但它們的語義卻不同， 因為如果 A 裏有任何一行不能匹配 B 和 C的連接表中的行，它也必須被輸出。因此這裏規劃器對連接順序沒有什麼選擇：它必須先連接 B 到 C，然後把 A 連接到該結果上。 相應地，這個查詢比前麵一個花在規劃上的時間更少。在其它情況下，規劃器就有可能確定多種連接順序都是安全的。例如，給定：
SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
將 A 首先連接到 B 或 C 都是有效的。當前，隻有FULL JOIN完全約束連接順序。大多數涉及LEFT JOIN或RIGHT JOIN的實際情況都在某種程度上可以被重新排列。
顯式連接語法（INNER JOIN、CROSS JOIN或無修飾的JOIN）在語義上和FROM中列出輸入關係是一樣的， 因此它不約束連接順序。
即使大多數類型的JOIN並不完全約束連接順序，但仍然可以指示PostgreSQL查詢規劃器將所有JOIN子句當作有連接順序約束來對待。例如，這裏的三個查詢在邏輯上是等效的：
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
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);
但如果我們告訴規劃器遵循JOIN的順序，那麼第二個和第三個還是要比第一個花在規劃上的時間少。 這個效果對於隻有三個表的連接而言是微不足道的，但對於數目眾多的表，可能就是救命稻草了。
要強製規劃器遵循顯式JOIN的連接順序， 我們可以把運行時參數join_collapse_limit設置為 1（其它可能值在下文討論)。
你不必為了縮短搜索時間來完全約束連接順序，因為可以在一個普通FROM列表裏使用JOIN操作符。例如，考慮：
SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
如果設置join_collapse_limit = 1，那麼這就強迫規劃器先把 A 連接到 B， 然後再連接到其它的表上，但並不約束它的選擇。在這個例子中，可能的連接順序的數目減少了 5 倍。
按照這種方法約束規劃器的搜索是一個有用的技巧，不管是對減少規劃時間還是對引導規劃器生成好的查詢計劃。 如果規劃器按照默認選擇了一個糟糕的連接順序，你可以通過JOIN語法強迫它選擇一個更好的順序 — 假設你知道一個更好的順序。我們推薦進行實驗。
一個非常相近的影響規劃時間的問題是把子查詢壓縮到它們的父查詢中。例如，考慮：
SELECT *
FROM x, y,
(SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;
這種情況可能在使用包含連接的視圖時出現；該視圖的SELECT規則將被插入到引用視圖的地方，得到與上文非常相似的查詢。 通常，規劃器會嚐試把子查詢壓縮到父查詢裏，得到：
SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
這樣通常會生成一個比獨立的子查詢更好些的計劃（例如，outer 的WHERE條件可能先把 X 連接到 A 上，這樣就消除了 A 中的許多行， 因此避免了形成子查詢的全部邏輯輸出）。但是同時，我們增加了規劃的時間； 在這裏，我們用五路連接問題替代了兩個獨立的三路連接問題。這樣的差別是巨大的，因為可能的計劃數的是按照指數增長的。 如果有超過from_collapse_limit個FROM項將會導致父查詢，規劃器將嚐試通過停止提升子查詢來避免卡在巨大的連接搜索問題中。你可以通過調高或調低這個運行時參數在規劃時間和計劃的質量之間取得平衡。
from_collapse_limit和join_collapse_limit的命名相似，因為它們做的幾乎是同一件事：一個控製規劃器何時將把子查詢"平麵化"，另外一個控製何時把顯式連接平麵化。通常，你要麼把join_collapse_limit設置成和from_collapse_limit一樣（這樣顯式連接和子查詢的行為類似）， 要麼把join_collapse_limit設置為 1（如果你想用顯式連接控製連接順序）。 但是你可以把它們設置成不同的值，這樣你就可以細粒度地調節規劃時間和運行時間之間的平衡。
第一次填充數據庫時可能需要插入大量的數據。本節包含一些如何讓這個處理盡可能高效的建議。
4.1. 禁用自動提交
在使用多個INSERT時，關閉自動提交並且隻在最後做一次提交（在普通 SQL 中，這意味著在開始發出BEGIN並且在結束時發出COMMIT。某些客戶端庫可能背著你就做了這些，在這種情況下你需要確定在你需要做這些時該庫確實幫你做了）。如果你允許每一個插入都被獨立地提交，PostgreSQL要為每一個被增加的行做很多工作。在一個事務中做所有插入的一個額外好處是：如果一個行的插入失敗則所有之前插入的行都會被回滾，這樣你不會被卡在部分載入的數據中。
4.2. 使用COPY
使用COPY在一條命令中裝載所有記錄，而不是一係列INSERT命令。 COPY命令是為裝載大量行而優化過的； 它沒INSERT那麼靈活，但是在大量數據裝載時導致的負荷也更少。 因為COPY是單條命令，因此使用這種方法填充表時無須關閉自動提交。
如果你不能使用COPY，那麼使用PREPARE來創建一個預備INSERT語句也有所幫助，然後根據需要使用EXECUTE多次。這樣就避免了重複分析和規劃INSERT的負荷。不同接口以不同的方式提供該功能， 可參閱接口文檔中的"預備語句"。
請注意，在載入大量行時，使用COPY幾乎總是比使用INSERT快， 即使使用了PREPARE並且把多個插入被成批地放入一個單一事務。
同樣的事務中，COPY比更早的CREATE TABLE或TRUNCATE命令更快。 在這種情況下，不需要寫 WAL，因為在一個錯誤的情況下，包含新載入數據的文件不管怎樣都將被移除。不過，隻有當wal_level設置為minimal（此時所有的命令必須寫 WAL）時才會應用這種考慮。
4.3. 移除索引
如果你正在載入一個新創建的表，最快的方法是創建該表，用COPY批量載入該表的數據，然後創建表需要的任何索引。在已存在數據的表上創建索引要比在每一行被載入時增量地更新它更快。
如果你正在對現有表增加大量的數據，刪除索引、載入表然後重新創建索引可能是最好的方案。 當然，在缺少索引的期間，其它數據庫用戶的數據庫性能將會下降。 我們在刪除唯一索引之前還需要仔細考慮清楚，因為唯一約束提供的錯誤檢查在缺少索引的時候會丟失。
4.4. 移除外鍵約束
和索引一樣，"成批地"檢查外鍵約束比一行行檢查效率更高。 因此，先刪除外鍵約束、載入數據然後重建約束會很有用。 同樣，載入數據和約束缺失期間錯誤檢查的丟失之間也存在平衡。
更重要的是，當你在已有外鍵約束的情況下向表中載入數據時， 每個新行需要一個在服務器的待處理觸發器事件（因為是一個觸發器的觸發會檢查行的外鍵約束）列表的條目。載入數百萬行會導致觸發器事件隊列溢出可用內存， 造成不能接受的交換或者甚至是命令的徹底失敗。因此在載入大量數據時，可能需要（而不僅僅是期望）刪除並重新應用外鍵。如果臨時移除約束不可接受，那唯一的其他辦法可能是就是將載入操作分解成更小的事務。
4.5. 增加maintenance_work_mem
在載入大量數據時，臨時增大maintenance_work_mem配置變量可以改進性能。這個參數也可以幫助加速CREATE INDEX命令和ALTER TABLE ADD FOREIGN KEY命令。 它不會對COPY本身起很大作用，所以這個建議隻有在你使用上麵的一個或兩個技巧時才有用。
4.6. 增加max_wal_size
臨時增大max_wal_size配置變量也可以讓大量數據載入更快。 這是因為向PostgreSQL中載入大量的數據將導致檢查點的發生比平常（由checkpoint_timeout配置變量指定）更頻繁。無論何時發生一個檢查點時，所有髒頁都必須被刷寫到磁盤上。 通過在批量數據載入時臨時增加max_wal_size，所需的檢查點數目可以被縮減。
4.7. 禁用 WAL 歸檔和流複製
當使用 WAL 歸檔或流複製向一個安裝中載入大量數據時，在錄入結束後執行一次新的基礎備份比處理大量的增量 WAL 數據更快。為了防止載入時記錄增量 WAL，通過將wal_level設置為minimal、將archive_mode設置為off以及將max_wal_senders設置為零來禁用歸檔和流複製。 但需要注意的是，修改這些設置需要重啟服務。
除了避免歸檔器或 WAL 發送者處理 WAL 數據的時間之外，這樣做將實際上使某些命令更快， 因為它們被設計為在wal_level為minimal時完全不寫 WAL （通過在最後執行一個fsync而不是寫 WAL，它們能以更小地代價保證崩潰安全）。這適用於下列命令：
- CREATE TABLE AS SELECT
- CREATE INDEX（以及類似 ALTER TABLE ADD PRIMARY KEY的變體）
- ALTER TABLE SET TABLESPACE
- CLUSTER
- COPY FROM，當目標表已經被創建或者在同一個事務的早期被截斷
4.8. 事後運行ANALYZE
不管什麼時候你顯著地改變了表中的數據分布後，我們都強烈推薦運行ANALYZE。著包括向表中批量載入大量數據。運行ANALYZE（或者VACUUM ANALYZE）保證規劃器有表的最新統計信息。 如果沒有統計數據或者統計數據過時，那麼規劃器在查詢規劃時可能做出很差勁決定，導致在任意表上的性能低下。需要注意的是，如果啟用了 autovacuum 守護進程，它可能會自動運行ANALYZE；
4.9. 關於pg_dump的一些注記
pg_dump生成的轉儲腳本自動應用上麵的若幹個（但不是全部）技巧。 要盡可能快地載入pg_dump轉儲，你需要手工做一些額外的事情（請注意，這些要點適用於恢複一個轉儲，而不是創建它的時候。同樣的要點也適用於使用psql載入一個文本轉儲或用pg_restore從一個pg_dump歸檔文件載入）。
默認情況下，pg_dump使用COPY，並且當它在生成一個完整的模式和數據轉儲時， 它會很小心地先裝載數據，然後創建索引和外鍵。因此在這種情況下，一些指導方針是被自動處理的。你需要做的是：
- 為maintenance_work_mem和max_wal_size設置適當的（即比正常值大的）值。
- 如果使用 WAL 歸檔或流複製，在轉儲時考慮禁用它們。在載入轉儲之前，可通過將archive_mode設置為off、將wal_level設置為minimal以及將max_wal_senders設置為零（在錄入dump前）來實現禁用。 之後，將它們設回正確的值並執行一次新的基礎備份。
- 采用pg_dump和pg_restore的並行轉儲和恢複模式進行實驗並且找出要使用的最佳並發任務數量。通過使用-j選項的並行轉儲和恢複應該能為你帶來比串行模式高得多的性能。
- 考慮是否應該在一個單一事務中恢複整個轉儲。要這樣做，將-1或--single-transaction命令行選項傳遞給psql或pg_restore。 當使用這種模式時，即使是一個很小的錯誤也會回滾整個恢複，可能會丟棄已經處理了很多個小時的工作。根據數據間的相關性， 可能手動清理更好。如果你使用一個單一事務並且關閉了 WAL 歸檔，COPY命令將運行得最快。
- 如果在數據庫服務器上有多個 CPU 可用，可以考慮使用pg_restore的--jobs選項。這允許並行數據載入和索引創建。
- 之後運行ANALYZE。
一個隻涉及數據的轉儲仍將使用COPY，但是它不會刪除或重建索引，並且它通常不會觸碰外鍵。 [1] 因此當載入一個隻有數據的轉儲時，如果你希望使用那些技術，你需要負責刪除並重建索引和外鍵。在載入數據時增加max_wal_size仍然有用，但是不要去增加maintenance_work_mem；不如說在以後手工重建索引和外鍵時你已經做了這些。並且不要忘記在完成後執行ANALYZE。
Notes
[1]
你可以通過使用--disable-triggers選項的方法獲得禁用外鍵的效果 — 不過你要意識到這麼做是消除（而不隻是推遲）外鍵驗證。因此如果你使用該選項，就可能插入壞數據。
持久性是數據庫的一個保證已提交事務的記錄的特性（即使是發生服務器崩潰或斷電）。 然而，持久性會明顯增加數據庫的負荷，因此如果你的站點不需要這個保證，PostgreSQL可以被配置成運行更快。在這種情況下，你可以調整下列配置來提高性能。除了下麵列出的，在數據庫軟件崩潰的情況下也能保證持久性。當這些設置被使用時，隻有突然的操作係統停止會產生數據丟失或損壞的風險。
- 將數據庫集簇的數據目錄放在一個內存支持的文件係統上（即RAM磁盤）。這消除了所有的數據庫磁盤 I/O，但將數據存儲限製到可用的內存量（可能有交換區）。
- 關閉fsync；不需要將數據刷入磁盤。
- 關閉synchronous_commit；可能不需要在每次提交時 強製把WAL寫入磁盤。這種設置可能會在 數據庫崩潰時帶來事務丟失的風險（但是沒有數據破壞）。
- 關閉full_page_writes；不許要警惕部分頁麵寫入。
- 增加max_wal_size和checkpoint_timeout； 這會降低檢查點的頻率，但會 增加/pg_xlog的存儲要求。
- 創建不做日誌的表 來避免WAL寫入，不過這會讓表在崩潰時不安全。
最後更新：2017-08-18 16:32:21