使用 EXPLAIN 分析查询性能

概述

PostgreSQL 的 EXPLAIN 命令是数据库性能优化的核心工具，它能够显示查询计划器为每个查询创建的执行计划。掌握 EXPLAIN 的使用对于理解查询性能、优化慢查询和数据库调优至关重要。

TIP

为什么需要 EXPLAIN？在实际业务场景中，一个看似简单的查询可能涉及复杂的表连接、索引选择和数据排序。不同的执行计划可能导致几十倍甚至几百倍的性能差异。EXPLAIN 帮助我们透视 PostgreSQL 的"思考过程"。

EXPLAIN 基础语法

基本语法格式

EXPLAIN [ ( 选项 [, ...] ) ] 语句
EXPLAIN [ ANALYZE ] [ VERBOSE ] 语句

主要选项说明

选项	说明	适用场景
ANALYZE	实际执行查询并显示真实时间	性能测试和优化
VERBOSE	显示额外的详细信息	深入分析查询计划
COSTS	显示估算成本（默认开启）	成本分析
BUFFERS	显示缓冲区使用情况	内存使用分析
FORMAT	输出格式（TEXT/XML/JSON/YAML）	自动化分析

查询计划的基本组成

成本模型解读

每个执行节点都包含以下信息：

操作类型 (cost=启动成本..总成本 rows=估算行数 width=平均行宽度)

示例分析：

EXPLAIN SELECT * FROM products WHERE price > 100;

-- 输出结果
Seq Scan on products  (cost=0.00..445.00 rows=1500 width=244)
  Filter: (price > 100::numeric)

成本解释：

• 启动成本（0.00）：开始返回第一行前的准备工作
• 总成本（445.00）：处理所有行的总估算成本
• 估算行数（1500）：预期返回的行数
• 平均行宽度（244）：每行的平均字节数

INFO

成本单位说明 PostgreSQL 的成本单位是抽象的，通常基于磁盘页面读取。一个顺序磁盘页面读取的成本为 1.0，随机页面读取的成本为 4.0（可通过参数调整）。

常见的扫描类型详解

1. 顺序扫描（Sequential Scan）

顺序扫描逐行读取整个表，适用于小表或需要访问大部分数据的查询。

-- 示例：查询所有员工信息
EXPLAIN SELECT * FROM employees;

-- 执行计划
Seq Scan on employees  (cost=0.00..22.00 rows=1200 width=245)

业务场景：

• 数据仓库中的全表聚合分析
• 小型维度表的完整扫描
• 没有适当索引的条件查询

2. 索引扫描（Index Scan）

索引扫描使用索引快速定位数据行，适用于高选择性的查询。

-- 示例：根据员工ID查询
EXPLAIN SELECT * FROM employees WHERE employee_id = 12345;

-- 执行计划
Index Scan using employees_pkey on employees  (cost=0.29..8.30 rows=1 width=245)
  Index Cond: (employee_id = 12345)

性能特点：

• 启动成本低：快速定位目标数据
• 适合小结果集：当需要返回少量行时效率最高
• 随机 I/O：可能导致磁盘随机访问

业务场景应用：

-- 电商订单查询：根据订单号查找订单详情
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_no = 'ORD2024001';

-- 用户登录验证：根据用户名查找用户信息
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';

3. 位图扫描（Bitmap Scan）

位图扫描是 PostgreSQL 的创新优化，结合了索引扫描和顺序扫描的优势。

-- 示例：查询特定价格范围的产品
EXPLAIN SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- 执行计划
Bitmap Heap Scan on products  (cost=25.07..180.45 rows=120 width=244)
  Recheck Cond: ((price >= 100) AND (price <= 500))
  ->  Bitmap Index Scan on products_price_idx  (cost=0.00..25.04 rows=120 width=0)
        Index Cond: ((price >= 100) AND (price <= 500))

工作原理：

优势分析：

• 减少随机 I/O：将随机访问转换为顺序访问
• 高效的范围查询：适合返回中等数量行的查询
• 多索引组合：可以使用 BitmapAnd 和 BitmapOr 组合多个条件

4. 仅索引扫描（Index Only Scan）

当查询的所有列都包含在索引中时，PostgreSQL 可以只扫描索引而不访问表数据。

-- 示例：统计不同部门的员工数量
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

EXPLAIN SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

-- 执行计划
Index Only Scan using idx_dept_salary on employees  (cost=0.28..45.10 rows=8 width=12)

性能优势：

• 极低的 I/O 成本：避免访问表数据页
• 缓存友好：索引通常更容易保持在内存中
• 适合覆盖索引查询：查询列完全被索引覆盖

表连接类型深度解析

1. 嵌套循环连接（Nested Loop Join）

嵌套循环连接对外表的每一行，都会扫描内表寻找匹配的行。

-- 示例：查询员工及其部门信息
EXPLAIN SELECT e.name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.id AND e.salary > 50000;

-- 执行计划
Nested Loop  (cost=4.65..118.50 rows=10 width=64)
  ->  Index Scan using employees_salary_idx on employees e  (cost=0.29..45.30 rows=10 width=36)
        Index Cond: (salary > 50000)
  ->  Index Scan using departments_pkey on departments d  (cost=0.28..7.32 rows=1 width=32)
        Index Cond: (id = e.department_id)

性能特征：

适用场景：

• 小表连接：外表行数较少
• 高选择性连接：连接条件能显著减少数据量
• 有适当索引：内表连接列有索引支持

业务实例：

-- 查询VIP客户的最近订单
EXPLAIN ANALYZE
SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_level = 'VIP'
  AND o.order_date >= CURRENT_DATE - INTERVAL '30 days';

2. 哈希连接（Hash Join）

哈希连接将较小的表构建成内存哈希表，然后扫描较大的表进行探测。

-- 示例：分析订单和产品的关联
EXPLAIN SELECT o.order_id, p.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';

-- 执行计划示例
Hash Join  (cost=226.23..709.73 rows=1500 width=68)
  Hash Cond: (oi.product_id = p.product_id)
  ->  Hash Join  (cost=45.00..320.50 rows=1500 width=40)
        Hash Cond: (oi.order_id = o.order_id)
        ->  Seq Scan on order_items oi  (cost=0.00..250.00 rows=15000 width=24)
        ->  Hash  (cost=40.00..40.00 rows=400 width=20)
              ->  Index Scan using orders_date_idx on orders o  (cost=0.29..40.00 rows=400 width=20)
                    Index Cond: (order_date >= '2024-01-01'::date)
  ->  Hash  (cost=150.00..150.00 rows=5000 width=36)
        ->  Seq Scan on products p  (cost=0.00..150.00 rows=5000 width=36)

哈希连接工作流程：

性能特点：

• 高吞吐量：适合处理大量数据
• 内存敏感：需要足够内存构建哈希表
• 等值连接：仅支持等值连接条件
• 批处理友好：一次性处理所有匹配

3. 合并连接（Merge Join）

合并连接要求两个输入都按连接键排序，然后同步扫描两个排序后的数据流。

-- 示例：时间序列数据分析
EXPLAIN SELECT s1.timestamp, s1.value as sensor1_value, s2.value as sensor2_value
FROM sensor_data_1 s1
JOIN sensor_data_2 s2 ON s1.timestamp = s2.timestamp
WHERE s1.timestamp BETWEEN '2024-01-01' AND '2024-01-31';

-- 执行计划
Merge Join  (cost=0.56..233.49 rows=1000 width=24)
  Merge Cond: (s1.timestamp = s2.timestamp)
  ->  Index Scan using sensor1_timestamp_idx on sensor_data_1 s1  (cost=0.29..120.50 rows=1000 width=16)
        Index Cond: ((timestamp >= '2024-01-01') AND (timestamp <= '2024-01-31'))
  ->  Index Scan using sensor2_timestamp_idx on sensor_data_2 s2  (cost=0.28..112.99 rows=1000 width=16)
        Index Cond: ((timestamp >= '2024-01-01') AND (timestamp <= '2024-01-31'))

合并连接的优势：

• 内存效率：不需要将整个表加载到内存
• 大数据友好：可以处理超出内存限制的大型数据集
• 排序复用：如果数据已排序，避免额外排序成本

业务场景：

-- 金融交易对账：匹配银行流水和内部交易记录
EXPLAIN ANALYZE
SELECT b.transaction_id, b.amount, i.internal_ref
FROM bank_transactions b
JOIN internal_transactions i ON b.transaction_time = i.transaction_time
WHERE b.transaction_date = CURRENT_DATE
ORDER BY b.transaction_time;

子查询和复杂计划分析

子计划（SubPlan）

当查询包含相关子查询时，PostgreSQL 会为每个外层行执行子查询。

-- 示例：查找高于部门平均薪资的员工
EXPLAIN VERBOSE
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

-- 执行计划
Seq Scan on employees e  (cost=0.00..586095.00 rows=5000 width=36)
  Output: e.name, e.salary
  Filter: (e.salary > (SubPlan 1).col1)
  SubPlan 1
    ->  Aggregate  (cost=116.25..116.26 rows=1 width=32)
          Output: avg(e2.salary)
          ->  Seq Scan on employees e2  (cost=0.00..116.00 rows=100 width=4)
                Output: e2.salary
                Filter: (e2.department_id = e.department_id)

性能影响分析：

• 高成本操作：子查询为每个外层行执行一次
• 优化机会：考虑改写为窗口函数或 JOIN

优化改写示例：

-- 原始相关子查询（低效）
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees e2
    WHERE e2.department_id = e.department_id
);

-- 优化后的窗口函数版本（高效）
SELECT name, salary
FROM (
    SELECT name, salary,
           AVG(salary) OVER (PARTITION BY department_id) as dept_avg
    FROM employees
) t
WHERE salary > dept_avg;

哈希子计划（Hashed SubPlan）

对于 IN 或 NOT IN 子查询，PostgreSQL 可能使用哈希子计划优化。

-- 示例：查找没有订单的客户
EXPLAIN SELECT customer_name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- 执行计划
Seq Scan on customers c  (cost=61.77..531.77 rows=2500 width=64)
  Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
  SubPlan 1
    ->  Index Only Scan using orders_customer_idx on orders  (cost=0.28..59.27 rows=1000 width=4)

优化要点：

• 一次性构建：子查询只执行一次，结果缓存在哈希表中
• 内存限制：受 work_mem 参数限制
• NULL 值处理：注意 NOT IN 与 NULL 值的语义

EXPLAIN ANALYZE 实战分析

EXPLAIN ANALYZE 实际执行查询并提供真实的性能数据，是性能调优的核心工具。

基本使用和解读

-- 分析复杂查询的实际性能
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT c.customer_name,
       COUNT(o.order_id) as order_count,
       SUM(o.total_amount) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.registration_date >= '2024-01-01'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 5
ORDER BY total_spent DESC
LIMIT 10;

输出解读：

Limit  (cost=1547.83..1547.86 rows=10 width=84) (actual time=45.234..45.237 rows=10 loops=1)
  Output: c.customer_name, (count(o.order_id)), (sum(o.total_amount))
  Buffers: shared hit=1247 read=156
  ->  Sort  (cost=1547.83..1548.58 rows=300 width=84) (actual time=45.232..45.234 rows=10 loops=1)
        Output: c.customer_name, (count(o.order_id)), (sum(o.total_amount))
        Sort Key: (sum(o.total_amount)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=1247 read=156
        ->  HashAggregate  (cost=1535.00..1540.00 rows=300 width=84) (actual time=44.851..45.034 rows=287 loops=1)
              Output: c.customer_name, count(o.order_id), sum(o.total_amount)
              Group Key: c.customer_id, c.customer_name
              Planned Partitions: 4  Batches: 1  Memory Usage: 61kB
              Buffers: shared hit=1247 read=156

关键性能指标：

指标	含义	优化方向
actual time=45.234..45.237	实际执行时间（毫秒）	减少执行时间
rows=10	实际返回行数	验证估算准确性
loops=1	执行次数	避免意外的多次执行
shared hit=1247	缓冲区命中数	提高缓存命中率
read=156	磁盘读取页数	减少磁盘 I/O
Memory: 25kB	内存使用量	优化内存配置

性能瓶颈识别

1. 识别慢操作：

-- 查找执行时间最长的操作节点
-- 关注 actual time 较大的节点
Seq Scan on large_table  (cost=0.00..425678.25 rows=15678925 width=45)
                        (actual time=0.034..12847.234 rows=15678925 loops=1)

2. 识别估算偏差：

-- 估算行数与实际行数差异巨大
Hash Join  (cost=123.45..456.78 rows=100 width=64)
          (actual time=1234.567..2345.678 rows=1000000 loops=1)
--        ^^^^^^^^^ 估算100行
--                                       ^^^^^^^ 实际100万行

WARNING

估算偏差影响当估算行数与实际行数差异巨大时，查询计划器可能选择不优的执行计划。这通常需要更新表统计信息或调整查询写法。

3. 内存使用分析：

-- 监控内存溢出到磁盘的情况
HashAggregate  (cost=1234.56..2345.67 rows=50000 width=32)
              (actual time=567.89..789.01 rows=50000 loops=1)
  Group Key: department_id
  Planned Partitions: 4  Batches: 8  Memory Usage: 2048kB  Disk Usage: 15624kB
--                     ^^^^^^^^^ 发生磁盘溢出

查询优化实战技巧

1. 索引优化策略

创建合适的索引：

-- 分析查询模式
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123 AND order_date >= '2024-01-01'
ORDER BY order_date DESC;

-- 如果出现 Seq Scan，考虑创建复合索引
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);

-- 再次分析验证改进
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123 AND order_date >= '2024-01-01'
ORDER BY order_date DESC;

覆盖索引优化：

-- 原始查询需要访问表数据
EXPLAIN SELECT customer_id, order_date, total_amount
FROM orders WHERE status = 'completed';

-- 创建覆盖索引
CREATE INDEX idx_orders_status_covering
ON orders(status) INCLUDE (order_date, total_amount);

-- 查询现在可以使用 Index Only Scan

2. 连接优化技巧

强制使用特定连接类型：

-- 禁用某种连接类型进行对比测试
SET enable_hashjoin = off;
EXPLAIN ANALYZE SELECT ... FROM table1 t1 JOIN table2 t2 ON ...;

-- 恢复设置
SET enable_hashjoin = on;

连接顺序优化：

-- 使用 JOIN 语法明确连接顺序
SELECT /*+ USE_NESTED_LOOP(a b) */ *
FROM small_table a
JOIN large_table b ON a.id = b.small_table_id
WHERE a.active = true;

3. 统计信息管理

更新表统计信息：

-- 分析特定表
ANALYZE orders;

-- 分析所有表
ANALYZE;

-- 设置统计信息收集的详细程度
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

检查统计信息质量：

-- 查看表的统计信息
SELECT schemaname, tablename, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

性能监控和诊断

1. 慢查询日志分析

-- 启用慢查询日志
SET log_min_duration_statement = 1000; -- 记录超过1秒的查询

-- 分析日志中的查询计划
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
<慢查询语句>;

2. 系统视图监控

-- 查看当前活跃查询
SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE state = 'active';

-- 分析表的访问统计
SELECT schemaname, tablename, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

3. 执行计划缓存

-- 使用 pg_stat_statements 扩展分析查询性能
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

高级 EXPLAIN 选项

BUFFERS 选项详解

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table WHERE indexed_column = 'value';

-- 输出包含缓冲区统计
Buffers: shared hit=245 read=12 dirtied=3 written=1
--       ^^^^^^^^^ 缓存命中   ^^^^^^^ 磁盘读取
--                           ^^^^^^^^^ 脏页数  ^^^^^^^^^ 写入磁盘页数

缓冲区指标含义：

• shared hit：从共享缓冲区命中的页数
• shared read：从磁盘读取的页数
• shared dirtied：被标记为脏的页数
• shared written：写入磁盘的页数

FORMAT 选项应用

-- JSON 格式便于程序化分析
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT customer_name, COUNT(*)
FROM customers c JOIN orders o ON c.id = o.customer_id
GROUP BY customer_name;

常见性能问题和解决方案

1. 全表扫描问题

问题识别：

-- 大表出现 Seq Scan
Seq Scan on orders  (cost=0.00..425678.25 rows=15678925 width=45)
                   (actual time=0.034..12847.234 rows=15678925 loops=1)
  Filter: (order_date >= '2024-01-01'::date)
  Rows Removed by Filter: 12345678

解决方案：

-- 创建适当的索引
CREATE INDEX idx_orders_date ON orders(order_date);

-- 考虑分区表
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

2. 连接性能问题

嵌套循环过度执行：

-- 内层循环执行次数过多
Nested Loop  (cost=1.23..45678.90 rows=100 width=64)
            (actual time=0.456..23456.789 rows=100 loops=1)
  ->  Seq Scan on customers  (cost=0.00..123.45 rows=10000 width=32)
                            (actual time=0.012..234.567 rows=10000 loops=1)
  ->  Index Scan using orders_customer_idx on orders  (cost=0.43..4.56 rows=1 width=32)
                                                      (actual time=2.345..2.346 rows=0 loops=10000)
        Index Cond: (customer_id = customers.id)

优化策略：

-- 改用哈希连接
SET enable_nestloop = off;
-- 或者优化查询条件减少外层行数
SELECT ... FROM customers WHERE active = true AND ...

3. 排序性能问题

内存不足导致外部排序：

Sort  (cost=123456.78..234567.89 rows=1000000 width=32)
     (actual time=12345.678..23456.789 rows=1000000 loops=1)
  Sort Key: order_date DESC
  Sort Method: external merge  Disk: 456789kB
--            ^^^^^^^^^^^^^ 使用磁盘排序，性能较差

优化方案：

-- 增加工作内存
SET work_mem = '256MB';

-- 或使用索引避免排序
CREATE INDEX idx_orders_date_desc ON orders(order_date DESC);

最佳实践总结

1. 日常使用建议

TIP

性能调优流程

1. 识别慢查询：使用 pg_stat_statements 或慢查询日志
2. 分析执行计划：使用 EXPLAIN ANALYZE 详细分析
3. 找出瓶颈：关注高成本操作和实际执行时间
4. 制定优化策略：索引、查询重写、参数调整
5. 验证改进效果：重新分析执行计划确认优化效果

2. 监控指标体系

层级	关键指标	阈值建议
查询级别	执行时间、返回行数	>1 秒需关注
I/O 级别	缓冲区命中率、磁盘读取	命中率>95%
内存级别	工作内存使用、溢出到磁盘	避免磁盘溢出
系统级别	CPU 使用率、I/O 等待	CPU<80%, I/O 等待<20%

3. 索引设计原则

-- 1. 查询频繁的过滤条件
CREATE INDEX idx_orders_status ON orders(status) WHERE status IN ('pending', 'processing');

-- 2. 覆盖查询所需列
CREATE INDEX idx_orders_covering ON orders(customer_id) INCLUDE (order_date, total_amount);

-- 3. 支持排序需求
CREATE INDEX idx_orders_date_desc ON orders(order_date DESC, customer_id);

-- 4. 复合索引列顺序：选择性高的列在前
CREATE INDEX idx_orders_multi ON orders(status, customer_id, order_date);

通过系统性地使用 EXPLAIN 和 EXPLAIN ANALYZE，结合业务场景和性能指标，我们可以持续优化 PostgreSQL 数据库的查询性能，确保系统在各种负载下都能保持良好的响应速度。