PostgreSQL 的统计收集器（Statistics Collector）

pg_stat_user_tables 和 pg_stat_user_indexes

在 PostgreSQL 中，pg_stat_user_tables 和 pg_stat_user_indexes 是两个重要的系统视图，用于监控表的扫描和索引的使用情况。它们属于 PostgreSQL 的统计收集器（Statistics Collector）功能，默认启用（需确保 track_counts 参数为 on）。以下是它们的详细说明和用法：

1. `pg_stat_user_tables`

功能

记录当前数据库中用户表的扫描统计信息（不包括系统表）。

常用字段

字段名	类型	说明
`schemaname`	`name`	表所属的模式名（如 `public`）
`relname`	`name`	表名
`seq_scan`	`bigint`	全表扫描（Sequential Scan）的次数
`seq_tup_read`	`bigint`	全表扫描读取的行数
`idx_scan`	`bigint`	索引扫描（Index Scan）的次数
`idx_tup_fetch`	`bigint`	通过索引扫描获取的行数
`n_tup_ins`	`bigint`	插入的行数
`n_tup_upd`	`bigint`	更新的行数
`n_tup_del`	`bigint`	删除的行数
`n_live_tup`	`bigint`	当前表中的活行数（估算）
`n_dead_tup`	`bigint`	当前表中的死行数（估算）
`last_vacuum`	`timestamp`	最后一次手动 `VACUUM` 时间
`last_autovacuum`	`timestamp`	最后一次自动 `VACUUM` 时间
`last_analyze`	`timestamp`	最后一次手动 `ANALYZE` 时间
`last_autoanalyze`	`timestamp`	最后一次自动 `ANALYZE` 时间

示例查询

(1) 查看表的扫描频率

SELECT 
    schemaname,
    relname AS table_name,
    seq_scan AS full_table_scans,
    idx_scan AS index_scans,
    round(idx_scan * 100.0 / NULLIF(seq_scan + idx_scan, 0), 2) AS index_scan_percentage
FROM 
    pg_stat_user_tables
WHERE 
    schemaname = 'public'  -- 替换为你的模式名
ORDER BY 
    idx_scan DESC NULLS LAST;  -- 按索引扫描次数降序排列

说明：

index_scan_percentage 计算索引扫描占比，帮助判断表是否被高效查询。
如果 seq_scan 远高于 idx_scan，可能缺少合适的索引。

(2) 检查表的死行和自动清理情况

SELECT 
    schemaname,
    relname AS table_name,
    n_dead_tup AS dead_rows,
    last_autovacuum,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size
FROM 
    pg_stat_user_tables
WHERE 
    n_dead_tup > 10000  -- 死行数超过 1 万
ORDER BY 
    n_dead_tup DESC;

说明：

死行（n_dead_tup）是已删除但未被 VACUUM 回收的行，可能导致表膨胀。
结合 last_autovacuum 检查自动清理是否正常运行。

2. `pg_stat_user_indexes`

功能

记录当前数据库中用户索引的使用统计信息（不包括系统表索引）。

常用字段

字段名	类型	说明
`schemaname`	`name`	索引所属的模式名
`relname`	`name`	索引所属的表名
`indexrelname`	`name`	索引名
`idx_scan`	`bigint`	索引被扫描的次数
`idx_tup_read`	`bigint`	从索引读取的行数
`idx_tup_fetch`	`bigint`	从表获取的行数（与 `idx_tup_read` 可能不同，如索引覆盖扫描）

示例查询

(1) 查看索引的使用频率

SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scans,
    pg_size_pretty(pg_relation_size(indexrelname::regclass)) AS index_size
FROM 
    pg_stat_user_indexes
WHERE 
    schemaname = 'public'  -- 替换为你的模式名
ORDER BY 
    idx_scan DESC NULLS LAST;  -- 按扫描次数降序排列

说明：

扫描次数（idx_scan）低的索引可能是冗余的，可考虑删除。
结合 pg_relation_size 查看索引大小，优化存储。

(2) 检查未使用的索引

SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scans
FROM 
    pg_stat_user_indexes
WHERE 
    schemaname = 'public'
    AND idx_scan = 0  -- 从未被扫描的索引
ORDER BY 
    pg_relation_size(indexrelname::regclass) DESC;  -- 按索引大小降序排列

说明：

未使用的索引（idx_scan = 0）会占用存储并降低写入性能，建议删除。
但需确认是否为备用索引或未来查询使用。

(3) 分析索引扫描效率

SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    CASE 
        WHEN idx_tup_fetch = 0 THEN 0
        ELSE round(idx_tup_read * 100.0 / idx_tup_fetch, 2)
    END AS read_fetch_ratio  -- 读取行数与获取行数的比例
FROM 
    pg_stat_user_indexes
WHERE 
    schemaname = 'public'
ORDER BY 
    read_fetch_ratio DESC;

说明：

read_fetch_ratio 接近 100% 表示索引覆盖扫描（高效）。
比例低可能意味着索引选择性差或查询需要回表大量数据。

3. 结合 `pg_stat_user_tables` 和 `pg_stat_user_indexes`

示例：分析表的索引使用情况

SELECT 
    t.schemaname,
    t.relname AS table_name,
    t.seq_scan AS full_table_scans,
    t.idx_scan AS table_index_scans,
    i.indexrelname AS index_name,
    i.idx_scan AS index_scans,
    pg_size_pretty(pg_relation_size(i.indexrelname::regclass)) AS index_size
FROM 
    pg_stat_user_tables t
LEFT JOIN 
    pg_stat_user_indexes i ON t.schemaname = i.schemaname AND t.relname = i.relname
WHERE 
    t.schemaname = 'public'
ORDER BY 
    t.relname, i.idx_scan DESC NULLS LAST;

4. 注意事项

统计信息重置：
- 统计信息在数据库重启或执行 pg_stat_reset() 后会被重置。
- 可通过 pg_stat_reset_shared('bgwriter') 重置部分统计信息。
自动清理（Autovacuum）：
- 确保 autovacuum 正常运行，避免死行堆积影响查询性能。
索引维护：
- 定期检查未使用的索引（idx_scan = 0）并删除。
- 对高频扫描但低选择性的索引考虑重建或优化查询。
扩展统计信息：
- 对于复杂查询，启用 track_io_timing 和 track_functions 获取更详细的性能数据。

总结

pg_stat_user_tables：监控表的扫描方式（全表 vs 索引）、行变更和死行情况。
pg_stat_user_indexes：监控索引的使用频率和效率，识别冗余索引。
结合使用：通过关联分析优化表设计和索引策略，提升查询性能。

通过定期检查这些视图，可以及时发现数据库的性能瓶颈并进行优化。

pg_stat_statements

pg_stat_statements 是 PostgreSQL 中一个核心的性能监控扩展，用于追踪和分析 SQL 语句的执行统计信息，帮助管理员快速定位性能瓶颈。以下是其关键功能、配置方法及使用场景的详细说明：

一、核心功能

执行统计聚合
- 记录所有 SQL 语句的执行次数、总耗时、平均耗时、最大/最小耗时等，自动归一化处理相似查询（如参数不同的查询合并统计），避免日志膨胀。
- 示例查询：
```
SELECT query, calls, total_time, mean_time 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;
```
  返回总耗时最长的 10 条查询，快速识别性能热点。
资源消耗分析
- 跟踪 I/O 消耗（如 blk_read_time、blk_write_time）、共享内存命中率（shared_blks_hit）、临时缓冲区使用等，辅助优化查询计划。
- 示例：
```
SELECT query, 
       round(blk_read_time + blk_write_time, 2) AS io_time 
FROM pg_stat_statements 
ORDER BY io_time DESC 
LIMIT 5;
```
  筛选出 I/O 密集型查询，针对性优化索引或分区策略。
持久化存储
- 默认在服务器重启后保留统计信息（通过 pg_stat_statements.save = on 配置），支持长期趋势分析。

二、配置与安装

启用扩展

修改 postgresql.conf：

shared_preload_libraries = 'pg_stat_statements'  # 预加载模块
pg_stat_statements.max = 10000                  # 最大跟踪语句数（默认5000）
pg_stat_statements.track = all                  # 跟踪顶层和嵌套语句
pg_stat_statements.track_utility = on            # 跟踪DDL等工具命令

重启 PostgreSQL 服务使配置生效。

创建扩展
在目标数据库中执行：
```
CREATE EXTENSION pg_stat_statements;
```

重置统计数据

SELECT pg_stat_statements_reset();  -- 清空当前统计信息

三、典型使用场景

定位慢查询

场景：用户反馈应用响应变慢。

操作：

SELECT query, 
       round(total_time::numeric, 2) AS total_ms,
       round(mean_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
WHERE total_time > 1000  -- 筛选总耗时超过1秒的查询
ORDER BY total_time DESC;

结果：发现某条复杂 JOIN 查询耗时占比 80%，进一步通过 EXPLAIN ANALYZE 分析执行计划，优化索引或重写 SQL。

监控高频查询

场景：数据库负载突增。

操作：

SELECT query, calls, 
       round(calls::numeric / extract(epoch FROM now() - pg_postmaster_start_time()) * 60, 2) AS qps
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

结果：识别出每秒调用超 1000 次的轻量级查询，考虑缓存或批量处理。

索引优化验证

场景：新建索引后需验证效果。

操作：

-- 对比索引创建前后的查询耗时
SELECT query, 
       round(total_time::numeric, 2) AS total_ms,
       rows AS fetched_rows
FROM pg_stat_statements
WHERE query LIKE '%FROM orders WHERE user_id%';

结果：若总耗时显著下降且扫描行数减少，说明索引生效。

四、高级技巧

结合 track_io_timing
- 在 postgresql.conf 中启用 track_io_timing = on，精确统计 I/O 等待时间，区分 CPU 密集型与 I/O 密集型查询。
定期归档统计数据
- 通过脚本定期导出 pg_stat_statements 数据至外部系统（如 Prometheus + Grafana），实现可视化监控与告警。
排除干扰查询
- 使用 WHERE query NOT LIKE '%pg_stat_statements%' 过滤掉监控工具自身的查询，避免数据污染。

五、注意事项

性能开销：跟踪所有语句会增加约 5%-10% 的 CPU 负载，生产环境建议仅在必要时开启 track_utility。
语句归一化：参数化查询（如 WHERE id = $1）会被合并统计，若需精确分析特定参数的查询，需结合日志或 auto_explain 扩展。
权限控制：仅超级用户可修改配置参数，普通用户需通过 GRANT 访问视图。

posted @ 2025-08-27 13:46 蓝迷梦阅读(89) 评论(0) 收藏举报

刷新页面返回顶部

蓝迷梦

PostgreSQL 的统计收集器（Statistics Collector）

pg_stat_user_tables 和 pg_stat_user_indexes

1. pg_stat_user_tables

功能

常用字段

示例查询

(1) 查看表的扫描频率

(2) 检查表的死行和自动清理情况

2. pg_stat_user_indexes

功能

常用字段

示例查询

(1) 查看索引的使用频率

(2) 检查未使用的索引

(3) 分析索引扫描效率

3. 结合 pg_stat_user_tables 和 pg_stat_user_indexes

示例：分析表的索引使用情况

4. 注意事项

总结

pg_stat_statements

一、核心功能

二、配置与安装

三、典型使用场景

四、高级技巧

五、注意事项

公告

1. `pg_stat_user_tables`

2. `pg_stat_user_indexes`

3. 结合 `pg_stat_user_tables` 和 `pg_stat_user_indexes`