Chapter 13. 性能提升技巧

Table of Contents
13.1. 使用 EXPLAIN
13.2. 規劃器使用的統計訊息
13.3. 用明確的 JOIN (連接)控制規劃器
13.4. 向資料庫中添加記錄
13.4.1. 關閉自動提交
13.4.2. 使用 COPY
13.4.3. 刪除索引
13.4.4. 增大 maintenance_work_mem
13.4.5. 增大 checkpoint_segments
13.4.6. 事後執行ANALYZE

查詢的性能可能受多種因素影響。 其中一些因素可以由用戶操縱,而其他的則屬於下層系統設計的基本問題了。 本章我們提供一些有關理解和調節 PostgreSQL 性能的線索。

13.1. 使用 EXPLAIN

PostgreSQL 為給它的每個查詢產生一個查詢規劃。 為匹配查詢結構和資料屬性選擇正確的規劃對性能絕對有關鍵性的影響。 您可以使用 EXPLAIN 命令察看系統為每個查詢生成的查詢規劃是什麼。 閱讀查詢規劃是一門值得寫一個相當長的教學的學問, 而我這份文件可不是這樣的教學,但是這裡有一些基本的訊息。

目前被 EXPLAN 引用的數字是:

開銷是以磁盤頁面的存取為單位計算的。 (預計的 CPU 處理用一些非常隨意的捏造的權值被轉換成磁盤頁面單位。 如果您想試驗這些東西,請參閱在 Section 16.4.5.2 裡的執行時參數列資料表。)

有一點很重要:那就是一個上層節點的開銷包括它的所有子節點的開銷。 還有一點也很重要:就是這個開銷只反映規劃器/優化器關心的東西。 尤其是開銷沒有把結果行傳遞給前端的時間考慮進去, 這個時間可能在真正的總時間裡面佔據相當重要的份量; 但是被規劃器忽略了,因為它無法透過修改規劃來改變之。 (我們相信,每個正確的規劃都將輸出同樣的記錄集。)

輸出的行數有一些小技巧,因為它不是查詢處理/掃瞄過的行數,通常會少一些, 反映對應用於此節點上的任意WHERE子句條件的選擇性估計。 通常而言,頂層的行預計會接近於查詢實際返回,更新,或刪除的行數。

下面是幾個例子(用的是經過 VACUUM ANALYZE 後的回歸測試資料庫以及 7.3 的開發代碼):

EXPLAIN SELECT * FROM tenk1;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..333.00 rows=10000 width=148)

這個例子就像例子本身一樣直接了當。如果您做一個

SELECT * FROM pg_class WHERE relname = 'tenk1';

您會發現tenk1有 233 磁盤頁面和 10000 行。 因此開銷計算為 233 次頁面讀取,定義為每塊 1.0, 加上 10000 * cpu_tuple_cost,目前是 0.01(用命令 SHOW cpu_tuple_cost 查看)。

現在讓我們修改查詢並增加一個WHERE條件:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..358.00 rows=1033 width=148)
   Filter: (unique1 < 1000)

預計的輸出行數降低了,因為有WHERE子句。 不過,掃瞄仍將必須訪問所有 10000 行,因此開銷沒有降低; 實際上它還增加了一些以反映檢查WHERE條件的額外 CPU 時間。

這條查詢實際選擇的行數是 1000,但是預計的數目只是個大概。 如果您試圖重複這個試驗,那麼您很可能得到有些不同的預計; 還有,這個預計會在每次 ANALYZE 命令之後改變, 因為 ANALYZE 生成的統計是從該資料表中隨機抽取的樣本計算的。

把查詢修改為限制條件更嚴格:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.33 rows=49 width=148)
   Index Cond: (unique1 < 50)

這時您會看到,如果我們把WHERE條件變得足夠有選擇性, 規劃器將最終決定一次索引掃瞄將比一次順序掃瞄快。 因為有索引,這個規劃將只需要訪問 50 條記錄, 因此儘管每條記錄單獨的抓取比順序讀取整個磁盤頁面的開銷大, 它(這個查詢規劃)還是勝出。

WHERE子句裡面增加另外一個條件:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND stringu1 = 'xxx';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.45 rows=1 width=148)
   Index Cond: (unique1 < 50)
   Filter: (stringu1 = 'xxx'::name)

新增的條件 stringu1 = 'xxx' 減少了預計的輸出行, 但是沒有減少開銷,因為我們仍然需要訪問相同的行。 請注意 stringu1 子句不能當做一個索引條件施用 (因為這個索引只是在 unique1 列上有)。 它是當做一個從索引中檢索出的行的過濾器來用的。 因此開銷實際上略微增加了一些以反映這個額外的檢查。

讓我們試著使用我們上面討論的字串連接兩個資料表:

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
----------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                                      (cost=0.00..179.33 rows=49 width=148)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                                      (cost=0.00..3.01 rows=1 width=148)
         Index Cond: ("outer".unique2 = t2.unique2)

在這個嵌套循環連線裡,外層掃瞄和我們前一個例子是一樣的, 因此它的開銷和行數是一樣的,因為我們對那個節點應用了WHERE子句 unique1 < 50t1.unique2 = t2.unique2 這時還不相關, 因此它沒有影響外層掃瞄的行計數。 對於內層掃瞄, 目前的外層掃瞄行的unique2值被插入到內層索引掃瞄以生成一個象 t2.unique2 = constant 這樣的索引條件。這樣我們就得到與我們想要的和查詢 EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42 同樣的內層掃瞄規劃和開銷。 然後再以外層掃瞄的開銷為基礎設置循環節點的開銷, 加上一個為每個外層行掃瞄重複的內層掃瞄(這裡是 49 * 3.01), 再加上一點點處理連線的 CPU 時間。

在這個例子裡,連接的輸出行數與兩個掃瞄的行數的乘積相同, 但是通常並不是這樣的,因為通常您會有提及兩個資料表的WHERE子句, 因此它只能應用於連接(join)點,而不能影響兩個關係的輸入掃瞄。 比如,如果我們加一條 WHERE ... AND t1.hundred < t2.hundred, 將減少輸出行數,但是不改變任何一個輸入掃瞄。

尋找另外一個規劃的方法是透過設置每種規劃類型的允許/禁止開關, 強制規劃器拋棄它認為優秀的(掃瞄)策略。 (這個工具目前比較原始,但很有用。又見Section 13.3。)

SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=179.45..563.06 rows=49 width=296)
   Hash Cond: ("outer".unique2 = "inner".unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..333.00 rows=10000 width=148)
   ->  Hash  (cost=179.33..179.33 rows=49 width=148)
         ->  Index Scan using tenk1_unique1 on tenk1 t1
                                    (cost=0.00..179.33 rows=49 width=148)
               Index Cond: (unique1 < 50)

這個規劃仍然試圖用同樣的索引掃瞄從tenk1 裡面取出感興趣的 50 行, 把它們藏在一個在內存裡的散列(哈希)資料表裡,然後對tenk2 做一次順序掃瞄,對每一條tenk2記錄檢測上面的散列(哈希)資料表, 尋找可能匹配t1.unique2 = t2.unique2 的行。 讀取tenk1和建立散列資料表是此散列連線的全部啟動開銷, 因為我們在開始讀取tenk2 之前不可能獲得任何輸出行。 這個連線的總的預計時間同樣還包括相當重的檢測散列(哈希)資料表 10000 次的 CPU 時間。不過,請注意,我們需要對 179.33 乘 10000; 散列(哈希)資料表的在這個規劃類型中只需要設置一次。

我們可以用EXPLAIN ANALYZE檢查規劃器的估計值的準確性。 這個命令實際上執行該查詢然後顯示每個規劃節點內實際執行時間的和以及單純EXPLAIN顯示的估計開銷。 比如,我們可以像下面這樣獲取一個結果:

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
                                 (actual time=1.18..29.82 rows=50 loops=1)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                  (cost=0.00..179.33 rows=49 width=148)
                                 (actual time=0.63..8.91 rows=50 loops=1)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                  (cost=0.00..3.01 rows=1 width=148)
                                 (actual time=0.29..0.32 rows=1 loops=50)
         Index Cond: ("outer".unique2 = t2.unique2)
 Total runtime: 31.60 msec

請注意 "actual time" 數值是以真實時間的毫秒計的, 而 "cost" 估計值是以任意磁盤抓取的單元計的; 因此它們很可能不一致。我們要關心的事是兩組比值是否一致。

在一些查詢規劃裡,一個子規劃節點很可能執行多次。 比如,在上面的嵌套循環的規劃裡,內層的索引掃瞄是對每個外層行執行一次的。 在這種情況下,"loops" 報告該節點執行的總數目, 而顯示的實際時間和行數目是每次執行的平均值。 這麼做的原因是令這些數字與開銷預計顯示的數字具有可比性。 要乘以 "loops" 值才能獲得在該節點時間花費的總時間。

EXPLAIN ANALYZE 顯示的 "Total runtime" 包括執行器啟動和關閉的時間, 以及花在處理結果行上的時間。它不包括分析,重寫,或者規劃的時間。 對於SELECT查詢,總執行時間通常只是比從頂層規劃節點匯報出來的總時間略微大些。 對於INSERTUPDATE,和 DELETE 查詢, 總執行時間可能會顯著增大,因為它包括花費在處理結果行上的時間。 在這些查詢裡,頂層規劃節點的時間實際上是花在計算新行和/或定位舊行上的時間,但是不包括花在執行改動上的時間。

如果EXPLAIN的結果除了在您實際測試的情況之外不能推導出其它的情況, 那它就什麼用都沒有;比如,在一個小得像玩具的資料表上的結果不能適用於大資料表。 規劃器的開銷計算不是線性的,因此它很可能對大些或者小些的資料表選擇不同的規劃。 一個極端的例子是一個只佔據一個磁盤頁面的資料表,在這樣的資料表上,不管它有沒有索引可以使用, 您幾乎都總是得到順序掃瞄規劃。規劃器知道不管在任何情況下它都要進行一個磁盤頁面的讀取, 所以再擴大幾個磁盤頁面讀取以查找索引是沒有意義的。