對于sql優化，除了索引之外，執行計劃和統計信息是無法繞開的一個話題，如果sql優化（所有的RDBMS）脫離了統計信息的話就少了一個為什么的過程，味道就感覺少了一大半。
剛接觸Postgresql，粗淺地學習總結一下Postgresql相關的統計信息。

postgresql 進程模型

開始之前，有必要了解一下postgresql的進程結構。

postgresql 進程模型，與MySQL或者SQLServer的單進程多線程機制不同，Postgresql為多進程模型，每個進負責特定的任務，同時每個進程自身有各自的運行或者喚醒機制。
相比單進程多線程的MySQL，各個子線程寄宿與主進程中的方式相比，postgresql這一點可以直觀地來描述每個進程以及其作用。
如下是一個Postgresql實例的所有進程信息，這里僅關注autovacuum lancher和stat collector，后文會提交到這兩個進程。

另：MySQL的單進程多線程服務模型，注：mysql_safe進程僅僅起到mysqld進程carsh后進程喚醒的作用，其他后臺功能以線程的形式寄宿在mysqld主進程。

SQLServer的進程就太熟悉了，默認進程名稱就是實例名，默認MSSQL

postgresql中的兩種“統計信息”

在MySQL或者SQLServer中，統計信息這個術語是描述數據分布狀態的一種信息，由后臺線程或者人為命令觸發更新。
與SQLServer或者MySQL中的一些術語不同，postgresql中的stats分為兩類如下兩類
1，第一類是類似于SQLServer或者MySQL中的性能計數器和系統表，這些數據用來描述數據庫的負載，或者數據庫對象使用情況，
       SQLServer的性能計數器和各種系統表和DMV，或者MySQL中的performance_schema中的的信息
2，第二類是類似于SQLServer或者MySQL中的統計信息直方圖，用來描述數據的分布，為優化器生成執行計劃提供依據。
    這一類統計信息基本上等價于SQLServer或者MySQL中用來指導執行計劃的統計信息
    “統計信息”在Postgresql中的含義，與SQLServer或者MySQL相比，第一類信息的稱呼上存在一些不同。
參考這里：https://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql

• postgresql中的負載指標“統計信息”（Monitoring stats）

負載指標由上文提到的stats collector進程來實時收集更新。PostgreSQL的統計數據收集器是一個支持收集和報告服務器活動信息的子系統。
目前，收集器可以計算對磁盤塊和單行項中的表和索引的訪問次數。
它還跟蹤每個表中的總行數，以及關于vacuum的信息，并分析每個表的操作。
同時還可以記錄基于sql語句執行的代價信息。這部分與該主題關系不大，就不展開詳述。

• postgresql中的數據分布狀態描述“統計信息”（Data distribution stats）

對于第二類統計信息，也即用來描述數據分析狀態的信息，有兩種生成方式
1，后臺進程autovacuum lancher會在特定的情況下觸發統計信息的更新，這一點類似于SQLServer統計信息的自動更新機制。
2，手動執行analyze table，這一點類似于在SQLServer中手動執行統計信息更新。
　比如update statistics  *** (***) with sample 30 percent，或者MySQL中的analyze table  table_name
　此外，基于postgresql的MVCC機制生成的“非活動數據”，更新可見性映射，凍結事務處理（保護老舊數據不會由于事務ID回卷或多事務ID回卷而丟失），也是由autovacuum 進程來清理，同時，還負責XID的清理工作。

有兩種VACUUM的變體：標準VACUUM和VACUUM FULL。
1，標準形式的VACUUM可以和生產數據庫操作并行運行（SELECT、INSERT、UPDATE和DELETE等命令將繼續正常工作，但在清理期間你無法使用ALTER TABLE等命令來更新表的定義）。
2，VACUUM FULL可以收回更多磁盤空間但是運行起來更慢，且vacuum full不會有后臺進程主從觸發（只能手動執行）。
另外，VACUUM FULL類似于表的重建或者說碎片整理，同時需要一個大小相當于原始表的額外空間。
要求在其工作的表上得到一個排他鎖，因此無法和對此表的其他使用并行。因此，通常管理員應該努力使用標準VACUUM并且避免VACUUM FULL。

以上信息作為鋪墊，以下簡單概述postgresql中的“統計信息”，所謂的兩種統計信息區別其實在含義上非常容易理解，同時會涉及兩種統計信息的系統表。
大概列舉一下相關的系統表以及相關參數，簡述相關的含義。
 
如下為測試demo表以及測試數據的生成

create table myschema.table_test
(
    c1 serial  primary key,
    c2 int,
    c3 varchar(100),
    c4 varchar(100),
    c5 timestamp
)

create or replace function random_string(integer)
returns text as
$body$
    select upper(array_to_string(array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM (ceil(random()*62))::int FOR 1) FROM generate_series(1, $1)), ''));
$body$
language sql volatile;

insert into myschema.table_test (c2,c3,c4,c5)
select cast(random()*500000 as int),random_string(10),random_string(10), cast( now()-'1 min'::interval * random()*500000  as timestamp )
from generate_series(1,1000000)

postgresql中關于數據庫負載的統計信息

• 庫級別信息摘要

在庫級別，pg_stat_databases用來描述描述"庫"級別的摘要信息，包括庫名，當前庫事務提交次數，回滾次數，讀寫次數，死鎖等等信息。
這些信息可以觀察到到一個庫的負載情況和健康狀況。

• 表級別的信息摘要

在表級別，由pg_stat_user_tables來描述某個具體的表中的信息，包括增刪查改的次數，數據行等摘要信息。
這些信息可以衡量一個表的冷熱程度，活躍性，以及體量以及一些analyze時間相關的信息。

pg_class 來描述表的物理存儲信息，包括數據行數，數據頁的個數。

關于postgresql中的描述數據分布統計信息

pg_stats用來描述一個表中所有的字段的數據分布信息，為執行計劃決策提供依據，與SQLServer的直方圖類似，熟悉的配方熟悉的味道，只有管理員賬號能夠訪問
類似于SQLServer的統計信息+直方圖，也即執行dbcc show_statistics(***,***)的效果，或者MySQL中的information_schema.column_statistics表中的信息（簡直一模一樣）。
這一點，SQLServer的直方圖用一種相對比較直觀的方式展示了出來。

select *  from pg_stats   WHERE tablename = 'table_test';

schemaname             | myschema
tablename              | table_test
attname                | c1
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | -1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {15,9799,20037,30372,40276,……………………,990687,999949}
correlation            | 1
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

schemaname             | myschema
tablename              | table_test
attname                | c2
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | -0.330106
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {23,4712,9677,14189,19403,………………490576,495541,499975}
correlation            | -0.00480835
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

schemaname             | myschema
tablename              | table_test
attname                | c5
inherited              | f
null_frac              | 0
avg_width              | 8
n_distinct             | -0.993476
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {"2019-08-05 14:29:35.515329","2019-08-08 19:17:14.628418",……"2020-07-14 18:25:47.515329","2020-07-17 19:40:48.015329"}
correlation            | -0.00216757
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

schemaname             | myschema
tablename              | table_test
attname                | c4
inherited              | f
null_frac              | 0
avg_width              | 11
n_distinct             | -1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {0035UXPI6A,0N8JC5OIER,1BZZAU76H5,…………ZQSMJJRFWE,ZZZYYV9TKJ}
correlation            | -0.00186405
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |


schemaname             | myschema
tablename              | table_test
attname                | c3
inherited              | f
null_frac              | 0
avg_width              | 11
n_distinct             | -1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {000XXEZ4HN,0N3GEAC1QS,…………,Z5ANIIBHDO,ZH6ZYR94CJ,ZQW7M2HZ4I,ZZZENAC3OQ}
correlation            | 0.00391295
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

Time: 1.259 ms

關于pg_stat系統表的詳細描述如下

名字	類型	引用	描述
schemaname	name	pg_namespace.nspname	包含此表的模式名字
tablename	name	pg_class.relname	表的名字
attname	name	pg_attribute.attname	這一行描述的字段的名字
inherited	bool		如果為真，那么這行包含繼承的子字段，不只是指定表的值。
null_frac	real		記錄中字段為空的百分比
avg_width	integer		字段記錄以字節記的平均寬度
n_distinct	real		如果大于零，就是在字段中獨立數值的估計數目。如果小于零， 就是獨立數值的數目被行數除的負數。 用負數形式是因為ANALYZE 認為獨立數值的數目是隨著表增長而增長； 正數的形式用于在字段看上去好像有固定的可能值數目的情況下。比如， -1 表示一個唯一字段，獨立數值的個數和行數相同。
most_common_vals	anyarray		一個字段里最常用數值的列表。如果看上去沒有啥數值比其它更常見，則為 null
most_common_freqs	real[]		一個最常用數值的頻率的列表，也就是說，每個出現的次數除以行數。 如果most_common_vals是 null ，則為 null。
histogram_bounds	anyarray		一個數值的列表，它把字段的數值分成幾組大致相同熱門的組。 如果在most_common_vals里有數值，則在這個餅圖的計算中省略。  如果字段數據類型沒有<操作符或者most_common_vals 列表代表了整個分布性，則這個字段為 null。
correlation	real		統計與字段值的物理行序和邏輯行序有關。它的范圍從 -1 到 +1 。 在數值接近 -1 或者 +1 的時候，在字段上的索引掃描將被認為比它接近零的時候開銷更少， 因為減少了對磁盤的隨機訪問。 如果字段數據類型沒有<操作符，那么這個字段為null。
most_common_elems	anyarray		經常在字段值中出現的非空元素值的列表。（標量類型為空。）
most_common_elem_freqs	real[]		最常見元素值的頻率列表，也就是，至少包含一個給定值的實例的行的分數。 每個元素頻率跟著兩到三個附加的值；它們是在每個元素頻率之前的最小和最大值， 還有可選擇的null元素的頻率。 當most_common_elems 為null時，為null）
elem_count_histogram	real[]		該字段中值的不同非空元素值的統計直方圖，跟著不同非空元素的平均值。（標量類型為空。）

 
pg_statistic是基于pg_stats的視圖，以更加友好以及可讀的方式展現統計信息，普通用戶可以訪問

統計信息的更新

1，統計信息的手動更新：analyze table_name

 　2，統計信息的自動更新

開始之前，對比SQLServer和MySQL中統計信息的自動更新的出發情況，統計信息更新是一個非常有意思的話題。
SQLServer是表中的輸入寫入（增刪改）超過閾值500 + (20 %×表數據總量)之后會自動觸發更新，以為默認情況下可以認為這是一個寫死的參數。
因為SQLServer統計信息的更新會有非常多的問題，雖然SQLServer有一個trace flag 2371
可以改變改規則，但也屬于半遮半掩的一個非開放功能對于MySQL或者postgresql，類似所有的參數都是可配置化的，因此非常透明
MySQL是innodb_stats_auto_recalc打開的情況下，增刪改的次數大于表中已有數據的10%之后主動觸發更新。

2.1 自動更新的開關
首先autovacuum開關需要打開，也即上文中提到的autovacuum lancher進程實現，在表中的數據滿足一定條件之后的定時更新　　
這里的autovacuum是這個自動化更新的開關。默認打開。

2.2關于自動更新的觸發機制
也類似于MySQL，子線程會根據上下文，存在一個工作頻率，postgresql在打開autovacuum基礎上，
autovacuum進程執行統計信息更新的喚醒頻率，以及工作線程數，依次對各個表執行并發清理，
autovacuum_naptime喚醒頻率默認為1min，autovacuum_max_workers工作線程默認為3個，被喚醒的工作線程會并發對庫中的滿足更新條件的表進行統計信息更新。

2.3關于自動更新統計信息的閾值
這里會涉及兩個參數autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor
anl_base_thresh默認值時50，anl_scale_factor默認值時0.2，這都是可配置的，而且是每個表可以獨立配置的，這里難免會想到SqlServer的這個閾值也是類似變化數量超過500 + (20 %×表數據總量)
autovacuum進程進行 vacuum 觸發條件表上增刪改的行數 >= autovacuum_vacuum_scale_factor* reltuples(表上記錄數) + autovacuum_vacuum_threshold

 　　2.4關于自動更新的采樣范圍

　　這里涉及一個default_statistics_target參數，該參數默認值是100.范圍是1~10000。
　　參考這里，提了一個非常好的問題：https://dba.stackexchange.com/questions/200136/postgresql-what-the-default-statistics-target-value-really-means
　　采樣容量：采用300×default_statistics_target=30000作為采樣的樣本默認容量，default_statistics_target是一個可配置化的參數，默認值變為100
　　這一點類似于MySQL中的STATS_SAMPLE_PAGES，也就是更新統計信息時候采樣的比例，只不過這里是page頁面的個數，也是可以基于表來配置的。
　　難免又想到Server的類似配置項，SqlServer沒有該配置項，不過一切都不是問題，可以換種方式間接使實現。
　　多扯一句，很多人在講什么SQL優化，執行計劃，索引之類的，如果拋開統計信息不談，基本上還是那種初級的手段，或者根本就沒遇到過復雜的場景。
　　上面說了，對于采樣容量，300×default_statistics_target=30000作為采樣的樣本默認容量，default_statistics_target可配置化，這一切看起來都沒有問題，其實可以仔細考慮一下。
　　對于某些大表或者數據分布不均勻的請，300×default_statistics_target是不一定夠的，對于10W行的表，采用30000的采樣容量，跟1000W行的表采用同樣的容量，統計信息得到的數據準確性差別是非常大的。
　　因此，如何根據具體的表，具體的索引結構，具體的應用場景，調整default_statistics_target這個變量就是非常有必要的了。
　　上面說了，MySQL和SQLServer可以在表級別定義一個STATS_SAMPLE_PAGES或者default_statistics_target的取樣范圍。
　　sqlserver有一個類似于與此的統計采樣百分比，update statistics  *** (***) with sample 30 percent，就是這里的30，這個值可以從0~100%，隨意指定。
　　可見，很多東西是殊途同歸的，只是外面的一層馬甲不一樣而已。（為什么往往要從一定范圍內采樣而不是100%采樣？其實腳后跟就能想明白）
　　
　　statistics采樣比例是一個可以在列級別指定的參數，范圍是0~1000，語法是：Alter table <table_name> alter column <column_name> set statistics < value from 1 to 1000 > ;
　　
　　這個字段級統計信息的屬性記錄在pg_attribute這個系統表中。
　　

2.5 統計信息最后一次更新之后的變化
統計信息更新日志，pg_stat_all_tables表存儲了所有表的最后一次更新歷史信息（last_analyza），以及最后一次更新之后數據發生的變化情況(n_mod_since_analyze)，這是一個非常因吹斯汀的數據。
上面把統計信息各種閾值，各種觸發條件七七八八地列舉的差不多了，有沒有表再回頭關注最后一次統計信息更新之后表的基數的變化？
肯定是有必要的，上面說了，即便是default_statistics_target是一個可以調整的參數，但不一定知道具體哪個值是合理的或者說是可行的。
那么，就可以觀察，在執行計劃使用統計信息做預估，出現偏差的臨界點，就需要重新收集更新統計信息了，
那么此時就可以結合pg_stat_all_tables此時舉上次收集完統計信息變化的情況，來反推autovacuum_vacuum_scale_factor這個值，從而更加科學地去設置autovacuum_vacuum_scale_factor這個因子。

　3 手工創建統計信息

開始之前現提出一個問題：為什么需要手動創建統計信息？
通常情況下，統計信息在滿足一定條件，且取樣達到一定程度之后，可以得到一個相對準確的統計信息，一切看起來都是水到渠成。
但是不排除一些個特殊情況，需要手動創建統計信息來實現預估的準確性，比如數據傾斜嚴重的情況下，又難以100%取樣（即便100%取樣，統計信息還有一個“步長”的限制），
此時手動創建統計信息，從而更好指導執行計劃的生成。
這一點SQLServer和Postgresql都是支持的，MySQL這一點是不支持的。
這個就稍微扯遠一點，SQLServer中對于select * from table where c1 = m and c2 = n這種語句，返回行數是如何預估的？
如之前提到過的，假如c1的選擇性為p1,c2的選擇性為p2，表中的總行數為table_rowcount，暫忽略索引自身以及統計信息準確性帶來的影響，以此為前提。
在SQLServer 2012中是預估返回函數是p₁*p₂*table_rowcount，
在SQLServer 2014或者更高版本中，這個算法發生了變化，是P₁*P₂^1/2*table_rowcount
簡單demo一下，假設在c2和c3字段上某些條件值分布的特別不均勻（嚴重傾斜）的情況下，創建這么一個統計信息之后，可以指導執行計劃在遇到類似的查詢條件之后，做出更加準確的預估。

以上同理，Postgresql中也可以創建類似的統計信息，但又不完全相同，postgresql 更強調的是兩個或多個字段之間的關聯性。
對于預估的返回行數是會直接影響執行計劃的，這個結果是比較重要的，但是SQLServer中不管是第一種算法或者是第二種算法，都是基于一種“猜測”的方式，而沒有具體依賴于數據的關聯性。
那么，Postgresql恰恰就改進了這一點
demo如下
CREATE statistics if not exists statis_c2_c5 (dependencies) ON c2, c5 FROM myschema.table_test;
系統表pg_statistic_ext中存放了改擴展統計信息。
對于多列（多個字段）統計信息，會計算列之間的依賴度來生成統計信息，而非SQLServer的p1*p2*table_rowcount或者P1*P2^1/2*table_rowcount等“猜測”的方式。


當然這個多列統計信息之間的依賴性，涉及的東西太多，如果準確地計算相關列之間的依賴(stdkind的f算法)是一個難題，這其中的算法筆者目前也尚不清楚，需要進一步的挖掘。

Vacuum和Analyze

統計信息的更新由vacuum進程實現，也可以有手動analyze實現，那么，Vacuum和Analyze的區別是什么？
autovcuum lancher進程中會定期的執行autovcuum ，Analyze是autovcuum其中的一步，會主動被觸發。
Vacuum是Analyze的超集，Vacuum包含一系列的清理、表的重建、以及表的統計信息更新，換句話說就是，vacuum包含但不限于analyze table來更新統計信息。
參考這里：Using ANALYZE to optimize PostgreSQL queries
Vacuuming isn't the only periodic maintenance your database needs. You also need to analyze the database so that the query planner has table statistics it can use when deciding how to execute a query. Simply put: Make sure you're running ANALYZE frequently enough, preferably via autovacuum. And increase the default_statistics_target (in postgresql.conf) to 100.