The most powerful tool at our disposal for understanding and optimizing SQL
queries is EXPLAIN ANALYZE.
我们可以用来理解和优化 SQL 查询的最强大的工具是 EXPLAIN ANALYZE 。
It is a Postgres command that:
这是一个 Postgres 命令：
- accepts a statement such as
SELECT ...,UPDATE ..., orDELETE ...
接受诸如SELECT ...、UPDATE ...或DELETE ...之类的语句 - executes the statement 执行语句
- provides a query plan detailing what approach the planner took to executing the statement provided,
instead of returning the data
提供一个查询计划，详细说明规划器采用什么方法来执行所提供的语句，而不是返回数据
Here’s a query example pulled from the Postgres Using EXPLAIN page:
下面是从 Postgres 使用 EXPLAIN 页面提取的查询示例：
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2
ORDER BY t1.fivethous;
And the query plan it generates:
以及它生成的查询计划：
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
Sort Key: t1.fivethous
Sort Method: quicksort Memory: 77kB
-> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
-> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
Index Cond: (unique1 < 100)
Planning time: 0.194 ms
Execution time: 8.008 ms
Postgres builds a tree structure of plan nodes representing the different
actions taken, with the root and each -> pointing to one of them.
Postgres 构建了一个计划节点的树结构，代表所采取的不同操作，根和每个 -> 都指向其中一个。
In some
cases, EXPLAIN ANALYZE provides additional execution statistics beyond the
execution times and row counts, such as Sort and Hash above. Any line other
than the first without an -> is such information, so the structure of the
query is:
在某些情况下， EXPLAIN ANALYZE 提供除执行时间和行数之外的附加执行统计信息，例如上面的 Sort 和 Hash 。除了第一行之外没有 -> 的任何行都是这样的信息，因此查询的结构是：
Sort
└── Hash Join
├── Seq Scan
└── Hash
└── Bitmap Heap Scan
└── Bitmap Index Scan
Each tree’s branches represent sub-actions, and you’d work inside-out to
determine what’s happening “first” (though the order of nodes at the same level
could be different).
每棵树的分支代表子操作，您需要从内到外地确定“首先”发生的事情（尽管同一级别的节点顺序可能不同）。
Postgres EXPLAIN ANALYZE Query Plan Example Inside-out
Postgres EXPLAIN ANALYZE 查询计划示例由内而外
The first thing done is a Bitmap Index Scan on the tenk_unique1 index:
完成的第一件事是 tenk_unique1 索引上的 Bitmap Index Scan ：
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
Index Cond: (unique1 < 100)
This corresponds to the SQL WHERE t1.unique1 < 100. Postgres is finding the
locations of the rows matching the index condition unique1 < 100. The rows
themselves aren’t being returned here. The cost estimate (cost=0.00..5.04
rows=101 width=0) means that Postgres expects that it will “cost” 5.04 of an arbitrary unit of computation to find
these values.
这对应于 SQL WHERE t1.unique1 < 100 。 Postgres 正在查找与索引条件 unique1 < 100 匹配的行的位置。此处不会返回行本身。成本估计 (cost=0.00..5.04
rows=101 width=0) 意味着Postgres预计它将“花费”5.04个任意计算单位来找到这些值。
The 0.00 is the cost at which this node can begin working (in this
case, just startup time for the query). rows is the estimated number of rows
this Index Scan will return, and width is the estimated size in bytes of the
returned rows (0 because we only care about the location, not the content of the
rows).
0.00 是该节点可以开始工作的成本（在本例中，只是查询的启动时间）。 rows 是此索引扫描将返回的估计行数， width 是返回行的估计大小（以字节为单位）（0 是因为我们只关心位置，而不关心行的内容）。
Because we ran EXPLAIN with the ANALYZE option, the query was actually
executed and timing information was captured. (actual time=0.049..0.049
rows=100 loops=1) means that the index scan was executed 1 time (the loops
value), that it returned 100 rows, and that the actual time was 0.
因为我们使用 ANALYZE 选项运行 EXPLAIN ，所以实际执行了查询并捕获了计时信息。 (actual time=0.049..0.049
rows=100 loops=1) 表示索引扫描执行了 1 次（ loops 值），返回 100 行，实际时间为 0。
In the case
of a node executed more than once, the actual time is an average of each
iteration and you would multiply the value by the number of loops to get the real
time. The range values may also differ which gives an idea of min/max times
spent. This establishes a ratio for the costs that each cost unit of 0.049ms / 5.04 units ≈ 0.01ms/unit for this
query.
如果节点执行多次，则实际时间是每次迭代的平均值，您可以将该值乘以循环数以获得实际时间。范围值也可能不同，这给出了花费的最小/最大时间的概念。这为该查询建立了一个成本比率，即每个成本单位 0.049 毫秒 / 5.04 单位 ≈ 0.01 毫秒/单位。
The results of the Index Scan are passed up to a Bitmap Heap Scan action. In
this node, Postgres is taking the locations of the rows in the tenk1 table,
aliased as t1, where unique1 < 100 and fetching the rows from the table
itself.
索引扫描的结果被传递到 Bitmap Heap Scan 操作。在此节点中，Postgres 获取 tenk1 表（别名为 t1，其中 unique1 < 100 ）中行的位置，并从表本身获取行。
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
We can see that the cost expectations, when multiplied by the 0.01 value we
calculated, would mean a rough expected time of (229.20 - 5.07) * 0.01 ≈ 2.24ms, and we see an actual time of
0.526ms per row, which is off by a factor of 4. This may be because the cost
estimate is an upper bound and not all rows needed to be read, or because the
recheck condition is always true.
我们可以看到，成本预期乘以我们计算的 0.01 值时，意味着粗略的预期时间为 (229.20 - 5.07) * 0.01 ≈ 2.24ms，而我们看到每行的实际时间为 0.526ms，这与预期不符。增加了 4 倍。这可能是因为成本估算是上限并且并非所有行都需要读取，或者因为重新检查条件始终为 true。
The combination of Bitmap Index Scan and Bitmap Heap Scan is much more
expensive than reading the rows sequentially from the table (a Seq Scan), but
because relatively few rows need to be visited in this case the two step process
ends up being faster. This is further sped by sorting the rows into physical
order before fetching them, which minimizes the cost of separate fetches. The
“bitmap” in the node names does the sorting.
Bitmap Index Scan 和 Bitmap Heap Scan 的组合比从表中顺序读取行（ Seq Scan ）要昂贵得多，但因为在这种情况下需要访问的行相对较少，所以两步过程最终会更快。通过在获取行之前将行按物理顺序排序，可以进一步加快速度，从而最大限度地减少单独获取的成本。节点名称中的“位图”进行排序。
The results of the heap scan, those rows from tenk1 for which unique1 < 100 is
true, are inserted into an in-memory Hash table as they are read. As we can see
by the costs, this takes no time at all.
堆扫描的结果（来自 tenk1 且 unique1 < 100 为 true 的那些行）会在读取时插入到内存中的哈希表中。从成本来看，这根本不需要时间。
-> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
The Hash node includes information about the number of hash buckets and batches, as
well as peak memory usage. If Batches > 1 there’s also disk usage involved, but
that is not shown. The memory usage makes sense at 100 rows * 244 bytes = 24.4 kB, which is close enough to the
28kB memory usage. We can assume it’s the memory taken by the Hash keys
themselves.
哈希节点包括有关哈希桶和批次的数量以及峰值内存使用情况的信息。如果批次 > 1，还会涉及磁盘使用情况，但不会显示。内存使用量为 100 行 * 244 字节 = 24.4 kB，这与 28kB 内存使用量足够接近。我们可以假设这是哈希键本身占用的内存。
Next, Postgres reads all 10000 rows from tenk2 (aliased as t2) and checks them
against the Hash of tenk1 rows. Hash Join means that the rows of one table are
entered into an in-memory hash (which we’ve built up to so far), after which the
rows of another table is scanned and its values probed against the hash table
for matches.
接下来，Postgres 从 tenk2（别名为 t2）读取所有 10000 行，并根据 tenk1 行的哈希值检查它们。哈希联接意味着一个表的行被输入到内存中的哈希中（到目前为止我们已经构建了该哈希），然后扫描另一个表的行并根据哈希表探测其值以查找匹配项。
We see the conditions of the “match” on the second line, Hash
Cond: (t2.unique2 = t1.unique2). Note that because the query is selecting all
values from both tenk1 and tenk2, the width of each row doubles during the Hash
Join.
我们在第二行看到“匹配”的条件， Hash
Cond: (t2.unique2 = t1.unique2) 。请注意，由于查询从 tenk1 和 tenk2 中选择所有值，因此在哈希连接期间每行的宽度加倍。
-> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
Now that all rows that meet our conditions have been collected, we can sort the
result set by the Sort Key: t1.fivethous.
现在已经收集了满足条件的所有行，我们可以按 Sort Key: t1.fivethous 对结果集进行排序。
Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
Sort Key: t1.fivethous
Sort Method: quicksort Memory: 77kB
The Sort node includes information about the algorithm used to sort,
quicksort, whether the sort was done in memory or on disk (which greatly
effects speed), and the amount of memory/disk space needed.
Sort 节点包含有关用于排序的算法的信息， quicksort 、排序是在内存中还是在磁盘上完成（这极大地影响速度）以及所需的内存/磁盘空间量。
Use Postgres Query Plans to optimize performance
使用 Postgres 查询计划来优化性能
Understanding how to read query plans is great for optimizing queries. For
example, Seq Scan nodes often indicate an opportunity for an index to be added,
which is much faster to read.
了解如何读取查询计划对于优化查询非常有用。例如，Seq Scan 节点通常表示有机会添加索引，这样读取速度要快得多。
Familiarizing yourself with these plans will make
you a better database engineer. For more examples of query plans, read Using
EXPLAIN from which this example was taken.
熟悉这些计划将使您成为一名更好的数据库工程师。有关查询计划的更多示例，请阅读本示例的“Using EXPLAIN ”。
Now that you’re able to read a query plan, learn to optimize performance.
现在您已经能够阅读查询计划，接下来学习优化性能。
