EXPLAIN ANALYZE是优化PostgreSQL中SQL语句的关键。本文并不试图解释它的所有内容。相反，我想给您一个简短的介绍，解释要查找的内容并向您展示一些有用的工具来可视化输出。
怎么进行EXPLAIN ANALYZE？
如果您的 SQL 语句执行得太慢，您想知道发生了什么以及如何修复它。在 SQL 中，很难看到引擎如何花费时间，因为它不是过程语言，而是声明性语言：你描述的是你想要得到的结果，而不是如何计算它。该EXPLAIN命令显示优化器生成的执行计划。这很有帮助，但通常您需要更多信息。
您可以通过EXPLAIN在括号中添加选项来获取此类附加信息。
最重要的EXPLAIN选项
- ANALYZE: 使用这个关键字，EXPLAIN不仅会显示计划和 PostgreSQL 的估计值，还会执行查询（所以要小心使用UPDATE和DELETE！）并显示每一步的实际执行时间和行数。这对于分析 SQL 性能是必不可少的。
- BUFFERS: 该关键字只能与 一起使用ANALYZE，它显示每一步读取、写入和脏的 8kB 块数。你总是想要这个。
- VERBOSE: 如果指定此选项，则EXPLAIN显示执行计划中每个步骤的所有输出表达式。这通常只是混乱，没有它你会更好，但如果执行器将时间花在一个经常执行的、昂贵的函数上，它会很有用。
- SETTINGS：此选项自 v12 以来存在，并包括所有与输出中的默认值不同的性能相关参数。
- WAL：在 v13 中引入，此选项显示数据修改语句引起的 WAL 使用情况。您只能将它与ANALYZE. 这总是有用的信息！
通常，最好的调用方式EXPLAIN是：
EXPLAIN (ANALYZE, BUFFERS) /* SQL statement */;
包括SETTINGS如果你是V12或更好，WAL数据修改从V13声明。
设置track_io_timing = on获取有关 I/O 性能的数据是非常值得称赞的。
注意事项和限制
您不能EXPLAIN用于所有类型的语句：它仅支持SELECT, INSERT, UPDATE, DELETE, EXECUTE（准备好的语句）CREATE TABLE … AS和DECLARE（游标的）。
请注意，这EXPLAIN ANALYZE会显着增加查询执行时间的开销，因此如果语句花费更长的时间，请不要担心。
查询执行时间总是有一定的变化，因为在第一次执行期间数据可能不在缓存中。这就是为什么重复EXPLAIN ANALYZE几次并查看结果是否改变很有价值的原因。
你从什么信息中得到的EXPLAIN ANALYZE？
有些信息是针对执行计划的每个节点显示的，有些则显示在页脚中。
EXPLAIN 没有选择
PlainEXPLAIN将为您提供估计成本、估计行数和平均结果行的估计大小。估计查询成本的单位是人为的（1 是在顺序扫描期间读取 8kB 页面的成本）。有两个成本值：启动成本（返回第一行的成本）和总成本（返回所有行的成本）。
EXPLAIN SELECT count(*) FROM c WHERE pid = 1 AND cid > 200;
QUERY PLAN
------------------------------------------------------------
Aggregate (cost=219.50..219.51 rows=1 width=8)
-> Seq Scan on c (cost=0.00..195.00 rows=9800 width=0)
Filter: ((cid > 200) AND (pid = 1))
(3 rows)
ANALYZE选项的输出
ANALYZE为您提供第二个括号，其中包含以毫秒为单位的实际执行时间、实际行计数和显示该节点执行频率的循环计数。它还显示过滤器已删除的行数。
EXPLAIN (ANALYZE) SELECT count(*) FROM c WHERE pid = 1 AND cid > 200;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Aggregate (cost=219.50..219.51 rows=1 width=8) (actual time=4.286..4.287 rows=1 loops=1)
-> Seq Scan on c (cost=0.00..195.00 rows=9800 width=0) (actual time=0.063..2.955 rows=9800 loops=1)
Filter: ((cid > 200) AND (pid = 1))
Rows Removed by Filter: 200
Planning Time: 0.162 ms
Execution Time: 4.340 ms
(6 rows)
在页脚中，您可以看到 PostgreSQL 计划和执行查询所用的时间。您可以使用SUMMARY OFF.
BUFFERS选项的输出
此选项显示在hit每个节点的缓存 ( )中找到了多少数据块，有多少必须read来自磁盘，有多少written以及有多少dirtied。在最近的版本中，如果优化器没有在缓存中找到其所有数据，则页脚包含与优化器完成的工作相同的信息。
如果track_io_timing = on，您将获得所有 I/O 操作的计时信息。
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM c WHERE pid = 1 AND cid > 200;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Aggregate (cost=219.50..219.51 rows=1 width=8) (actual time=2.808..2.809 rows=1 loops=1)
Buffers: shared read=45
I/O Timings: read=0.380
-> Seq Scan on c (cost=0.00..195.00 rows=9800 width=0) (actual time=0.083..1.950 rows=9800 loops=1)
Filter: ((cid > 200) AND (pid = 1))
Rows Removed by Filter: 200
Buffers: shared read=45
I/O Timings: read=0.380
Planning:
Buffers: shared hit=48 read=29
I/O Timings: read=0.713
Planning Time: 1.673 ms
Execution Time: 3.096 ms
(13 rows)
如何读取EXPLAIN ANALYZE输出
您会看到，即使是简单的查询，您最终也会得到相当多的信息。要提取有意义的信息，您必须知道如何阅读它。
首先，你要明白一个PostgreSQL执行计划是一个由多个节点组成的树状结构。顶部节点（上Aggregate图）位于顶部，下部节点缩进并以箭头 ( ->)开头。缩进相同的节点处于同一级别（例如，两个关系与一个连接组合）。
PostgreSQL 自顶向下执行计划，也就是说，它从为顶部节点生成第一个结果行开始。执行器“按需”处理下层节点，也就是说，它只从它们中获取与计算上层节点的下一个结果所需的一样多的结果行。这会影响您如何阅读“成本”和“时间”：上层节点的启动时间至少与下层节点的启动时间一样长，总时间也是如此。如果你想找到在一个节点上花费的净时间，你必须减去在较低节点上花费的时间。并行查询使这更加复杂。
最重要的是，您必须将成本和时间乘以“循环”的数量，以获得在节点上花费的总时间。
在EXPLAIN ANALYZE输出中关注什么
- 查找花费大部分执行时间的节点。
- 找到估计行数与实际行数显着不同的最低节点。很多时候，这是性能不佳的原因，其他地方的执行时间长只是基于错误估计的错误计划选择的结果。“显着不同”通常意味着 10 倍左右。
- 使用删除许多行的过滤条件查找长时间运行的顺序扫描。这些都是很好的索引候选对象。
解释EXPLAIN ANALYZE输出的工具
由于阅读较长的执行计划非常麻烦，因此有一些工具试图将这个“文本海洋”可视化：
Depesz 的EXPLAIN ANALYZE可视化工具
该工具可以在https://explain.depesz.com/找到。如果您将执行计划粘贴到文本区域并点击“提交”，您将得到如下输出：
执行计划看起来与原始计划有些相似，但在视觉上更令人愉悦。有一些有用的附加功能：
- 计算并显示每个节点的总执行时间和净执行时间。这可以为您节省很多工作！次数最多的节点用红色背景突出显示，很容易被发现。
- 在标题“行 x”下，您可以看到 PostgreSQL 高估或低估了行数的因素。错误的估计以红色背景突出显示。
- 如果您单击一个节点，它下面的所有内容都将隐藏。这使您可以忽略长期执行计划中无趣的部分并专注于重要的部分。
- 如果您将鼠标悬停在一个节点上，它的所有直接子节点都会以星号突出显示。这使得在大型执行计划中找到它们变得容易。
我特别喜欢这个工具的一点是EXPLAIN，一旦您专注于一个有趣的节点，所有原始文本都在那里供您查看。外观和感觉显然更“老派”和严肃，这个网站已经存在很长时间了。
Dalibo 的EXPLAIN ANALYZE可视化工具
该工具可以在https://explain.dalibo.com/找到。再次粘贴原始执行计划并点击“提交”。输出显示为一棵树：
最初，显示会隐藏详细信息，但您可以通过单击一个节点来显示它们，就像我对上图中的第二个节点所做的那样。在左侧，您会看到所有节点的小概览，从那里您可以跳转到右侧以获取详细信息。增加价值的功能是：
- 在左侧，您会看到表示相对净执行时间的条形图，让您可以专注于最昂贵的节点。
- 同样在左侧，您可以选择“估计”以查看 PostgreSQL 高估或低估了每行的行数。
- 最后，您可以单击“缓冲区”以查看哪些节点使用了最多的 8kB 块。这是有用的信息，因为它显示了执行时间取决于数据缓存情况的节点。
- 在右侧，您可以通过单击展开一个节点，并在多个选项卡中获取所有详细信息。
- 您可以通过单击隐藏在节点右下角的“十字准线”图标来折叠树中节点下的所有内容。
这个工具的好处是它使执行计划的树结构清晰可见。外观和感觉是最新的。不利的一面是，它在某种程度上隐藏了详细信息，您必须学习在哪里搜索它。
结论
EXPLAIN (ANALYZE, BUFFERS)（track_io_timing打开）将向您展示诊断 SQL 语句性能问题所需的一切。为了避免淹没在文字的海洋中，您可以使用 Depesz 或 Dalibo 的可视化工具。两者都提供大致相同的功能。
