2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG093-PG慢查询：定位与优化方法

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

本文档风哥主要介绍PostgreSQL慢查询的定位和优化方法，包括慢查询的概念、原因、检测方法和优化策略。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

from oracle:www.itpux.com

Part01-基础概念与理论知识

1.1 PostgreSQL慢查询的概念

PostgreSQL慢查询是指执行时间超过预设阈值的SQL查询。慢查询会占用大量系统资源，影响数据库性能和用户体验。识别和优化慢查询是数据库性能调优的重要组成部分。更多视频教程www.fgedu.net.cn

PostgreSQL慢查询的定义：

  • 执行时间超过预设阈值的查询
  • 通常指执行时间超过1秒的查询
  • 会占用大量CPU、内存和IO资源
  • 可能导致数据库性能下降

1.2 PostgreSQL慢查询的原因

PostgreSQL慢查询的常见原因包括：

# PostgreSQL慢查询的常见原因
– 缺少索引：查询没有使用索引，导致全表扫描
– 索引使用不当：使用了不合适的索引，或索引失效
– 查询语句复杂：查询语句过于复杂，包含多个表连接和子查询
– 数据量过大：表数据量过大，查询需要处理大量数据
– 统计信息过时：表的统计信息过时，导致优化器生成不佳的执行计划
– 系统资源不足：CPU、内存或IO资源不足
– 锁竞争：查询被其他事务锁定，导致等待
– 配置不当：PostgreSQL配置参数设置不合理
– 硬件性能：存储设备性能不足，导致IO瓶颈

1.3 PostgreSQL慢查询的影响

PostgreSQL慢查询的影响包括：

  • 系统资源占用：慢查询会占用大量CPU、内存和IO资源
  • 性能下降：慢查询会导致数据库性能下降，影响其他查询
  • 用户体验：慢查询会导致应用响应时间延长，影响用户体验
  • 系统负载：慢查询会增加系统负载，可能导致系统崩溃
  • 成本增加：慢查询会增加硬件成本和维护成本

Part02-生产环境规划与建议

2.1 PostgreSQL慢查询规划

PostgreSQL慢查询规划要点：

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

# 慢查询规划步骤
1. 定义慢查询阈值：根据业务需求和系统性能，定义慢查询的时间阈值
2. 配置慢查询日志：开启慢查询日志，记录慢查询的详细信息
3. 监控慢查询：定期监控慢查询，及时发现问题
4. 分析慢查询：分析慢查询的原因，找出性能瓶颈
5. 优化慢查询：根据分析结果，优化慢查询
6. 验证优化效果：测试优化后的查询性能
7. 持续监控：持续监控慢查询，确保优化效果

# 慢查询阈值设置
– 一般设置：1秒
– 严格设置：500毫秒
– 宽松设置：5秒
– 具体设置应根据业务需求和系统性能确定

# 慢查询监控工具
– PostgreSQL慢查询日志
– pg_stat_statements扩展
– pgBadger日志分析工具
– 第三方监控工具（如Prometheus、Grafana）

2.2 慢查询检测方法

PostgreSQL慢查询检测方法：

# 慢查询检测方法
1. 慢查询日志：开启慢查询日志，记录慢查询的详细信息
2. pg_stat_statements：使用pg_stat_statements扩展，统计查询执行情况
3. 实时监控：使用pg_stat_activity视图，监控当前正在执行的查询
4. 性能分析工具：使用EXPLAIN ANALYZE分析查询执行计划
5. 第三方监控工具：使用第三方监控工具，如Prometheus、Grafana

# 慢查询日志配置
– log_min_duration_statement：设置慢查询的时间阈值
– log_statement：设置要记录的语句类型
– log_line_prefix：设置日志行前缀，包含时间、用户、数据库等信息
– log_analyze：记录执行计划的详细信息

# pg_stat_statements配置
– shared_preload_libraries = ‘pg_stat_statements’
– pg_stat_statements.max = 10000
– pg_stat_statements.track = all
– pg_stat_statements.track_utility = on
– pg_stat_statements.track_planning = on

2.3 慢查询优化策略

PostgreSQL慢查询优化策略：

  • 索引优化：创建合适的索引，避免全表扫描
  • 查询语句优化：优化查询语句，减少不必要的计算和数据传输
  • 表结构优化：优化表结构，合理设计字段和数据类型
  • 统计信息更新：定期更新表的统计信息，确保优化器生成正确的执行计划
  • 配置调优：调整PostgreSQL配置参数，优化系统性能
  • 硬件升级：升级硬件，如使用SSD存储、增加内存等
  • 查询拆分：将复杂查询拆分为多个简单查询
  • 缓存优化：使用缓存，减少数据库访问
风哥提示：慢查询优化是一个持续的过程，需要定期监控和分析慢查询，根据业务需求和系统变化不断调整优化策略。学习交流加群风哥QQ113257174

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

3.1 慢查询监控

3.1.1 配置慢查询日志

# 配置慢查询日志
$ sudo vi /postgresql/data/postgresql.conf

# 修改慢查询日志参数
log_min_duration_statement = 1000 # 记录执行时间超过1000毫秒的查询
log_statement = ‘mod’ # 记录修改数据的语句
log_line_prefix = ‘%t [%p]: [%l-1] fgedu=%u,db=%d,fgapp=%a,client=%h ‘ # 日志行前缀
log_analyze = on # 记录执行计划的详细信息

# 保存并退出

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

# 验证修改结果
$ sudo -u pgsql psql -c “SHOW log_min_duration_statement;”

log_min_duration_statement
—————————-
1000ms
(1 row)

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

log_statement
—————
mod
(1 row)

3.1.2 启用pg_stat_statements扩展

# 启用pg_stat_statements扩展
$ sudo vi /postgresql/data/postgresql.conf

# 修改shared_preload_libraries参数
shared_preload_libraries = ‘pg_stat_statements’

# 添加pg_stat_statements配置
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.track_planning = on

# 保存并退出

# 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql

# 创建pg_stat_statements扩展
$ sudo -u pgsql psql -c “CREATE EXTENSION pg_stat_statements;”

# 验证pg_stat_statements扩展
$ sudo -u pgsql psql -c “SELECT * FROM pg_stat_statements LIMIT 5;”

3.1.3 实时监控慢查询

# 实时监控慢查询
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, state, query_start, now() – query_start as duration, query FROM pg_stat_activity WHERE state = ‘active’ AND now() – query_start > interval ‘1 second’ ORDER BY duration DESC;”

# 查看当前正在执行的查询
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, state, query_start, now() – query_start as duration, query FROM pg_stat_activity WHERE state = ‘active’ ORDER BY duration DESC;”

# 查看长时间运行的查询
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, state, query_start, now() – query_start as duration, query FROM pg_stat_activity WHERE now() – query_start > interval ‘5 seconds’ ORDER BY duration DESC;”

3.2 慢查询分析

3.2.1 分析慢查询日志

# 查看慢查询日志
$ sudo tail -f /postgresql/data/log/postgresql-$(date +%Y-%m-%d).log | grep “duration:”

# 使用pgBadger分析慢查询日志
$ pgbadger /postgresql/data/log/postgresql-$(date +%Y-%m-%d).log -o slow_query_report.html

# 查看pg_stat_statements中的慢查询
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”

# 分析查询执行计划
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

3.2.2 分析查询执行计划

# 分析查询执行计划
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

# 查看索引使用情况
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

# 查看表统计信息
$ sudo -u pgsql psql -d fgedu_production -c “SELECT * FROM pg_stat_fgedu_tables WHERE relname = ‘fgedu_employees’;”

# 查看索引统计信息
$ sudo -u pgsql psql -d fgedu_production -c “SELECT * FROM pg_stat_fgedu_indexes WHERE relname = ‘fgedu_employees’;”

3.3 慢查询优化

3.3.1 索引优化

# 创建索引
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);”

# 查看索引
$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_employees”

# 分析索引使用情况
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

# 优化索引
$ sudo -u pgsql psql -d fgedu_production -c “REINDEX INDEX idx_fgedu_employees_salary;”

# 删除无用索引
$ sudo -u pgsql psql -d fgedu_production -c “DROP INDEX IF EXISTS idx_fgedu_employees_old;”

3.3.2 查询语句优化

# 优化前的查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

# 优化后的查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT id, name, salary FROM fgedu_employees WHERE salary > 8000;”

# 优化连接查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT e.name, d.name AS department_name FROM fgedu_employees e JOIN fgedu_departments d ON e.department = d.name WHERE e.salary > 8000;”

# 优化子查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE department IN (SELECT name FROM fgedu_departments WHERE location = ‘Beijing’);”

# 优化聚合查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT department, COUNT(*) FROM fgedu_employees GROUP BY department;”

3.3.3 统计信息更新

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

# 更新所有表的统计信息
$ sudo -u pgsql psql -d fgedu_production -c “ANALYZE;”

# 查看统计信息
$ sudo -u pgsql psql -d fgedu_production -c “SELECT * FROM pg_stat_fgedu_tables WHERE relname = ‘fgedu_employees’;”

# 查看列的统计信息
$ sudo -u pgsql psql -d fgedu_production -c “SELECT * FROM pg_stats WHERE tablename = ‘fgedu_employees’;”

Part04-生产案例与实战讲解

4.1 慢查询检测案例

4.1.1 案例描述

场景：一个Web应用，用户反映页面加载缓慢，需要检测和分析慢查询。

4.1.2 实施方案

# 1. 配置慢查询日志

$ sudo vi /postgresql/data/postgresql.conf

# 修改慢查询日志参数
log_min_duration_statement = 500 # 记录执行时间超过500毫秒的查询
log_statement = ‘all’ # 记录所有语句
log_line_prefix = ‘%t [%p]: [%l-1] fgedu=%u,db=%d,fgapp=%a,client=%h ‘ # 日志行前缀

# 保存并退出

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

# 2. 启用pg_stat_statements扩展

$ sudo vi /postgresql/data/postgresql.conf

# 修改shared_preload_libraries参数
shared_preload_libraries = ‘pg_stat_statements’

# 添加pg_stat_statements配置
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.track_planning = on

# 保存并退出

# 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql

# 创建pg_stat_statements扩展
$ sudo -u pgsql psql -c “CREATE EXTENSION pg_stat_statements;”

# 3. 监控慢查询

# 实时监控慢查询
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, state, query_start, now() – query_start as duration, query FROM pg_stat_activity WHERE state = ‘active’ AND now() – query_start > interval ‘0.5 seconds’ ORDER BY duration DESC;”

# 查看pg_stat_statements中的慢查询
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”

# 4. 分析慢查询日志

$ sudo tail -f /postgresql/data/log/postgresql-$(date +%Y-%m-%d).log | grep “duration:”

# 使用pgBadger分析慢查询日志
$ pgbadger /postgresql/data/log/postgresql-$(date +%Y-%m-%d).log -o slow_query_report.html

4.2 慢查询分析案例

4.2.1 案例描述

场景：通过监控发现一个慢查询，需要分析其执行计划和原因。

4.2.2 实施方案

# 1. 查看慢查询

$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;”

queryid | query | calls | total_exec_time | mean_exec_time | rows
———+——-+——-+—————-+—————-+——
123456 | SELECT * FROM fgedu_employees WHERE salary > $1 | 100 | 50000 | 500 | 1000

# 2. 分析查询执行计划

$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

QUERY PLAN
———————————————————————————————————————-
Seq Scan on fgedu_employees (cost=0.00..1000.00 rows=1000 width=100) (actual time=0.010..5.000 rows=1000 loops=1)
Filter: (salary > 8000)
Rows Removed by Filter: 9000
Planning Time: 0.100 ms
Execution Time: 5.100 ms

# 3. 分析表结构和索引

$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_employees”

Table “public.fgedu_employees”
Column | Type | Collation | Nullable | Default
———-+———+———–+———-+————————————–
id | integer | | not null | nextval(‘fgedu_employees_id_seq’::regclass)
name | text | | not null |
department | text | | not null |
salary | numeric | | not null |
hire_date | date | | not null |
Indexes:
“fgedu_employees_pkey” PRIMARY KEY, btree (id)

# 4. 分析统计信息

$ sudo -u pgsql psql -d fgedu_production -c “SELECT * FROM pg_stat_fgedu_tables WHERE relname = ‘fgedu_employees’;”

relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_analyze | vacuum_count | analyze_count
——-+————+—————+———-+————–+———-+—————+———–+———–+———–+—————+————+————+———————+————-+————–+————–+—————
12345 | public | fgedu_employees | 1000 | 1000000 | 0 | 0 | 10000 | 0 | 0 | 0 | 10000 | 0 | 0 | | 2026-04-01 | 0 | 1

# 5. 分析原因

# 原因：
# 1. 没有为salary列创建索引，导致全表扫描
# 2. 表数据量较大，全表扫描需要处理大量数据
# 3. 统计信息已更新，但索引缺失

4.3 慢查询优化案例

4.3.1 案例描述

场景：分析发现一个慢查询，需要通过创建索引和优化查询语句来提高性能。

4.3.2 实施方案

# 1. 创建索引

$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);”

# 2. 验证索引创建

$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_employees”

Table “public.fgedu_employees”
Column | Type | Collation | Nullable | Default
———-+———+———–+———-+————————————–
id | integer | | not null | nextval(‘fgedu_employees_id_seq’::regclass)
name | text | | not null |
department | text | | not null |
salary | numeric | | not null |
hire_date | date | | not null |
Indexes:
“fgedu_employees_pkey” PRIMARY KEY, btree (id)
“idx_fgedu_employees_salary” btree (salary)

# 3. 分析优化后的查询执行计划

$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

QUERY PLAN
———————————————————————————————————————-
Bitmap Heap Scan on fgedu_employees (cost=10.00..100.00 rows=1000 width=100) (actual time=0.010..0.500 rows=1000 loops=1)
Recheck Cond: (salary > 8000)
Heap Blocks: exact=100
-> Bitmap Index Scan on idx_fgedu_employees_salary (cost=0.00..9.75 rows=1000 width=0) (actual time=0.005..0.005 rows=1000 loops=1)
Index Cond: (salary > 8000)
Planning Time: 0.100 ms
Execution Time: 0.600 ms

# 4. 优化查询语句

# 优化前的查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

# 优化后的查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT id, name, salary FROM fgedu_employees WHERE salary > 8000;”

QUERY PLAN
———————————————————————————————————————-
Index Only Scan using idx_fgedu_employees_salary on fgedu_employees (cost=0.29..50.00 rows=1000 width=50) (actual time=0.005..0.300 rows=1000 loops=1)
Index Cond: (salary > 8000)
Heap Fetches: 0
Planning Time: 0.100 ms
Execution Time: 0.400 ms

# 5. 验证优化效果

# 查看pg_stat_statements中的查询执行情况
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements WHERE query LIKE ‘%salary >%’ ORDER BY mean_exec_time ASC;”

# 比较优化前后的执行时间
# 优化前：5.100 ms
# 优化后：0.400 ms
# 性能提升：约12倍

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，建议定期监控和分析慢查询，及时发现和解决性能问题。通过创建合适的索引、优化查询语句和更新统计信息，可以显著提高查询性能。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL慢查询最佳实践

PostgreSQL慢查询最佳实践：

  • 定期监控：定期监控慢查询，及时发现性能问题
  • 分析执行计划：使用EXPLAIN ANALYZE分析查询执行计划
  • 创建合适的索引：根据查询模式创建合适的索引
  • 优化查询语句：优化查询语句，减少不必要的计算和数据传输
  • 更新统计信息：定期更新表的统计信息
  • 配置调优：调整PostgreSQL配置参数，优化系统性能
  • 硬件升级：根据需要升级硬件，如使用SSD存储
  • 持续优化：持续监控和优化慢查询，确保系统性能

5.2 慢查询常见问题

慢查询常见问题及解决方案：

  • 缺少索引：解决方案：创建合适的索引
  • 索引使用不当：解决方案：优化索引设计，确保索引被正确使用
  • 查询语句复杂：解决方案：优化查询语句，拆分复杂查询
  • 统计信息过时：解决方案：定期更新统计信息
  • 系统资源不足：解决方案：增加系统资源，如内存、CPU等
  • 锁竞争：解决方案：优化事务设计，减少锁竞争
  • 配置不当：解决方案：调整PostgreSQL配置参数
  • 硬件性能：解决方案：升级硬件，如使用SSD存储

5.3 慢查询优化技巧

慢查询优化技巧：

  • 索引优化：
    • 为经常查询的列创建索引
    • 为连接条件创建索引
    • 为WHERE子句中的列创建索引
    • 避免创建过多索引，影响写入性能
    • 定期重建索引，保持索引效率
  • 查询语句优化：
    • 只选择需要的列，避免SELECT *
    • 使用LIMIT限制返回行数
    • 避免在WHERE子句中使用函数
    • 优化连接查询，使用合适的连接方式
    • 避免使用子查询，改用连接查询
  • 统计信息优化：
    • 定期执行ANALYZE更新统计信息
    • 对于大型表，使用ANALYZE VERBOSE
    • 调整default_statistics_target参数
  • 配置优化：
    • 调整shared_buffers参数
    • 调整work_mem参数
    • 调整effective_cache_size参数
    • 调整random_page_cost和sequential_page_cost参数
风哥提示：慢查询优化是数据库性能调优的重要组成部分，需要定期监控和分析慢查询，根据业务需求和系统变化不断调整优化策略。通过创建合适的索引、优化查询语句和更新统计信息，可以显著提高查询性能。from PostgreSQL:www.itpux.com

持续改进：慢查询优化是一个持续的过程，需要根据业务需求和系统变化不断调整和优化。建议建立慢查询监控和优化的标准流程，定期审查和调整优化策略，以保持系统的最佳性能。

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

相关推荐

联系我们

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

微信号：itpux-com

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