👋 大家好，欢迎来到我的技术博客！
📚 在这里，我会分享学习笔记、实战经验与技术思考，力求用简单的方式讲清楚复杂的问题。
🎯 本文将围绕PostgreSQL这个话题展开，希望能为你带来一些启发或实用的参考。
🌱 无论你是刚入门的新手，还是正在进阶的开发者，希望你都能有所收获！

---

PostgreSQL - 执行计划的查看与基础分析方法

在现代应用开发中，数据库性能往往是系统整体性能的关键瓶颈。作为一款功能强大、开源且高度可扩展的关系型数据库，PostgreSQL 在企业级应用中被广泛采用。然而，即使拥有再优秀的数据库引擎，如果 SQL 查询编写不当或缺乏对执行计划的理解，依然会导致严重的性能问题。

理解 PostgreSQL 的执行计划（Execution Plan）是每一位后端开发者和 DBA 必须掌握的核心技能。它不仅揭示了数据库如何执行你的查询，还提供了优化方向的重要线索。本文将深入探讨 PostgreSQL 执行计划的查看方式、核心概念、常见节点类型、性能指标解读，并结合 Java 应用场景给出实用示例，帮助你从“会写 SQL”进阶到“会写高效 SQL”。

---

什么是执行计划？

执行计划是 PostgreSQL 查询规划器（Planner）为执行一条 SQL 语句所生成的详细操作步骤。当你提交一个查询时，PostgreSQL 并不会立即执行它，而是先进行成本估算（Cost Estimation），基于统计信息（如表大小、索引分布、列值频率等）生成一个或多个可能的执行路径，然后选择预计总成本最低的方案作为最终执行计划。

这个计划以树状结构呈现，从最底层的操作（如扫描表）开始，逐层向上组合，最终返回结果。每一层节点代表一种操作类型（如 Seq Scan、Index Scan、Hash Join 等），并附带关键性能指标，如启动成本（Startup Cost）、总成本（Total Cost）、实际行数（Actual Rows）等。

💡 小知识：PostgreSQL 的查询优化器是基于成本模型（Cost-based Optimizer, CBO）的，而非规则模型（RBO）。这意味着它依赖于准确的统计信息来做出最优决策。

---

如何查看执行计划？

PostgreSQL 提供了两种主要命令来查看执行计划：

1. EXPLAIN —— 查看预估执行计划

EXPLAIN SELECT * FROM users WHERE age > 30;

该命令不会真正执行查询，仅返回规划器根据统计信息估算出的执行计划。输出包含每个节点的预估成本和预估行数。

2. EXPLAIN (ANALYZE) —— 查看实际执行计划

EXPLAIN (ANALYZE) SELECT * FROM users WHERE age > 30;

此命令会实际执行查询，并在返回结果的同时提供实际运行时间、实际行数、循环次数等真实性能数据。这是性能调优中最常用的命令。

⚠️ 注意：EXPLAIN (ANALYZE) 会修改数据吗？
不会！对于 SELECT 语句是安全的。但对于 UPDATE、DELETE 或 INSERT ... RETURNING 等写操作，EXPLAIN (ANALYZE) 会真正执行并修改数据！务必在测试环境使用。

常用选项组合

除了 ANALYZE，EXPLAIN 还支持多个选项，常用组合如下：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) 
SELECT u.name, o.total 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'active';

• ANALYZE：执行查询并返回实际性能数据。
• BUFFERS：显示 I/O 缓冲区使用情况（需开启 track_io_timing）。
• VERBOSE：提供更多细节，如表别名、字段来源等。
• FORMAT JSON：以 JSON 格式输出，便于程序解析（默认为文本格式）。

🔗 官方文档参考：PostgreSQL EXPLAIN 文档

---

执行计划基础结构解析

让我们通过一个简单例子理解执行计划的结构：

EXPLAIN (ANALYZE) SELECT name FROM users WHERE id = 100;

输出可能如下：

Index Only Scan using users_pkey on users  (cost=0.29..8.31 rows=1 width=12)
  (actual time=0.025..0.026 rows=1 loops=1)
  Index Cond: (id = 100)
Planning Time: 0.150 ms
Execution Time: 0.050 ms

关键字段解读

• 节点类型：Index Only Scan 表示使用了索引覆盖扫描，无需回表。
• 成本（Cost）：
  • 0.29：启动成本（返回第一行所需成本）
  • 8.31：总成本（返回所有行的总成本）
  • 成本单位是“磁盘页面读取”的抽象单位，非真实时间。
• 行宽（width）：12 表示每行平均 12 字节。
• 实际时间（actual time）：单位为毫秒，0.025..0.026 表示从启动到返回第一行耗时 0.025ms，总耗时 0.026ms。
• 实际行数（rows）：返回 1 行。
• 循环次数（loops）：该节点被执行了 1 次。

📌 重要原则：预估行数（rows）与实际行数（Actual Rows）的偏差越大，说明统计信息越不准确，可能导致次优执行计划。

---

常见执行计划节点类型详解

理解不同节点的含义是分析执行计划的基础。以下是 PostgreSQL 中最常见的几类节点：

1. 扫描类节点（Scan Nodes）

✅ Seq Scan（顺序扫描）

全表扫描，逐行读取。适用于无索引或返回大量数据的场景。

EXPLAIN SELECT * FROM logs; -- 大概率触发 Seq Scan

⚠️ 如果小表上出现 Seq Scan 是正常的；但大表上频繁 Seq Scan 可能意味着缺失索引。

✅ Index Scan（索引扫描）

通过索引定位行，然后回表获取完整数据。适用于选择性高的条件。

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- 假设 email 有唯一索引

✅ Index Only Scan（索引覆盖扫描）

所需数据全部在索引中，无需回表。性能最优！

EXPLAIN SELECT id FROM users WHERE id BETWEEN 1 AND 1000;
-- 若 id 是主键（即索引），则可能触发 Index Only Scan

💡 提示：要实现 Index Only Scan，查询字段必须全部包含在索引中，且表的 visibility map 需及时更新（可通过 VACUUM 维护）。

2. 连接类节点（Join Nodes）

✅ Nested Loop

对左表每行，遍历右表匹配。适合左表小、右表有索引的情况。

✅ Hash Join

构建哈希表（通常为较小表），然后探测大表。内存充足时效率高。

✅ Merge Join

要求两表已按连接键排序。适合大表且已排序的场景。

🔗 更多连接算法原理可参考：PostgreSQL Join Strategies

3. 聚合与排序节点

✅ HashAggregate

基于哈希表进行分组聚合，速度快，但消耗内存。

✅ GroupAggregate

要求输入已按 GROUP BY 列排序，适合已排序数据。

✅ Sort

显式排序操作。若 Sort Method: external merge Disk 出现，说明内存不足，使用了磁盘临时文件，性能较差。

---

执行计划中的关键性能指标

除了节点类型，还需关注以下指标：

1. 预估 vs 实际行数偏差

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status = 'pending';

输出片段：

Seq Scan on orders  (cost=0.00..1000.00 rows=10000 width=100)
  (actual time=0.100..50.000 rows=500000 loops=1)

• 预估 1 万行，实际 50 万行 → 偏差 50 倍！
• 后续基于此预估的连接、排序等操作都可能选择错误策略。

解决方案：

• 手动更新统计信息：ANALYZE orders;
• 增加统计目标：ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

2. 启动时间（Startup Time）过高

某些操作（如 Sort、HashAggregate）需要处理完所有输入才能返回第一行，导致启动时间长。

EXPLAIN (ANALYZE) SELECT * FROM large_table ORDER BY created_at LIMIT 10;

若未使用索引，可能先全表排序再取前 10 行，效率极低。

优化方案：在 created_at 上创建索引，使查询变为 Index Scan + Limit，启动时间趋近于 0。

3. 循环次数（Loops）异常

在嵌套循环中，若内层循环次数远高于预期，说明外层行数预估不准。

Nested Loop  (rows=1000 loops=1)
  ->  Seq Scan on table_a  (rows=1000)
  ->  Index Scan on table_b  (rows=1 loops=1000)  -- 执行了 1000 次！

若 table_b 的索引选择性差，每次扫描返回大量数据，总成本将飙升。

---

使用 BUFFERS 分析 I/O 性能

开启 BUFFERS 可查看缓存命中情况，判断是否频繁读磁盘：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM logs WHERE event_time > '2023-01-01';

输出可能包含：

Buffers: shared hit=1200 read=800

• hit=1200：从 shared_buffers 命中 1200 个页面（内存）
• read=800：从磁盘读取 800 个页面

理想情况：read 接近 0，说明数据基本在缓存中。
问题信号：高 read 值，尤其在重复查询中，说明缓存不足或查询模式不佳。

🔧 建议：生产环境应开启 track_io_timing = on（默认 off），以获取更精确的 I/O 时间。

---

Java 应用中如何获取与分析执行计划？

在 Java 应用中，我们通常通过 JDBC 执行 SQL。虽然不能直接使用 EXPLAIN 返回 ResultSet，但可以通过以下方式获取执行计划：

方法一：执行 EXPLAIN 作为普通查询

import java.sql.*;

public class ExplainPlanExample {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String user = "user";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement()) {

            // 获取执行计划（文本格式）
            String explainSql = "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age > 30";
            ResultSet rs = stmt.executeQuery(explainSql);

            System.out.println("=== Execution Plan ===");
            while (rs.next()) {
                System.out.println(rs.getString(1)); // EXPLAIN 结果只有一列
            }
        }
    }
}

⚠️ 注意：EXPLAIN (ANALYZE) 对写操作会真实执行！建议在 Java 中封装安全开关。

方法二：使用 pg_stat_statements 扩展（推荐用于生产监控）

pg_stat_statements 是 PostgreSQL 官方提供的扩展，可记录所有 SQL 的执行统计信息，包括平均执行时间、调用次数、块 I/O 等。

启用步骤：

1. 在 postgresql.conf 中添加：

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all
   

2. 重启 PostgreSQL。
3. 创建扩展：

   CREATE EXTENSION pg_stat_statements;
   

Java 查询慢 SQL 示例：

public List<SlowQuery> getTopSlowQueries(Connection conn) throws SQLException {
    String sql = """
        SELECT query, 
               calls,
               total_exec_time / calls AS avg_time_ms,
               rows / calls AS avg_rows
        FROM pg_stat_statements
        ORDER BY total_exec_time DESC
        LIMIT 10;
        """;

    try (PreparedStatement ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {

        List<SlowQuery> list = new ArrayList<>();
        while (rs.next()) {
            SlowQuery sq = new SlowQuery();
            sq.setQuery(rs.getString("query"));
            sq.setAvgTimeMs(rs.getDouble("avg_time_ms"));
            sq.setAvgRows(rs.getDouble("avg_rows"));
            list.add(sq);
        }
        return list;
    }
}

🔗 了解更多：pg_stat_statements 官方文档

方法三：集成 APM 工具（如 Prometheus + Grafana）

通过 postgres_exporter 将 PostgreSQL 指标暴露给 Prometheus，再用 Grafana 可视化，实现长期性能监控。

---

常见性能问题与优化策略

问题 1：缺失索引导致全表扫描

现象：大表上 Seq Scan，actual rows 高，执行时间长。

优化：

-- 为高频过滤字段创建索引
CREATE INDEX idx_users_age ON users(age);

💡 使用 pg_stat_user_indexes 查看索引使用率，删除未使用的索引。

问题 2：JOIN 顺序错误

现象：大表驱动小表，Nested Loop 效率低下。

原因：统计信息不准或连接条件复杂。

优化：

• 确保连接字段有索引。
• 使用 ANALYZE 更新统计。
• 考虑重写查询，明确驱动表（如使用 CTE）。

问题 3：排序使用磁盘临时文件

现象：Sort Method: external merge Disk: 12345kB

优化：

• 增加 work_mem（会话级或全局）：

  SET work_mem = '64MB';
  

• 为排序字段创建索引，避免显式排序。

问题 4：高并发下的锁竞争

虽然执行计划不直接显示锁，但可通过 pg_stat_activity 结合分析。

建议：

• 避免长事务。
• 使用 SELECT FOR UPDATE SKIP LOCKED 处理队列场景。

---

执行计划的高级技巧

1. 强制使用特定索引（调试用）

-- 禁用顺序扫描（仅用于测试！）
SET enable_seqscan = off;
EXPLAIN SELECT * FROM users WHERE age > 30;
-- 观察是否使用索引，以及成本变化
SET enable_seqscan = on; -- 记得恢复！

其他开关：enable_indexscan, enable_hashjoin 等。

⚠️ 切勿在生产环境永久关闭优化器选项！

2. 使用 auto_explain 自动记录慢查询计划

在 postgresql.conf 中配置：

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = true
auto_explain.log_buffers = true

重启后，所有执行时间超过 1 秒的查询会自动记录执行计划到日志。

🔗 参考：auto_explain 扩展文档

3. 可视化执行计划工具

虽然本文不使用图片，但推荐几个在线工具可粘贴 EXPLAIN (FORMAT JSON) 输出生成可视化图：

• https://explain.dalibo.com/ —— 简洁直观，支持动画
• https://pgexplain.dev/ —— 功能丰富，支持对比

---

案例分析：电商订单查询优化

假设我们有一个电商系统，需要查询某用户最近 30 天的订单及商品信息。

初始查询

SELECT o.id, o.total, p.name, p.price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 12345
  AND o.created_at >= NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC;

执行计划分析

假设 EXPLAIN (ANALYZE) 输出显示：

• Seq Scan on orders（orders 表 1000 万行）
• Nested Loop 执行 5000 次（因 user_id=12345 有 5000 个订单）
• 总执行时间 2.5 秒

优化步骤

1. 为 orders 表添加复合索引：

   CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
   

2. 确保 order_items 和 products 有主键索引（通常已有）

3. 重跑查询，观察执行计划变为：

   • Index Scan using idx_orders_user_created
   • Index Only Scan on order_items
   • 总时间降至 50ms

Java 代码集成

在 Spring Boot 中，可通过 AOP 记录慢查询：

@Aspect
@Component
public class SlowQueryLogger {
    
    private static final long SLOW_THRESHOLD_MS = 1000;
    
    @Around("@annotation(org.springframework.web.bind.annotation.RequestMapping)")
    public Object logSlowQueries(ProceedingJoinPoint joinPoint) throws Throwable {
        long start = System.currentTimeMillis();
        Object result = joinPoint.proceed();
        long duration = System.currentTimeMillis() - start;
        
        if (duration > SLOW_THRESHOLD_MS) {
            // 记录日志或发送告警
            log.warn("Slow query detected: {} ms", duration);
        }
        return result;
    }
}

结合 pg_stat_statements，可实现端到端的性能监控闭环。

---

总结与最佳实践

理解并善用 PostgreSQL 的执行计划，是提升应用性能的关键一步。以下是几点核心建议：

1. 定期更新统计信息：ANALYZE 应纳入日常维护。
2. 优先使用 EXPLAIN (ANALYZE, BUFFERS)：真实数据胜过预估。
3. 关注预估与实际的偏差：这是优化器“失明”的信号。
4. 索引不是越多越好：权衡写性能与存储成本。
5. 在 Java 应用中集成监控：通过 pg_stat_statements 或 APM 工具实现主动发现。
6. 不要盲目相信“经验”：同一查询在不同数据量下可能需要不同策略。

最后记住：没有放之四海而皆准的优化方案，只有基于数据和执行计划的理性决策。

🌟 延伸阅读：

• PostgreSQL Performance Tuning Guide
• Use The Index, Luke! —— 索引原理经典教程

愿你在数据库性能优化的道路上，少走弯路，多出成果！🚀

---

🙌 感谢你读到这里！
🔍 技术之路没有捷径，但每一次阅读、思考和实践，都在悄悄拉近你与目标的距离。
💡 如果本文对你有帮助，不妨 👍 点赞、📌 收藏、📤 分享 给更多需要的朋友！
💬 欢迎在评论区留下你的想法、疑问或建议，我会一一回复，我们一起交流、共同成长 🌿
🔔 关注我，不错过下一篇干货！我们下期再见！✨