60.1. 行預期的例子 · PostgreSQL 中文文檔 9.3

# 60.1\. 行預期的例子下面的例子使用的PostgreSQL回歸測試數據庫中的表。輸出結果是從8.3版獲得的。之前或之后版本的動作可能會有所變化。同時需要注意的是，在產生統計信息時，`ANALYZE`使用的是隨機采樣，在使用一次新的`ANALYZE`之后，結果可能會發生輕微的改變。讓我們以一個很簡單的查詢開始： ``` EXPLAIN SELECT * FROM tenk1; QUERY PLAN ------------------------------------------------------------- Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244) ``` 規劃器如何判斷`tenk1`里面行的基數在[Section 14.2](#calibre_link-1188)里面介紹，為了完整，在這里重復一下。行數或頁數是從`pg_class`里面查出來的： ``` SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1'; relpages | reltuples ----------+----------- 358 | 10000 ``` 這些數字表示表中當前最新的`VACUUM`或者`ANALYZE`。之后，規劃器取出表中當前實際的塊號（這個操作的開銷很小，不需要掃描全表）。如果與`relpages`不同，那么根據達到的一個當前函數估計值， `reltuples`會進行一定的縮放。在這種情況下，`relpages`的值是最新的，因此估計的行與`reltuples`相同。換一個在WHERE子句里面帶有范圍條件的例子： ``` EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000; QUERY PLAN -------------------------------------------------------------------------------- Bitmap Heap Scan on tenk1 (cost=24.06..394.64 rows=1007 width=244) Recheck Cond: (unique1 < 1000) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0) Index Cond: (unique1 < 1000) ``` 規劃器檢查`WHERE`子句條件，并為`pg_operator`中的`<`執行器查找可選函數。將保持在`oprrest`列中，并且在這個例子中的條目是`scalarltsel`。 `scalarltsel`函數從`unique1`為 `unique1`檢索直方圖。對于手工查詢來說，這樣做可以更方便，更直觀的查看`pg_stats`視圖： ``` SELECT histogram_bounds FROM pg_stats WHERE tablename='tenk1' AND attname='unique1'; histogram_bounds ------------------------------------------------------ {0,993,1997,3050,4040,5036,5957,7057,8029,9016,9995} ``` 然后，把直方圖里面包含"< 1000"的部分找出來。這就是選擇性。直方圖把范圍分隔成相同頻率的段，所以要做的只是把的數值所在的段找出來，然后計算它里面占的_部分_以及_所有_該值之前的部分。值1000很明顯在第二個段(993-1997)里，因此，假設每個段里面的分布是線性的，那么就可以計算出選擇性： ``` selectivity = (1 + (1000 - bucket[2].min)/(bucket[2].max - bucket[2].min))/num_buckets = (1 + (1000 - 993)/(1997 - 993))/10 = 0.100697 ``` 也就是一個段加上第二個段的線性部分，除以總段數。那么估計的行數現在可以用選擇性和`tenk1`的基數之積計算： ``` rows = rel_cardinality * selectivity = 10000 * 0.100697 = 1007 (rounding off) ``` 然后考慮一個`WHERE`子句里等于條件的例子： ``` EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'CRAAAA'; QUERY PLAN ---------------------------------------------------------- Seq Scan on tenk1 (cost=0.00..483.00 rows=30 width=244) Filter: (stringu1 = 'CRAAAA'::name) ``` 規劃器再次檢查`WHERE`子句條件，并為`=`（是`eqsel`）查找可選函數。對于等價估計而言，直方圖并不是有用的；相反， _最常見的值_(MCV)列表可以用來決定可選項。讓我們來看一下MCV，帶有一些額外的列會很有效： ``` SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename='tenk1' AND attname='stringu1'; null_frac | 0 n_distinct | 676 most_common_vals | {EJAAAA,BBAAAA,CRAAAA,FCAAAA,FEAAAA,GSAAAA,JOAAAA,MCAAAA,NAAAAA,WGAAAA} most_common_freqs | {0.00333333,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003} ``` 因為MCV中有`CRAAAA`，那么可選項只是MCF列表中的一個相關條目： ``` selectivity = mcf[3] = 0.003 ``` 像之前一樣，行的估計數只是和前面一樣用`tenk1`的基數乘以選擇性： ``` rows = 10000 * 0.003 = 30 ``` 現在看看同樣的查詢，但是字符串常量是不在MCV列表里的: ``` EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'xxx'; QUERY PLAN ---------------------------------------------------------- Seq Scan on tenk1 (cost=0.00..483.00 rows=15 width=244) Filter: (stringu1 = 'xxx'::name) ``` 這個時候的問題是完全不同的一個：在數據值_不在_ MCV列表里面時，如何估計選擇性就是完全另外一個問題了。解決方法是利用該值不在列表里頭的事實，結合已知的所有MCV出現的頻率，用減法得出： ``` selectivity = (1 - sum(mvf))/(num_distinct - num_mcv) = (1 - (0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003))/(676 - 10) = 0.0014559 ``` 也就是，為MCV增加所有的頻率，并且從1減去，然后用_其它_無重復值的個數來分開。這相當于假設不是MCV中的列的分數巨暈的分布在所有其他不同值中。需要注意的是，沒有NULL值，因此不需要擔心這些（否則需要從分子中減去NULL分數）。估算的行數然后照例計算： ``` rows = 10000 * 0.0014559 = 15 (rounding off) ``` 之前帶有`unique1 < 1000`的例子是`scalarltsel`實際執行的簡單化。現在已經看過了使用MCV的例子，可以增加一些具體細節了。這個例子這樣子是正確的，因為`unique1`是一個唯一屬性列，那么它沒有MCV（顯然，沒有一個值能比其它值更通用）。對一個非唯一屬性列而言，通常會有直方圖和MCV列表，并且直方圖不包括MCV表示的列總體那部分。在這種情況下， `scalarltsel`直接應用條件到每個 MCV列表的值上（如"< 1000"），并且增加那些條件判斷為真的MCV的頻率。這給出準確的是MCV表的部分的選擇的準確估計。然后直方圖使用與上述方式相同的估計選擇表的部分，其不是MCV，那么組合這兩個數字來估計總的選擇性。例如，考慮 ``` EXPLAIN SELECT * FROM tenk1 WHERE stringu1 < 'IAAAAA'; QUERY PLAN ------------------------------------------------------------ Seq Scan on tenk1 (cost=0.00..483.00 rows=3077 width=244) Filter: (stringu1 < 'IAAAAA'::name) ``` 我們已看到關于stringu1的MCV信息，這里是它的直方圖： ``` SELECT histogram_bounds FROM pg_stats WHERE tablename='tenk1' AND attname='stringu1'; histogram_bounds -------------------------------------------------------------------------------- {AAAAAA,CQAAAA,FRAAAA,IBAAAA,KRAAAA,NFAAAA,PSAAAA,SGAAAA,VAAAAA,XLAAAA,ZZAAAA} ``` 檢查MCV列表，我們發現前6項滿足條件`stringu1 <'IAAAAA'`，而不是最后4項，所以最常見的部分MCV選擇性是 ``` selectivity = sum(relevant mvfs) = 0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 = 0.01833333 ``` 累加所有的MCF，也告訴我們由 MCVs表示的常見的總比例是0.03033333，而且因此由直方圖表示的比例是0.96966667。（再次,沒有NULL，否則這里我們排斥它們）我們可以看到`IAAAAA`值落在第三段直方圖的結尾部分。關于不同字符串的頻率使用些較普通的假設，規劃器達到估計0.298387為直方圖中小于`IAAAAA`的部分。我們然后組合估計值為MCV和非MCV常見: ``` selectivity = mcv_selectivity + histogram_selectivity * histogram_fraction = 0.01833333 + 0.298387 * 0.96966667 = 0.307669 rows = 10000 * 0.307669 = 3077 (rounding off) ``` 尤其是在這個例子中，MCV列表的糾正很小，因為列分布實際上很平坦。（統計分析顯示這些特殊值往往比其它的更常見大部分由于抽樣誤差）在更典型的情況下這里有些值顯著的比其它的更常見，這復雜的處理過程，有用的提高了精度，因為選擇性對于那些最常見的值來說，查找準確。現在考慮一個`WHERE`子句中帶有多個條件的情況： ``` EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000 AND stringu1 = 'xxx'; QUERY PLAN -------------------------------------------------------------------------------- Bitmap Heap Scan on tenk1 (cost=23.80..396.91 rows=1 width=244) Recheck Cond: (unique1 < 1000) Filter: (stringu1 = 'xxx'::name) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0) Index Cond: (unique1 < 1000) ``` 規劃器認為這兩個條件是獨立的，因此可以同時執行語句的獨立查詢： ``` selectivity = selectivity(unique1 < 1000) * selectivity(stringu1 = 'xxx') = 0.100697 * 0.0014559 = 0.0001466 rows = 10000 * 0.0001466 = 1 (rounding off) ``` 需要注意的是，從位圖索引掃描中返回的估計行數值影響索引使用的條件；這一點很重要，因為它會影響之后的堆棧抓取估計開銷。最后檢查一個包含連接的查找： ``` EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2; QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=4.64..456.23 rows=50 width=488) -> Bitmap Heap Scan on tenk1 t1 (cost=4.64..142.17 rows=50 width=244) Recheck Cond: (unique1 < 50) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.63 rows=50 width=0) Index Cond: (unique1 < 50) -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..6.27 rows=1 width=244) Index Cond: (unique2 = t1.unique2) ``` 在`tenk1`上的`unique1 < 50`限制在嵌套循環連接之前計算。這個條件是用類似上面的那個范圍例子的方法處理的。但是這次數值50落在`unique1`的直方圖表的第一個段內： ``` selectivity = (0 + (50 - bucket[1].min)/(bucket[1].max - bucket[1].min))/num_buckets = (0 + (50 - 0)/(993 - 0))/10 = 0.005035 rows = 10000 * 0.005035 = 50 (rounding off) ``` 此鏈接的限制是`t2.unique2 = t1.unique2`。操作符是我們熟悉的`=`，然而可選函數是從`pg_operator`的`oprjoin`字段獲得的，并且是`eqjoinsel`。 `eqjoinsel`為`tenk2`和 `tenk1`查找統計信息： ``` SELECT tablename, null_frac,n_distinct, most_common_vals FROM pg_stats WHERE tablename IN ('tenk1', 'tenk2') AND attname='unique2'; tablename | null_frac | n_distinct | most_common_vals -----------+-----------+------------+------------------ tenk1 | 0 | -1 | tenk2 | 0 | -1 | ``` 在這個例子里，沒有`unique2`的MCV信息，因為所有數值看上去都是唯一的，因此可以使用一個只依賴唯一數值數目和NULL數目百分比的算法來給兩個表計算(選擇性)： ``` selectivity = (1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1, 1/num_distinct2) = (1 - 0) * (1 - 0) / max(10000, 10000) = 0.0001 ``` 也就是說，把每個表都減去一里面NULL的比例，然后除以數值的最大值。連接可能選出來的行數是以嵌套循環里的兩個輸入值的笛卡爾積的總行數，乘以選擇性計算出來的： ``` rows = (outer_cardinality * inner_cardinality) * selectivity = (50 * 10000) * 0.0001 = 50 ``` 這里有兩列的MCV列表， `eqjoinsel`將直接使用MCV列表比較來決定連接由MCV表示的常見列部分的選擇。下面常見的剩下的估計值跟顯示這里的方法相同。需要注意的是，`inner_cardinality`表示為10000，也就是未修改的`tenk2`大小。它可能出現從檢查`EXPLAIN`輸出，其連接行的估計值來自50 * 1，就是，由外部行數乘以由每個內部索引掃描的`tenk2`獲取的估計行數。但是這不是那種情況：估計連接關系的大小在考慮任何特定的連接計劃之前。如果任何事情工作很好，那么兩種方式估計的連接大小將產生相關的同樣的答案，但是由于四舍五入誤差和其它因素它們有時差異較明顯。在`src/backend/optimizer/util/plancat.c`中有對一個表大小的估計（在任何`WHERE`字句之前）。在`src/backend/optimizer/path/clausesel.c`中有對字句選擇性的通用邏輯。在`src/backend/utils/adt/selfuncs.c`中有特定操作符的可選函數。