18.7. 查詢規劃 · PostgreSQL 中文文檔 9.3

# 18.7\. 查詢規劃 ## 18.7.1\. 規劃器方法配置這些配置參數提供了影響查詢優化器選擇查詢規劃的原始方法。如果優化器為特定的查詢選擇的缺省規劃并不是最優，那么我們就可以通過使用這些配置參數強制優化器選擇一個不同的規劃來_臨時_解決這個問題。更好的改善優化器選擇規劃的方法包括調節規劃器開銷常量、手動運行[ANALYZE](#calibre_link-589)、增大配置參數[default_statistics_target](#calibre_link-979)的值、使用`ALTER TABLE SET STATISTICS`為某個字段增加收集的統計信息。 `enable_bitmapscan` (`boolean`) 打開或者關閉規劃器對位圖掃描規劃類型的使用。缺省是`on`。 `enable_hashagg` (`boolean`) 打開或者關閉規劃器對Hash聚集規劃類型的使用。缺省是`on`。 `enable_hashjoin` (`boolean`) 打開或者關閉規劃器對Hash連接規劃類型的使用。缺省是`on`。 `enable_indexscan` (`boolean`) 打開或者關閉規劃器對索引掃描規劃類型的使用。缺省是`on`。 `enable_indexonlyscan` (`boolean`) 打開或關閉規劃器對唯一索引掃描規劃類型的使用。缺省是`on`。 `enable_material` (`boolean`) 打開或關閉查詢規劃器使用物化。不可能完全抑制物化，但是關閉這個變量會阻止規劃器插入物化節點，除非它是必需正確的情況。默認值是`on`。 `enable_mergejoin` (`boolean`) 打開或者關閉規劃器對融合連接規劃類型的使用。缺省是`on`。 `enable_nestloop` (`boolean`) 打開或者關閉規劃器對嵌套循環連接規劃類型的使用。我們不可能完全消除嵌套循環連接，但是把這個變量關閉就會讓規劃器在存在其它方法的時候優先選擇其它方法。缺省是`on`。 `enable_seqscan` (`boolean`) 打開或者關閉規劃器對順序掃描規劃類型的使用。我們不可能完全消除順序掃描，但是把這個變量關閉會讓規劃器在存在其它方法的時候優先選擇其它方法。缺省是`on`。 `enable_sort` (`boolean`) 打開或者關閉規劃器使用明確的排序步驟。我們不可能完全消除明確的排序，但是把這個變量關閉可以讓規劃器在存在其它方法的時候優先選擇其它方法。缺省是`on`。 `enable_tidscan` (`boolean`) 打開或者關閉規劃器對TID掃描規劃類型的使用。缺省是`on`。 ## 18.7.2\. 規劃器開銷常量本節中描述的_開銷_可以按照任意標準度量。我們只關心其相對值，因此以相同的系數縮放它們將不會對規劃器產生任何影響。傳統上，它們以抓取順序頁的開銷作為基準單位。也就是說將`seq_page_cost`設為`1.0`，同時其它開銷參數對照它來設置。當然你也可以使用其它基準，比如以毫秒計的實際執行時間。 > **Note:** 糟糕的是，現在還沒有定義得很合理的方法來判斷下面出現的"開銷"變量族的理想數值。它們最好按照某個特定安裝的平均查詢開銷來衡量。這意味著僅僅根據很少量的試驗結果來修改它們是很危險的。 `seq_page_cost` (`floating point`) 設置規劃器計算一次順序磁盤頁面抓取的開銷。默認值是1.0。這個值可以通過設置同名表空間參數表的特定表和索引覆蓋。（參閱[ALTER TABLESPACE](#calibre_link-113)） `random_page_cost` (`floating point`) 設置規劃器計算一次非順序磁盤頁面抓取的開銷。默認值是4.0。這個值可以通過設置同名表空間參數表的特定表和索引覆蓋。（參閱[ALTER TABLESPACE](#calibre_link-113)）。 (相對于`seq_page_cost`)減少這個值將導致更傾向于使用索引掃描，而增加這個值將導致更傾向于使用順序掃描。可以通過同時增加或減少這兩個值來調整磁盤I/O相對于CPU的開銷(在下面的參數中描述)。機械磁盤存儲的隨機訪問比4次順序訪問往往更加昂貴。然而，下面使用缺省（4.0），因為大多數隨機訪問磁盤，比如索引讀取是在緩存中。默認值可以作為模擬隨機存取比順序存取慢40倍，而預計90％隨機讀取到緩存中。如果您認為90％的緩存率是你的工作量的錯誤假設，你可以增加random_page_cost更好地反映隨機存儲讀取的真實成本。相應地，如果你的數據很可能完全在高速緩存中，如當數據庫比總的服務器內存較小的時候，可以適當減少 random_page_cost。存儲具有相對順序的低隨機讀取成本，例如固態硬盤，可能還可以更好地與random_page_cost的低值建模。 > **Tip:** 雖然允許你將`random_page_cost`設置的比`seq_page_cost`小，但是物理上的實際情況并不受此影響。然而當所有數據庫都位于內存中時，兩者設置為相等是非常合理的，因為在此情況下，亂序抓取并不比順序抓取開銷更大。同樣，在緩沖率很高的數據庫上，你應當相對于CPU開銷同時降低這兩個值，因為獲取內存中的頁比通常情況下的開銷小許多。 `cpu_tuple_cost` (`floating point`) 設置規劃器計算在一次查詢中處理一個數據行的開銷。缺省是0.01。 `cpu_index_tuple_cost` (`floating point`) 設置規劃器計算在一次索引掃描中處理每條索引行的開銷。缺省是0.005。 `cpu_operator_cost` (`floating point`) 設置規劃器計算在一次查詢中執行一個操作符或函數的開銷。缺省是0.0025。 `effective_cache_size` (`integer`) 為規劃器設置在一次索引掃描中可用的磁盤緩沖區的有效大小。這個參數會在計算一個索引的預計開銷值的時候加以考慮。更高的數值會導致更可能使用索引掃描，更低的數值會導致更有可能選擇順序掃描。在設置這個參數的時候，你還應該考慮PostgreSQL的數據文件會使用的共享緩沖區和內核的磁盤緩沖區。另外，還要考慮預計會使用不同索引的并發查詢數目，因為它們必須共享可用的內存空間。這個參數對PostgreSQL分配的共享內存大小沒有影響，它也不會使用內核磁盤緩沖，它只用于估算。該系統還并未假設數據仍保留在查詢之間的磁盤緩存中。默認值是128兆字節(`128MB`)。 ## 18.7.3\. 基因查詢優化器基因查詢優化（GEQO）是一種算法，采用啟發式搜索查詢規劃。這樣可以為復雜查詢（鏈接著很多關系）減少規劃時間，生產規劃成本有時低于由正常窮舉搜索算法發現的那些。獲取更多信息，請參閱[Chapter 53](#calibre_link-529)。 `geqo` (`boolean`) 允許或禁止基因查詢優化，這是缺省值。在生產中最好不要關閉它。 `geqo_threshold`變量提供了GEQO的更精細方法。 `geqo_threshold` (`integer`) 只有當涉及的`FROM`關系數量至少有這么多個的時候，才使用基因查詢優化。（請注意一個`FULL OUTER JOIN`構造只算一個`FROM`項）。缺省是12。對于數量小于此值的查詢，也許使用判定性的窮舉搜索更有效。但是對于有許多表的查詢，規劃器做判斷要花很多時間。往往比執行一個次優規劃時間更長。因此，查詢大小閾值是管理使用GEQO的簡單方法。 `geqo_effort` (`integer`) 控制GEQO里規劃時間和查詢規劃的有效性之間的平衡。這個變量必須是一個范圍從1到10的整數。缺省值是5。大的數值增加花在進行查詢規劃上面的時間，但是也很可能會提高選中更有效的查詢規劃的幾率。 `geqo_effort`實際上并沒有直接干什么事情；只是用于計算其它那些影響GEQO行為變量的缺省值(在下面描述)。如果你愿意，你可以手工設置其它參數。 `geqo_pool_size` (`integer`) 控制GEQO使用的池大小。池大小是基因全體中的個體數量。它必須至少是2，并且有用的數值通常在100和1000之間。如果把它設置為零(缺省)，那么就會基于`geqo_effort`和查詢中表的數量選取一個合適的值。 `geqo_generations` (`integer`) 控制GEQO使用的子代數目。子代的意思是算法的迭代次數。它必須至少是1，有用的值范圍和池大小相同。如果設置為零(缺省)，那么將基于`geqo_pool_size`選取合適的值。 `geqo_selection_bias` (`floating point`) 控制GEQO使用的選擇性偏好。選擇性偏好是在一個種群中的選擇性壓力。數值可以是1.5到2.0之間；缺省是2.0。 `geqo_seed` (`floating point`) 控制隨機數發生器的初始值，它使用由GEQO通過連接順序搜索空間來選擇隨機路徑。該值的范圍可以從零（默認值）到一。各種不同的值改變探索連接路徑的設置，并可能導致發現或好或壞的最佳路徑。 ## 18.7.4\. 其它規劃器選項 `default_statistics_target` (`integer`) 為沒有用`ALTER TABLE SET STATISTICS`設置字段相關目標的表中其它字段設置缺省統計目標。更大的數值增加了`ANALYZE`所需要的時間，但是可能會改善規劃器的估計質量。缺省值是100。有關PostgreSQL的查詢規劃器使用的統計的更多信息，請參考[Section 14.2](#calibre_link-1188)。 `constraint_exclusion` (`enum`) 控制查詢規劃器使用的表約束優化查詢。`constraint_exclusion`允許的值是 `on`（檢查所有表的約束）， `off`（永遠不檢查約束），以及 `partition`（檢查僅用于繼承子表和`UNION ALL`子查詢的約束）。 `partition`是默認設置。它往往使用繼承和分區表來提高性能。當這個參數為特定表允許時，那么規劃器用查詢條件和`CHECK`約束進行比較，并且在查詢條件和約束沖突的情況下，忽略對表的掃描。比如： ``` CREATE TABLE parent(key integer, ...); CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent); CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent); ... SELECT * FROM parent WHERE key = 2400; ``` 在打開約束排除的時候，這個`SELECT`將完全不會掃描`child1000`。這樣可以提高性能。目前，constraint exclusion默認啟用，僅適用于那些通常用來實現表分區情況。在簡單查詢中為所有表強加額外開銷計劃而打開它是很明顯的，并且經常會產生不受益于簡單查詢。如果你沒有分區表，您可能更愿意將其完全關閉。參考[Section 5.9.4](#calibre_link-1442)獲取有關使用約束排除和分區的更多信息。 `cursor_tuple_fraction` (`floating point`) 設置被檢索的游標行分數的規劃器估計。默認值是0.1。這個設置較小的值偏好規劃器使用"fast start"規劃游標，當可能花費很長時間讀取所有行時，這將很快檢索出前幾行。較大的值把更多的重點放在總的估計時間上。 1.0的最大設置，規劃游標類似于定期查詢，只考慮總估計時間，而不是多長時間傳遞第一行。 `from_collapse_limit` (`integer`) 如果生成的`FROM`列表不超過這個限制的項數，規劃器將把子查詢融合到上層查詢。小的數值降低規劃的時間，但是可能會生成差些的查詢計劃。缺省是8。更多信息請查看[Section 14.3](#calibre_link-1189)。設置這個值到[geqo_threshold](#calibre_link-611)或者GEQO規劃器觸發，導致非最優規劃。參閱[Section 18.7.3](#calibre_link-1419)。 `join_collapse_limit` (`integer`) 如果得出的列表不超過這個數目的項，那么規劃器將把除`FULL JOIN`之外的`JOIN`構造抹平到`FROM`列表項中。小的數值降低規劃的時間，但是可能會生成差些的查詢計劃。缺省時，這個值和`from_collapse_limit`相同，這樣適合大多數場合。把它設置為1則避免任何`JOIN`的融合，這樣就將明確使用語句中的連接順序。查詢優化器并不是總能選取最優的連接順序；高級用戶可以選擇暫時把這個變量設置為1，然后明確地聲明他們需要的連接順序。更多信息參見[Section 14.3](#calibre_link-1189)。設置這個值到[geqo_threshold](#calibre_link-611)或者GEQO規劃器觸發，導致非最優規劃。參閱[Section 18.7.3](#calibre_link-1419)。