PostgreSQL 查询计划的剖析

引言

理解 PostgreSQL 查询计划是开发人员和数据库管理员都至关重要的技能。这可能是我们开始优化查询时首先会查看的内容，也是验证和确认我们优化后的查询是否确实如预期那样优化的第一步。

PostgreSQL 数据库中的查询生命周期

在我们尝试阅读查询计划之前，提出一些非常基本的问题很重要

• 我们为什么需要查询计划？
• 计划中到底代表了什么？
• PostgreSQL 难道不够智能，不能自动优化我的查询吗？为什么我要担心规划器？
• 规划器是我唯一需要关注的吗？

每个查询都会经历不同的阶段，理解每个阶段对数据库的意义很重要。

第一阶段是通过 JDBC/ODBC（分别由 Microsoft 和 Oracle 创建的用于与数据库交互的 API）或其他方式（例如 PSQL（Postgres 的终端前端））连接到数据库。

第二阶段是将查询转换为称为解析树的中间格式。讨论 解析树 的内部结构超出了本文的范围，但您可以将其想象成 SQL 查询的编译形式。

第三阶段是我们所说的重写系统/规则系统。它接收第二阶段生成的解析树，并以规划器/优化器可以开始处理它的方式对其进行重写。

第四阶段是最重要的阶段，也是数据库的核心。没有规划器，执行器将无法知道如何执行查询，使用哪些索引，是否扫描较小的表以消除更多不必要的行等。本阶段将是本文讨论的重点。

第五个也是最后一个阶段是执行器，它执行实际执行并返回结果。几乎所有数据库系统都遵循一个或多或少与上述类似的过程。

数据设置

让我们设置一些带有虚假数据的虚拟表来运行我们的实验。

create table fake_data(id serial, name text, sentence text, company text);

然后用数据填充此表。我使用了以下 Python 脚本生成随机行。

from faker import Faker

fake = Faker()
# Change this range to whatever value you like
MAX_RANGE = 1000

with open('data.csv', 'w') as f:
    for i in range(0, MAX_RANGE):
        name = fake.name().replace(",", "")
        
        sentence = fake.sentence(
            nb_words=16, variable_nb_words=True
        ).replace(",", "")
        
        company = fake.company().replace(",", "")

        content = "'" + name + "'" + "," + \
                  "'" + sentence + "'" + "," \
                  + "'" + company + "'" + "\n"

        f.write(content)

该脚本使用 Faker 库 生成虚假数据。它将在根目录生成一个 csv 文件，可以使用以下命令作为常规 csv 导入到 PostgreSQL 中

COPY fake_data(name, sentence, company) 
FROM '/path/to/csv' DELIMITER ','

由于 id 是序列化的，它将由 PostgreSQL 自动填充。该表现在包含 1119284 条记录。

SELECT COUNT(*) FROM fake_data;

下面的大多数示例都将基于上述表格。它故意保持简单，以专注于过程而不是表格/数据的复杂性。

帖子的特色图片来自 Depesz 在线解释工具。

进入规划阶段

PostgreSQL 和许多其他数据库系统允许用户深入了解规划阶段实际发生的情况。我们可以通过运行一个称为 EXPLAIN 的命令来做到这一点。

PostgreSQL EXPLAIN 查询

EXPLAIN SELECT * FROM fake_data LIMIT 10;

通过使用 EXPLAIN，您可以在数据库实际执行查询计划之前查看它们。我们将在下面的部分中了解其中每个部分的含义，但首先让我们看看 EXPLAIN 的另一个扩展版本，称为 EXPLAIN ANALYSE。

Explain Analyze 一起使用

EXPLAIN ANALYSE SELECT * FROM fake_data LIMIT 10;

与 EXPLAIN 不同，EXPLAIN ANALYSE 实际上会在数据库中运行查询。此选项对于理解规划器是否没有正确发挥作用非常有用，即 EXPLAIN 和 EXPLAIN ANALYSE 生成的计划是否存在巨大差异。

PostgreSQL 对 ANAYLYZE 和 ANALYSE 都适用

数据库中的缓冲区和缓存是什么？

让我们来看一个更受关注的指标：BUFFERS。它解释了有多少数据来自 PostgreSQL 缓存，有多少数据需要从磁盘中获取。

EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM fake_data LIMIT 10 OFFSET 200

Buffers : shared hit=5 意味着有五页 pages 是从 PostgreSQL 缓存本身获取的。让我们调整查询以从不同的行进行偏移。

EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM fake_data LIMIT 10 OFFSET 500

Buffers: shared hit=7 read=5 表示有 5 个页面来自磁盘。read 部分是显示来自磁盘的页面数量的变量，而 hit 如前所述来自缓存。如果我们再次执行相同的查询（请记住 ANALYSE 会运行查询），那么所有数据现在都来自缓存。

PostgreSQL 使用一种称为 LRU（最近最少使用）缓存的机制来存储内存中经常使用的数据。了解缓存的工作原理及其重要性是另一篇文章的主题，但现在我们需要理解的是，PostgreSQL 拥有一个坚如磐石的缓存机制，我们可以使用 EXPLAIN (ANALYSE, BUFFERS) 命令查看其工作原理。

VERBOSE 命令参数

EXPLAIN (ANALYSE,BUFFERS,VERBOSE) SELECT * FROM fake_data LIMIT 10 OFFSET 500

Verbose 是另一个提供额外信息的命令参数。

请注意，Output: id, name, sentence, company 是附加信息。在复杂的查询计划中，还会打印大量其他信息。默认情况下，COSTS 和 TIMING 选项设置为 TRUE，除非您想将它们设置为 FALSE，否则无需显式指定。

Postgres Explain 中的 FORMAT

PostgreSQL 能够以 JSON 等漂亮的格式提供查询计划，以便这些计划能够以与语言无关的方式进行解释。

EXPLAIN (ANALYSE,BUFFERS,VERBOSE,FORMAT JSON) SELECT * FROM fake_data LIMIT 10 OFFSET 500

将以 JSON 格式打印查询计划。您可以通过复制其输出并将其插入到另一个表中，如以下 GIF 所示，在 Arctype 中查看此格式。

还有各种其他格式

• 文本（默认）
• JSON（上述示例）
• XML
• YAML

还有两个选项，名为 SETTINGS 和 WAL，可以包含在查询计划中，但这些超出了本文的范围。

总结：

• EXPLAIN 是您通常会开始使用的计划类型，也是生产系统中最常用的。
• EXPLAIN ANALYSE 用于运行查询并获取查询计划。通过它您可以获取计划中计划时间和执行时间的细分，以及与执行查询的成本和实际时间的比较。
• EXPLAIN (ANALYSE, BUFFERS) 在 analyze 的基础上使用，以获取有多少行/页面来自缓存和磁盘，以及缓存的行为方式。
• EXPLAIN (ANALYSE, BUFFERS, VERBOSE) 用于获取有关查询的详细和额外信息。
• EXPLAIN(ANALYSE,BUFFERS,VERBOSE,FORMAT JSON) 是以特定格式导出数据的方式；在本例中，是 JSON。

在下一节中，我们将使用这些工具来检查 PostgreSQL 查询计划的工作原理。为了便于阅读，我们将只查看 PostgreSQL 查询计划的文本格式。

查询计划的元素

任何查询计划，无论其复杂性如何，都具有一些基本结构。在本节中，我们将重点关注这些结构，它们将帮助我们抽象地理解查询计划。

查询的节点

一个查询计划由节点组成

EXPLAIN SELECT * FROM fake_data LIMIT 10 OFFSET 500;

一个节点可以被认为是数据库执行的一个阶段。节点通常是嵌套的，如上所示；Seq Scan 在其之前和之上完成，然后应用 Limit 子句。让我们添加一个 Where 子句以进一步理解嵌套。

EXPLAIN SELECT * FROM fake_data where NAME = 'Sandra Smith' LIMIT 10

执行从内到外进行。

• 过滤 name = Sandra Smith 的行
• 使用上述过滤器进行顺序扫描
• 在顶部应用 limit 子句

如您所见，数据库识别出只需要 10 行，并且在达到所需的 10 行后不再扫描。请注意，我已经关闭了 SET max_parallel_workers_per_gather =0;，以便计划更简单。我们将在后续文章中探讨并行化。

查询规划器中的成本

成本是数据库查询计划的关键部分，由于其表示方式，它们很容易被误解。让我们再次查看带成本的简单计划。

有几点需要注意

• LIMIT 子句的启动成本不为零。这是因为启动成本会累加到顶部，您看到的是其下方节点的成本。
• 总成本是一个任意度量，与规划器相关性更大，而不是用户。在任何实际用例中，您都不会同时获取整个表数据。
• 顺序扫描在估算方面非常糟糕，因为数据库不知道如何优化它们。索引可以极大地加快带有 WHERE 子句的查询。
• Width 很重要，因为行的宽度越大，需要从磁盘中获取的数据就越多。这就是为什么遵循数据库表的规范化非常重要。

如果实际运行查询，那么成本将更有意义。

数据库规划和执行

规划时间和执行时间是仅通过 EXPLAIN ANALYSE 选项才能获得的指标。

规划器（规划时间）根据各种参数决定查询应如何运行，而执行器（执行时间）运行查询。上述参数是抽象的，适用于任何类型的查询。运行时以毫秒表示。在许多情况下，规划时间和执行时间可能不接近，如上所示，规划器可能需要更多时间来规划查询，而执行器花费的时间更少，这通常不是情况。它们不一定需要相互匹配，但如果它们偏差很大，那么是时候反思为什么会发生这种情况了。

在典型的 OLTP 系统（如 PostgreSQL）中，除非是分析查询/大量写入/已知异常，否则任何规划和执行的总时间应小于 50 毫秒。请记住，OLTP 代表在线事务处理。在典型业务中，事务通常从数千到数百万。这些执行时间应始终仔细观察，因为这些较小的昂贵查询可能会累加并增加巨大的开销。

后续步骤

我们已经涵盖了从查询生命周期到规划器如何做出决策的主题，我故意省略了节点类型（扫描、排序、连接）等主题，因为它们需要专门的文章。本文的目标是提供对查询规划器工作原理、影响其决策的因素以及 PostgreSQL 提供的工具的广泛理解，以更好地理解规划器。

让我们重新审视上面提出的问题。

Q：我们为什么需要查询计划？

A：“有计划的傻瓜胜过没有计划的天才！”——古老的 Arctype 谚语。计划是绝对必要的，尤其是在基于统计数据做出决策时，它能帮助决定采取哪条路径。

Q：计划中到底代表了什么？

A：计划包括节点、成本、规划时间和执行时间。节点是查询的基本组成部分。成本是节点的基本属性。规划时间和执行时间用于查看实际时间。

Q：PostgreSQL 难道不够智能，不能自动优化我的查询吗？为什么我要担心规划器？

A：PostgreSQL 实际上已经尽可能智能了。规划器在每个版本中都变得越来越好，但没有完全自动化/完美的规划器。这实际上不切实际，因为一种优化可能对一个查询有利，但对另一个查询不利。规划器必须在某个地方划定界限，并提供一致的行为和性能。开发人员/DBA 有很多责任来编写优化的查询并更好地理解数据库行为。

Q：规划器是我唯一需要关注的吗？

A：当然不是。还有许多其他非常重要的因素，例如应用程序的领域专业知识、表设计和数据库架构等。但作为开发人员/DBA，理解和提高这些抽象技能对于我们的职业生涯至关重要。

有了这些基本知识，我们现在可以自信地阅读任何计划，并对正在发生的事情形成一个高层次的概念。查询优化是一个非常广泛的话题，需要了解数据库内部发生的各种事情。在后续文章中，我们将看到不同类型的查询及其节点的规划和执行方式，以及影响规划器行为的因素以及我们如何优化它们。