PostgreSQL 执行计划详解

在 PostgreSQL 中，执行计划是指查询优化器生成的一份详细的查询执行计划。执行计划可以帮助我们了解查询的执行过程，包括查询的执行顺序、使用的索引、使用的连接方式等等。通过分析执行计划，我们可以找到查询性能瓶颈，并进行优化。

执行计划的生成过程

在 PostgreSQL 中，执行计划的生成过程可以分为以下几个步骤：

语法分析：将查询语句解析成一棵语法树。
语义分析：对语法树进行语义分析，包括表名解析、列名解析、类型检查等等。
优化器：对查询语句进行优化，生成最优的执行计划。
执行计划：将优化后的执行计划转换成可执行的代码，并执行查询。

在这个过程中，优化器是最关键的一步。优化器会根据查询语句的特点，选择最优的执行计划。执行计划的优化过程非常复杂，包括选择最优的连接方式、选择最优的索引、选择最优的排序方式等等。在优化器的帮助下，PostgreSQL 可以处理非常复杂的查询语句，并且保证查询的性能。

执行计划的解读方法

在 PostgreSQL 中，我们可以使用 EXPLAIN 命令来查看执行计划。EXPLAIN 命令可以将查询语句的执行计划输出到控制台或者文件中。下面是一个简单的例子：

EXPLAIN SELECT * FROM users WHERE age > 18;

执行上述命令后，我们可以得到如下的执行计划：

Seq Scan on users (cost=0.00..1.00 rows=1 width=4)
  Filter: (age > 18)

执行计划的输出包括以下几个部分：

访问方式：Seq Scan 表示使用顺序扫描方式访问表。
访问成本：cost=0.00..1.00 表示访问该表的成本为 0.00 到 1.00。
行数估计：rows=1 表示查询结果的行数为 1。
列宽度：width=4 表示查询结果的列宽度为 4 字节。
过滤条件：Filter: (age > 18) 表示使用 age 列的值大于 18 作为过滤条件。

执行计划的输出非常详细，可以帮助我们了解查询的执行过程。下面我们将详细介绍执行计划的各个部分。

访问方式

执行计划的第一部分是访问方式。访问方式表示查询引擎访问表的方式。在 PostgreSQL 中，常见的访问方式包括：

Seq Scan：顺序扫描，即按照表中数据的物理存储顺序进行扫描。
Index Scan：索引扫描，即使用索引进行扫描。
Bitmap Index Scan：位图索引扫描，即使用位图索引进行扫描。
Bitmap Heap Scan：位图堆扫描，即使用位图索引进行扫描，并且需要访问堆数据。
Nested Loop：嵌套循环，即使用嵌套循环的方式进行连接操作。
Hash Join：哈希连接，即使用哈希表进行连接操作。
Merge Join：归并连接，即使用归并排序的方式进行连接操作。

不同的访问方式对应不同的查询场景。例如，当查询条件使用了索引列时，使用 Index Scan 访问方式可以提高查询性能。当查询需要进行连接操作时，使用 Nested Loop、Hash Join 或 Merge Join 访问方式可以提高查询性能。

访问成本

执行计划的第二部分是访问成本。访问成本表示查询引擎访问表的成本。在 PostgreSQL 中，访问成本是一个非常重要的指标，它可以帮助我们选择最优的执行计划。访问成本包括两个部分：

startup cost：启动成本，表示查询引擎启动的成本，包括打开文件、分配内存等等。
total cost：总成本，表示查询引擎执行查询的总成本，包括访问表、访问索引、排序等等。

访问成本越低，执行计划越优。在选择执行计划时，我们应该选择总成本最低的执行计划。

行数估计

执行计划的第三部分是行数估计。行数估计表示查询结果的行数。在 PostgreSQL 中，行数估计是一个非常重要的指标，它可以帮助我们选择最优的执行计划。行数估计包括以下几个部分：

rows：估计的行数。
loops：循环次数，表示查询引擎需要循环多少次才能得到所有的结果。

行数估计越准确，执行计划越优。在选择执行计划时，我们应该选择行数估计最准确的执行计划。

列宽度

执行计划的第四部分是列宽度。列宽度表示查询结果的列宽度。在 PostgreSQL 中，列宽度是一个非常重要的指标，它可以帮助我们选择最优的执行计划。列宽度包括以下几个部分：

width：列宽度，表示查询结果的列宽度。

列宽度越小，执行计划越优。在选择执行计划时，我们应该选择列宽度最小的执行计划。

过滤条件

执行计划的最后一部分是过滤条件。过滤条件表示查询引擎使用的过滤条件。在 PostgreSQL 中，过滤条件是一个非常重要的指标，它可以帮助我们选择最优的执行计划。过滤条件包括以下几个部分：

Filter：过滤条件，表示查询引擎使用的过滤条件。

过滤条件越简单，执行计划越优。在选择执行计划时，我们应该选择过滤条件最简单的执行计划。

执行计划的优化技巧

在 PostgreSQL 中，我们可以通过优化执行计划来提高查询性能。下面是一些常用的执行计划优化技巧。

使用索引

在 PostgreSQL 中，使用索引可以提高查询性能。索引可以帮助我们快速定位符合条件的数据。在使用索引时，我们应该选择最优的索引类型。常见的索引类型包括：

B 树索引：适用于等值查询和范围查询。
哈希索引：适用于等值查询。
GiST 索引：适用于空间数据类型。
GIN 索引：适用于全文搜索和数组类型。

在选择索引类型时，我们应该根据查询场景选择最优的索引类型。同时，我们还应该避免使用过多的索引，因为索引会占用磁盘空间，并且会影响插入和更新操作的性能。

避免使用 SELECT *

在 PostgreSQL 中，使用 SELECT 会导致查询所有列，包括不需要的列。这会浪费网络带宽和内存资源。在查询时，我们应该只查询需要的列，避免使用 SELECT 。

使用 LIMIT 和 OFFSET

在 PostgreSQL 中，使用 LIMIT 和 OFFSET 可以限制查询结果的行数和偏移量。这可以提高查询性能，并且可以减少网络带宽和内存资源的消耗。在查询时，我们应该尽可能地使用 LIMIT 和 OFFSET。

使用 EXISTS 子查询

在 PostgreSQL 中，使用 EXISTS 子查询可以提高查询性能。EXISTS 子查询可以帮助我们快速判断是否存在符合条件的数据。在查询时，我们应该尽可能地使用 EXISTS 子查询。

使用 JOIN 查询

在 PostgreSQL 中，使用 JOIN 查询可以提高查询性能。JOIN 查询可以帮助我们快速进行连接操作。在使用 JOIN 查询时，我们应该选择最优的连接方式。常见的连接方式包括：

Nested Loop：嵌套循环，适用于小数据集。
Hash Join：哈希连接，适用于大数据集。
Merge Join：归并连接，适用于有序数据集。

在选择连接方式时，我们应该根据查询场景选择最优的连接方式。

总结

在本文中，我们介绍了 PostgreSQL 执行计划的生成过程、执行计划的解读方法和执行计划的优化技巧。执行计划可以帮助我们了解查询的执行过程，包括查询的执行顺序、使用的索引、使用的连接方式等等。通过分析执行计划，我们可以找到查询性能瓶颈，并进行优化。同时，我们还介绍了一些常用的执行计划优化技巧，包括使用索引、避免使用 SELECT *、使用 LIMIT 和 OFFSET、使用 EXISTS 子查询和使用 JOIN 查询等等。这些技巧可以帮助我们提高查询性能，从而提高应用程序的响应速度。

目录CONTENT

PostgreSQL 学习笔记 - 执行计划