PostgreSQL 中的性能视图
对于 DBA 来讲，有时候需要快速定位问题，找到具体是那一条 SQL 拖慢了整个系统，具体是哪些因素导致查询速度变慢。PostgreSQL 为我们维护数据库提供了大量的视图，我在这里列举一些常用的。这些视图一般都是以 pg_stat* 开头的，我们可以通过这些视图获取数据库目前运行的状态。我在这里列举一些性能相关的视图。
最常用的视图之一是 pg_stat_activity, 这个视图展示了当前运行的服务器进程信息，包括它正在访问哪个数据库、它正在使用哪个系统进程、哪个用户正在访问这个进程、当前的查询以及查询运行的时间长度，视图的结构如下所示。
postgres=# \d pg_stat_activity
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
state_change | timestamp with time zone |
waiting | boolean |
state | text |
backend_xid | xid |
backend_xmin | xid |
query | text |
如果要追踪一个特定的进程，SQL 语句如下所示：
select * from pg_stat_activity where pid = 4689;
例：查询空闲连接的数目
select count(*) from pg_stat_activity where state='idle';
pg_stat_database 这个表存储了数据库静态统计数据，如连接到数据库服务器的进程数量、事务提交和回滚的数量以及块和列的统计信息，表的结构如下所示：
postgres=# \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
----------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
temp_files | bigint |
temp_bytes | bigint |
deadlocks | bigint |
blk_read_time | double precision |
blk_write_time | double precision |
stats_reset | timestamp with time zone |
例如，查询事务的提交与回滚的信息：
postgres=# select datid, datname, xact_commit, xact_rollback from pg_stat_database;
datid | datname | xact_commit | xact_rollback
---------+-------------------------+-------------+---------------
1 | template1 | 0 | 0
13290 | template0 | 0 | 0
13295 | postgres | 159782 | 0
53222 | m_prod_20170728 | 0 | 0
54161 | activiti | 0 | 0
73747 | prod_boko_20170915 | 2935668 | 89
3384033 | mailstack_prod_20190416 | 11148 | 14
5245932 | mailbase_prod_20200227 | 520199 | 250
67240 | activiti_20170915 | 848603 | 0
(9 rows)
pg_stat_all_tables 这个表为每个表存储了一些静态数据，其中包括顺序扫描静态数据、索引扫描静态数据以及在这个表上执行其他操作的数据。因为这个表中存储了所有现存表的数据，所以默认的 select 将会返回大量的数据。
postgres=# \d pg_stat_all_tables;
View "pg_catalog.pg_stat_all_tables"
Column | Type | Modifiers
---------------------+--------------------------+-----------
relid | oid |
schemaname | name |
relname | name |
seq_scan | bigint |
seq_tup_read | bigint |
idx_scan | bigint |
idx_tup_fetch | bigint |
n_tup_ins | bigint |
n_tup_upd | bigint |
n_tup_del | bigint |
n_tup_hot_upd | bigint |
n_live_tup | bigint |
n_dead_tup | bigint |
n_mod_since_analyze | bigint |
last_vacuum | timestamp with time zone |
last_autovacuum | timestamp with time zone |
last_analyze | timestamp with time zone |
last_autoanalyze | timestamp with time zone |
vacuum_count | bigint |
autovacuum_count | bigint |
analyze_count | bigint |
autoanalyze_count | bigint |
slect * from pg_stat_all_tables; 这条 SQL 将会返回大量的数据，如果仅仅需要查询用户表，可以查询 pg_stat_user_tables 这个视图。如果只需要查询系统表，可以查询 pg_stat_sys_tables。
关于性能视图总结如下：
PostgreSQL 对象级别的统计信息视图：
pg_stat_database pg_stat_all_tables pg_stat_sys_tables pg_stat_user_tables pg_stat_all_indexes pg_stat_sys_indexes pg_stat_user_indexes
PostgreSQL 提供的IO情况的视图:
pg_statio_all_tables pg_statio_sys_tables pg_statio_user_tables pg_statio_all_indexes pg_statio_sys_indexes pg_statio_user_indexes pg_statio_all_sequences pg_statio_sys_sequences pg_statio_user_sequences
2020.04.29 调整格式