印刻数据库

外观

PostgreSQL 执行计划分析 ​

执行计划基础 ​

执行计划是PostgreSQL查询优化器生成的查询执行方案，它决定了如何访问表数据、如何连接多个表以及如何处理查询的各个部分。理解执行计划是进行性能优化的基础。

执行计划的生成过程 ​

  1. 查询解析：将SQL语句解析为抽象语法树
  2. 查询重写：对查询进行逻辑优化，如常量折叠、子查询消除等
  3. 计划生成：基于统计信息生成多个可能的执行计划
  4. 成本估算：计算每个执行计划的成本
  5. 计划选择：选择成本最低的执行计划

EXPLAIN命令 ​

EXPLAIN命令用于查看PostgreSQL生成的执行计划，而不实际执行查询。

sql
-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age > 30;

-- 查看执行计划并显示实际执行统计信息
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

-- 查看执行计划并显示详细的成本信息
EXPLAIN VERBOSE SELECT * FROM users WHERE age > 30;

-- 查看执行计划并显示缓冲区使用情况
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age > 30;

执行计划解读 ​

执行计划的组成元素 ​

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

常见的执行节点类型 ​

  1. 表扫描节点

    • Seq Scan：顺序扫描整个表
    • Index Scan：使用索引查找行，然后回表获取完整数据
    • Index Only Scan：仅使用索引即可获取所需数据，无需回表
    • Bitmap Heap Scan：结合位图索引和堆扫描，适用于多个索引条件
    • Bitmap Index Scan：用于生成位图索引

  2. 连接节点

    • Nested Loop：嵌套循环连接，适用于小结果集
    • Hash Join：哈希连接，适用于中等结果集
    • Merge Join：合并连接，适用于大结果集且已排序的数据

  3. 其他节点

    • Sort：排序操作
    • Group：分组操作
    • Aggregate：聚合操作
    • Limit：限制结果集大小
    • Subquery Scan：子查询扫描

执行计划成本指标 ​

执行计划中的成本指标包括：

  • Startup Cost：获取第一行结果所需的成本
  • Total Cost：获取所有结果所需的总成本
  • Plan Rows：估计的结果行数
  • Plan Width：估计的每行平均宽度（字节）

实际执行统计信息（使用EXPLAIN ANALYZE）包括：

  • Actual Rows：实际返回的行数
  • Actual Time：实际执行时间（毫秒）

执行计划分析实战 ​

示例1：简单查询的执行计划 ​

sql
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

执行计划输出：

Seq Scan on users  (cost=0.00..155.00 rows=1000 width=44) (actual time=0.012..0.456 rows=1000 loops=1)
  Filter: (age > 30)
  Rows Removed by Filter: 9000
Planning Time: 0.052 ms
Execution Time: 0.567 ms

分析：

  • 使用了顺序扫描（Seq Scan），因为表中只有10000行，且age列没有索引
  • 过滤条件移除了9000行，只返回1000行
  • 执行时间较短，约0.567毫秒

示例2：带索引的查询执行计划 ​

sql
-- 创建索引
CREATE INDEX idx_users_age ON users(age);

-- 再次执行查询
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

执行计划输出：

Index Scan using idx_users_age on users  (cost=0.29..128.29 rows=1000 width=44) (actual time=0.015..0.342 rows=1000 loops=1)
  Index Cond: (age > 30)
Planning Time: 0.123 ms
Execution Time: 0.435 ms

分析：

  • 使用了索引扫描（Index Scan），因为age列现在有索引
  • 执行时间比顺序扫描略快，约0.435毫秒
  • 成本从155.00降低到128.29

示例3：多表连接的执行计划 ​

sql
EXPLAIN ANALYZE SELECT u.name, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 30
AND o.order_date > '2023-01-01';

执行计划输出：

Hash Join  (cost=138.29..305.79 rows=500 width=52) (actual time=0.387..1.234 rows=500 loops=1)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (cost=0.00..150.00 rows=5000 width=28) (actual time=0.008..0.456 rows=5000 loops=1)
        Filter: (order_date > '2023-01-01'::date)
        Rows Removed by Filter: 5000
  ->  Hash  (cost=128.29..128.29 rows=1000 width=24) (actual time=0.367..0.368 rows=1000 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 65kB
        ->  Index Scan using idx_users_age on users u  (cost=0.29..128.29 rows=1000 width=24) (actual time=0.012..0.234 rows=1000 loops=1)
              Index Cond: (age > 30)
Planning Time: 0.189 ms
Execution Time: 1.345 ms

分析：

  • 使用了哈希连接（Hash Join），因为orders表较大，适合哈希连接
  • 首先通过索引扫描获取符合条件的用户，然后构建哈希表
  • 然后顺序扫描orders表，过滤出符合条件的订单，再与哈希表进行连接

性能瓶颈识别 ​

常见的性能瓶颈 ​

  1. 全表扫描（Seq Scan）：当表较大且没有合适的索引时
  2. 索引扫描但回表过多：当索引不包含查询所需的所有列时
  3. 排序操作（Sort）：当查询需要排序且没有合适的索引时
  4. 嵌套循环连接（Nested Loop）：当连接的表较大时
  5. 高成本的聚合操作：当聚合的数据量较大时

如何识别性能瓶颈 ​

  1. 查看执行计划中的成本指标，重点关注总成本较高的节点
  2. 比较估计行数和实际行数，如差异较大可能是统计信息过时
  3. 查看实际执行时间，找出耗时最长的节点
  4. 注意执行计划中的警告信息，如"Seq Scan on large_table"

执行计划优化建议 ​

1. 更新统计信息 ​

当估计行数与实际行数差异较大时，可能是统计信息过时导致的：

sql
-- 更新单个表的统计信息
ANALYZE users;

-- 更新数据库中所有表的统计信息
ANALYZE VERBOSE;

2. 添加合适的索引 ​

根据查询条件和连接条件添加索引：

sql
-- 为过滤条件添加索引
CREATE INDEX idx_orders_order_date ON orders(order_date);

-- 为连接条件添加索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 创建复合索引
CREATE INDEX idx_orders_user_id_order_date ON orders(user_id, order_date);

3. 优化查询语句 ​

  • 避免在WHERE子句中对列进行函数操作
  • 避免使用SELECT *，只查询所需的列
  • 优化子查询，考虑使用JOIN替代
  • 合理使用LIMIT和OFFSET

4. 调整数据库配置 ​

根据执行计划的情况，调整相关的数据库配置参数：

  • work_mem：影响排序和哈希操作的内存使用
  • maintenance_work_mem：影响索引创建和维护的内存使用
  • random_page_cost：影响索引扫描和顺序扫描的成本估算
  • effective_cache_size：影响查询计划的选择

版本差异 ​

PostgreSQL 12+ 执行计划增强 ​

  1. 并行查询增强：支持更多类型的并行操作，如并行哈希连接
  2. 增量排序：提高排序操作的效率
  3. 执行计划缓存改进：减少计划生成的开销

PostgreSQL 13+ 执行计划增强 ​

  1. JIT编译：支持即时编译，加速某些查询的执行
  2. 分区表增强：提高分区表的查询性能
  3. 执行计划可视化：提供更好的执行计划输出格式

PostgreSQL 14+ 执行计划增强 ​

  1. 索引增强：支持更多类型的索引，如BRIN索引的增强
  2. 查询优化器改进：提高查询计划的质量
  3. 执行计划统计信息增强：提供更详细的执行统计信息

常见问题（FAQ） ​

Q1: 为什么执行计划显示的估计行数与实际行数差异很大？ ​

A1: 可能是统计信息过时导致的。可以使用ANALYZE命令更新表的统计信息，或者调整autovacuum相关配置，让PostgreSQL自动更新统计信息。

Q2: 如何查看执行计划的详细信息？ ​

A2: 可以使用EXPLAIN VERBOSE命令查看详细的执行计划，包括列信息、表信息等。使用EXPLAIN (ANALYZE, BUFFERS)可以查看实际执行统计信息和缓冲区使用情况。

Q3: 为什么添加了索引，但查询没有使用？ ​

A3: 可能有以下原因：

  • 表太小，顺序扫描比索引扫描更快
  • 索引选择性不高，返回的数据量太大
  • 查询条件没有使用索引的前缀列
  • 统计信息过时，优化器选择了错误的执行计划

Q4: 如何优化嵌套循环连接？ ​

A4: 可以尝试以下方法：

  • 确保连接条件上有合适的索引
  • 考虑调整join_collapse_limit参数，控制连接顺序的优化
  • 对于大表连接，考虑使用哈希连接或合并连接

Q5: 如何查看执行计划的总成本？ ​

A5: 执行计划中的Total Cost列显示了获取所有结果所需的总成本。总成本是基于CPU成本和I/O成本计算的，单位是任意的，但可以用于比较不同执行计划的相对成本。

Q6: 如何使用pg_stat_statements扩展分析查询性能？ ​

A6: 首先需要安装并启用pg_stat_statements扩展：

sql
-- 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 查看查询统计信息
SELECT queryid, query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

pg_stat_statements扩展可以跟踪所有SQL语句的执行统计信息，包括调用次数、总执行时间、平均执行时间等，是分析查询性能的有力工具。

总结 ​

执行计划分析是PostgreSQL性能优化的基础，通过理解执行计划，可以识别查询的性能瓶颈，并采取相应的优化措施。在实际生产环境中，应该定期分析执行计划，优化慢查询，提高数据库的整体性能。

在进行执行计划分析时，需要注意：

  1. 结合实际执行统计信息（EXPLAIN ANALYZE）进行分析
  2. 关注成本较高的节点，找出性能瓶颈
  3. 定期更新统计信息，确保执行计划的准确性
  4. 根据查询特点添加合适的索引
  5. 优化查询语句，避免不必要的操作
  6. 调整数据库配置，适应不同的查询场景

通过不断的分析和优化，可以使PostgreSQL数据库在生产环境中获得更好的性能表现。