PostgreSQL：小白也能懂的查询优化技巧

通过实际示例来深入理解 PostgreSQL 的查询执行计划

来自Greg Rakozy的照片，在Unsplash。 （来自Unsplash）

今天，用户对所用程序的期望值很高。他们期待这些程序具备出色的功能，运行速度快，且资源占用要合理。

作为开发者，我们应该努力为用户提供最好的体验。数据库经常会成为瓶颈，而优化查询和消除瓶颈并不容易。不幸的是，随着程序越来越复杂，数据量也越来越大，写出完美的SQL查询也越来越难。

今天，我将重点介绍一种使用[Explain]子句查找瓶颈的方法。我的目标是向你展示，找到并消除这些瓶颈并不像你想的那么难。每个人都能轻松找到自己的瓶颈。

本文的文章代码可以在GitHub上找到，具体位置如下。

注：除非特别说明，所有图片均为作者提供。

我们来解释一下吧 📜

与数据库的交互操作是通过声明性语言，如SQL，完成的。数据库在幕后决定如何执行以及执行哪些操作，唯一提供的信息是执行计划。

这个限制使得在实际操作中几乎不可能实现合适的调试工具和性能分析工具。所以我们基本上只能靠执行计划来解决问题了。

注意流行词来了 🐝!! 我的目标是让更多人了解和使用执行计划。

在 PostgreSQL 中，要查看执行计划，可以使用 Explain 或 Explain analyze 命令：

• EXPLAIN 显示规划器要做什么。
• EXPLAIN ANALYZE 显示规划器要做什么，执行查询，并展示它是如何做的。

小贴士 #1 💃：至少在你的职业生涯里，尝试一次查看执行计划。这在各个数据库中都差不多，这是一项在公司里并不常见的技能。

小贴士 #2 💃：尽量使用 EXPLAIN ANALYZE，因为大多数情况下它能提供更多详情：

注意：警告 #1 ⚠️ 不要在类似 DELETE/UPDATE 的破坏性命令中使用 EXPLAIN ANALYZE，使用 EXPLAIN 就行了，而且不会执行查询。

警告 #2 ⚠️ 在资源紧张的情况下，比如生产监控时，不要使用 EXPLAIN ANALYZE。如果查询永远无法完成，使用 EXPLAIN 即可，且不会执行查询。

_解释_非常有用，因为它能指出查询变慢的原因，包括：，

• 缺少索引或过度使用分区。
• 没有优化的数据库设置。
• 不必要的操作。
• 陈旧的统计信息。
• 过多的输入输出操作。

对于更为细心的人，你可以看到在下图中“Explain”句子的语法。

PostgreSQL 解释子句语法

来了解解剖结构吧

我们将用它作为一个简单的查询示例：我们想要统计没有 Twitter 账号的用户数量。

使用 `EXPLAIN ANALYZE` 来解释并分析以下 SQL 语句：统计 `users` 表中 `twitter` 字段不为空的记录数：`SELECT COUNT(*) FROM users WHERE twitter != '';

我们能看到 EXPLAIN ANALYZE 返回的执行计划

乍一看可能很复杂，甚至比我们的查询还要长得多，在实际执行计划的小例子中，不集中注意力的话可能会让人感到不知所措 😭。

不过，它确实提供了一些有用的信息。可以看到，查询执行花了1.27秒，，查询规划只花了0.4毫秒，几乎可以忽略的时间。

可以看到查询计划和执行所花费的时间

执行计划以倒置树的形式呈现。在下面的图中，你可以看到它被分成了不同的节点，每个节点表示一个不同的操作，不论是 聚合 还是 扫描。

可以看到查询规划和执行花了多长时间

有多种节点操作类型，包括扫描相关的（如“Seq Scan”，“Index Only Scan”……），连接相关的（如“Hash Join”，“Nested Loop”……），聚合相关的（如“GroupAggregate”，“Aggregate”……）以及其他操作（如“Limit”，“Sort”，“生成子查询”……）。幸运的是，你不需要记住这些特定的操作类型。

小提示 #3 💃：关键是要集中注意力，查看有问题的节点。

小贴士 💃：遇到问题时，查查这些节点的意思，在explain术语表中查找。

现在，我们来看看怎么找出有问题的那个节点。

在每个节点上，我们可以看到很多信息

咱们来看看这些指标真正代表了什么。

• 实际循环数：同一个节点执行的循环次数为1。为了得到总的耗时和行数，需要将实际耗时和行数乘以循环次数的值。
• 实际行数：Aggregate 节点的实际行数为1（每轮平均且循环次数为1）。
• 计划行数：Aggregate 节点预计生成的行数为1。预计的行数可能因统计数据而有所不同。
• 实际启动时间：Aggregate 节点返回第一行所花费的时间为1271.157毫秒（累积包括之前的操作）。
• 启动成本：代表返回 Aggregate 节点第一行所需时间的任意单位是845110（累积了之前的操作）。
• 实际总时间：Aggregate 节点返回所有行的总时间为1271.158毫秒（每轮平均且循环次数为1，并包括了之前的操作）。
• 总成本：代表返回 Aggregate 节点所有行所需时间的任意单位是845110（累计）。
• 计划宽度：Aggregate 节点预计的行平均大小为8字节。

小贴士 #5 💃：小心循环，记得，在关注实际行数（Actual Rows）和实际总时间（Actual Total Time）时，考虑循环次数。

我们下一节通过一个具体例子来详细说明。

示例：性能优化示例🐆

我们继续用之前的查询。

    EXPLAIN ANALYZE  
    SELECT COUNT(*) FROM users WHERE twitter != '';

解释并分析从users表中选择twitter字段不为空的记录数的SQL查询

我们将重点关注实际耗时最长的顺序扫描节点。

我们主要关注的是用户表的顺序扫描操作，这个操作过滤掉了2,487,813条记录，耗时1.27秒。

但我们只是没什么用的人类。让我们去谷歌查一下（也可以用ChatGPT）！

搜索如何在PostgreSQL中加快顺序扫描

在 users 表上创建名为 twitter_test 的索引，该索引基于 twitter 列

我们做得好多了，不过就实际时间而言，扫描部分仍然是耗时最长的。

我们可以看到现在我们在用户表上执行了一个仅索引扫描。这从1.27秒缩短到了0.29秒，确实不错，但对我们来说还不够。

小贴士 #6 💃：一步一步慢慢优化查询。

我们可以通过下面的缓冲参数来了解传递给扫描的数据量。

小贴士 #7 💃：在比较执行计划时，多关注几个关键指标哦。

    EXPLAIN (ANALYZE, BUFFERS)  
    SELECT COUNT(*) FROM users WHERE twitter != ''

这个SQL查询用于解释并分析从users表中获取twitter字段不为空的记录数量。

我们可以看到很多关于数据命中缓存或写入磁盘的信息。

我们需要读取来自缓存（400 MB）的 51,854 页，所以调整设置可能不会有很大改善，所以调整设置可能不会有太大帮助。

但是，我们还有其他办法。由于扫描过滤掉了2,487,813行数据，我们可以将索引改为部分索引，但这样做不是免费的。它会导致写入速度变慢，并且需要额外的存储空间，这会带来不小的开销。这对垂直扩展的系统影响很大。

#8 小贴士 💃：世界上没有免费的午餐。

挺好的优化选项👍🏻

这篇博客已经相当长了，所以我不会过多地详述细节。当查询变慢时，这些可能是你首先想解决的问题。

• 选择恰当的扫描方式。
• 选择恰当的连接方式。
• 选择恰当的连接顺序（或方式）。
• 尽早推送过滤条件，尽可能地。
• 在必要时减少磁盘IO操作。

手动检查特定优化时，可以开启或关闭相关设置。

    SET enable_seqscan TO off; ,  
    EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500; -- 这条命令用于分析查询性能，其中foo表中c1列大于500的行将被选中。
    SET enable_seqscan TO on; ,

注释：enable_seqscan 控制是否允许顺序扫描，顺序扫描是数据库查询的一种策略，它按照数据在磁盘上的物理顺序进行访问。关闭顺序扫描可能有助于优化某些查询的执行效率。

警告 #3 ⚠️：在尝试了基本的优化措施之后再开启或关闭设置，因为大多数情况下，PostgreSQL通常知道自己在做什么。

有更简单的方法吗？拜托了🙏

很遗憾地，EXPLAIN 并不完美，这就是为什么它并不在每个开发人员的工具箱中，

你并没有生产环境中所有执行计划的历史记录。
可以使用像auto_explain和pg_stat_plans这样的工具，在满足某些条件时记录执行计划，以减少对生产环境的影响，从而弥补这一不足。另一种方法是记录在什么时间运行了哪些查询并尝试重现，但实际上比想象的要复杂，因为需要手动记录和重构查询。

使用“裸执行计划”来优化复杂查询并不简单，因为它们通常很长且难以理解。此外，它们通常无法解释为什么特定优化未能实施，也无法提供如何有效重写查询以提升性能的指导。

然而，这个问题可以通过采用高度规范化的结构和元数据，利用专门的工具来应对。这些工具帮助你或模型专注于重要的事情（无论是架构、瓶颈等问题）。一些最突出的工具包括例如：

• eversql — 一个成熟的解决方案，旨在为您在 PostgreSQL 查询中提供更改建议。
• metis — 旨在作为防护措施，为 PostgreSQL 数据库提供变更建议，作为开发和 CI/CD 流程的一部分。
• QueryFlow — 一个开源工具，允许识别多个查询中的错误并进行性能调整（因为最难调试的查询在单独测试时表现良好）。

小贴士 #9 💃：用工具让生活更省心吧。

我来给你演示一下使用像QueryFlow这样的工具有多方便（For more details you can read the following）。

查询流程图执行计划的可视化，重点在于时长

很明显，_只索引扫描_的宽度远远大于聚合，这表明我们应该在这里重点关注。在多个复杂查询中，其他工具在这方面往往不足。

最后的留言

在这篇文章中，我们回顾了一些常见的导致原本很好的SQL在时间敏感的应用程序中运行速度过慢的原因，并通过一种传说性的方法来识别这些问题并避免它们。

由于话题的广泛性，我未涉及许多优化内容。为此，如果你想更进一步，我在最后添加了一些额外的资源。

我对未来抱有乐观的态度。我相信这类工具将会变得像在Python中轻松地打开文件一样简单，无论是通过整合到IDEs和客户端，还是提供SAS解决方案。这将使我们能够更加积极主动，而不是被动反应。

我希望我已经能够与你分享我对这个迷人的主题的热情，希望你会发现这对你有帮助，一如既往的，我总是乐于接受任何建设性的意见。

更多学习资料📚

• PostgreSQL执行计划的深入理解（视频）
• EXPLAIN解析（视频）
• 了解EXPLAIN