| PostgreSQL 7.4 文檔 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Fast Forward | Next | |
查詢的性能可能受多種因素影響。 其中一些因素可以由用戶操縱，而其他的則屬于下層系統設計的基本問題了。 本章我們提供一些有關理解和調節 PostgreSQL 性能的線索。
PostgreSQL 為給它的每個查詢產生一個查詢規劃。 為匹配查詢結構和數據屬性選擇正確的規劃對性能絕對有關鍵性的影響。 你可以使用 EXPLAIN 命令察看系統為每個查詢生成的查詢規劃是什麼。 閱讀查詢規劃是一門值得寫一個相當長的教程的學問， 而我這份文檔可不是這樣的教程，但是這裡有一些基本的信息。
目前被 EXPLAN 引用的數字是：
預計的啟動開銷（在輸出掃描開始之前消耗的時間，也就是，在一個排序節點裡做排續的時間）。
預計的總開銷（如果所有的行都被檢索的話，不過很可能不是這樣：比如 LIMIT 將在總開銷的一小部分就停止）。
預計的這個規劃節點輸出的行數。 （同樣，只執行到完成為止）。
預計的這個規劃節點的行的平均寬度（以字節計算）。
開銷是以磁盤頁面的存取為單位計算的。 （預計的 CPU 處理用一些非常隨意的捏造的權值被轉換成磁盤頁面單位。 如果你想試驗這些東西，請參閱在 Section 16.4 裡的運行時參數列表。）
有一點很重要：那就是一個上層節點的開銷包括它的所有子節點的開銷。 還有一點也很重要：就是這個開銷只反映規劃器/優化器關心的東西。 尤其是開銷沒有把結果行傳遞給前端的時間考慮進去， 這個時間可能在真正的總時間裡面佔據相當重要的分量； 但是被規劃器忽略了，因為它無法通過修改規劃來改變之。 （我們相信，每個正確的規劃都將輸出同樣的記錄集。）
輸出的行數有一些小技巧，因為它不是查詢處理/掃描過的行數，通常會少一些， 反映對應用于此節點上的任意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 < 50。 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 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查詢，總運行時間通常只是比從頂層規劃節點匯報出來的總時間略微大些。 對于INSERT，UPDATE，和 DELETE 查詢， 總運行時間可能會顯著增大，因為它包括花費在處理結果行上的時間。 在這些查詢裡，頂層規劃節點的時間實際上是花在計算新行和/或定位舊行上的時間，但是不包括花在執行改動上的時間。
如果EXPLAIN的結果除了在你實際測試的情況之外不能推導出其它的情況， 那它就什麼用都沒有；比如，在一個小得象玩具的表上的結果不能適用于大表。 規劃器的開銷計算不是線性的，因此它很可能對大些或者小些的表選擇不同的規劃。 一個極端的例子是一個只佔據一個磁盤頁面的表，在這樣的表上，不管它有沒有索引可以使用， 你幾乎都總是得到順序掃描規劃。規劃器知道不管在任何情況下它都要進行一個磁盤頁面的讀取， 所以再擴大幾個磁盤頁面讀取以查找索引是沒有意義的。