對于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）
比如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，
大概列舉一下相關的系統表以及相關引數，簡述相關的含義， 如下為測驗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[] | 該欄位中值的不同非空元素值的統計直方圖，跟著不同非空元素的平均值，（標量型別為空，） |
統計資訊的更新
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關于自動更新的采樣范圍
參考這里，提了一個非常好的問題：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中是預估回傳函式是p1*p2*table_rowcount，
在SQLServer 2014或者更高版本中，這個演算法發生了變化，是P1*P21/2*table_rowcount
簡單demo一下，假設在c2和c3欄位上某些條件值分布的特別不均勻（嚴重傾斜）的情況下，創建這么一個統計資訊之后，可以指導執行計劃在遇到類似的查詢條件之后，做出更加準確的預估，
詳細資訊參考筆者的上一篇譯文：https://www.cnblogs.com/wy123/p/13306673.html
需要注意的是，在創建完統計資訊之后，且執行analyze table，且指定的列數與統計資訊中的一致的時候，才會生成該統計資訊，
db01=# drop statistics if exists statis_c2_c5; DROP STATISTICS db01=# create statistics if not exists statis_c2_c5(dependencies)on c2,c5 from myschema.table_test; CREATE STATISTICS db01=# create statistics if not exists statis_c2_c5(dependencies)on c2,c5 from myschema.table_test; NOTICE: statistics object "statis_c2_c5" already exists, skipping CREATE STATISTICS db01=# analyze myschema.table_test(c2,c5); ANALYZE db01=# select * from pg_statistic_ext where stxname like '%statis_c2_c5%'; -[ RECORD 1 ]---+----------------------------------------- stxrelid | 18730 stxname | statis_c2_c5 stxnamespace | 2200 stxowner | 10 stxkeys | 2 5 stxkind | {f} stxndistinct | stxdependencies | {"2 => 5": 0.817946, "5 => 2": 0.998274}當然這個多列統計資訊之間的依賴性，涉及的東西太多，如果準確地計算相關列之間的依賴(stdkind的f演算法)是一個難題，
這其中的演算法到底是如何實作的，原理是什么，計算出來結果的準確性如何等等一系列問題，筆者目前也尚不清楚，需要進一步的挖掘（備注：20200722已放棄，如下），
20200722更新：
因為統計資訊作為關系資料庫系統中最復雜的組件之一，看到相關系度的計算原理和公式之后，就開始想念我的高數老師……,神似貝葉斯定理？？？
已經開始放棄原理的探索了……，開始無條件相信原始碼中這部分功能實作的人，
感慨，在非關系資料庫中，相比關系資料是是沒有也不需要如此復雜的組件的，因此在某些場景下，簡單就是美，
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.參考鏈接： http://www.postgres.cn/docs/9.4/monitoring-stats.html
https://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql
http://www.postgres.cn/docs/10/routine-vacuuming.html
https://www.postgresql.org/docs/12/planner-stats.html
https://www.postgresql.org/docs/11/runtime-config-query.html
https://pganalyze.com/blog/postgresql-log-monitoring-101-deadlocks-checkpoints-blocked-queries
https://www.cybertec-postgresql.com/en/setting-postgresql-configuration-parameters/
https://coderbook.com/@marcus/postgres-autovacuum-vacuum-and-analyze-explained/
https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1158.html
標籤：PostgreSQL
上一篇：Oracle重做日志和日志挖掘