2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG227-PG优化器统计信息：收集与使用

教程整理：风哥教程  |  更新时间：2026-01-22  |  教程分类：PostgreSQL教程  |  文档学习：16

本文档风哥主要介绍PostgreSQL数据库的优化器统计信息，包括统计信息的收集、使用、调优等内容，风哥教程参考PostgreSQL官方文档Statistics Collector内容，适合数据库管理员和开发者在学习和测试中使用。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 PostgreSQL优化器统计信息概念

优化器统计信息是PostgreSQL查询优化器用来生成执行计划的重要数据，它包含了表和索引的统计信息，如行数、数据分布、索引大小等。这些统计信息帮助查询优化器估算查询成本，选择最优的执行计划。

PostgreSQL优化器统计信息的特点：

  • 自动收集：通过ANALYZE命令或自动清理收集
  • 存储在系统表中：pg_statistic系统表
  • 用于成本估算：帮助优化器选择执行计划
  • 定期更新：确保统计信息的准确性
  • 可配置：可以调整收集策略

1.2 PostgreSQL优化器统计信息类型

PostgreSQL优化器统计信息的类型：

  • 表统计信息：行数、页数量、行宽等
  • 列统计信息：唯一值数量、空值数量、数据分布等
  • 索引统计信息：索引大小、叶子节点数量等
  • 扩展统计信息：列之间的相关性

1.3 PostgreSQL优化器统计信息优势

PostgreSQL优化器统计信息的优势：

  • 提高查询性能：帮助优化器选择最优执行计划
  • 减少资源消耗：避免不必要的全表扫描
  • 适应数据变化：随着数据变化更新统计信息
  • 支持复杂查询：为复杂查询提供准确的成本估算
  • 可扩展性：支持扩展统计信息
风哥提示：了解PostgreSQL的优化器统计信息，有助于理解数据库的查询优化机制，为SQL优化提供基础。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 PostgreSQL优化器统计信息配置

PostgreSQL优化器统计信息配置建议：

# 优化器统计信息配置参数

# 自动清理配置
autovacuum = on # 启用自动清理
autovacuum_max_workers = 3 # 自动清理工作进程数
autovacuum_naptime = 10min # 自动清理间隔
autovacuum_vacuum_threshold = 50 # 自动清理阈值
autovacuum_analyze_threshold = 50 # 自动分析阈值
autovacuum_vacuum_scale_factor = 0.1 # 自动清理比例因子
autovacuum_analyze_scale_factor = 0.05 # 自动分析比例因子

# 统计信息收集配置
default_statistics_target = 100 # 默认统计信息目标

# 示例：修改优化器统计信息配置
ALTER SYSTEM SET autovacuum_analyze_scale_factor = ‘0.025’;
ALTER SYSTEM SET default_statistics_target = ‘200’;
SELECT pg_reload_conf();

2.2 PostgreSQL优化器统计信息收集

PostgreSQL优化器统计信息收集建议：

# 优化器统计信息收集

# 手动收集统计信息
ANALYZE fgedu_fgedus;
ANALYZE fgedu_orders;

# 收集所有表的统计信息
ANALYZE;

# 收集特定列的统计信息
ANALYZE fgedu_fgedus (fgeduname, email);

# 分析详细程度
ALTER TABLE fgedu_fgedus ALTER COLUMN email SET STATISTICS 1000;

# 示例：收集统计信息
— 手动收集
ANALYZE fgedu_fgedus;

— 查看统计信息
SELECT
relname,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_fgedu_tables
WHERE relname LIKE ‘fgedu_%’;

2.3 PostgreSQL优化器统计信息监控

PostgreSQL优化器统计信息监控建议：

  • 统计信息更新时间：监控最后分析时间
  • 统计信息准确性：监控表和列的统计信息
  • 自动清理执行情况：监控自动清理的执行情况
  • 查询性能：监控查询执行计划的质量
风哥教程针对风哥教程针对风哥教程针对生产环境建议：建立完善的优化器统计信息监控体系，及时发现和解决统计信息相关问题。定期分析统计信息使用情况，优化统计信息收集策略。学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 PostgreSQL优化器统计信息实施

3.1.1 统计信息配置实施

# 统计信息配置实施

# 步骤1：修改全局配置
ALTER SYSTEM SET autovacuum = ‘on’;
ALTER SYSTEM SET autovacuum_max_workers = ‘4’;
ALTER SYSTEM SET autovacuum_naptime = ‘5min’;
ALTER SYSTEM SET autovacuum_vacuum_threshold = ’50’;
ALTER SYSTEM SET autovacuum_analyze_threshold = ’50’;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = ‘0.05’;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = ‘0.025’;
ALTER SYSTEM SET default_statistics_target = ‘200’;
SELECT pg_reload_conf();

# 步骤2：表级配置
ALTER TABLE fgedu_fgedus SET (
autovacuum_enabled = true,
autovacuum_vacuum_threshold = 50,
autovacuum_analyze_threshold = 50,
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.025
);

# 步骤3：列级配置
ALTER TABLE fgedu_fgedus ALTER COLUMN email SET STATISTICS 1000;

# 步骤4：验证配置
SHOW autovacuum;
SHOW autovacuum_analyze_scale_factor;
SHOW default_statistics_target;

# 结果示例
autovacuum
————
on

autovacuum_analyze_scale_factor
——————————–
0.025

default_statistics_target
—————————
200

3.1.2 统计信息收集实施

# 统计信息收集实施

# 步骤1：手动收集统计信息
ANALYZE fgedu_fgedus;
ANALYZE fgedu_orders;
ANALYZE fgedu_products;

# 步骤2：创建扩展统计信息
CREATE STATISTICS fgedu_fgedus_stats (dependencies) ON fgeduname, email FROM fgedu_fgedus;
CREATE STATISTICS fgedu_orders_stats (ndistinct, dependencies) ON customer_id, order_date FROM fgedu_orders;

# 步骤3：更新扩展统计信息
ANALYZE fgedu_fgedus;
ANALYZE fgedu_orders;

# 步骤4：查看统计信息
SELECT
stxname,
stxnamespace::regnamespace,
stxrelid::regclass,
stxkeys,
stxkind
FROM pg_statistic_ext
WHERE stxrelid::regclass::text LIKE ‘fgedu_%’;

# 结果示例
-[ RECORD 1 ]-+————————
stxname | fgedu_fgedus_stats
stxnamespace | public
stxrelid | fgedu_fgedus
stxkeys | {1,2}
stxkind | {d}

-[ RECORD 2 ]-+————————
stxname | fgedu_orders_stats
stxnamespace | public
stxrelid | fgedu_orders
stxkeys | {1,3}
stxkind | {n,d}

3.2 PostgreSQL优化器统计信息策略

3.2.1 统计信息收集策略

# 统计信息收集策略

# 策略1：定期手动收集
– 对于重要表，定期执行ANALYZE
– 对于数据变化频繁的表，增加收集频率
– 对于大型表，考虑使用采样

# 策略2：优化自动清理
– 调整autovacuum参数
– 为不同表设置不同的收集策略
– 监控自动清理执行情况

# 策略3：使用扩展统计信息
– 对于相关列，创建扩展统计信息
– 对于复杂查询，使用扩展统计信息
– 定期更新扩展统计信息

# 示例：统计信息收集策略
— 定期手动收集
ANALYZE fgedu_fgedus;

— 优化自动清理
ALTER TABLE fgedu_fgedus SET (
autovacuum_analyze_scale_factor = 0.025
);

— 使用扩展统计信息
CREATE STATISTICS fgedu_fgedus_stats (dependencies) ON fgeduname, email FROM fgedu_fgedus;
ANALYZE fgedu_fgedus;

3.2.2 统计信息调优策略

# 统计信息调优策略

# 策略1：调整统计信息目标
– 对于重要列，增加统计信息目标
– 对于大型表，增加统计信息目标
– 对于数据分布不均匀的列，增加统计信息目标

# 策略2：使用采样
– 对于大型表，使用采样收集统计信息
– 调整采样比例
– 平衡准确性和收集时间

# 策略3：监控统计信息质量
– 定期检查统计信息的准确性
– 分析执行计划是否合理
– 及时更新过时的统计信息

# 示例：统计信息调优策略
— 调整统计信息目标
ALTER TABLE fgedu_fgedus ALTER COLUMN email SET STATISTICS 1000;

— 使用采样
ANALYZE fgedu_fgedus TABLESAMPLE SYSTEM (10);

— 监控统计信息质量
SELECT
relname,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_fgedu_tables
WHERE relname LIKE ‘fgedu_%’;

3.3 PostgreSQL优化器统计信息调优

3.3.1 统计信息目标调优

# 统计信息目标调优

# 全局统计信息目标
default_statistics_target = 100 # 默认值

# 表级统计信息目标
ALTER TABLE fgedu_fgedus SET (autovacuum_analyze_scale_factor = 0.025);

# 列级统计信息目标
ALTER TABLE fgedu_fgedus ALTER COLUMN email SET STATISTICS 1000;

# 示例：统计信息目标调优
— 全局配置
ALTER SYSTEM SET default_statistics_target = ‘200’;

— 列级配置
ALTER TABLE fgedu_fgedus ALTER COLUMN email SET STATISTICS 1000;
ALTER TABLE fgedu_orders ALTER COLUMN order_date SET STATISTICS 500;

— 验证配置
SELECT
attname,
attstattarget
FROM pg_attribute
WHERE attrelid = ‘fgedu_fgedus’::regclass AND attstattarget > 0;

# 结果示例
attname | attstattarget
——–+—————
email | 1000

3.3.2 自动清理调优

# 自动清理调优

# 全局配置
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 5min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025

# 表级配置
ALTER TABLE fgedu_fgedus SET (
autovacuum_enabled = true,
autovacuum_vacuum_threshold = 50,
autovacuum_analyze_threshold = 50,
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.025,
autovacuum_freeze_max_age = 150000000
);

# 示例：自动清理调优
— 全局配置
ALTER SYSTEM SET autovacuum_max_workers = ‘4’;
ALTER SYSTEM SET autovacuum_naptime = ‘5min’;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = ‘0.025’;

— 表级配置
ALTER TABLE fgedu_fgedus SET (
autovacuum_analyze_scale_factor = 0.025
);

— 验证配置
SELECT
relname,
autovacuum_enabled,
autovacuum_analyze_scale_factor
FROM pg_class c
JOIN pg_stat_fgedu_tables s ON c.oid = s.relid
WHERE relname LIKE ‘fgedu_%’;

风哥提示：优化器统计信息调优是提高PostgreSQL性能的关键，通过合理的配置和策略，可以显著提高查询性能。建议根据实际需求，制定适合的统计信息管理策略。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 PostgreSQL优化器统计信息实战案例

4.1.1 统计信息更新案例

# 统计信息更新案例

# 场景：电商网站用户表

# 问题：查询性能下降
– 查询时间：5秒
– 执行计划：全表扫描
– 统计信息：过时

# 解决方法
– 更新统计信息
– 创建合适的索引
– 优化SQL语句

# 示例：统计信息更新
— 查看统计信息
SELECT
relname,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_fgedu_tables
WHERE relname = ‘fgedu_fgedus’;

— 更新统计信息
ANALYZE fgedu_fgedus;

— 查看更新后的统计信息
SELECT
relname,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_fgedu_tables
WHERE relname = ‘fgedu_fgedus’;

— 测试查询性能
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 结果示例
QUERY PLAN
——————————————————————–
Index Scan using idx_fgedu_fgedus_email on fgedu_fgedus (cost=0.29..8.31 rows=1 width=100)
Index Cond: (email = ‘fgedu@fgedu.net.cn’)
Planning Time: 0.103 ms
Execution Time: 0.123 ms

4.2 PostgreSQL优化器统计信息工具使用

4.2.1 使用pg_stat_fgedu_tables查看统计信息

# 使用pg_stat_fgedu_tables查看统计信息

# 查看表统计信息
SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
vacuum_count,
autovacuum_count,
analyze_count,
autoanalyze_count
FROM pg_stat_fgedu_tables
WHERE relname LIKE ‘fgedu_%’;

# 结果示例
-[ RECORD 1 ]——+————————
relname | fgedu_fgedus
n_live_tup | 1000000
n_dead_tup | 10000
last_vacuum | 2024-01-01 12:00:00
last_autovacuum | 2024-01-01 11:00:00
last_analyze | 2024-01-01 12:00:00
last_autoanalyze | 2024-01-01 11:00:00
vacuum_count | 5
autovacuum_count | 10
analyze_count | 5
autoanalyze_count | 10

# 查看列统计信息
SELECT
attname,
n_distinct,
most_common_vals,
most_common_freqs,
histogram_bounds
FROM pg_stats
WHERE tablename = ‘fgedu_fgedus’ AND attname = ’email’;

4.3 PostgreSQL优化器统计信息常见问题

PostgreSQL优化器统计信息常见问题及解决方法：

# 常见问题1：统计信息过时

# 症状：执行计划选择不当，查询性能下降

# 解决方法
– 执行ANALYZE
ANALYZE fgedu_fgedus;

– 调整自动清理参数
ALTER SYSTEM SET autovacuum_analyze_scale_factor = ‘0.025’;

# 常见问题2：统计信息不准确

# 症状：执行计划成本估算错误

# 解决方法
– 增加统计信息目标
ALTER TABLE fgedu_fgedus ALTER COLUMN email SET STATISTICS 1000;

– 使用扩展统计信息
CREATE STATISTICS fgedu_fgedus_stats (dependencies) ON fgeduname, email FROM fgedu_fgedus;

# 常见问题3：自动清理不执行

# 症状：统计信息长时间未更新

# 解决方法
– 检查自动清理配置
SHOW autovacuum;

– 手动执行VACUUM ANALYZE
VACUUM ANALYZE fgedu_fgedus;

# 常见问题4：统计信息收集时间过长

# 症状：ANALYZE执行时间过长

# 解决方法
– 使用采样
ANALYZE fgedu_fgedus TABLESAMPLE SYSTEM (10);

– 分批收集
ANALYZE fgedu_fgedus (fgeduname);
ANALYZE fgedu_fgedus (email);

# 常见问题5：扩展统计信息未使用

# 症状：复杂查询执行计划不佳

# 解决方法
– 创建扩展统计信息
CREATE STATISTICS fgedu_orders_stats (dependencies) ON customer_id, order_date FROM fgedu_orders;

– 更新扩展统计信息
ANALYZE fgedu_orders;

风哥教程针对风哥教程针对风哥教程针对生产环境建议：定期监控优化器统计信息，及时发现和解决统计信息相关问题。建立统计信息维护计划，确保统计信息的准确性和及时性。from PostgreSQL视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 PostgreSQL优化器统计信息最佳实践

PostgreSQL优化器统计信息最佳实践：

  • 定期更新统计信息：确保统计信息的准确性
  • 优化自动清理配置：根据表的特性调整参数
  • 使用扩展统计信息：对于相关列创建扩展统计信息
  • 调整统计信息目标：根据列的重要性调整
  • 监控统计信息质量：定期检查统计信息的准确性
  • 使用采样：对于大型表使用采样收集统计信息
  • 平衡准确性和性能：在统计信息准确性和收集性能之间取得平衡
风哥提示：优化器统计信息是PostgreSQL查询优化的基础，合理的统计信息管理可以显著提高查询性能。建议根据实际需求，制定适合的统计信息管理策略。

5.2 PostgreSQL优化器统计信息检查清单

# 优化器统计信息检查清单
– [ ] 统计信息是否最新
– [ ] 自动清理是否启用
– [ ] 自动清理参数是否优化
– [ ] 统计信息目标是否合理
– [ ] 扩展统计信息是否使用
– [ ] 统计信息收集频率是否适当
– [ ] 统计信息收集时间是否合理
– [ ] 统计信息质量是否良好

# 优化器统计信息维护清单
– [ ] 每日：监控统计信息更新情况
– [ ] 每周：执行ANALYZE重要表
– [ ] 每月：调整自动清理参数
– [ ] 每季度：创建扩展统计信息
– [ ] 每年：评估统计信息管理策略
– [ ] 定期：测试统计信息质量

5.3 PostgreSQL优化器统计信息工具推荐

PostgreSQL优化器统计信息工具推荐：

  • ANALYZE：收集统计信息
  • pg_stat_fgedu_tables：查看表统计信息
  • pg_stats：查看列统计信息
  • pg_statistic_ext：查看扩展统计信息
  • pg_stat_progress_analyze：查看分析进度
  • auto_explain：自动记录执行计划
  • pgBadger：日志分析工具
  • pganalyze：性能分析工具
持续改进：优化器统计信息管理是一个持续的过程，建议定期评估统计信息使用情况，根据业务需求和系统负载，不断调整和优化统计信息管理策略。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息