EXPLAIN ANALYZE — 什么按什么顺序看

一句话总结

阅读执行计划并不是浏览节点名称，而是寻找优化器的预测和实际结果之间的差距。

为什么需要这个？

慢速查询中加上了EXPLAIN ANALYZE，但经常会看着填满屏幕的括号内的数字很久，然后跳过“看到Seq Scan了，让我们创建索引吧”。虽然结论是正确的，但计划想告诉我们的通常是其他事情。

如果没有差距，优化器就是在自己知道的信息范围内选择了最好的，剩下的瓶颈是物理问题。如果差距很大，优化器是在错误的前提下准确计算的，需要修改的目标不是查询，而是统计。

怎么行动

**EXPLAIN只制定计划，EXPLAIN ANALYZE实际执行。**所以EXPLAIN ANALYZE UPDATE ...真的执行UPDATE。分析写入查询时，必须用事务包裹起来并回滚。

阅读的顺序是从最深入的节点开始。深度越深，就越先执行，父节点在子节点完成后完成。还有父节点的actual time是包括子节点的时间在内的累积值。如果不知道这个事实，就会得出“连接需要131ms”的错误结论。

cost=1842.00..24310.55是从前面到第一行，从后面到最后一行的费用。单位不是毫秒，而是将读取一页顺序页面设为1.0的任意单位。所以与其他查询的cost进行比较没有意义，也没有依据“cost超过多少就危险”这样的标准。

真正要看的是同一个节点内的两个数字。

->  Index Scan using idx_orders_status on orders o
      (cost=0.42..8.44 rows=1 width=20)
      (actual time=0.031..214.882 rows=482913 loops=1)

预计1行，实际48万行。差了4万倍以上。优化器会判断“只有1行会出现，所以只要附加到叠加循环里就可以了”，但前提崩溃，内部节点会重复48万次。这时要解决的不是连接提示，而是统计。

loops必须乘以才能看。 显示的时间和行数是1次执行为基准的平均值。actual time=0.011 rows=4 loops=52310这样一来，总时间大约是575ms，总行大约是20万行。因为计划上哪里都没有写575这个数字，所以如果不亲自算乘法的话，就会错过瓶颈。

BUFFERS没有选项的EXPLAIN ANALYZE是半个。shared hit在缓冲快取中找到的区块，shared read是缓存以外读取的区块。同样的查询昨天是20ms，今天是900ms的原因一般不是计划，而是在缓存中。

在现场相遇的样子

Filter哇Index Cond的差异很重要。Filter是在读完行后丢弃的，Index Cond是本来就不读的。Rows Removed by Filter如果下面有大数字，那就是有机会将该条件提升到索引中的信号。

在Hash Join中Batches如果不是1，哈希表work_mem意思是无法全部放入，所以被分成磁盘。在这种情况下，与其创建索引，不如把该会话的work_mem上传的更有效。但是上传全局设置的话很危险。work_mem因为不是连接党，而是每个查询内的排序或哈希运算分配。

统计有误的时候做什么

前面看到的4万倍的差距不是优化器懒惰造成的，而是错误的前提 这是上面准确计算的结果。那么就需要修改前提。

**首先看看统计数据是否过时。**大量插入或删除后不久，统计数据与实际情况 大不相同。虽然有自动更新的装置，但其门槛取决于表的大小。 因为是成正比的，所以在大的表格上需要积累相当大的变化才能产生收益。大规模作业之后，用手 一次更新是正规做法。

**看看样本是否不足。**统计是从样本中制作的，基本样本规模不大。 不是。在价格非常多样的热量或偏重严重的热量中，该样本的实际分布 不能控制。可以将样本目标提高到十个单位，提高后再重新统计。 要制作才能反映出来。

看看热量是否相互纠缠。这是最常忽略的地方。优化器 基本上假设条件彼此独立，然后乘以选择度。但是 city = '서울'科region = '수도권'不是独立。乘起来比实际要多得多。 出现一个小数，相信那个小数，选择重叠循环。多个列的关系一起 如果制作扩展统计的话，这种类型的误差就会消失。

用表达式过滤也一样。lower(email) = ...像这样加上函数的话，那个 因为没有关于表达式的统计数据，所以优化器使用固定的近似值。表达式 创建索引后，可以脱索引，同时也会产生该表达式的统计。

而且也有无法修复的情况。条件会根据不同的表格值而变化， 同一查询根据参数遇到完全不同的分布的情况。这时计划 在强制之前，首先审查将查询分成两部分。一个查询有两种 如果在做完全不同的事情，要求优化器选择一个计划。 本身就很勉强。

下次实习要做的事情

直接测量了EXPLAIN和EXPLAIN ANALYZE的差异、BUFFERS的效用、loops的乘法、Hash的Batches，以及强制烧写索引时反而变慢的情况，并将其作为文件留存下来。