执行计划分析

执行计划是PostgreSQL查询优化器为SQL语句生成的执行方案，它决定了SQL语句如何执行，包括表的访问方式、连接顺序、连接方法等。通过分析执行计划，可以识别查询性能瓶颈，优化SQL语句和数据库配置，从而提高查询性能。本文将详细介绍执行计划的基本概念、分析方法和优化策略。

执行计划基本概念

1. 查询优化器

PostgreSQL查询优化器（Query Optimizer）负责为每个SQL语句生成最优执行计划，它基于成本模型选择成本最低的执行计划。

成本模型：考虑CPU成本、I/O成本、内存使用等因素
统计信息：基于pg_statistic表中的统计信息估算执行成本
索引选择：评估不同索引的成本，选择最优索引
连接顺序：评估不同表连接顺序的成本，选择最优顺序
连接方法：选择合适的连接方法（嵌套循环、哈希连接、合并连接）

2. 执行计划节点

执行计划由多个节点组成，每个节点代表一个操作，如扫描表、连接表、排序、聚合等。

叶节点：直接访问表或索引，如Seq Scan、Index Scan等
中间节点：对叶节点的结果进行处理，如Nested Loop、Hash Join、Sort等
根节点：执行计划的入口，如Result、Limit等

3. 执行计划成本

执行计划中的成本包括：

启动成本：获取第一行结果所需的成本
总成本：获取所有结果所需的成本
预估行数：优化器预估的结果行数
预估宽度：优化器预估的每行结果的宽度（字节数）

EXPLAIN命令

EXPLAIN命令用于查看PostgreSQL为SQL语句生成的执行计划。

1. 基本使用

sql

-- 查看预估执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 查看实际执行计划（包含实际执行时间和行数）
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 查看详细的执行计划，包括缓冲区使用
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM users WHERE email = 'test@example.com';

2. EXPLAIN选项

选项	描述
ANALYZE	执行查询并显示实际执行时间和行数
BUFFERS	显示缓冲区使用情况
VERBOSE	显示详细信息，包括列名、表OID等
COSTS	显示成本估算（默认开启）
SETTINGS	显示影响执行计划的配置参数
SUMMARY	显示执行计划的摘要信息
TIMING	显示每个节点的执行时间（默认开启，需配合ANALYZE）
FORMAT	指定输出格式（TEXT, XML, JSON, YAML）

3. 输出格式

文本格式

QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on users  (cost=0.00..100.00 rows=1 width=100) (actual time=0.010..0.010 rows=1 loops=1)
  Filter: (email = 'test@example.com'::text)
  Rows Removed by Filter: 999
  Buffers: shared hit=1
Planning Time: 0.050 ms
Execution Time: 0.020 ms

JSON格式

sql

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE email = 'test@example.com';

输出：

json

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Relation Name": "users",
      "Alias": "users",
      "Startup Cost": 0.0,"Total Cost": 100.0,
      "Plan Rows": 1,
      "Plan Width": 100,
      "Actual Startup Time": 0.010,
      "Actual Total Time": 0.010,
      "Actual Rows": 1,
      "Actual Loops": 1,
      "Filter": "(email = 'test@example.com'::text)",
      "Rows Removed by Filter": 999,
      "Shared Hit Blocks": 1,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 0.050,
    "Execution Time": 0.020
  }
]

执行计划节点分析

1. 表扫描节点

Seq Scan（全表扫描）

Seq Scan on users  (cost=0.00..100.00 rows=1 width=100) (actual time=0.010..0.010 rows=1 loops=1)
  Filter: (email = 'test@example.com'::text)
  Rows Removed by Filter: 999

特点：

扫描表中的所有行
适用于小表或没有合适索引的情况
成本与表的大小成正比

优化建议：

如果表较大，考虑创建合适的索引
增加work_mem参数，提高排序和哈希操作的性能

Index Scan（索引扫描）

Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1 width=100) (actual time=0.010..0.010 rows=1 loops=1)
  Index Cond: (email = 'test@example.com'::text)

特点：

使用索引查找符合条件的行
然后回表获取完整数据
适用于选择性高的查询条件

优化建议：

确保索引选择性高（返回的行数少）
考虑创建覆盖索引，避免回表操作

Index Only Scan（仅索引扫描）

Index Only Scan using idx_users_email on users  (cost=0.29..4.30 rows=1 width=50)
  Index Cond: (email = 'test@example.com'::text)
  Heap Fetches: 0

特点：

只扫描索引，不需要回表
适用于查询的列都包含在索引中
需要表的可见性映射（VM）支持

优化建议：

定期执行VACUUM，更新可见性映射
创建覆盖索引，包含查询所需的所有列

Bitmap Heap Scan + Bitmap Index Scan（位图堆扫描+位图索引扫描）

Bitmap Heap Scan on users  (cost=4.30..12.30 rows=5 width=100)
  Recheck Cond: (age > 30)
  ->  Bitmap Index Scan on idx_users_age  (cost=0.00..4.30 rows=5 width=0)
        Index Cond: (age > 30)

特点：

先使用位图索引扫描获取符合条件的行的位置
然后使用位图堆扫描批量获取数据
适用于选择性中等的查询条件

优化建议：

增加work_mem参数，提高位图操作的性能
考虑调整random_page_cost参数，优化索引扫描成本估算

2. 连接节点

Nested Loop（嵌套循环连接）

Nested Loop  (cost=0.58..20.60 rows=5 width=200)
  ->  Index Scan using idx_users_id on users  (cost=0.29..8.30 rows=1 width=100)
        Index Cond: (id = 1)
  ->  Index Scan using idx_orders_user_id on orders  (cost=0.29..12.30 rows=5 width=100)
        Index Cond: (user_id = users.id)

特点：

外层循环驱动内层循环
适用于外层结果集小的情况
支持所有连接类型（INNER, LEFT, RIGHT, FULL）

优化建议：

确保外层查询返回的行数少
为连接列创建索引
考虑调整join_collapse_limit参数，控制连接顺序优化

Hash Join（哈希连接）

Hash Join  (cost=16.30..32.30 rows=50 width=200)
  Hash Cond: (orders.user_id = users.id)
  ->  Seq Scan on orders  (cost=0.00..10.00 rows=100 width=100)
  ->  Hash  (cost=12.00..12.00 rows=30 width=100)
        ->  Seq Scan on users  (cost=0.00..12.00 rows=30 width=100)
              Filter: (age > 30)

特点：

先将小表构建哈希表
然后扫描大表，使用哈希表查找匹配行
适用于等值连接，且其中一个表较小的情况

优化建议：

增加work_mem参数，提高哈希表的大小
确保小表先被处理（PostgreSQL会自动优化）
适用于OLAP查询

Merge Join（合并连接）

Merge Join  (cost=24.30..40.30 rows=50 width=200)
  Merge Cond: (users.id = orders.user_id)
  ->  Sort  (cost=12.00..12.30 rows=30 width=100)
        Sort Key: users.id
        ->  Seq Scan on users  (cost=0.00..12.00 rows=30 width=100)
              Filter: (age > 30)
  ->  Sort  (cost=12.00..12.50 rows=100 width=100)
        Sort Key: orders.user_id
        ->  Seq Scan on orders  (cost=0.00..10.00 rows=100 width=100)

特点：

先对两个表进行排序
然后合并排序后的结果
适用于连接列已排序或有索引的情况
支持所有连接类型

优化建议：

为连接列创建索引，避免排序操作
增加work_mem参数，提高排序性能
适用于大表连接

3. 其他常用节点

Sort（排序）

Sort  (cost=12.30..12.50 rows=50 width=100)
  Sort Key: created_at DESC
  ->  Seq Scan on orders  (cost=0.00..10.00 rows=50 width=100)
        Filter: (user_id = 1)

优化建议：

增加work_mem参数，提高排序性能
考虑创建索引，避免排序操作
限制排序的行数

Aggregate（聚合）

Aggregate  (cost=12.50..12.51 rows=1 width=8)
  ->  Seq Scan on orders  (cost=0.00..10.00 rows=50 width=8)
        Filter: (user_id = 1)

优化建议：

为聚合列创建索引
考虑使用部分聚合（PostgreSQL 11+支持）
限制聚合的行数

Limit（限制）

Limit  (cost=0.29..4.30 rows=10 width=100)
  ->  Index Scan using idx_users_created_at on users  (cost=0.29..40.30 rows=100 width=100)
        Order By: created_at DESC

优化建议：

为ORDER BY列创建索引
限制返回的行数
考虑使用游标或基于主键的分页

执行计划分析方法

1. 识别性能瓶颈

高成本节点：关注总成本高的节点
实际行数与预估行数差异大：说明统计信息不准确
大量回表操作：考虑创建覆盖索引
排序和哈希操作：增加work_mem参数
全表扫描：考虑创建合适的索引

2. 分析执行计划示例

示例查询

sql

EXPLAIN ANALYZE SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 30
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

执行计划

Limit  (cost=52.30..52.33 rows=10 width=104) (actual time=0.100..0.100 rows=10 loops=1)
  ->  Sort  (cost=52.30..52.40 rows=30 width=104) (actual time=0.100..0.100 rows=10 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  HashAggregate  (cost=49.90..50.30 rows=30 width=104) (actual time=0.080..0.080 rows=30 loops=1)
              Group Key: u.name
              Batches: 1  Memory Usage: 40kB
              ->  Hash Join  (cost=21.00..47.40 rows=50 width=36) (actual time=0.050..0.060 rows=50 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..20.00 rows=100 width=12) (actual time=0.010..0.020 rows=100 loops=1)
                    ->  Hash  (cost=18.50..18.50 rows=20 width=32) (actual time=0.030..0.030 rows=20 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 13kB
                          ->  Seq Scan on users u  (cost=0.00..18.50 rows=20 width=32) (actual time=0.010..0.020 rows=20 loops=1)
                                Filter: (age > 30)
                                Rows Removed by Filter: 30
Planning Time: 0.150 ms
Execution Time: 0.150 ms

分析

连接方式：使用Hash Join，因为orders表较大，users表较小
聚合方式：使用HashAggregate，内存中完成聚合
排序方式：使用top-N heapsort，只排序前10行
表扫描：users表使用Seq Scan，因为age > 30的选择性不高
性能瓶颈：没有明显的性能瓶颈，执行时间较短

3. 常见问题与解决方案

问题	表现	解决方案
全表扫描	Seq Scan节点，成本高	创建合适的索引
回表操作	Index Scan，Heap Fetches次数多	创建覆盖索引
排序成本高	Sort节点，成本高	增加work_mem，创建索引
哈希操作内存不足	Hash Join或HashAggregate，Batches > 1	增加work_mem
预估行数不准确	实际行数与预估行数差异大	执行ANALYZE，更新统计信息
嵌套循环成本高	Nested Loop，外层行数多	优化连接顺序，使用Hash Join

执行计划优化策略

1. 索引优化

创建合适的索引：根据查询条件创建索引
覆盖索引：包含查询所需的所有列
表达式索引：对于使用函数或表达式的查询条件
部分索引：只包含经常查询的数据
多列索引：根据查询的列顺序创建

2. 查询语句优化

**避免SELECT ***：只查询需要的列
优化WHERE条件：将选择性高的条件放在前面
避免在WHERE条件中使用函数：否则索引失效
优化JOIN语句：确保连接列有索引
使用LIMIT：限制返回的行数

3. 数据库配置优化

work_mem：调整排序和哈希操作的内存
random_page_cost：调整随机页面访问成本，适合SSD存储
seq_page_cost：调整顺序页面访问成本
effective_cache_size：调整优化器对可用缓存的估算
enable_*：启用或禁用特定的执行计划节点

4. 统计信息优化

定期执行ANALYZE：更新表的统计信息
调整default_statistics_target：增加统计信息的采样数量
使用ALTER TABLE ... ALTER COLUMN ... SET STATISTICS：为特定列设置更高的统计信息采样数量

最佳实践

1. 执行计划分析流程

使用EXPLAIN ANALYZE查看实际执行计划
识别成本最高的节点
分析节点的执行时间和行数
检查实际行数与预估行数的差异
识别需要优化的部分（索引、查询语句、配置等）
实施优化措施
重新执行EXPLAIN ANALYZE，验证优化效果

2. 常见优化场景

场景1：全表扫描优化

问题：查询使用全表扫描，性能差

解决方案：

创建合适的索引
优化查询条件，提高选择性
考虑分区表

场景2：回表操作优化

问题：索引扫描后需要回表，性能差

解决方案：

创建覆盖索引
只查询索引包含的列
考虑使用Index Only Scan

场景3：排序成本高优化

问题：排序操作占用大量内存和时间

解决方案：

增加work_mem参数
创建索引，避免排序
限制排序的行数

场景4：连接性能差优化

问题：表连接操作成本高

解决方案：

为连接列创建索引
优化连接顺序
增加work_mem参数（对于Hash Join）
考虑使用物化视图

版本差异注意事项

PostgreSQL版本差异

版本	执行计划特性差异
PostgreSQL 16	增强了执行计划的并行查询支持
PostgreSQL 15	改进了执行计划的统计信息估算
PostgreSQL 14	引入了增量排序（Incremental Sort）
PostgreSQL 13	改进了分区表的执行计划
PostgreSQL 12	引入了物化CTE（Materialized CTE）
PostgreSQL 11	引入了并行聚合和并行哈希连接
PostgreSQL 10	引入了并行查询

执行计划节点变化

节点	版本	变化
Index Only Scan	PostgreSQL 9.6+	引入仅索引扫描
Parallel Seq Scan	PostgreSQL 10+	引入并行全表扫描
Parallel Hash Join	PostgreSQL 11+	引入并行哈希连接
Incremental Sort	PostgreSQL 14+	引入增量排序
Merge Append	PostgreSQL 9.5+	改进了分区表的合并操作

总结

执行计划分析是PostgreSQL查询优化的核心。通过理解执行计划的基本概念、节点类型和成本估算，可以识别查询的性能瓶颈，并采取相应的优化措施。

在实际运维中，DBA应该：

定期分析慢查询的执行计划
识别并优化高成本的执行计划节点
合理创建和维护索引
优化查询语句，减少不必要的操作
调整数据库配置，提高查询性能
定期更新统计信息，确保优化器生成准确的执行计划

通过有效的执行计划分析和优化，可以显著提高PostgreSQL数据库的查询性能，提升应用程序的响应速度。

执行计划分析 ​

执行计划基本概念 ​

1. 查询优化器 ​

2. 执行计划节点 ​

3. 执行计划成本 ​

EXPLAIN命令 ​

1. 基本使用 ​

2. EXPLAIN选项 ​

3. 输出格式 ​

文本格式 ​

JSON格式 ​

执行计划节点分析 ​

1. 表扫描节点 ​

Seq Scan（全表扫描） ​

Index Scan（索引扫描） ​

Index Only Scan（仅索引扫描） ​

Bitmap Heap Scan + Bitmap Index Scan（位图堆扫描+位图索引扫描） ​

2. 连接节点 ​

Nested Loop（嵌套循环连接） ​

Hash Join（哈希连接） ​

Merge Join（合并连接） ​

3. 其他常用节点 ​

Sort（排序） ​

Aggregate（聚合） ​

Limit（限制） ​

执行计划分析方法 ​

1. 识别性能瓶颈 ​

2. 分析执行计划示例 ​

示例查询 ​

执行计划 ​

分析 ​

3. 常见问题与解决方案 ​

执行计划优化策略 ​

1. 索引优化 ​

2. 查询语句优化 ​

3. 数据库配置优化 ​

4. 统计信息优化 ​

最佳实践 ​

1. 执行计划分析流程 ​

2. 常见优化场景 ​

场景1：全表扫描优化 ​

场景2：回表操作优化 ​

场景3：排序成本高优化 ​

场景4：连接性能差优化 ​

版本差异注意事项 ​

PostgreSQL版本差异 ​

执行计划节点变化 ​

总结 ​

执行计划分析

执行计划基本概念

1. 查询优化器

2. 执行计划节点

3. 执行计划成本

EXPLAIN命令

1. 基本使用

2. EXPLAIN选项

3. 输出格式

文本格式

JSON格式

执行计划节点分析

1. 表扫描节点

Seq Scan（全表扫描）

Index Scan（索引扫描）

Index Only Scan（仅索引扫描）

Bitmap Heap Scan + Bitmap Index Scan（位图堆扫描+位图索引扫描）

2. 连接节点

Nested Loop（嵌套循环连接）

Hash Join（哈希连接）

Merge Join（合并连接）

3. 其他常用节点

Sort（排序）

Aggregate（聚合）

Limit（限制）

执行计划分析方法

1. 识别性能瓶颈

2. 分析执行计划示例

示例查询

执行计划

分析

3. 常见问题与解决方案

执行计划优化策略

1. 索引优化

2. 查询语句优化

3. 数据库配置优化

4. 统计信息优化

最佳实践

1. 执行计划分析流程

2. 常见优化场景

场景1：全表扫描优化

场景2：回表操作优化

场景3：排序成本高优化

场景4：连接性能差优化

版本差异注意事项

PostgreSQL版本差异

执行计划节点变化

总结