2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG228-PG执行计划：理解与分析

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

本文档风哥主要介绍PostgreSQL数据库的执行计划，包括执行计划的概念、类型、分析和优化等内容，风哥教程参考PostgreSQL官方文档Query Planning内容，适合数据库管理员和开发者在学习和测试中使用。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 PostgreSQL执行计划概念

执行计划是PostgreSQL查询优化器为SQL语句生成的执行步骤，它描述了如何获取数据、如何连接表、如何使用索引等操作。执行计划的质量直接影响查询性能，因此理解和分析执行计划是SQL优化的关键。

PostgreSQL执行计划的特点：

  • 树状结构：执行计划是一个树状结构，每个节点代表一个操作
  • 成本估算：每个操作都有成本估算，包括启动成本和总运行成本
  • 操作类型：包括顺序扫描、索引扫描、嵌套循环连接、哈希连接等
  • 可解释性：可以通过EXPLAIN命令查看执行计划
  • 动态生成：根据统计信息和查询条件动态生成

1.2 PostgreSQL执行计划类型

PostgreSQL执行计划的类型：

  • 顺序扫描（Seq Scan）：全表扫描，遍历表中的所有行
  • 索引扫描（Index Scan）：使用索引查找数据
  • 索引唯一扫描（Index Only Scan）：只使用索引获取数据，不访问表
  • 位图扫描（Bitmap Heap Scan）：结合位图索引和堆扫描
  • 嵌套循环连接（Nested Loop）：嵌套循环方式连接表
  • 哈希连接（Hash Join）：使用哈希表连接表
  • 合并连接（Merge Join）：对排序后的数据进行合并连接
  • 聚集操作（Aggregate）：执行聚合函数
  • 排序操作（Sort）：对数据进行排序
  • 限制操作（Limit）：限制返回行数

1.3 PostgreSQL执行计划组件

PostgreSQL执行计划的组件：

  • 节点类型：执行计划中的每个节点代表一个操作
  • 成本估算：每个节点的启动成本和总运行成本
  • 行数估算：每个节点处理的行数估算
  • 宽度估算：每行数据的宽度估算
  • 过滤条件：节点的过滤条件
  • 索引信息：使用的索引名称和条件
  • 连接条件：表连接的条件
  • 排序键：排序操作的排序键
  • 聚集函数：聚集操作的函数
风哥提示：了解PostgreSQL的执行计划，有助于理解SQL语句的执行过程，为SQL优化提供基础。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 PostgreSQL执行计划配置

PostgreSQL执行计划配置建议：

# 执行计划配置参数

# 优化器配置
optimizer = on # 启用查询优化器

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

# 成本配置
random_page_cost = 4.0 # 随机页面访问成本
seq_page_cost = 1.0 # 顺序页面访问成本
cpu_tuple_cost = 0.01 # 处理每行数据的CPU成本
cpu_index_tuple_cost = 0.005 # 处理每个索引元组的CPU成本
cpu_operator_cost = 0.0025 # 处理每个操作符的CPU成本

# 示例：修改执行计划配置
ALTER SYSTEM SET random_page_cost = ‘1.1’;
ALTER SYSTEM SET default_statistics_target = ‘200’;
SELECT pg_reload_conf();

2.2 PostgreSQL执行计划分析

PostgreSQL执行计划分析建议：

# 执行计划分析

# 基本分析
EXPLAIN SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 详细分析
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 详细分析（包括缓冲区使用）
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 详细分析（包括计时）
EXPLAIN (ANALYZE, TIMING) SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 示例：执行计划分析
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’)
Execution Time: 0.123 ms

2.3 PostgreSQL执行计划优化

PostgreSQL执行计划优化建议：

  • 创建合适的索引：根据查询条件创建索引
  • 优化SQL语句：避免不必要的列和表
  • 更新统计信息：确保统计信息的准确性
  • 调整配置参数：根据硬件情况调整成本参数
  • 使用分区表：对于大型表使用分区表
  • 使用物化视图：对于复杂查询使用物化视图
风哥教程针对风哥教程针对风哥教程针对生产环境建议：建立完善的执行计划分析体系，及时发现和解决执行计划相关问题。定期分析执行计划，优化SQL语句和索引。学习交流加群风哥QQ113257174

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

3.1 PostgreSQL执行计划实施

3.1.1 执行计划分析实施

# 执行计划分析实施

# 步骤1：分析执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 步骤2：创建索引
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

# 步骤3：再次分析执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 步骤4：优化SQL语句
EXPLAIN ANALYZE SELECT id, fgeduname, email FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 示例：执行计划分析实施
— 分析执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

— 创建索引
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

— 再次分析执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 结果示例
— 优化前
QUERY PLAN
——————————————————————–
Seq Scan on fgedu_fgedus (cost=0.00..1000.00 rows=1 width=100)
Filter: (email = ‘fgedu@fgedu.net.cn’)
Execution Time: 1.234 ms

— 优化后
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’)
Execution Time: 0.123 ms

3.1.2 执行计划优化实施

# 执行计划优化实施

# 步骤1：分析复杂查询
EXPLAIN ANALYZE SELECT
c.customer_name,
p.product_name,
SUM(o.amount) as total_amount
FROM fgedu_customers c
JOIN fgedu_orders o ON c.customer_id = o.customer_id
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY c.customer_name, p.product_name
ORDER BY total_amount DESC
LIMIT 10;

# 步骤2：创建索引
CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders(order_date);
CREATE INDEX idx_fgedu_order_items_order_id ON fgedu_order_items(order_id);
CREATE INDEX idx_fgedu_order_items_product_id ON fgedu_order_items(product_id);

# 步骤3：更新统计信息
ANALYZE fgedu_customers;
ANALYZE fgedu_orders;
ANALYZE fgedu_order_items;
ANALYZE fgedu_products;

# 步骤4：再次分析执行计划
EXPLAIN ANALYZE SELECT
c.customer_name,
p.product_name,
SUM(o.amount) as total_amount
FROM fgedu_customers c
JOIN fgedu_orders o ON c.customer_id = o.customer_id
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY c.customer_name, p.product_name
ORDER BY total_amount DESC
LIMIT 10;

3.2 PostgreSQL执行计划策略

3.2.1 执行计划分析策略

# 执行计划分析策略

# 策略1：定期分析
– 定期分析重要查询的执行计划
– 监控执行计划的变化
– 及时发现性能问题

# 策略2：分类分析
– 按查询类型分类分析执行计划
– 针对不同类型的查询制定优化策略
– 重点关注复杂查询的执行计划

# 策略3：对比分析
– 对比优化前后的执行计划
– 分析索引对执行计划的影响
– 评估SQL语句优化的效果

# 示例：执行计划分析策略
— 定期分析
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date >= ‘2024-01-01’;

— 分类分析
— 简单查询
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE id = 1;

— 复杂查询
EXPLAIN ANALYZE SELECT
c.customer_name,
p.product_name,
SUM(o.amount) as total_amount
FROM fgedu_customers c
JOIN fgedu_orders o ON c.customer_id = o.customer_id
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.product_id
GROUP BY c.customer_name, p.product_name;

— 对比分析
— 优化前
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date >= ‘2024-01-01’;

— 创建索引
CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders(order_date);

— 优化后
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date >= ‘2024-01-01’;

3.2.2 执行计划优化策略

# 执行计划优化策略

# 策略1：索引优化
– 根据查询条件创建合适的索引
– 考虑复合索引
– 避免过度索引

# 策略2：SQL语句优化
– 避免SELECT *
– 使用WHERE子句过滤数据
– 优化JOIN操作
– 避免复杂的子查询

# 策略3：统计信息优化
– 定期更新统计信息
– 调整统计信息目标
– 使用扩展统计信息

# 策略4：配置参数优化
– 调整成本参数
– 优化内存配置
– 调整并行查询参数

# 示例：执行计划优化策略
— 索引优化
CREATE INDEX idx_fgedu_orders_customer_date ON fgedu_orders(customer_id, order_date);

— SQL语句优化
— 优化前
SELECT * FROM fgedu_fgedus WHERE email LIKE ‘%fgedu.net.cn’;

— 优化后
SELECT id, fgeduname, email FROM fgedu_fgedus WHERE email LIKE ‘%fgedu.net.cn’;

— 统计信息优化
ANALYZE fgedu_fgedus;

— 配置参数优化
ALTER SYSTEM SET random_page_cost = ‘1.1’;

3.3 PostgreSQL执行计划调优

3.3.1 执行计划调优步骤

# 执行计划调优步骤

# 步骤1：分析执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 步骤2：识别性能瓶颈
– 全表扫描
– 嵌套循环连接
– 排序操作
– 聚集操作

# 步骤3：制定优化策略
– 创建索引
– 优化SQL语句
– 更新统计信息
– 调整配置参数

# 步骤4：实施优化
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

# 步骤5：验证优化效果
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 示例：执行计划调优
— 分析执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date >= ‘2024-01-01’;

— 识别性能瓶颈
— 发现全表扫描

— 制定优化策略
— 创建索引

— 实施优化
CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders(order_date);

— 验证优化效果
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date >= ‘2024-01-01’;

风哥提示：执行计划调优是提高PostgreSQL性能的关键，通过分析执行计划，可以识别性能瓶颈并采取相应的优化措施。建议定期分析执行计划，不断优化SQL语句和索引。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 PostgreSQL执行计划实战案例

4.1.1 执行计划优化案例

# 执行计划优化案例

# 场景：电商网站订单查询

# 问题：查询性能慢
– 查询时间：5秒
– 执行计划：全表扫描
– 数据量：1000万行

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

# 示例：执行计划优化
— 原始查询
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’ AND customer_id = 1;

— 执行计划
QUERY PLAN
——————————————————————–
Seq Scan on fgedu_orders (cost=0.00..10000.00 rows=1000 width=100)
Filter: ((order_date >= ‘2024-01-01’) AND (order_date <= '2024-12-31') AND (customer_id = 1)) Execution Time: 5.123 ms -- 创建索引 CREATE INDEX idx_fgedu_orders_customer_date ON fgedu_orders(customer_id, order_date); -- 优化后查询 EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' AND customer_id = 1; -- 执行计划 QUERY PLAN -------------------------------------------------------------------- Index Scan using idx_fgedu_orders_customer_date on fgedu_orders (cost=0.29..8.31 rows=1000 width=100) Index Cond: ((customer_id = 1) AND (order_date >= ‘2024-01-01’) AND (order_date <= '2024-12-31')) Execution Time: 0.123 ms

4.2 PostgreSQL执行计划工具使用

4.2.1 使用EXPLAIN命令分析执行计划

# 使用EXPLAIN命令分析执行计划

# 基本用法
EXPLAIN SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 详细分析
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 包括缓冲区使用
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 包括计时
EXPLAIN (ANALYZE, TIMING) SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 包括详细信息
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 示例：使用EXPLAIN命令
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

# 结果示例
QUERY PLAN
——————————————————————–
Index Scan using idx_fgedu_orders_order_date on fgedu_orders (cost=0.29..1234.56 rows=10000 width=100)
Index Cond: ((order_date >= ‘2024-01-01’) AND (order_date <= '2024-12-31')) Buffers: shared hit=10 read=20 Execution Time: 1.234 ms

4.3 PostgreSQL执行计划常见问题

PostgreSQL执行计划常见问题及解决方法：

# 常见问题1：全表扫描

# 症状：执行计划显示全表扫描，查询性能慢

# 解决方法
– 创建合适的索引
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

– 优化SQL语句
SELECT id, fgeduname FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 常见问题2：嵌套循环连接

# 症状：执行计划显示嵌套循环连接，查询性能慢

# 解决方法
– 创建合适的索引
CREATE INDEX idx_fgedu_orders_customer_id ON fgedu_orders(customer_id);

– 调整连接顺序
— 优化SQL语句，调整表的连接顺序

# 常见问题3：排序操作

# 症状：执行计划显示排序操作，查询性能慢

# 解决方法
– 创建索引，利用索引排序
CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders(order_date DESC);

– 避免不必要的排序
— 优化ORDER BY子句

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

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

# 解决方法
– 更新统计信息
ANALYZE fgedu_fgedus;

– 调整统计信息目标
ALTER TABLE fgedu_fgedus ALTER COLUMN email SET STATISTICS 1000;

# 常见问题5：参数类型不匹配

# 症状：索引未使用，执行计划显示全表扫描

# 解决方法
– 确保参数类型匹配
SELECT * FROM fgedu_fgedus WHERE id = 1; — 正确
SELECT * FROM fgedu_fgedus WHERE id = ‘1’; — 错误

风哥教程针对风哥教程针对风哥教程针对生产环境建议：定期分析执行计划，及时发现和解决执行计划相关问题。建立执行计划分析流程，确保SQL语句的高效执行。from PostgreSQL视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 PostgreSQL执行计划最佳实践

PostgreSQL执行计划最佳实践：

  • 定期分析执行计划：确保SQL语句的高效执行
  • 创建合适的索引：根据查询条件创建索引
  • 优化SQL语句：避免不必要的列和表
  • 更新统计信息：确保统计信息的准确性
  • 调整配置参数：根据硬件情况调整成本参数
  • 使用分区表：对于大型表使用分区表
  • 监控执行计划：及时发现执行计划的变化
  • 持续优化：不断优化SQL语句和索引
风哥提示：执行计划是SQL优化的关键，通过分析执行计划，可以识别性能瓶颈并采取相应的优化措施。建议建立执行计划分析习惯，不断提高SQL语句的执行效率。

5.2 PostgreSQL执行计划检查清单

# 执行计划检查清单
– [ ] 执行计划是否使用索引
– [ ] 执行计划是否有全表扫描
– [ ] 执行计划是否有嵌套循环连接
– [ ] 执行计划是否有排序操作
– [ ] 执行计划成本估算是否合理
– [ ] 执行计划行数估算是否准确
– [ ] 索引是否被正确使用
– [ ] SQL语句是否优化

# 执行计划维护清单
– [ ] 每日：分析重要查询的执行计划
– [ ] 每周：优化慢查询的执行计划
– [ ] 每月：更新统计信息
– [ ] 每季度：调整索引策略
– [ ] 每年：评估执行计划管理策略
– [ ] 定期：测试执行计划性能

5.3 PostgreSQL执行计划工具推荐

PostgreSQL执行计划工具推荐：

  • EXPLAIN命令：分析执行计划
  • auto_explain：自动记录执行计划
  • pg_stat_statements：统计SQL执行信息
  • pg_show_plans：查看正在执行的查询计划
  • pgBadger：日志分析工具
  • pganalyze：性能分析工具
  • PostgreSQL EXPLAIN可视化工具：可视化执行计划
持续改进：执行计划优化是一个持续的过程，建议定期评估执行计划使用情况，根据业务需求和系统负载，不断调整和优化SQL语句、索引和配置参数。

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

相关推荐

联系我们

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

微信号：itpux-com

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