PostgreSQL · 特性分析 · 統計資訊計算方法

一條sql在pg中的執行過程是：

postgresql的sql優化、執行方式為代價模型。而這裡的各路徑的代價計算，則是依賴于系統表中的統計資訊。那麼這些統計資訊如何得來的？就是這裡要讨論的問題。

postgresql是catalog-driven型的資料庫，引擎運作過程中所有所需的資料、資訊都存放在系統表中，統計資訊不例外。這些統計資訊，則是通過sql指令vacuum和analyze分别寫入pg_class和pg_statistic中的。

<a href="http://www.postgresql.org/docs/9.1/static/sql-analyze.html" target="_blank">參考官方文檔analyze</a>

pg_class的表結構如下：

這裡比較關注的是relpages和reltuples兩個字段，分别表示這張表占了多少磁盤頁和行數。其中行數是估計值。而這兩個字段的值是通過vacuum、analyze（或create index)來更新的。

<a href="http://www.postgresql.org/docs/9.4/static/catalog-pg-class.html" target="_blank">參考官方文檔pg_class</a>

pg_statistic的表結構如下：

這裡的stanullfrac、stadistinct、stakindn、staopn、stanumbersn、stavaluesn等是我們所關注的值。其中：

stakindn

用于表示後面number、values所表示的資料用途，被用于生成pg_stats。如1則表示是mcv的值；2表示直方圖（histogram）的值；3表示相關性（correlation）的值等。kind的取值範圍：1~99，內核占用；100~199，postgis占用；200~299，esri st_geometry幾何系統占用；300~9999，公共占用。

staopn

用于表示該統計值支援的操作，如’=’或’&lt;’等。

stanumbersn

如果是mcv類型（即kind=1），那麼這裡即是下面對應的stavaluesn出現的機率值，即mcf。

stavaluesn

anyarray類型的資料，內核特殊類型，不可更改。是統計資訊的值部分，與kind對應。如kind=2的時候，則這裡的值表示直方圖。

這些值的更新都是通過analyze完成，n的取值是[1, 5]，由pg內核決定的。将來有可能更多。

<a href="http://www.postgresql.org/docs/9.4/static/catalog-pg-statistic.html" target="_blank">參考官方文檔pg_statistic</a>

vacuum和analyze的執行可以通過兩種方式來觸發，一種是db使用者執行，如定時腳本或人工執行；一種是autovacuum。兩個操作持有相同類型的鎖shareupdateexclusivelock，與ddl互斥。

autovacuum是postgresql提供的一個deamon程序，會在一定時間內或者dml多到一定程度時觸發vacuum或analyze。這裡的一定時間和一定程度可以通過autovacuum的一系列配置實作，如autovacuum_naptime、autovacuum_max_workers 、autovacuum_vacuum_threshold等；且vacuum和analyze的觸發算法和依賴參數并不盡相同。

vacuum本身除了負責更新relpages和reltuples等之外，最主要的是：

回收被更新和删除占用的空間

回收事務id，當機老的事務id，以防止這部分老資料丢失

而analyze則主要是收集統計資訊，并存儲到pg_statistic表中。其主要的步驟如下：

以共享排他鎖（shareupdateexclusivelock）打開表

這個鎖會與ddl之上所有的操作互斥，詳細的互斥關系如下，其值越大鎖粒度越大：

選擇采樣函數

如果是普通表或者物化視圖，則采樣函數采用acquire_sample_rows；如果是外表，那麼外表所用的插件需要fdw的實作，如postgres_fdw的postgresanalyzeforeigntable。

檢查表的每個字段

在真正開始分析之前，先檢查每個字段，并傳回vacattrstats結構體。後面所有的分析都将在此檢查之上進行。

vacattrstats結構體如下：

具體的針對字段檢查的步驟如下：

确定這個字段是否可以分析，如果不可以，則傳回null。

一般有兩種情況緻使這個字段不進行分析：字段已被删除（已删除的字段還存在于系統表中，隻是作了标記）；使用者指定了字段。

擷取資料類型，并決定針對該類型的采樣資料量和統計函數

不同的類型，其分析函數也不同，比如array_typanalyze。如果該類型沒有對應的分析函數，則采用标準的分析函數std_typanalyze。

以标準分析函數為例，其确定了兩個地方：采樣後用于統計的函數（compute_scalar_stats或compute_minimal_stats，和采樣的記錄數（現在預設是300 * 100）。

索引

索引在pg中，是以與表類似的方式存在的。當analyze沒有指定字段，或者是繼承表的時候，也會對索引進行統計資訊的計算。以accesssharelock打開該表上所有的鎖，同樣的檢查索引的每個字段是否需要統計、如何統計等。

采樣

先擷取所需資料量的檔案塊

周遊這些塊，根據vitter算法，選擇出所需資料量的記錄時以頁為機關，盡量讀取該頁中所有的完整記錄，以減少io；按照實體存儲的位置排序，後續會用于計算相關性（correlation）。

這裡的采樣并不會處理事務中的記錄，如正在插入或删除的記錄。但如果删除或插入操作是在目前analyze所在的事務執行的，那麼插入的是被記為live_tuples并且加入統計的；删除的會被記為dead_tuples而不加入統計。

由此會可能産生兩個問題：

當有另外一個連接配接正好也在進行統計的時候，自然會産生不同的統計值，且後來者會直接覆寫前者。當統計期間有較多的事務在執行，且很快結束，那麼結果與實際情況可能有點差别。

當有超長的事務出現，當事務結束時，統計資訊與實際情況可能有較大的差距。

以上兩種情況，重複執行analyze即可。但有可能因統計資訊不準确導緻的執行計劃異常而造成短時間的性能波動，需要注意！這裡也說明了長事務的部分危害。

統計、計算

在擷取到相應樣本資料後，針對每個字段分别進行分析。

首先會依據目前字段的值，對記錄進行排序。因在取出樣本資料的時候，按照tuple在磁盤中的位置順序取出的，是以對值進行排序後即可計算得出相關性。另外，在排序後，也更容易計算統計值的頻率，進而得出mcv和mcf。這裡采用的快速排序！

之後，會根據每個值進行分析：

如果是null，則計數

null機率的計算公式是：stanullfrac = null_number / sample_row_number。

如果是變長字段，如text等，則需要計算平均寬度

計算出現最多的值，和相應頻率

基數的計算

該部分計算稍微複雜一些，分為以下三種情況：

當采樣中的值沒有重複的時候，則認為所有的值唯一，stadistinct = -1。

當采樣中的每個值都出現重複的時候，則認為基數有限，則stadistinct = distinct_value_number

當采樣中的值中，存在有唯一值并且存在不唯一值的時候，則依據以下的公式（by haas and stokes in ibm research report rj 10025）：

其中，n是指所有的記錄數，即pg_class.reltuples；n是指sample_row_number，即采樣的記錄數；f1則是隻出現一次的值的資料；d則是采樣中所有的值的數量。

mcv / mcf

并不是所有采樣的值都會被列入mcv/mcf。首先是如果可以，則将所有采樣的記錄放到mcv中，如表所有的記錄都已經取作采樣的時候；其次，則是選取那些出現頻率超過平均值的值，事實上是超過平均值的25%；那些出現頻率大于直方圖的個數的倒數的時候等。

直方圖

計算直方圖，會首先排除掉mcv中的值。

其計算公式相對比較簡單，如下：

values[(i * (nvals - 1)) / (num_hist - 1)]

i指直方圖中的第幾列；nvals指目前還有多少個值；num_hist則指直方圖中還有多少列。計算完成後，kind的值會被置為2。

到此，采樣的統計基本結束。

完成采樣的計算後，通過內部函數更新相關的統計資訊到pg_statistic，更新relpages和totale rows到pg_class中。即完成了一次統計資訊的收集。