# 执行计划分析

URL: https://caijiao.org/postgresql/performance/query-optimization/explain
Source: docs/postgresql/performance/query-optimization/explain.md
Description: 介绍如何分析 PostgreSQL 执行计划，帮助开发者理解查询执行的过程，从而优化查询性能。

在 PostgreSQL 中，执行计划是查询优化器根据查询语句、表的统计信息和索引选择的执行策略。分析执行计划可以帮助开发者理解查询的执行过程，并找出瓶颈，以便优化查询性能。

## 什么是执行计划？

执行计划（Execution Plan）描述了数据库如何执行一个查询。它展示了执行该查询时使用的操作、访问方法（如扫描表或索引）、连接方式（如嵌套循环连接或合并连接）等。执行计划是优化查询性能的关键工具，可以帮助我们发现查询中的潜在问题。

## 如何获取执行计划

在 PostgreSQL 中，可以使用 `EXPLAIN` 命令来获取查询的执行计划。`EXPLAIN` 命令输出一个查询的执行计划，不会实际执行查询。它显示了查询优化器为某个查询选择的执行策略。

```sql
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
````

该命令返回执行计划，但不返回实际的数据。可以通过 `EXPLAIN ANALYZE` 来执行查询并返回实际的执行时间和其它详细信息。

```sql
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;
```

`EXPLAIN ANALYZE` 命令会实际执行查询并返回执行时间、行数、缓存命中等信息，是深入分析查询性能的好工具。

## 执行计划的输出

执行计划输出的内容通常包含以下几个部分：

### 1. 节点类型（Node Type）

每个操作符都有一个节点类型，表示该操作符的执行方式。例如：

* **Seq Scan**：顺序扫描，表示全表扫描。
* **Index Scan**：索引扫描，表示使用索引扫描表。
* **Bitmap Index Scan**：位图索引扫描，表示使用位图索引扫描数据。
* **Nested Loop**：嵌套循环连接，通常用于小表连接。
* **Hash Join**：哈希连接，通常用于大表连接。
* **Merge Join**：合并连接，适用于已排序数据。

### 2. 行数估算（Rows）

行数估算是 PostgreSQL 查询优化器对每个步骤中处理的行数的估算。通过估算的行数，可以了解某个操作是否处理了过多的行，从而影响查询效率。

### 3. 真实行数（Actual Rows）

在执行计划中，`EXPLAIN ANALYZE` 会显示实际扫描的行数。与估算的行数对比，若存在较大的差异，则可能需要检查统计信息的准确性。

### 4. 成本（Cost）

每个节点都有一个成本估算值，它表示查询执行的费用。这个成本通常由两个部分组成：

* **启动成本**：开始执行该操作所需的时间。
* **总成本**：执行该操作的总时间，包括所有子操作的执行时间。

成本越高，表示该操作执行的开销越大。

```plaintext
Cost: 0.00..100.00
```

### 5. 时间（Time）

`EXPLAIN ANALYZE` 会显示每个操作实际执行的时间，包括启动时间和总时间。这有助于理解查询中哪个部分耗费了最多的时间。

```plaintext
Actual time: 0.100..0.150 ms
```

### 6. 连接方式（Join Type）

在连接查询中，执行计划会显示使用的连接方式，如：

* **Nested Loop**：嵌套循环连接。
* **Merge Join**：合并连接。
* **Hash Join**：哈希连接。

### 7. 过滤条件（Filter）

在每个操作中，`EXPLAIN ANALYZE` 可能会显示某个节点的过滤条件，例如：

```plaintext
Filter: (age > 30)
```

这表示在执行该操作时，数据库对数据应用了过滤条件。

## 分析执行计划

分析执行计划的目的是找出查询中的瓶颈。以下是一些常见的性能问题及其解决方案：

### 1. 全表扫描（Seq Scan）

如果查询使用了全表扫描（`Seq Scan`），而不是使用索引扫描，通常意味着查询无法利用索引。全表扫描会扫描表中的所有行，通常会导致较慢的查询。

#### 解决方案

* **添加索引**：确保查询中涉及的列有索引，尤其是 WHERE 子句、JOIN 子句和 ORDER BY 子句中的列。
* **检查统计信息**：如果查询无法使用索引，可能是统计信息过时，使用 `ANALYZE` 更新统计信息。

### 2. 索引扫描的低效使用（Index Scan）

如果查询使用了索引扫描（`Index Scan`），但执行计划显示该操作的成本很高，可能是索引选择不合理。

#### 解决方案

* **检查索引类型**：确保选择了合适的索引类型（如 B-tree、GIN、GiST 等）。
* **复合索引**：使用复合索引来覆盖多个查询条件，避免多次扫描索引。

### 3. 嵌套循环连接（Nested Loop）

嵌套循环连接（`Nested Loop`）通常在小表连接时较为高效，但在大表连接时会导致查询性能下降。

#### 解决方案

* **优化连接条件**：确保连接条件能够利用索引。
* **使用更高效的连接方式**：尝试使用哈希连接（`Hash Join`）或合并连接（`Merge Join`）来优化大表的连接。

### 4. 多次排序（Sorting）

查询中如果涉及多次排序操作，可能会导致性能问题，尤其是在处理大量数据时。

#### 解决方案

* **避免重复排序**：检查查询中是否有重复的排序操作，可以通过修改查询或创建合适的索引来避免。
* **使用合适的索引**：索引可以加速 `ORDER BY` 操作，尤其是对排序列的索引。

### 5. 索引扫描与顺序扫描的选择

如果查询优化器选择了索引扫描，但执行计划显示该索引扫描效率低下，可能是因为该索引不适合查询条件。

#### 解决方案

* **调整查询语句**：修改查询语句，使其更适合索引扫描。
* **创建新的索引**：为查询条件创建新的索引。

## 执行计划优化技巧

### 1. 更新统计信息

执行计划的质量取决于准确的统计信息。使用 `ANALYZE` 命令来更新表的统计信息，以确保查询优化器能选择最佳的执行计划。

```sql
ANALYZE employees;
```

### 2. 使用 EXPLAIN 命令分析慢查询

使用 `EXPLAIN ANALYZE` 命令分析慢查询，找出执行时间较长的部分，并进行优化。

### 3. 避免不必要的子查询

有些查询中使用了嵌套的子查询，可能会导致额外的计算和资源消耗。通过将子查询转化为 JOIN 操作，可能会提高查询效率。

```sql
SELECT e.name
FROM employees e
WHERE e.department_id IN (SELECT d.id FROM departments d WHERE d.name = 'Sales');
```

可以重写为：

```sql
SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales';
```

## 小结

执行计划分析是 PostgreSQL 性能优化的关键步骤。通过 `EXPLAIN` 和 `EXPLAIN ANALYZE` 命令，开发者可以查看查询的执行过程，找出瓶颈并进行优化。了解不同的执行节点类型、优化策略以及如何选择合适的索引和连接方式，将有助于提高查询性能。