14.3. 用明確的JOIN控制規劃器 · PostgreSQL 中文文檔 9.3

# 14.3\. 用明確的`JOIN`控制規劃器我們可以在一定程度上用明確的`JOIN`語法控制查詢規劃器。要明白為什么有這茬事，我們首先需要一些背景知識。在簡單的連接查詢里，比如: ``` SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id; ``` 規劃器可以按照任何順序自由地連接給出的表。比如，它可以生成一個查詢規劃先用`WHERE`條件`a.id = b.id`把 A 連接到 B ，然后用另外一個`WHERE`條件把 C 連接到這個表上來，或者也可以先連接 B 和 C 然后再連接 A ，同樣得到這個結果。或者也可以連接 A 到 C 然后把結果與 B 連接—不過這么做效率比較差，因為必須生成完整的 A 和 C 的迪卡爾積，而在查詢里沒有可用的`WHERE`子句可以優化該連接(PostgreSQL執行器里的所有連接都發生在兩個輸入表之間，所以在這種情況下它必須先得出一個結果)。重要的一點是這些連接方式給出語義上相同的結果，但在執行開銷上卻可能有巨大的差別。因此，規劃器會對它們進行檢查并找出最高效的查詢規劃。如果查詢只涉及兩或三個表，那么在查詢里不會有太多需要考慮的連接。但是潛在的連接順序的數目隨著表數目的增加程指數增加的趨勢。當超過十個左右的表以后，實際上根本不可能對所有可能做一次窮舉搜索，甚至對六七個表都需要相當長的時間進行規劃。如果有太多輸入的表， PostgreSQL規劃器將從窮舉搜索切換為_基因_概率搜索，以減少可能性數目(樣本空間)。切換的閾值是用運行時參數[geqo_threshold](#calibre_link-611)設置的。基因搜索花的時間少，但是并不一定能找到最好的規劃。當查詢涉及外部連接時，規劃器就不像對付普通(內部)連接那么自由了。比如，看看下面這個查詢: ``` SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); ``` 盡管這個查詢的約束和前面一個非常相似，但它們的語義卻不同，因為如果 A 里有任何一行不能匹配B和C的連接里的行，那么該行都必須輸出。因此這里規劃器對連接順序沒有什么選擇：它必須先連接 B 到 C ，然后把 A 連接到該結果上。因此，這個查詢比前面一個花在規劃上的時間少。在其它情況下，規劃器就有可能確定多種連接順序都是安全的。比如，對于: ``` SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id); ``` 將 A 首先連接到 B 或 C 都是有效的。當前，只有`FULL JOIN`完全強制連接順序。大多數`LEFT JOIN`或者`RIGHT JOIN`都可以在某種程度上重新排列。明確的連接語法(`INNER JOIN`, `CROSS JOIN`或無修飾的`JOIN`)語義上和 `FROM`中列出輸入關系是一樣的，因此我們沒有必要約束連接順序。即使大多數`JOIN`并不完全強迫連接順序，但仍然可以明確的告訴PostgreSQL 查詢規劃器`JOIN`子句的連接順序。比如，下面三個查詢邏輯上是等效的： ``` SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id; SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id; SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); ``` 但如果我們告訴規劃器遵循`JOIN`的順序，那么第二個和第三個還是要比第一個花在規劃上的時間少。這個作用對于只有三個表的連接而言是微不足道的，但對于數目眾多的表，可能就是救命稻草了。要強制規劃器遵循準確的`JOIN`連接順序，我們可以把運行時參數[join_collapse_limit](#calibre_link-1205)設置為 1(其它可能的數值在下面討論)。你完全不必為了縮短搜索時間來約束連接順序，因為在一個簡單的`FROM`列表里使用`JOIN`操作符就很好了。比如考慮： ``` SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...; ``` 如果設置`join_collapse_limit` = 1，那么這句話就相當于強迫規劃器先把A連接到B ，然后再連接到其它的表上，但并不約束其它的選擇。在本例中，可能的連接順序的數目減少了 5 倍。按照上面的想法考慮規劃器的搜索問題是一個很有用的技巧，不管是對減少規劃時間還是對引導規劃器生成好的規劃都很有幫助。如果缺省時規劃器選擇了一個糟糕的連接順序，你可以用`JOIN`語法強迫它選擇一個更好的— (假設知道一個更好的順序)。所以我們建議多試驗。一個非常相近的影響規劃時間的問題是把子查詢壓縮到它們的父查詢里面。比如，考慮下面的查詢: ``` SELECT * FROM x, y, (SELECT * FROM a, b, c WHERE something) AS ss WHERE somethingelse; ``` 這個情況可能在那種包含連接的視圖中出現；該視圖的`SELECT`規則將被插入到引用視圖的場合，生成非常類似上面的查詢。通常，規劃器會試圖把子查詢壓縮到父查詢里，生成: ``` SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; ``` 這樣通常會生成一個比獨立的子查詢更好些的規劃。比如，外層的`WHERE`條件可能先把X連接到 A 上，這樣就消除了 A 中的許多行，因此避免了形成全部子查詢邏輯輸出的需要。但是同時，我們增加了規劃的時間；在這里，我們有一個用五路連接代替兩個獨立的三路連接的問題，這樣的差距是巨大的，因為可能的規劃數的是按照指數增長的。規劃器將在父查詢可能超過`from_collapse_limit`個`FROM`項的時候，不再壓縮子查詢，以此來避免巨大的連接搜索數。你可以通過調整這個運行時參數來在規劃時間和規劃質量之間作出平衡。 [from_collapse_limit](#calibre_link-1206)和[join_collapse_limit](#calibre_link-1205) 名字類似是因為他們做的事情幾乎相同：一個控制規劃器何時把子查詢"平面化"，另外一個控制何時把明確的連接平面化。通常，你要么把`join_collapse_limit`設置成和`from_collapse_limit`一樣(明確連接和子查詢的行為類似)，要么把`join_collapse_limit`設置為 1(如果你想用明確連接控制連接順序)。但是你可以把它們設置成不同的值，這樣你就可以在規劃時間和運行時間之間進行仔細的調節。