Chapter 10. 性能提升技巧

Table of Contents
10.1. 使用 EXPLAIN
10.2. 規劃器使用的統計資訊
10.3. 用明確的 JOIN (連線)控制規劃器
10.4. 向資料庫中加入記錄
10.4.1. 關閉自動提交
10.4.2. 使用 COPY FROM
10.4.3. 刪除索引
10.4.4. 事後運行 ANALYZE

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

10.1. 使用 EXPLAIN

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

目前被 EXPLAN 參照的數位是:

開銷是以硬碟頁面的存取為單位計算的. (預計的 CPU 處理用一些非常隨意的捏造的權值被轉換成硬碟頁面單位。 如果你想試驗這些東西,請參閱在 PostgreSQL 7.3 管理員手冊 裡的運行時參數清單.)

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

輸出的行數有一些小處理,因為它不是 查詢處理/掃描過的行數 --- 通常會少一些, 反映對應用於此系統上的任意WHERE幾句約束的選擇性估計. 通常而言,頂層的行預計會接近於查詢實際傳回,更新,或刪除的行數

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

regression=# 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條件:

regression=# 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 生成的統計是從該表中 隨機抽取的樣本計算的.

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

regression=# 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條件:

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND
regression-# 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 列上有).它是當做一個從 索引中檢索出的行的過濾器來用的.因此開銷實際上略微增加了一些以 反映這個額外的檢查.

讓我們試著使用我們上面討論的資料域連線兩個表:

regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# 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)

在這個巢狀循環連結裡,外層掃描和我們前一個範例是一樣的, 因此它的開銷和行數是一樣的,因為我們對那個系統應用了 unique1 < 50WHERE幾句。 t1.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 10.3.)

regression=# SET enable_nestloop = off;
SET
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# 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顯示的估計開銷.比如,我們可以象下面這樣獲取一個結果︰

regression=# EXPLAIN ANALYZE
regression-# SELECT * FROM tenk1 t1, tenk2 t2
regression-# 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的結果除了在你實際測試的情況之外不能推匯出其它的情況, 那它就什麼用都沒有﹔比如,在一個小得象玩具的表上的結果不能 適用於大表.規劃器的開銷計算不是線性的,因此它很可能對大些或者 小些的表選擇不同的規劃.一個歲N搌瑤d例是一個只占據一個硬碟頁面的表, 在這樣的表上,不管它有沒有索引可以使用,你幾乎都總是得到順序 掃描規劃.規劃器知道不管在任何情況下它都要進行一個硬碟頁面的讀取, 所以再擴大幾個硬碟頁面讀取以尋找索引是沒有意義的.