PostgreSQLの統計情報コレクタ主要ビューまとめ

# 統計情報コレクタとは
PostgreSQLの統計情報コレクタはサーバの活動状況に関する情報を収集し、報告するサブシステムです。
アクセス統計情報は稼働統計情報や実行時統計情報とも呼ばれ、サーバ全体の活動状況の統計が収集されます。
アクセス統計情報を参照するためには定義済みのビューや関数を利用します。

# アクセス統計情報一覧
## 標準統計情報ビュー
### pg_stat_activity
サーバプロセス単位にクエリの状態やプロセスの現在の活動情に関連した情報を表示します。
主に問題が発生しているクエリやプロセスを特定することを目的に利用します。

【SELECT結果サンプル】

```
postgres=# select pid, query_start, query, state from pg_stat_activity ;
 pid |          query_start          |                             query                             | state  
-----+-------------------------------+---------------------------------------------------------------+--------
  63 |                               |                                                               | 
  65 |                               |                                                               | 
  83 | 2020-12-12 02:45:10.656851+00 | select pid, query_start, query, state from pg_stat_activity ; | active
  61 |                               |                                                               | 
  60 |                               |                                                               | 
  62 |                               |                                                               | 
(6 rows)
```

## pg_stat_database
データベース毎の統計情報を表示します。
各テーブルやインデックスの細かな利用状況の把握にはpg_stat_user_table等の別ビューを利用することになります。
また、`blks_hit`列と`blks_read`列の値からキャッシュヒット率を算出可能
キャッシュヒット率算出式： `blks_hit/(blks_hit + blks_read)`

【SELECT結果サンプル】

```
postgres=# select * from pg_stat_database;
 datid |  datname  | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | checksum_failures | checksum_last_failure | blk_read_time | blk_write_time |          stats_reset          
-------+-----------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+-------------------+-----------------------+---------------+----------------+-------------------------------
     0 |           |           0 |           0 |             0 |        14 |      303 |          137 |          64 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |                   |                       |             0 |              0 | 2020-12-12 02:40:13.486843+00
 13408 | postgres  |           1 |          52 |             2 |       239 |     3476 |        23272 |        1300 |           35 |           2 |           0 |         0 |          0 |          0 |         0 |                   |                       |             0 |              0 | 2020-12-12 02:40:13.486805+00
     1 | template1 |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |                   |                       |             0 |              0 | 
 13407 | template0 |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |                   |                       |             0 |              0 | 
(4 rows)
```

## pg_stat_bgwriter
チェックポイントやバックグラウンドライタに関するデータベースクラスタ全体の統計情報を表示します。
パラメータチューニングの指標値として利用可能です。
`checkpoints_req`の値が大きい場合はチェックポイントが頻発している可能性があるため、
`checkpoint_segments`の増加の検討が必要です。

【SELECT結果サンプル】

```
postgres=# select * from pg_stat_bgwriter ;
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc |          stats_reset          
-------------------+-----------------+-----------------------+----------------------+--------------------+---------------+------------------+-----------------+-----------------------+---------------+-------------------------------
                 4 |               0 |                  3401 |                    1 |                 32 |             0 |                0 |               2 |                     0 |           256 | 2020-12-12 02:39:55.072841+00
(1 row)
```

## pg_stat_all_tables
テーブルあたり1行の形式で、テーブルへのアクセス統計情報を表示します。
テーブルスキャン1回分の読み取り行数、バキュームの対象量を確認可能です。
また、HOT更新の比率も確認可能です。
`n_tup_hot_upd/N_tup_upd`

`pg_stat_all_tables`と似たビューとして、`pg_stat_sys_tables`や`pg_stat_user_tabls`がありますが、これらのビューの違いは検索条件のみです。
`pg_stat_sys_tables`と`pg_stat_user_tables`の内容は全て
`pg_stat_all_tables`に内包されます。

【SELECT結果サンプル】

```
postgres=# select * from pg_stat_all_tables ;
 relid |     schemaname     |         relname         | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count 
-------+--------------------+-------------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------+-----------------+--------------+------------------+--------------+------------------+---------------+-------------------
  4171 | pg_toast           | pg_toast_1247           |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  4151 | pg_toast           | pg_toast_1417           |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
 13253 | information_schema | sql_languages           |        0 |            0 |          |               |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  3592 | pg_catalog         | pg_shseclabel           |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
 13270 | pg_toast           | pg_toast_13268          |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  3603 | pg_catalog         | pg_ts_config_map        |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  3079 | pg_catalog         | pg_extension            |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  2830 | pg_toast           | pg_toast_2604           |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
 13243 | information_schema | sql_features            |        0 |            0 |          |               |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
   826 | pg_catalog         | pg_default_acl          |        0 |            0 |        2 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
・・・
```

## pg_statio_all_tables
テーブルあたり1行の形式でブロック単位のI/Oに関する統計情報を表示します。
基本的に`*_blks_read`はディスク、`*_blks_hit`はメモリ読み込みを意味するので、
`*_blks_read`が低いほど性能上は有利です。
ただし、`*_blks_read`はOSシステムキャッシュから読み込まれた場合であってもカウントアップされるので、
必ずしもディスクアクセス量をそのまま表示しているわけではないことに注意が必要です。

【SELECT結果サンプル】

```
postgres=# select * from pg_statio_all_tables;
 relid |     schemaname     |         relname         | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+--------------------+-------------------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
   826 | pg_catalog         | pg_default_acl          |              0 |             0 |             1 |            3 |               0 |              0 |              0 |             0
 13263 | information_schema | sql_parts               |              0 |             0 |               |              |               0 |              0 |              0 |             0
  3118 | pg_catalog         | pg_foreign_table        |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
  3596 | pg_catalog         | pg_seclabel             |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
  2603 | pg_catalog         | pg_amproc               |              3 |            66 |             3 |          175 |                 |                |                |              
  4165 | pg_toast           | pg_toast_3350           |              0 |             0 |             0 |            0 |                 |                |                |              
・・・
```

## pg_stat_all_indexes
インデックス毎のアクセスに関する統計情報を表示します。
`idx_tup_read`列の値は該当のインデックスが利用された際に取得したエントリ数だけ加算されますが、
`idx_tip_fetch`列の値は`BitmapIndexScan`として利用された場合には加算されません。


【SELECT結果サンプル】

```
postgres=# select * from pg_stat_all_indexes;
 relid | indexrelid | schemaname |         relname         |                 indexrelname                  | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------------------+-----------------------------------------------+----------+--------------+---------------
 16387 |      16389 | pg_toast   | pg_toast_16384          | pg_toast_16384_index                          |        0 |            0 |             0
  4159 |       4160 | pg_toast   | pg_toast_2600           | pg_toast_2600_index                           |        0 |            0 |             0
  2830 |       2831 | pg_toast   | pg_toast_2604           | pg_toast_2604_index                           |        0 |            0 |             0
  4161 |       4162 | pg_toast   | pg_toast_3456           | pg_toast_3456_index                           |        0 |            0 |             0
  2832 |       2833 | pg_toast   | pg_toast_2606           | pg_toast_2606_index                           |        0 |            0 |             0
  4143 |       4144 | pg_toast   | pg_toast_826            | pg_toast_826_index                            |        0 |            0 |             0
  2834 |       2835 | pg_toast   | pg_toast_2609           | pg_toast_2609_index                           |        0 |            0 |             0
  4145 |       4146 | pg_toast   | pg_toast_3466           | pg_toast_3466_index                           |        0 |            0 |             0
  4147 |       4148 | pg_toast   | pg_toast_3079           | pg_toast_3079_index                           |        0 |            0 |             0
  4149 |       4150 | pg_toast   | pg_toast_2328           | pg_toast_2328_index                           |        0 |            0 |             0
  4151 |       4152 | pg_toast   | pg_toast_1417           | pg_toast_1417_index                           |        0 |            0 |             0
  4153 |       4154 | pg_toast   | pg_toast_3118           | pg_toast_3118_index                           |        0 |            0 |             0
  4155 |       4156 | pg_toast   | pg_toast_3394           | pg_toast_3394_index                           |        0 |            0 |             0
  4157 |       4158 | pg_toast   | pg_toast_2612           | pg_toast_2612_index                           |        0 |            0 |             0
  4163 |       4164 | pg_toast   | pg_toast_2615           | pg_toast_2615_index                           |        0 |            0 |             0
  4165 |       4166 | pg_toast   | pg_toast_3350           | pg_toast_3350_index                           |        0 |            0 |             0
  4167 |       4168 | pg_toast   | pg_toast_3256           | pg_toast_3256_index                           |        0 |            0 |             0
  2836 |       2837 | pg_toast   | pg_toast_1255           | pg_toast_1255_index                           |        0 |            0 |             0
  2838 |       2839 | pg_toast   | pg_toast_2618           | pg_toast_2618_index                           |        7 |           19 |            19
  3598 |       3599 | pg_toast   | pg_toast_3596           | pg_toast_3596_index                           |        0 |            0 |             0
  2840 |       2841 | pg_toast   | pg_toast_2619           | pg_toast_2619_index                           |        1 |            1 |             1
・・・
```

## pg_statio_all_indexes
インデックス毎のI/Oに関する統計情報を表示します。
`pg_statio_user_indexes`と`pg_statio_sys_indexes`も同じ情報を保持します。

【SELECT結果サンプル】

```
postgres=# select * from pg_statio_all_indexes;
 relid | indexrelid | schemaname |         relname         |                 indexrelname                  | idx_blks_read | idx_blks_hit 
-------+------------+------------+-------------------------+-----------------------------------------------+---------------+--------------
  2600 |       2650 | pg_catalog | pg_aggregate            | pg_aggregate_fnoid_index                      |             2 |            1
  2601 |       2651 | pg_catalog | pg_am                   | pg_am_name_index                              |             0 |            0
  2601 |       2652 | pg_catalog | pg_am                   | pg_am_oid_index                               |             0 |            0
  2602 |       2653 | pg_catalog | pg_amop                 | pg_amop_fam_strat_index                       |             3 |           20
・・・
```


## 各標準統計ビューのクエリについて
各標準統計ビューが実際にどのようなクエリを発行しているかは`pg_views`の`definition`列で確認できます。


【SELECT結果サンプル】

```
postgres=# select * from pg_views where viewname = 'pg_stat_user_tables';
 schemaname |      viewname       | viewowner |                                                                             definition                                                                             
------------+---------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 pg_catalog | pg_stat_user_tables | postgres  |  SELECT pg_stat_all_tables.relid,                                                                                                                                 +
            |                     |           |     pg_stat_all_tables.schemaname,                                                                                                                                +
            |                     |           |     pg_stat_all_tables.relname,                                                                                                                                   +
            |                     |           |     pg_stat_all_tables.seq_scan,                                                                                                                                  +
            |                     |           |     pg_stat_all_tables.seq_tup_read,                                                                                                                              +
            |                     |           |     pg_stat_all_tables.idx_scan,                                                                                                                                  +
            |                     |           |     pg_stat_all_tables.idx_tup_fetch,                                                                                                                             +
            |                     |           |     pg_stat_all_tables.n_tup_ins,                                                                                                                                 +
            |                     |           |     pg_stat_all_tables.n_tup_upd,                                                                                                                                 +
            |                     |           |     pg_stat_all_tables.n_tup_del,                                                                                                                                 +
            |                     |           |     pg_stat_all_tables.n_tup_hot_upd,                                                                                                                             +
            |                     |           |     pg_stat_all_tables.n_live_tup,                                                                                                                                +
            |                     |           |     pg_stat_all_tables.n_dead_tup,                                                                                                                                +
            |                     |           |     pg_stat_all_tables.n_mod_since_analyze,                                                                                                                       +
            |                     |           |     pg_stat_all_tables.last_vacuum,                                                                                                                               +
            |                     |           |     pg_stat_all_tables.last_autovacuum,                                                                                                                           +
            |                     |           |     pg_stat_all_tables.last_analyze,                                                                                                                              +
            |                     |           |     pg_stat_all_tables.last_autoanalyze,                                                                                                                          +
            |                     |           |     pg_stat_all_tables.vacuum_count,                                                                                                                              +
            |                     |           |     pg_stat_all_tables.autovacuum_count,                                                                                                                          +
            |                     |           |     pg_stat_all_tables.analyze_count,                                                                                                                             +
            |                     |           |     pg_stat_all_tables.autoanalyze_count                                                                                                                          +
            |                     |           |    FROM pg_stat_all_tables                                                                                                                                        +
            |                     |           |   WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
(1 row)
```


## 参考
- [postgresqlドキュメント](https://www.postgresql.jp/document/12/html/monitoring-stats.html)
- [LPI-Japan OSS-DB Gold 認定教材 PostgreSQL 高度技術者育成テキスト](https://www.amazon.co.jp/LPI-Japan-OSS-DB-Gold-PostgreSQL-%E9%AB%98%E5%BA%A6%E6%8A%80%E8%A1%93%E8%80%85%E8%82%B2%E6%88%90%E3%83%86%E3%82%AD%E3%82%B9%E3%83%88/dp/B00P4WD4QG/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&crid=2DNZ3L0MXCFG1&dchild=1&keywords=oss-db+gold&qid=1607741375&sprefix=OSS-DB+%2Caps%2C240&sr=8-1)