PostgreSQL7.0手冊-用戶手冊-17. 理解性能
2019-09-08 23:33:07
供稿：網友
第十七章. 理解性能
查詢性能可能受許多因素的影響．這些因素里面有一些是可以由用戶控制的，而其它因素取決于他所用的（數據庫）系統．
有一些性能因素，比如索引的創建和海量數據的裝載在其他地方討論．本章將討論 EXPLAIN 命令，然后將展示一個查詢的細節是如何影響查詢規劃進而影響整體性能的．
使用 EXPLAIN
作者：由 Tom Lane 寫做，源于 2000-03-27 的電子郵件．
規劃分析是一門值得寫一個教程的學問，而我沒有足夠的時間寫呢么一個．這里是我寫的一些簡短的不完善的解釋．
目前被 EXPLAN 引用的數字是：
預計的啟動開銷（在輸出掃描開始之前消耗的時間，比如，在一個 SORT 節點里做排續的時間）．
預計的總開銷（如果所有的記錄都被檢索的話，不過很可能不是這樣 --- 比如 LIMIT 將在總開銷的一小部分就停止）．
預計的這個規劃節點輸出的行數．
預計的這個規劃節點的行的平均寬度（以字節計算）．
開銷是以磁盤頁面的存取為單位計算的．（預計的 CPU 處理用一些非常隨意的捏造的權值被轉換成磁盤頁面單位。如果你想試驗這些東西，請參閱 SET 的手冊頁。）有一點很重要：那就是一個上層節點的開銷包括它的所有子節點的開銷。還有一點也很重要：就是這個開銷只反映規劃器/優化器關心的東西。尤其是開銷沒有把結果記錄傳遞給前端的時間考慮進去 --- 這個時間可能在真正的總時間里面占據相當重要的位置，但是被規劃器忽略了，因為它無法通過修改規劃來改變之。（我們相信，每個正確的規劃都將輸出同樣的記錄集。）
輸出行有一點小技巧，因為輸出的行不是被查詢處理/掃描過的行 --- 通常輸出行會少一些，反映應用于此節點上的任意 WHERE 子句的選擇性計算。
平均寬度是相當虛的東西，因為它實際上對變長度列沒有任何認識。我正在考慮將來改進這些東西，但是也可能不值得這樣做，因為寬度用的不是很多?！?
下面是幾個例子（用的是經過清理（vacuum）分析后的蛻變測試數據庫以及接近完成的 7.0 代碼）：
regression=# explain select * from tenk1;
NOTICE: 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?。ㄓ妹睢how cpu_tuple_cost 查看）?！?
現在讓我們修改查詢并增加條件子句：
regression=# explain select * from tenk1 where unique1 < 1000;
NOTICE: QUERY PLAN:
Seq Scan on tenk1 (cost=0.00..358.00 rows=1000 width=148)
輸出行的計算降低了，因為有 WHERE 子句。（這里的讓人驚訝的預計計算只是因為 tenk1 是一個非常簡單的例子 --- unique1 列有 10000 條獨立的值，范圍從 0 到 9999，因此計算器在列數值的最大值和最小值之間的線性插值沒什么用。）不過，這次掃描仍然需要訪問所有 10000 行，因此開銷沒有降低；實際上還增加了一些，以反映為了檢查 WHERE 條件多用的 CPU 時間?！?
把查詢修改為限制條件更嚴格：
regression=# explain select * from tenk1 where unique1 < 100;
NOTICE: QUERY PLAN:
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..89.35 rows=100 width=148)
這時你會看到，如果我們把 WHERE 條件變得足夠有選擇性，規劃器將最終決定一次索引掃描將比一次順序掃描快。因為索引，這個規劃將只需要訪問 100 條記錄，因此盡管每條記錄單獨的抓取開銷比較大，它（這個查詢規劃）還是勝出?！?
向條件里面增加另外一個條件：
regression=# explain select * from tenk1 where unique1 < 100 and
regression-# stringu1 = 'xxx';
NOTICE: QUERY PLAN:
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..89.60 rows=1 width=148)
新增的子句 "stringu1 = 'xxx'" 減少了預計的輸出行，但是沒有減少開銷，因為我們仍然需要訪問相同的記錄集?！?
讓我們試著使用我們上面討論的數據域聯接兩個表：
regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100
regression-# and t1.unique2 = t2.unique2;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..144.07 rows=100 width=296)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..89.35 rows=100 width=148)
-> Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..0.53 rows=1 width=148)
在這個嵌套循環聯接里，外層掃描和我們上面舉例的是一樣的，因此它的開銷和行數是一樣的，因為我們對那個節點應用了 "unique1 < 100" WHERE 子句。"t1.unique2 = t2.unique2" 這時還不相關，因此它沒有影響外層掃描的行計數。對于內層掃描，目前的外層掃描的記錄的 unique2 值被插入到內層索引掃描以生成一個象 "t2.unique2 = constant" 這樣的索引查詢。因此我們得到與我們想要的， 類似 "explain select * from tenk2 where unique2 = 42" 這個查詢同樣的內層掃描規劃和開銷。然后再以外層掃描的開銷為基礎設置循環節點的開銷，加上一個為每個外層掃描重復的內層掃描（這里是 100 * 0.53），加上一點點處理聯接的 CPU 時間?！?
在這個例子里，循環的輸出行數與兩個掃描的行數的乘積相同，但是通常并不是這樣的，因為通常你會有提及兩個關系的 WHERE 子句，因此只影響聯接的部分，而對兩者的輸入掃描均不影響。例如，如果我們加一條 "WHERE ... AND t1.hundred < t2.hundred"，將減少輸出行數，但是不會修改任何一個輸入掃描。
我們可以看一下我們強制規劃器忽視它認為優秀的（掃描）策略（這還是相當原始的工具，但是它是我們目前能夠用上的東西）的結果：
regression=# set enable_nestloop = off;
SET VARIABLE
regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100
regression-# and t1.unique2 = t2.unique2;
NOTICE: QUERY PLAN:
Hash Join (cost=89.60..574.10 rows=100 width=296)
-> Seq Scan on tenk2 t2
(cost=0.00..333.00 rows=10000 width=148)
-> Hash (cost=89.35..89.35 rows=100 width=148)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..89.35 rows=100 width=148)
這個規劃仍然試圖用同樣的索引掃描從 tenk1 里面取出感興趣的 100 行，把它們藏在一個在內存里的散列（哈希）表里，然后對 tenk2 做一次順序掃描，在每一條 tenk2 記錄上檢測上面的散列（哈希）表尋找可能匹配 "t1.unique2 = t2.unique2" 的記錄。讀取 tenk1 和建立散列表是此哈希聯接的全部啟動開銷，因為我們在開始讀取 tenk2 之前不可能獲得任何輸出記錄。這個聯接的總的預計時間同樣還包括相當重的檢測散列（哈希）表 10000 次的 CPU 時間。不過，請注意，我們不需要對 89.35 乘 10000；散列（哈希）表的在這個規劃類型中只需要設置一次。
-----------------------------------------------------------------------------
