PostgreSQL的视图pg_stat_user_tables

PostgreSQL的视图pg_stat_user_tables

pg_stat_user_tables 是 PostgreSQL 中的一个系统视图，用于显示用户定义的表的统计信息。这些统计信息包括表的访问情况、修改情况以及很多其他的性能指标。这个视图为数据库管理员提供了丰富的数据，可以帮助他们进行表的监控和性能分析。

pg_stat_user_tables 视图的主要列和其含义

示例查询

显示所有用户表的基本统计信息

SELECT 
    schemaname, 
    relname, 
    seq_scan, 
    seq_tup_read, 
    idx_scan, 
    idx_tup_fetch, 
    n_tup_ins, 
    n_tup_upd, 
    n_tup_del, 
    n_live_tup, 
    n_dead_tup 
FROM 
    pg_stat_user_tables;

获取特定表的统计信息

SELECT 
    * 
FROM 
    pg_stat_user_tables 
WHERE 
    schemaname = 'public' 
    AND relname = 'your_table_name';

获取所有表的索引使用情况

SELECT 
    schemaname, 
    relname, 
    idx_scan, 
    idx_tup_fetch 
FROM 
    pg_stat_user_tables 
WHERE 
    idx_scan > 0;

获取表的更新/插入/删除操作统计信息

SELECT 
    schemaname, 
    relname, 
    n_tup_ins AS inserts, 
    n_tup_upd AS updates, 
    n_tup_del AS deletes 
FROM 
    pg_stat_user_tables 
ORDER BY 
    inserts DESC;

查看需要进行 VACUUM 的表（死行数较多）

SELECT 
    schemaname, 
    relname, 
    n_live_tup, 
    n_dead_tup 
FROM 
    pg_stat_user_tables 
WHERE 
    n_dead_tup > 1000;  -- 此处条件可根据实际需求调整

统计信息的维护

常规的 VACUUM 和 ANALYZE 操作可以帮助维护 pg_stat_user_tables中的统计信息：

• 手动执行 VACUUM:

  VACUUM FULL your_table_name;
  

• 手动执行 ANALYZE:

  ANALYZE your_table_name;
  

• 自动执行 VACUUM 和 ANALYZE: PostgreSQL 具有自动执行 VACUUM 和 ANALYZE 的功能，可以通过配置参数调整这些操作的频率和策略。

通过 pg_stat_user_tables 视图，数据库管理员可以详细了解表的使用情况和性能指标，从而更好地进行数据库的性能调优和维护。