?
This document uses PHP Chinese website manual Release
14.1. 使用EXPLAIN
PostgreSQL對(duì)每個(gè)查詢產(chǎn)生一個(gè)查詢規(guī)劃。 為匹配查詢結(jié)構(gòu)和數(shù)據(jù)屬性選擇正確的規(guī)劃對(duì)性能絕對(duì)有關(guān)鍵性的影響。 因此系統(tǒng)包含了一個(gè)復(fù)雜的規(guī)劃器用于尋找最優(yōu)的規(guī)劃。 你可以使用EXPLAIN命令察看規(guī)劃器為每個(gè)查詢生成的查詢規(guī)劃是什么。 閱讀查詢規(guī)劃是一門(mén)值得專門(mén)寫(xiě)一厚本教程的學(xué)問(wèn)，而這份文檔可不是這樣的教程，但是這里有一些基本的信息
查詢規(guī)劃的結(jié)構(gòu)是一個(gè)規(guī)劃節(jié)點(diǎn)的樹(shù)。最底層的節(jié)點(diǎn)是表掃描節(jié)點(diǎn)：它們從表中返回原始數(shù)據(jù)行。 不同的表訪問(wèn)模式有不同的掃描節(jié)點(diǎn)類型：順序掃描、索引掃描、位圖索引掃描。 如果查詢需要連接、聚集、排序、或者對(duì)原始行的其它操作，那么就會(huì)在掃描節(jié)點(diǎn)"之上"有其它額外的節(jié)點(diǎn)。 并且，做這些操作通常都有多種方法，因此在這些位置也有可能出現(xiàn)不同的節(jié)點(diǎn)類型。 EXPLAIN給規(guī)劃樹(shù)中每個(gè)節(jié)點(diǎn)都輸出一行，顯示基本的節(jié)點(diǎn)類型和規(guī)劃器為執(zhí)行這個(gè)規(guī)劃節(jié)點(diǎn)預(yù)計(jì)的開(kāi)銷值。 第一行(最上層的節(jié)點(diǎn))是對(duì)該規(guī)劃的總執(zhí)行開(kāi)銷的預(yù)計(jì)；這個(gè)數(shù)值就是規(guī)劃器試圖最小化的數(shù)值。
這里是一個(gè)簡(jiǎn)單的例子，只是用來(lái)顯示輸出會(huì)有些什么內(nèi)容 [1]
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
EXPLAIN引用的數(shù)值是:
預(yù)計(jì)的啟動(dòng)開(kāi)銷。在輸出掃描開(kāi)始之前消耗的時(shí)間，也就是在一個(gè)排序節(jié)點(diǎn)里執(zhí)行排序的時(shí)間
預(yù)計(jì)所有行都被檢索的總開(kāi)銷。 不過(guò)事實(shí)可能不是這樣：比如帶有LIMIT子句的查詢將會(huì)在limit規(guī)劃節(jié)點(diǎn)的輸入節(jié)點(diǎn)里很快停止
預(yù)計(jì)這個(gè)規(guī)劃節(jié)點(diǎn)輸出的行數(shù)。同樣，只執(zhí)行到完成為止
預(yù)計(jì)這個(gè)規(guī)劃節(jié)點(diǎn)的行平均寬度(以字節(jié)計(jì)算)。
開(kāi)銷是用規(guī)劃器根據(jù)成本參數(shù)(參見(jiàn)Section 18.6.2)捏造的單位來(lái)衡量的，習(xí)慣上以磁盤(pán)頁(yè)面抓取為單位。 也就是seq_page_cost將被按照習(xí)慣設(shè)為1.0(一次順序的磁盤(pán)頁(yè)面抓取)， 其它開(kāi)銷參數(shù)將參照它來(lái)設(shè)置。 本節(jié)的例子都假定這些參數(shù)使用默認(rèn)值。
有一點(diǎn)很重要：一個(gè)上層節(jié)點(diǎn)的開(kāi)銷包括它的所有子節(jié)點(diǎn)的開(kāi)銷。 還有一點(diǎn)也很重要：這個(gè)開(kāi)銷只反映規(guī)劃器關(guān)心的東西，尤其是沒(méi)有把結(jié)果行傳遞給客戶端的時(shí)間考慮進(jìn)去， 這個(gè)時(shí)間可能在實(shí)際的總時(shí)間里占據(jù)相當(dāng)重要的分量，但是被規(guī)劃器忽略了，因?yàn)樗鼰o(wú)法通過(guò)修改規(guī)劃來(lái)改變： 我們相信，每個(gè)正確的規(guī)劃都將輸出同樣的記錄集。
輸出的行數(shù)有一些小技巧，因?yàn)樗皇且?guī)劃節(jié)點(diǎn)處理/掃描過(guò)的行數(shù)，通常會(huì)少一些， 反映對(duì)應(yīng)用于此節(jié)點(diǎn)上的任意WHERE子句條件的選擇性估計(jì)。 通常而言，頂層的行預(yù)計(jì)會(huì)接近于查詢實(shí)際返回、更新、刪除的行數(shù)。
回到我們的例子：
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
這個(gè)例子就像例子本身一樣直接了當(dāng)。如果你做一個(gè)
SELECT relpages，reltuples FROM pg_class WHERE relname = 'tenk1';
你會(huì)發(fā)現(xiàn)tenk1有358磁盤(pán)頁(yè)面和10000行。
因此開(kāi)銷計(jì)算為358次頁(yè)面讀取，每次頁(yè)面讀取將消耗（頁(yè)面讀取數(shù)*seq_page_cost），
加上（掃描的行數(shù)*cpu_tuple_cost）。默認(rèn)，seq_page_cost是1.0，cpu_tuple_cost是0.01，
因此將消耗(358 * 1.0) + (10000 * 0.01) = 458。
現(xiàn)在讓我們修改查詢并增加一個(gè)WHERE條件：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 < 7000)
請(qǐng)注意EXPLAIN輸出顯示WHERE子句當(dāng)作一個(gè)""filter""條件。 這意味著規(guī)劃節(jié)點(diǎn)為它掃描的每一行檢查該條件，并且只輸出符合條件的行。 預(yù)計(jì)的輸出行數(shù)降低了，因?yàn)橛?tt class="LITERAL">WHERE子句。 不過(guò)，掃描仍將必須訪問(wèn)所有 10000 行，因此開(kāi)銷沒(méi)有降低； 實(shí)際上它還增加了一些以反映檢查WHERE條件的額外 CPU 時(shí)間
這條查詢實(shí)際選擇的行數(shù)是 7000，但是預(yù)計(jì)的數(shù)目只是個(gè)大概。 如果你試圖重復(fù)這個(gè)試驗(yàn)，那么你很可能得到不同的預(yù)計(jì)。 還有，這個(gè)預(yù)計(jì)會(huì)在每次ANALYZE命令之后改變， 因?yàn)?tt class="COMMAND">ANALYZE生成的統(tǒng)計(jì)是從該表中隨機(jī)抽取的樣本計(jì)算的。
把查詢限制條件改得更嚴(yán)格一些:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
這里，規(guī)劃器決定使用兩步的規(guī)劃： 最底層的規(guī)劃節(jié)點(diǎn)訪問(wèn)一個(gè)索引，找出匹配索引條件的行的位置，然后上層規(guī)劃節(jié)點(diǎn)真實(shí)地從表中抓取出那些行。 獨(dú)立地抓取數(shù)據(jù)行比順序地讀取它們的開(kāi)銷高很多，但是因?yàn)椴⒎撬斜淼捻?yè)面都被訪問(wèn)了， 這么做實(shí)際上仍然比一次順序掃描開(kāi)銷要少。 使用兩層規(guī)劃的原因是因?yàn)樯蠈右?guī)劃節(jié)點(diǎn)把索引標(biāo)識(shí)出來(lái)的行位置在讀取它們之前按照物理位置排序， 這樣可以最小化獨(dú)立抓取的開(kāi)銷。 節(jié)點(diǎn)名稱里面提到的"bitmap"是進(jìn)行排序的機(jī)制。
如果WHERE條件有足夠的選擇性，規(guī)劃器可能會(huì)切換到一個(gè)"簡(jiǎn)單的"索引掃描規(guī)劃：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 < 3)
在這個(gè)例子中，表的數(shù)據(jù)行是以索引順序抓取的，這樣就令讀取它們的開(kāi)銷更大， 但是這里的行少得可憐，因此對(duì)行位置的額外排序并不值得。 最常見(jiàn)的就是看到這種規(guī)劃類型只抓取一行， 以及那些要求ORDER BY條件匹配索引順序的查詢。
向WHERE子句里面增加另外一個(gè)條件：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Index Cond: (unique1 < 3)
Filter: (stringu1 = 'xxx'::name)
新增的條件stringu1 = 'xxx'減少了預(yù)計(jì)的輸出行， 但是沒(méi)有減少開(kāi)銷，因?yàn)槲覀內(nèi)匀恍枰L問(wèn)相同的行。 請(qǐng)注意，stringu1 子句不能當(dāng)做一個(gè)索引條件使用(因?yàn)檫@個(gè)索引只是在unique1列上有)。 它被當(dāng)做一個(gè)從索引中檢索出的行的過(guò)濾器來(lái)使用。 因此開(kāi)銷實(shí)際上略微增加了一些以反映這個(gè)額外的檢查。
如果在 WHERE 里面使用的好幾個(gè)字段上都有索引，那么規(guī)劃器可能會(huì)使用索引的 AND 或 OR 的組合：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=11.27..11.27 rows=11 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 > 9000)
但是這么做要求訪問(wèn)兩個(gè)索引，因此與只使用一個(gè)索引，而把另外一個(gè)條件只當(dāng)作過(guò)濾器相比， 這個(gè)方法未必是更優(yōu)。如果你改變涉及的范圍，你會(huì)看到規(guī)劃器相應(yīng)地發(fā)生變化。
讓我們?cè)囍褂梦覀兩厦嬗懻摰淖侄芜B接兩個(gè)表：
EXPLAIN SELECT *
FROM tenk1 t1，tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: (t2.unique2 = t1.unique2)
在這個(gè)嵌套循環(huán)里，外層的掃描是我們前面看到的同樣的位圖索引，因此其開(kāi)銷和行計(jì)數(shù)是一樣的， 因?yàn)槲覀冊(cè)谠摴?jié)點(diǎn)上附加了WHERE子句unique1 < 100。 此時(shí)t1.unique2 = t2.unique2子句還沒(méi)有什么關(guān)系，因此它不影響外層掃描的行計(jì)數(shù)。 對(duì)于內(nèi)層掃描，當(dāng)前外層掃描的數(shù)據(jù)行的unique2被插入內(nèi)層索引掃描生成類似 t2.unique2 =constant這樣的索引條件。 因此，我們拿到和從EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42那邊拿到的一樣的內(nèi)層掃描計(jì)劃和開(kāi)銷。 然后，以外層掃描的開(kāi)銷為基礎(chǔ)設(shè)置循環(huán)節(jié)點(diǎn)的開(kāi)銷，加上每個(gè)外層行的一個(gè)重復(fù)(這里是106*3.01)， 然后再加上連接處理需要的一點(diǎn)點(diǎn) CPU 時(shí)間。
在這個(gè)例子里，連接的輸出行數(shù)與兩個(gè)掃描的行數(shù)的乘積相同，但通常并不是這樣的， 因?yàn)橥ǔＤ銜?huì)有提及兩個(gè)表的 WHERE 子句，因此它只能應(yīng)用于連接(join)點(diǎn)，而不能影響兩個(gè)關(guān)系的輸入掃描。 比如，如果我們加一條WHERE ... AND t1.hundred < t2.hundred， 將減少輸出行數(shù)，但是不改變?nèi)魏我粋€(gè)輸入掃描。
尋找另外一個(gè)規(guī)劃的方法是通過(guò)設(shè)置每種規(guī)劃類型的允許/禁止開(kāi)關(guān)(在Section 18.6.1里描述)， 強(qiáng)制規(guī)劃器拋棄它認(rèn)為優(yōu)秀的(掃描)策略。 這個(gè)工具目前比較原始，但很有用。參閱Section 14.3。
SET enable_nestloop = off;
EXPLAIN SELECT *
FROM tenk1 t1，tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
這個(gè)規(guī)劃仍然試圖用同樣的索引掃描從tenk1里面取出感興趣的 100 行，
把它們藏在一個(gè)內(nèi)存 Hash 表里，然后對(duì) tenk2做一次順序掃描，
對(duì)每一條 tenk2記錄檢測(cè)上面的 Hash 表，
尋找可能匹配t1.unique2 = t2.unique2的行。
讀取 tenk1 和建立 Hash 表是此散列連接的全部啟動(dòng)開(kāi)銷，因?yàn)槲覀冊(cè)陂_(kāi)始讀取 tenk2之前不可能獲得任何輸出行。
這個(gè)連接的總預(yù)計(jì)時(shí)間同樣還包括相當(dāng)重的檢測(cè) Hash 表 10000 次的 CPU 時(shí)間。
不過(guò)，請(qǐng)注意，我們不需要對(duì) 232.35 乘 10000，因?yàn)?Hash 表的在這個(gè)規(guī)劃類型中只需要設(shè)置一次。
我們可以用EXPLAIN ANALYZE檢查規(guī)劃器的估計(jì)值的準(zhǔn)確性。 這個(gè)命令實(shí)際上執(zhí)行該查詢?nèi)缓箫@示每個(gè)規(guī)劃節(jié)點(diǎn)內(nèi)實(shí)際運(yùn)行時(shí)間的和以及單純EXPLAIN顯示的估計(jì)開(kāi)銷。 比如，我們可以像下面這樣獲取一個(gè)結(jié)果：
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1，tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
Index Cond: (t2.unique2 = t1.unique2)
Total runtime: 14.452 ms
請(qǐng)注意"actual time"數(shù)值是以真實(shí)時(shí)間的毫秒計(jì)的，而cost估計(jì)值是以任意磁盤(pán)抓取的單元計(jì)的； 因此它們很可能不一致。我們要關(guān)心的事是兩組比值是否一致。
在一些查詢規(guī)劃里，一個(gè)子規(guī)劃節(jié)點(diǎn)很可能運(yùn)行多次。 比如，在上面的嵌套循環(huán)的規(guī)劃里，內(nèi)層的索引掃描對(duì)每個(gè)外層行執(zhí)行一次。 在這種情況下，loops報(bào)告該節(jié)點(diǎn)執(zhí)行的總數(shù)目，而顯示的實(shí)際時(shí)間和行數(shù)目是每次執(zhí)行的平均值。 這么做的原因是令這些數(shù)字與開(kāi)銷預(yù)計(jì)顯示的數(shù)字具有可比性。 要乘以loops值才能獲得在該節(jié)點(diǎn)花費(fèi)的總時(shí)間。
EXPLAIN ANALYZE顯示的Total runtime包括執(zhí)行器啟動(dòng)和關(guān)閉的時(shí)間， 以及花在處理結(jié)果行上的時(shí)間。它不包括分析、重寫(xiě)、規(guī)劃的時(shí)間。 對(duì)于INSERT，UPDATE和DELETE命令， 總運(yùn)行時(shí)間可能會(huì)顯著增大，因?yàn)樗ɑㄙM(fèi)在處理結(jié)果行上的時(shí)間。 （這個(gè)節(jié)點(diǎn)之下的計(jì)劃節(jié)點(diǎn)代表定位舊行和/或計(jì)算新行）。如果存在， 在觸發(fā)器之前的執(zhí)行時(shí)間與相關(guān)的插入，更新或刪除節(jié)點(diǎn)有關(guān)。在每個(gè)觸發(fā)器（無(wú)論是之前還是之后）的時(shí)間 會(huì)單獨(dú)的顯示，并且是包含在總執(zhí)行時(shí)間中的。然后，需要注意的是，延遲約束觸發(fā)器在事務(wù)結(jié)束之前是不回執(zhí)行的 ，因此不會(huì)被EXPLAIN ANALYZE命令顯示。
有兩個(gè)顯著的原因使EXPLAIN ANALYZE測(cè)試的運(yùn)行時(shí)間偏離運(yùn)行相同查詢時(shí)的正常結(jié)果。
一個(gè)是網(wǎng)絡(luò)傳輸和I/O傳輸?shù)幕ㄙM(fèi)，另一個(gè)是，EXPLAIN ANALYZE命令本身會(huì)增加開(kāi)銷，
特別是機(jī)器的gettimeofday()內(nèi)核調(diào)用很慢時(shí)。
如果EXPLAIN的結(jié)果除了在你實(shí)際測(cè)試的情況之外不能推導(dǎo)出其它的情況，那它就什么用都沒(méi)有； 比如，在一個(gè)小得像玩具的表上的結(jié)果不能適用于大表。規(guī)劃器的開(kāi)銷計(jì)算不是線性的， 因此它很可能對(duì)大些或者小些的表選擇不同的規(guī)劃。 一個(gè)極端的例子是一個(gè)只占據(jù)一個(gè)磁盤(pán)頁(yè)面的表，在這樣的表上，不管它有沒(méi)有索引可以使用，你幾乎都總是得到順序掃描規(guī)劃。 規(guī)劃器知道不管在任何情況下它都要進(jìn)行一個(gè)磁盤(pán)頁(yè)面的讀取，所以再擴(kuò)大幾個(gè)磁盤(pán)頁(yè)面讀取以查找索引是沒(méi)有意義的。
Notes
| [1] | 例子來(lái)自執(zhí)行完VACUUM ANALYZE后的回歸測(cè)試數(shù)據(jù)庫(kù)，只用8.2的開(kāi)發(fā)源。 如果自己測(cè)試，可以得到相似的結(jié)果，但由于ANALYZE的統(tǒng)計(jì)是隨機(jī)的例子，而不是實(shí)際的，因此 會(huì)有輕微的不同。 |