PostgreSQL教程FG089-PG统计信息：收集与使用（优化器基础）

本文档风哥主要介绍PostgreSQL的统计信息收集、存储和使用，以及对查询优化的影响。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PostgreSQL统计信息的概念

PostgreSQL统计信息是关于数据库对象（如表、索引、列等）的元数据，用于查询优化器生成高效的执行计划。统计信息包括表的行数、列的分布情况、索引的使用情况等信息，这些信息对查询优化器选择最佳执行计划至关重要。更多视频教程www.fgedu.net.cn

PostgreSQL统计信息的主要内容：

表级统计信息：表的行数、数据块数、平均行长度等
列级统计信息：列的唯一值数、空值数、数据分布等
索引统计信息：索引的大小、唯一性、使用频率等
表达式统计信息：表达式的结果分布等

1.2 统计信息对查询优化的重要性

统计信息对查询优化的重要性主要体现在以下几个方面：

执行计划选择：查询优化器根据统计信息选择最佳执行计划，如顺序扫描还是索引扫描
连接顺序：优化器根据表的大小和连接条件选择最佳连接顺序
连接方法：优化器根据表的大小和统计信息选择最佳连接方法，如嵌套循环、哈希连接或排序合并连接
索引选择：优化器根据统计信息选择最佳索引
成本估算：优化器根据统计信息估算执行计划的成本

1.3 统计信息的存储和管理

PostgreSQL统计信息存储在系统表中，主要包括：

# PostgreSQL统计信息存储表
– pg_statistic：存储表和列的统计信息
– pg_statistic_ext：存储扩展统计信息
– pg_stat_fgedu_tables：存储用户表的统计信息
– pg_stat_fgedu_indexes：存储用户索引的统计信息
– pg_stat_all_tables：存储所有表的统计信息
– pg_stat_all_indexes：存储所有索引的统计信息

# 统计信息的更新机制
– 自动更新：当表的行数变化超过一定比例时自动更新
– 手动更新：使用ANALYZE命令手动更新
– 定期更新：通过调度任务定期更新

# 统计信息的配置参数
– autovacuum_analyze_threshold：自动分析的阈值
– autovacuum_analyze_scale_factor：自动分析的比例因子
– default_statistics_target：默认的统计信息目标值
– analyze_threshold_percent：分析的阈值百分比

Part02-生产环境规划与建议

2.1 PostgreSQL统计信息收集规划

PostgreSQL统计信息收集规划要点：

# 统计信息收集规划步骤
1. 分析数据库结构：了解表的大小、更新频率和查询模式
2. 确定收集策略：根据表的特点确定收集频率和方法
3. 配置收集参数：设置合适的统计信息参数
4. 测试收集效果：在测试环境中测试收集效果
5. 实施收集计划：在生产环境中实施收集计划
6. 监控和调整：监控收集效果，根据需要调整策略

# 统计信息收集频率
– 小表（<10,000行）：每周收集一次
– 中表（10,000-1,000,000行）：每天收集一次
– 大表（>1,000,000行）：每小时收集一次或使用增量收集
– 频繁更新的表：更频繁地收集

# 统计信息收集方法
– 全表分析：对整个表进行分析
– 采样分析：对表的一部分进行采样分析
– 增量分析：只分析修改的部分
– 表达式分析：分析表达式的结果分布

# 统计信息收集时间
– 选择系统负载低的时间进行收集
– 避免在业务高峰期收集
– 考虑使用维护窗口进行收集

2.2 统计信息配置建议

PostgreSQL统计信息配置建议：

# 统计信息配置参数
– default_statistics_target：默认的统计信息目标值，建议设置为100-1000
– autovacuum_analyze_threshold：自动分析的阈值，建议设置为50
– autovacuum_analyze_scale_factor：自动分析的比例因子，建议设置为0.1
– analyze_threshold_percent：分析的阈值百分比，建议设置为10
– autovacuum_max_workers：自动清理的最大工作线程数，建议设置为3-5
– autovacuum_naptime：自动清理的休眠时间，建议设置为10-15分钟

# 列级统计信息配置
– 对于基数高的列：设置更高的统计信息目标值
– 对于经常用于WHERE条件的列：设置更高的统计信息目标值
– 对于用于连接的列：设置更高的统计信息目标值
– 对于TEXT或JSON类型的列：设置合适的统计信息目标值

# 索引统计信息配置
– 定期分析索引：确保索引统计信息的准确性
– 监控索引使用情况：及时发现和优化未使用的索引
– 调整索引统计信息参数：根据索引的使用情况调整参数

# 表达式统计信息配置
– 对于复杂查询中的表达式：创建表达式统计信息
– 使用CREATE STATISTICS命令：为表达式创建统计信息
– 监控表达式统计信息的效果：根据需要调整

2.3 统计信息维护策略

PostgreSQL统计信息维护策略：

# 统计信息维护策略
1. 定期收集统计信息：根据表的大小和更新频率定期收集
2. 监控统计信息状态：定期检查统计信息的更新时间和准确性
3. 调整统计信息参数：根据查询性能调整统计信息参数
4. 清理过期统计信息：定期清理过期的统计信息
5. 备份统计信息：定期备份统计信息，防止统计信息丢失

# 统计信息监控
– 检查统计信息更新时间：使用pg_stat_fgedu_tables查看
– 检查统计信息准确性：比较统计信息与实际数据
– 监控查询性能：关注查询执行计划的变化
– 分析执行计划：检查优化器是否选择了最佳执行计划

# 统计信息故障排除
– 统计信息过时：重新收集统计信息
– 统计信息不准确：增加统计信息目标值，重新收集
– 查询计划不佳：检查统计信息，调整参数
– 性能下降：检查统计信息是否需要更新

# 统计信息最佳实践
– 建立统计信息收集计划：根据表的特点制定收集计划
– 自动化统计信息收集：使用cron或其他调度工具
– 监控统计信息效果：定期评估统计信息对查询性能的影响
– 持续优化：根据查询模式和数据变化调整统计信息策略

风哥提示：统计信息是查询优化的基础，需要定期收集和维护，确保统计信息的准确性和及时性，以提高查询性能。学习交流加群风哥QQ113257174

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

3.1 PostgreSQL统计信息收集

3.1.1 使用ANALYZE命令收集统计信息

# 以超级用户登录
$ psql -U pgsql -d fgedu_test

# 分析整个数据库
fgedu_test=# ANALYZE;

ANALYZE

# 分析指定表
fgedu_test=# ANALYZE fgedu_employees;

ANALYZE

# 分析指定表的指定列
fgedu_test=# ANALYZE fgedu_employees (name, department, salary);

ANALYZE

# 使用VERBOSE选项查看详细信息
fgedu_test=# ANALYZE VERBOSE fgedu_employees;

INFO: analyzing “public.fgedu_employees”
INFO: “fgedu_employees”: scanned 100 of 100 pages, containing 10000 live rows and 0 dead rows; 3000 rows in sample, 10000 estimated total rows
ANALYZE

# 使用SKIP_LOCKED选项跳过锁定的表
fgedu_test=# ANALYZE SKIP_LOCKED;

ANALYZE

3.1.2 配置自动统计信息收集

# 修改postgresql.conf配置文件
$ sudo vi /postgresql/data/postgresql.conf

# 添加以下配置
# 自动统计信息收集配置
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 10min
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_multixact_freeze_max_age = 400000000

# 保存并退出

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

pg_reload_conf
—————-
t
(1 row)

# 查看自动统计信息收集配置
$ sudo -u pgsql psql -c “SHOW autovacuum;”

autovacuum
————
on
(1 row)

$ sudo -u pgsql psql -c “SHOW autovacuum_analyze_threshold;”

autovacuum_analyze_threshold
——————————
50
(1 row)

$ sudo -u pgsql psql -c “SHOW autovacuum_analyze_scale_factor;”

autovacuum_analyze_scale_factor
———————————-
0.1
(1 row)

3.1.3 调整统计信息目标值

# 查看默认统计信息目标值
fgedu_test=# SHOW default_statistics_target;

default_statistics_target
—————————
100
(1 row)

# 调整整个数据库的默认统计信息目标值
fgedu_test=# ALTER SYSTEM SET default_statistics_target = ‘200’;

ALTER SYSTEM

# 重新加载配置
fgedu_test=# SELECT pg_reload_conf();

pg_reload_conf
—————-
t
(1 row)

# 调整特定表的统计信息目标值
fgedu_test=# ALTER TABLE fgedu_employees ALTER COLUMN salary SET STATISTICS 500;

ALTER TABLE

# 调整特定列的统计信息目标值
fgedu_test=# ALTER TABLE fgedu_employees ALTER COLUMN department SET STATISTICS 300;

ALTER TABLE

# 查看列的统计信息目标值
fgedu_test=# \d+ fgedu_employees

3.2 PostgreSQL统计信息分析

3.2.1 查看表统计信息

# 查看表的统计信息
fgedu_test=# SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_analyze
FROM
pg_stat_fgedu_tables
WHERE
relname = ‘fgedu_employees’;

# 查看表的大小和行数
fgedu_test=# SELECT
relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size,
pg_size_pretty(pg_relation_size(relid)) AS table_size,
n_live_tup
FROM
pg_stat_fgedu_tables
WHERE
schemaname = ‘public’
ORDER BY
pg_total_relation_size(relid) DESC;

relname | total_size | index_size | table_size | n_live_tup
—————-+————+————+————+————
fgedu_employees | 1280 kB | 576 kB | 704 kB | 10000
fgedu_departments | 16 kB | 16 kB | 0 kB | 10

3.2.2 查看列统计信息

# 查看列的统计信息
fgedu_test=# SELECT
attname,
n_distinct,
most_common_vals,
most_common_freqs,
histogram_bounds
FROM
pg_stats
WHERE
tablename = ‘fgedu_employees’
ORDER BY
attname;

attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
———–+————+——————+——————-+——————
department | 5 | {IT,HR,Finance,Sales,Marketing} | {0.3,0.2,0.2,0.2,0.1} |
hire_date | 365 | | | {2023-01-01,2023-04-01,2023-07-01,2023-10-01,2024-01-01}
id | 10000 | | | {1,2500,5000,7500,10000}
name | 10000 | | |
salary | 5000 | | | {3000,5000,7000,9000,11000}

# 查看特定列的统计信息
fgedu_test=# SELECT
attname,
n_distinct,
avg_width,
null_frac
FROM
pg_stats
WHERE
tablename = ‘fgedu_employees’ AND
attname = ‘salary’;

attname | n_distinct | avg_width | null_frac
———+————+———–+———–
salary | 5000 | 8 | 0

3.2.3 查看索引统计信息

# 查看索引的统计信息
fgedu_test=# SELECT
schemaname,
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM
pg_stat_fgedu_indexes
WHERE
relname = ‘fgedu_employees’;

# 查看索引的使用情况
fgedu_test=# SELECT
relname,
indexrelname,
idx_scan,
pg_size_pretty(pg_indexes_size(i.indexrelid)) AS index_size
FROM
pg_stat_fgedu_indexes i
JOIN
pg_index idx ON i.indexrelid = idx.indexrelid
WHERE
i.schemaname = ‘public’
ORDER BY
idx_scan DESC;

3.3 统计信息在查询优化中的使用

3.3.1 查看执行计划

# 查看查询执行计划
fgedu_test=# EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE department = ‘IT’ AND salary > 8000;

QUERY PLAN
——————————————————————————————————————————————–
Seq Scan on fgedu_employees (cost=0.00..211.00 rows=900 width=38) (actual time=0.012..1.023 rows=900 loops=1)
Filter: ((department = ‘IT’::text) AND (salary > 8000::numeric))
Rows Removed by Filter: 9100
Planning Time: 0.123 ms
Execution Time: 1.123 ms

# 创建索引后查看执行计划
fgedu_test=# CREATE INDEX idx_fgedu_employees_department_salary ON fgedu_employees(department, salary);

CREATE INDEX

# 重新分析表
fgedu_test=# ANALYZE fgedu_employees;

ANALYZE

# 查看执行计划
fgedu_test=# EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE department = ‘IT’ AND salary > 8000;

QUERY PLAN
——————————————————————————————————————————————–
Bitmap Heap Scan on fgedu_employees (cost=4.32..101.32 rows=900 width=38) (actual time=0.015..0.523 rows=900 loops=1)
Recheck Cond: ((department = ‘IT’::text) AND (salary > 8000::numeric))
Heap Blocks: exact=50
-> Bitmap Index Scan on idx_fgedu_employees_department_salary (cost=0.00..4.09 rows=900 width=0) (actual time=0.010..0.010 rows=900 loops=1)
Index Cond: ((department = ‘IT’::text) AND (salary > 8000::numeric))
Planning Time: 0.156 ms
Execution Time: 0.623 ms

3.3.2 统计信息对执行计划的影响

# 查看统计信息对执行计划的影响

# 插入大量数据
fgedu_test=# INSERT INTO fgedu_employees (name, department, salary, hire_date)
SELECT
‘Employee ‘ || i,
(ARRAY[‘IT’, ‘HR’, ‘Finance’, ‘Sales’, ‘Marketing’])[floor(random() * 5 + 1)],
3000 + floor(random() * 9000),
‘2023-01-01’::date + (i % 365)::integer
FROM
generate_series(10001, 20000) i;

INSERT 0 10000

# 不更新统计信息，查看执行计划
fgedu_test=# EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE department = ‘IT’ AND salary > 8000;

QUERY PLAN
——————————————————————————————————————————————–
Bitmap Heap Scan on fgedu_employees (cost=4.32..101.32 rows=900 width=38) (actual time=0.018..0.987 rows=1800 loops=1)
Recheck Cond: ((department = ‘IT’::text) AND (salary > 8000::numeric))
Heap Blocks: exact=95
-> Bitmap Index Scan on idx_fgedu_employees_department_salary (cost=0.00..4.09 rows=900 width=0) (actual time=0.012..0.012 rows=1800 loops=1)
Index Cond: ((department = ‘IT’::text) AND (salary > 8000::numeric))
Planning Time: 0.134 ms
Execution Time: 1.087 ms

# 更新统计信息后查看执行计划
fgedu_test=# ANALYZE fgedu_employees;

ANALYZE

fgedu_test=# EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE department = ‘IT’ AND salary > 8000;

QUERY PLAN
——————————————————————————————————————————————–
Bitmap Heap Scan on fgedu_employees (cost=8.32..201.32 rows=1800 width=38) (actual time=0.020..1.023 rows=1800 loops=1)
Recheck Cond: ((department = ‘IT’::text) AND (salary > 8000::numeric))
Heap Blocks: exact=95
-> Bitmap Index Scan on idx_fgedu_employees_department_salary (cost=0.00..7.87 rows=1800 width=0) (actual time=0.015..0.015 rows=1800 loops=1)
Index Cond: ((department = ‘IT’::text) AND (salary > 8000::numeric))
Planning Time: 0.145 ms
Execution Time: 1.123 ms

Part04-生产案例与实战讲解

4.1 统计信息收集案例

4.1.1 案例描述

场景：一个企业级PostgreSQL数据库，包含大量表和数据，需要制定合理的统计信息收集策略，确保查询性能。

4.1.2 实施方案

# 1. 分析数据库结构

# 查看数据库中的表和大小
$ psql -U pgsql -d fgedu_production -c “SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size, n_live_tup FROM pg_stat_fgedu_tables ORDER BY pg_total_relation_size(relid) DESC;”

# 2. 制定统计信息收集策略

# 创建统计信息收集脚本
$ cat > collect_statistics.sh << 'EOF' #!/bin/bash # collect_statistics.sh # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # PostgreSQL统计信息收集脚本 # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn PG_HOST="localfgedu.net.cn" PG_PORT="5432" PG_USER="pgsql" PG_DATABASE="fgedu_production" LOG_FILE="/var/log/postgresql/collect_statistics.log" # 记录开始时间 echo "[$(date)] 开始收集统计信息" >> $LOG_FILE

# 收集小表的统计信息
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c “SELECT relname FROM pg_stat_fgedu_tables WHERE n_live_tup < 10000 ORDER BY relname;" | grep -v relname | grep -v $ | grep -v $ | while read table; do echo "[$(date)] 分析表: $table" >> $LOG_FILE
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c “ANALYZE $table;”
sleep 1
done

# 收集中表的统计信息
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c “SELECT relname FROM pg_stat_fgedu_tables WHERE n_live_tup >= 10000 AND n_live_tup < 1000000 ORDER BY relname;" | grep -v relname | grep -v $ | grep -v $ | while read table; do echo "[$(date)] 分析表: $table" >> $LOG_FILE
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c “ANALYZE $table;”
sleep 2
done

# 收集大表的统计信息
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c “SELECT relname FROM pg_stat_fgedu_tables WHERE n_live_tup >= 1000000 ORDER BY relname;” | grep -v relname | grep -v $ | grep -v $ | while read table; do
echo “[$(date)] 分析表: $table” >> $LOG_FILE
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c “ANALYZE $table;”
sleep 5
done

# 记录结束时间
echo “[$(date)] 统计信息收集完成” >> $LOG_FILE
EOF

# 赋予脚本执行权限
$ chmod +x collect_statistics.sh

# 3. 配置定时任务

# 添加到crontab
$ crontab -e

# 添加以下内容
0 2 * * * /path/to/collect_statistics.sh

# 4. 监控统计信息收集

# 查看统计信息收集日志
$ tail -f /var/log/postgresql/collect_statistics.log

# 查看表的最后分析时间
$ psql -U pgsql -d fgedu_production -c “SELECT relname, last_analyze FROM pg_stat_fgedu_tables ORDER BY last_analyze DESC;”

4.2 查询优化案例

4.2.1 案例描述

场景：一个复杂查询执行缓慢，需要通过更新统计信息和调整参数来优化查询性能。

4.2.2 实施方案

# 1. 分析慢查询

# 查看慢查询
$ psql -U pgsql -d fgedu_production -c “EXPLAIN ANALYZE SELECT e.name, d.name AS department_name, s.amount FROM fgedu_employees e JOIN fgedu_departments d ON e.department = d.name JOIN fgedu_fgfgfgfgsales s ON e.id = s.employee_id WHERE e.salary > 8000 AND s.amount > 10000;”

# 2. 检查统计信息

# 查看表的统计信息
$ psql -U pgsql -d fgedu_production -c “SELECT relname, last_analyze FROM pg_stat_fgedu_tables WHERE relname IN (‘fgedu_employees’, ‘fgedu_departments’, ‘fgedu_fgfgfgfgsales’);”

# 3. 更新统计信息

# 更新表的统计信息
$ psql -U pgsql -d fgedu_production -c “ANALYZE fgedu_employees;”
$ psql -U pgsql -d fgedu_production -c “ANALYZE fgedu_departments;”
$ psql -U pgsql -d fgedu_production -c “ANALYZE fgedu_fgfgfgfgsales;”

# 4. 调整统计信息目标值

# 调整列的统计信息目标值
$ psql -U pgsql -d fgedu_production -c “ALTER TABLE fgedu_employees ALTER COLUMN salary SET STATISTICS 500;”
$ psql -U pgsql -d fgedu_production -c “ALTER TABLE fgedu_fgfgfgfgsales ALTER COLUMN amount SET STATISTICS 500;”

# 重新分析表
$ psql -U pgsql -d fgedu_production -c “ANALYZE fgedu_employees;”
$ psql -U pgsql -d fgedu_production -c “ANALYZE fgedu_fgfgfgfgsales;”

# 5. 查看优化后的执行计划

$ psql -U pgsql -d fgedu_production -c “EXPLAIN ANALYZE SELECT e.name, d.name AS department_name, s.amount FROM fgedu_employees e JOIN fgedu_departments d ON e.department = d.name JOIN fgedu_fgfgfgfgsales s ON e.id = s.employee_id WHERE e.salary > 8000 AND s.amount > 10000;”

# 6. 创建必要的索引

# 创建索引
$ psql -U pgsql -d fgedu_production -c “CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);”
$ psql -U pgsql -d fgedu_production -c “CREATE INDEX idx_fgedu_fgfgfgfgsales_employee_id_amount ON fgedu_fgfgfgfgsales(employee_id, amount);”

# 重新分析表
$ psql -U pgsql -d fgedu_production -c “ANALYZE fgedu_employees;”
$ psql -U pgsql -d fgedu_production -c “ANALYZE fgedu_fgfgfgfgsales;”

# 7. 再次查看执行计划

4.3 统计信息维护案例

4.3.1 案例描述

场景：一个生产环境的PostgreSQL数据库，需要建立完善的统计信息维护策略，确保统计信息的准确性和及时性。

4.3.2 实施方案

# 1. 配置自动统计信息收集

# 修改postgresql.conf配置文件
$ sudo vi /postgresql/data/postgresql.conf

# 添加以下配置
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 10min
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 200000000
autovacuum_multixact_freeze_max_age = 400000000

# 保存并退出

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

# 2. 创建统计信息维护脚本

$ cat > maintenance_statistics.sh << 'EOF' #!/bin/bash # maintenance_statistics.sh # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # PostgreSQL统计信息维护脚本 # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn PG_HOST="localfgedu.net.cn" PG_PORT="5432" PG_USER="pgsql" PG_DATABASE="fgedu_production" LOG_FILE="/var/log/postgresql/maintenance_statistics.log" REPORT_FILE="/var/log/postgresql/statistics_report_$(date +%Y%m%d).txt" # 记录开始时间 echo "[$(date)] 开始统计信息维护" >> $LOG_FILE

# 生成统计信息报告
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE << EOF > $REPORT_FILE
\echo “=== PostgreSQL统计信息报告 ===”
\echo “生成时间: $(date)”
\echo “”

\echo “=== 表统计信息 ===”
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_analyze
FROM
pg_stat_fgedu_tables
ORDER BY
last_analyze NULLS FIRST;
\echo “”

\echo “=== 索引统计信息 ===”
SELECT
schemaname,
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM
pg_stat_fgedu_indexes
ORDER BY
idx_scan DESC;
\echo “”

\echo “=== 统计信息配置 ===”
SHOW default_statistics_target;
SHOW autovacuum;
SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;
EOF

# 分析所有表
echo “[$(date)] 分析所有表” >> $LOG_FILE
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASE -c “ANALYZE;”

# 记录结束时间
echo “[$(date)] 统计信息维护完成” >> $LOG_FILE
echo “统计信息报告已生成: $REPORT_FILE” >> $LOG_FILE
EOF

# 赋予脚本执行权限
$ chmod +x maintenance_statistics.sh

# 3. 配置定时任务

# 添加到crontab
$ crontab -e

# 添加以下内容
0 1 * * * /path/to/maintenance_statistics.sh

# 4. 监控统计信息维护

# 查看维护日志
$ tail -f /var/log/postgresql/maintenance_statistics.log

# 查看统计信息报告
$ cat /var/log/postgresql/statistics_report_$(date +%Y%m%d).txt

# 查看表的统计信息状态
$ psql -U pgsql -d fgedu_production -c “SELECT relname, last_analyze, n_live_tup FROM pg_stat_fgedu_tables ORDER BY last_analyze DESC;”

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，建议建立完善的统计信息收集和维护策略，定期更新统计信息，调整统计信息参数，确保查询优化器能够生成高效的执行计划。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL统计信息最佳实践

PostgreSQL统计信息最佳实践：

学习交流加群风哥微信: itpux-com

定期收集统计信息：根据表的大小和更新频率定期收集统计信息
调整统计信息参数：根据表的特点和查询模式调整统计信息参数
监控统计信息状态：定期检查统计信息的更新时间和准确性
优化统计信息目标值：为重要列设置更高的统计信息目标值
使用自动统计信息收集：配置合适的自动统计信息收集参数
分析执行计划：定期分析执行计划，确保优化器选择了最佳执行计划
建立维护计划：建立定期的统计信息维护计划
持续优化：根据查询性能和数据变化持续优化统计信息策略

5.2 统计信息常见问题

统计信息常见问题及解决方案：

统计信息过时：定期收集统计信息，配置合适的自动收集参数
统计信息不准确：增加统计信息目标值，使用更全面的分析
查询计划不佳：更新统计信息，调整参数，创建必要的索引
统计信息收集时间长：使用采样分析，选择系统负载低的时间收集
统计信息占用空间大：定期清理过期统计信息，调整统计信息参数
自动统计信息收集不及时：调整自动收集参数，增加收集频率
表达式统计信息不足：创建表达式统计信息，使用CREATE STATISTICS
统计信息分布不均：使用更合适的统计信息目标值，增加分析深度

5.3 统计信息调优技巧

统计信息调优技巧：

from oracle:www.itpux.com

针对不同表的调优：

小表：使用默认统计信息目标值，定期收集
中表：适当增加统计信息目标值，每天收集
大表：使用采样分析，增加统计信息目标值，每小时收集
频繁更新的表：增加收集频率，使用增量分析

针对不同列的调优：

基数高的列：设置更高的统计信息目标值
用于WHERE条件的列：设置更高的统计信息目标值
用于连接的列：设置更高的统计信息目标值
TEXT或JSON类型的列：设置合适的统计信息目标值

针对不同查询的调优：

复杂查询：增加相关表和列的统计信息目标值
聚合查询：增加分组列的统计信息目标值
连接查询：增加连接列的统计信息目标值
范围查询：增加范围列的统计信息目标值

系统级调优：

调整autovacuum参数：根据系统负载和数据变化调整
增加maintenance_work_mem：提高统计信息收集速度
使用并行分析：在多核系统上使用并行分析
监控系统资源：确保统计信息收集不会影响系统性能

风哥提示：统计信息是查询优化的基础，需要定期收集和维护，确保统计信息的准确性和及时性。合理配置统计信息参数，针对不同表和列进行调优，可以显著提高查询性能。from PostgreSQL:www.itpux.com

持续改进：统计信息管理是一个持续的过程，需要根据业务需求和数据变化不断调整和优化。建议定期评估统计信息策略，持续改进统计信息管理方法，以提高查询性能和系统稳定性。

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

PostgreSQL教程FG089-PG统计信息：收集与使用（优化器基础）

Part01-基础概念与理论知识

1.1 PostgreSQL统计信息的概念

1.2 统计信息对查询优化的重要性

1.3 统计信息的存储和管理

Part02-生产环境规划与建议

2.1 PostgreSQL统计信息收集规划

2.2 统计信息配置建议

2.3 统计信息维护策略

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

3.1 PostgreSQL统计信息收集

3.1.1 使用ANALYZE命令收集统计信息

3.1.2 配置自动统计信息收集

3.1.3 调整统计信息目标值

3.2 PostgreSQL统计信息分析

3.2.1 查看表统计信息

3.2.2 查看列统计信息

3.2.3 查看索引统计信息

3.3 统计信息在查询优化中的使用

3.3.1 查看执行计划

3.3.2 统计信息对执行计划的影响

Part04-生产案例与实战讲解

4.1 统计信息收集案例

4.1.1 案例描述

4.1.2 实施方案

4.2 查询优化案例

4.2.1 案例描述

4.2.2 实施方案

4.3 统计信息维护案例

4.3.1 案例描述

4.3.2 实施方案

Part05-风哥经验总结与分享

5.1 PostgreSQL统计信息最佳实践

5.2 统计信息常见问题

5.3 统计信息调优技巧

相关推荐

联系我们