1. 查询数据库活动连接详情
SELECT
pid,
datname,
usename,
client_addr,
client_port,
application_name,
QUERY
FROM
pg_stat_activity --无过滤条件的
WHERE
1 = 1 --只查询 jdbc，适用于只查询云上集群内部连接数据库状态的
AND application_name LIKE '%JDBC%' --只查询某个数据库,例如： xxx-test,
xxx-prod
AND datname = 'xxx-prod'
2. 查询某数据库的索引大小
SELECT
relname AS "表名称",
indexrelname AS "索引名称",
pg_size_pretty (
pg_relation_size ( relid )) AS "索引大小"
FROM
pg_stat_user_indexes
WHERE
1 = 1 --例如查询 pms索引大小
AND schemaname = 'db_schema_name' --过滤表名称叫做 xxxxxx
AND relname = 'table_name'
ORDER BY
pg_relation_size ( relid ) DESC
3. 查询数据库状态-pg_stat_database
select * from pg_stat_database;
字段解释
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 | 2021-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减少临时文件的产生，并且同时这些操作的性能也会有较大的提升。
4. 查询表状态-pg_stat_user_tables
select * from pg_stat_user_tables;
字段解释
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 | 2021-04-07 00:22:00.955542-07 #上次手动vacuum的实际
last_autovacuum | #上次autovacuum的实际
last_analyze | #上次analyze时间
last_autoanalyze | 2021-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哪种操作多，也可以了解垃圾数据的数量。
5. 查询索引使用情况-pg_stat_user_indexes
select * from pg_stat_user_indexes;
字段解释
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的性能。
6. 查询表缓存命中-pg_statio_user_tables
select * from pg_statio_user_tables;
字段解释
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表的块数。
7. 查询耗时较高的写入-pg_stat_bgwriter
select * from pg_stat_bgwriter;
字段解释
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 | 2020-02-11 23:42:35.273758-08
通过这个视图，可以判断checkpoint以及max_wal_size是否合理
8. 查询sql执行开始的时间，和持续执行的时间
SELECT
procpid as "进程ID",
start as "进程开始时间",
now() - start as "经过时间",
current_query as "执行中的语句"
FROM
(
SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query,
pg_stat_get_backend_client_port(S.backendid) AS client_port
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
where
pg_stat_get_backend_activity(S.backendid)<>'SELECT 1'
and pg_stat_get_backend_activity(S.backendid)<>'SELECT ''x'''
and pg_stat_get_backend_activity(S.backendid)<>'SELECT ''Keep Alive'''
and pg_stat_get_backend_activity(S.backendid) not like 'SET SESSION%'
and pg_stat_get_backend_activity(S.backendid) not like 'SHOW TRANSACTION%'
and pg_stat_get_backend_activity(S.backendid) <> 'COMMIT'
and pg_stat_get_backend_activity(S.backendid) not like 'SELECT a.pid AS %'
and pg_stat_get_backend_activity_start(S.backendid) is not null
and pg_stat_get_backend_client_port(S.backendid)>0
) AS S
WHERE
current_query <> '<IDLE>' and current_query not like '%backend%' and current_query not like '%command%'
ORDER BY
start DESC;
9 查询sql命中率
SELECT
schemaname AS "库名",
relname AS "表名",
heap_blks_read,
heap_blks_hit,
heap_blks_hit / heap_blks_read AS "数据命中率",
idx_blks_read,
idx_blks_hit,
idx_blks_hit / idx_blks_read AS "索引命中率"
FROM
pg_statio_user_tables
WHERE
schemaname = 'ims'
AND heap_blks_read > 0
ORDER BY
heap_blks_hit DESC
10 查询各个数据库大小
SELECT
pg_database.datname,
pg_size_pretty ( pg_database_size ( pg_database.datname ) ) AS SIZE
FROM
pg_database
ORDER BY
pg_database_size ( pg_database.datname ) DESC
11 查询模式中的表大小
SELECT
relname,
pg_size_pretty ( pg_relation_size ( relid ) )
FROM
pg_stat_user_tables
WHERE
--修改查询的模式范围
schemaname in ('模式名称' )
ORDER BY
pg_relation_size ( relid ) DESC;