PostgreSQL統計信息的幾個重要視圖

1、pg_stat_database

yzs=# select *from pg_stat_database;
-[ RECORD 1 ]--+------------------------------
datid          | 13156                            #數據庫的oid
datname        | postgres                         #數據庫名
numbackends    | 0                                #訪問當前數據庫的連接數量
xact_commit    | 2357                             #該數據庫事務提交總量：和下面的rollback和作為TPS統計
xact_rollback  | 17                               #該數據庫事務rollback總量，如果特別多，需要看業務是否有問題了
blks_read      | 1946                             #總磁盤物理讀的塊數，這里的read可能是從 cache中讀取，如果很高需要結合blk_read_time看是否真的存在從磁盤讀取的情況
blks_hit       | 103625                           #從shared buffer命中塊數
tup_returned   | 1413113                          #對于表來說，是全表掃描的行數；對于索引是通過索引返回的索引行數，如果這個值明顯大于tup_fetched，說明當前數據庫存在大量的全表掃描。查看執行計劃，這個是databas全局級別的
tup_fetched    | 36041                            #指通過索引返回的行數
tup_inserted   | 104                              #插入的行數
tup_updated    | 0                                #更新的行數
tup_deleted    | 19                               #刪除的行數
conflicts      | 0                                #與恢復沖突取消的查詢次數，只會在備機上發生
temp_files     | 0                                #產生臨時文件的數量，如果這個值很高，需要調大work_mem
temp_bytes     | 0                                #臨時文件的大小
deadlocks      | 0                                #死鎖的數量，如果這個值很大說明業務邏輯有問題
blk_read_time  | 0                                #數據庫中花費在讀取文件的時間，這個值很高說明內存較小，需要頻繁從磁盤讀入數據文件
blk_write_time | 0                                #數據庫中花費在寫數據文件的時間，pg中臟頁一般寫入page cache，如果這個值較高，則說明cache較小，操作系統的cache需要更積極的寫入
stats_reset    | 2019-02-11 23:42:37.526743-08    #統計信息重置的時間

通過pg_stat_database可以大概了解數據庫的歷史情況。
比如tup_returned值明顯大于tup_fetched，歷史SQL語句很多是全表掃描，存在沒有使用索引的SQL，可結合pg_stat_statments查找慢SQL，也可結合pg_stat_user_table找全表掃描次數和行數最多的表；
通過看tup_updated很高，可以說明數據庫有頻繁的更新，這個時候需要關注vaccum相關的指標和長事務，如果沒有及時進行垃圾回收，會引起表膨脹；
temp_files較高說明存在很多排序，hash,或者聚合這種操作，可以增大work_mem減少臨時文件的產生，并且同時這些操作的性能也會有較大的提升。

2、pg_stat_user_tables

yzs=# select *from pg_stat_user_tables;
-[ RECORD 1 ]-------+------------------------------
relid               | 16440        #表oid
schemaname          | public       #模式名
relname             | t1           #表名
seq_scan            | 50           #這個表進行全表掃描的次數
seq_tup_read        | 1867763      #全表掃描的數據行數，如果這個值很大說明操作這個表的SQL語句很可能是全表掃描，需要結合執行計劃分析
idx_scan            |              #索引掃描的次數
idx_tup_fetch       |              #通過索引掃描返回的行數
n_tup_ins           | 1130502      #插入的數據行數
n_tup_upd           | 0            #更新的數據行數
n_tup_del           | 81920        #刪除的數據行數
n_tup_hot_upd       | 0            #hot update的數據行數，這個值與n_tup_upd接近說明更新性能較好，不需要更新索引
n_live_tup          | 655366       #活的行數量
n_dead_tup          | 0            #死記錄個數
n_mod_since_analyze | 6            #上次analyze的實際
last_vacuum         | 2019-04-07 00:22:00.955542-07 #上次手動vacuum的實際
last_autovacuum     |              #上次autovacuum的實際
last_analyze        |              #上次analyze時間
last_autoanalyze    | 2019-04-07 00:26:07.668391-07 #上次自動analyze時間
vacuum_count        | 2            #vacuum次數
autovacuum_count    | 0            #自動vacuum次數
analyze_count       | 0            #analyze次數
autoanalyze_count   | 10           #自動analyze次數

通過查詢pg_stat_user_tables，可以基本清除哪些表的全表掃描次數較多，表中DML哪種操作多，也可以了解垃圾數據的數量。

3、pg_stat_user_indexes

yzs=# select *from pg_stat_user_indexes;
-[ RECORD 1 ]-+----------
relid         | 16447      #相關表的oid
indexrelid    | 16450      #索引的oid
schemaname    | public     #模式名
relname       | t3         #表名
indexrelname  | t3_id_idx  #索引名
idx_scan      | 0          #通過索引掃描的次數，如果該值很小，說明該索引很少被用到，可以考慮刪除
idx_tup_read  | 0          #通過任意索引方法返回的索引行數
idx_tup_fetch | 0          #通過索引方法返回的數據行數

可以知道當前哪些索引頻繁使用，哪些是無效索引。無效索引可以刪除掉，減少磁盤空間的使用和提升insert、delete、update的性能。

4、pg_statio_user_tables

yzs=# select *from pg_statio_user_tables;
-[ RECORD 1 ]---+--------
relid           | 16447    
schemaname      | public
relname         | t3
heap_blks_read  | 1    #從page cache或磁盤讀取表的塊數
heap_blks_hit   | 1    #從shared buffer命中的塊數
idx_blks_read   | 0    #從page cache或磁盤讀取的索引的塊數
idx_blks_hit    | 0    #從shared buffer命中的索引塊數
toast_blks_read |      #從page cache或磁盤讀取的toast表的塊數
toast_blks_hit  |      #在shared buffer中命中toast表的塊數
tidx_blks_read  |      #從page cache或者磁盤中讀入的toast表索引的塊數
tidx_blks_hit   |      #在shared buffer中命中toast表索引的塊數

如果heap_blks_read、idx_blks_read很高，說明shared buffer較小，存在頻繁從磁盤或者page cache讀取到shared buffer中命中toast表的塊數。

5、?pg_stat_bgwriter

yzs=# select *from pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 206     #指超過checkpoint_timeout的時間后觸發的檢查點次數
checkpoints_req       | 8       #手動觸發checkpoint或者因為WAL文件數量達到max_wal_size時也會增加，如果這個值大于checkpoints_req說明checkpoint_timeout設置的不合理
checkpoint_write_time | 306582  #從shared buffer 中write到page cache花費的時間
checkpoint_sync_time  | 367     #checkpoint調用fsync將臟數據刷到磁盤花費的時間，如果這個值很長，容易造成IO抖動，需要增加checkpoint_timeout或者checkpoint_completion_target
buffers_checkpoint    | 6671    #通過checkpoint寫入臟塊的數量
buffers_clean         | 0       #通過bgwriter寫入塊的數量
maxwritten_clean      | 0       #bgwriter超過bgwriter_lru_maxpages時停止的次數，如果這個值很高，需要增加bgwriter_lru_maxpages
buffers_backend       | 7953    #通過backend寫入的塊數量
buffers_backend_fsync | 0       #backend需要fsync的次數
buffers_alloc         | 11613   #被分配的緩沖區數量
stats_reset           | 2019-02-11 23:42:35.273758-08

通過這個視圖，可以判斷checkpoint以及max_wal_size是否合理