PostgreSQL 規(guī)劃器使用的統(tǒng)計信息

最簡單的一類擴展統(tǒng)計信息跟蹤函數(shù)依賴，這是在數(shù)據(jù)庫范式定義中使用的概念。如果列a的值的知識足以決定列b的值，即不會有兩個行具有相同的a值但是有不同的b值，我們就說列b函數(shù)依賴于列 a。在一個完全規(guī)范化的數(shù)據(jù)庫中，函數(shù)依賴應(yīng)該僅存在于主鍵和超鍵上。不過，實際上很多數(shù)據(jù)集合會由于各種原因無法被完全規(guī)范化，常見的例子是為了性能而有意地反規(guī)范化。即使在一個完全規(guī)范化的數(shù)據(jù)庫中，也會有某些列之間的部分關(guān)聯(lián)，這些可以表達(dá)成部分函數(shù)依賴。

函數(shù)依賴的存在直接影響了特定查詢中估計的準(zhǔn)確性。如果一個查詢包含獨立列和依賴列上的條件，依賴列上的條件不會進(jìn)一步降低結(jié)果的尺寸。但是如果沒有函數(shù)依賴的知識，查詢規(guī)劃器將假定條件是獨立的，導(dǎo)致對結(jié)果尺寸的低估。

要告知規(guī)劃器有關(guān)函數(shù)依賴的信息，ANALYZE可以收集跨列依賴的測度。評估所有列組之間的依賴程度可能會昂貴到不可實現(xiàn)，因此數(shù)據(jù)收集被限制為針對那些在一個統(tǒng)計信息對象中一起出現(xiàn)的列組（用dependencies選項定義）。建議只對強相關(guān)的列組創(chuàng)建dependencies統(tǒng)計信息，以避免ANALYZE以及后期查詢規(guī)劃中不必要的開銷。

這里是一個收集函數(shù)依賴統(tǒng)計信息的例子：

CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxname, stxkeys, stxddependencies
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts';
 stxname | stxkeys |             stxddependencies             
---------+---------+------------------------------------------
 stts    | 1 5     | {"1 => 5": 1.000000, "5 => 1": 0.423130}
(1 row)

這里可以看到列1（郵編）完全決定列5（城市），因此系數(shù)為1.0，而城市僅決定42%的郵編，意味著有很多城市（58%）有多個郵編。

在為涉及函數(shù)依賴列的查詢計算選擇度時，規(guī)劃器會使用依賴系數(shù)來調(diào)整針對條件的選擇度估計，這樣就不會產(chǎn)生低估。

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';

單列統(tǒng)計信息存儲每一列中可區(qū)分值的數(shù)量。在組合多個列（例如GROUP BY a, b）時，如果規(guī)劃器只有單列統(tǒng)計數(shù)據(jù)，則對可區(qū)分值數(shù)量的估計常常會錯誤，導(dǎo)致選擇不好的計劃。

為了改進(jìn)這種估計，ANALYZE可以為列組收集可區(qū)分值統(tǒng)計信息。和以前一樣，為每一種可能的列組合做這件事情是不切實際的，因此只會為一起出現(xiàn)在一個統(tǒng)計信息對象（用ndistinct選項定義）中的列組收集數(shù)據(jù)。將會為列組中列出的列的每一種可能的組合都收集數(shù)據(jù)。

繼續(xù)之前的例子，ZIP代碼表中的可區(qū)分值計數(shù)可能像這樣：

CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxkeys AS k, stxdndistinct AS nd
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts2';
-[ RECORD 1 ]------------------------------------------------------?--
k  | 1 2 5
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
(1 row)

這表示有三種列組合有33178個可區(qū)分值：ZIP代碼和州、ZIP代碼和城市、ZIP代碼+城市+周（事實上對于表中給定的一個唯一的ZIP代碼，它們本來就應(yīng)該是相等的）。另一方面，城市和州的組合只有27435個可區(qū)分值。

建議只對實際用于分組的列組合以及分組數(shù)錯誤估計導(dǎo)致了糟糕計劃的列組合創(chuàng)建ndistinct統(tǒng)計信息對象。否則，ANALYZE循環(huán)只會被浪費。

為每列存儲的另一種統(tǒng)計信息是頻繁值列表。 這樣可以對單個列進(jìn)行非常準(zhǔn)確的估計，但是對于在多個列上具有條件的查詢，可能會導(dǎo)致嚴(yán)重的錯誤估計。

為了改善這種估計，ANALYZE可以收集列組合上的MCV列表。 與功能依賴和n-distinct系數(shù)類似，對每種可能的列分組進(jìn)行此操作都是不切實際的。 在這種情況下，甚至更是如此，因為MCV列表（與功能依賴性和n-distinct系數(shù)不同）存儲了公共列值。 因此，僅收集在使用mcv選項定義的統(tǒng)計對象中同時出現(xiàn)的那些列組的數(shù)據(jù)。

繼續(xù)前面的示例，郵政編碼表的MCV列表可能類似于以下內(nèi)容（與更簡單的統(tǒng)計信息不同，它需要一個函數(shù)來檢查MCV內(nèi)容）:

CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;

ANALYZE zipcodes;

SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
                pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';

 index |         values         | nulls | frequency | base_frequency 
-------+------------------------+-------+-----------+----------------
     0 | {Washington, DC}       | {f,f} |  0.003467 |        2.7e-05
     1 | {Apo, AE}              | {f,f} |  0.003067 |        1.9e-05
     2 | {Houston, TX}          | {f,f} |  0.002167 |       0.000133
     3 | {El Paso, TX}          | {f,f} |     0.002 |       0.000113
     4 | {New York, NY}         | {f,f} |  0.001967 |       0.000114
     5 | {Atlanta, GA}          | {f,f} |  0.001633 |        3.3e-05
     6 | {Sacramento, CA}       | {f,f} |  0.001433 |        7.8e-05
     7 | {Miami, FL}            | {f,f} |    0.0014 |          6e-05
     8 | {Dallas, TX}           | {f,f} |  0.001367 |        8.8e-05
     9 | {Chicago, IL}          | {f,f} |  0.001333 |        5.1e-05
   ...
(99 rows)

這表明城市和州的最常見組合是華盛頓特區(qū)，實際頻率（在樣本中）約為0.35%。 組合的基本頻率（根據(jù)簡單的每列頻率計算）僅為0.0027％，導(dǎo)致兩個數(shù)量級的低估。

建議僅在實際在條件中一起使用的列的組合上創(chuàng)建MCV統(tǒng)計對象，對于這些組合，錯誤估計組數(shù)會導(dǎo)致糟糕的執(zhí)行計劃。 否則，只會浪費ANALYZE和規(guī)劃時間。