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

postgresql 進程模型

postgresql中的兩種“統計信息”

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

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

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

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

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系統表的詳細描述如下

統計信息的更新

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關於自動更新的採樣範圍

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欄位上某些條件值分佈的特別不均勻（嚴重傾斜）的情況下，創建這麼一個統計信息之後，可以指導執行計劃在遇到類似的查詢條件之後，做出更加準確的預估。

p1*p2*table_rowcount或者P1*P2^1/2*table_rowcount等“猜測”的方式。
詳細信息參考筆者的上一篇譯文：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}

20200722更新：
因為統計信息作為關係資料庫系統中最複雜的組件之一，看到相關係度的計算原理和公式之後，就開始想念我的高數老師……,神似貝葉斯定理？？？
已經開始放棄原理的探索了……，開始無條件相信源碼中這部分功能實現的人。
感慨，在非關係資料庫中，相比關係數據是是沒有也不需要如此複雜的組件的，因此在某些場景下，簡單就是美。

Vacuum和Analyze