使用 EXPLAIN

PostgreSQL为每个收到的查询产生一个查询计划。选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝对是最关键的，因此系统包含了一个复杂的规划器来尝试选择好的计划。你可以使用EXPLAIN命令察看规划器为任何查询生成的查询计划。

查询计划的结构是一个计划结点的树。最底层的结点是扫描结点：它们从表中返回未经处理的行。不同的表访问模式有不同的扫描结点类型：顺序扫描、索引扫描、位图索引扫描。也还有不是表的行来源，例如VALUES子句和FROM中返回集合的函数，它们有自己的结点类型。如果查询需要连接、聚集、排序、或者在未经处理的行上的其它操作，那么就会在扫描结点之上有其它额外的结点来执行这些操作。并且，做这些操作通常都有多种方法，因此在这些位置也有可能出现不同的结点类型。EXPLAIN给计划树中每个结点都输出一行，显示基本的结点类型和结点信息。可能会出现从结点摘要行缩进的其他行，以显示结点的其他属性。

这里是一个简单的例子，只是用来显示输出看起来是什么样的：

EXPLAIN (costs off)
  SELECT * FROM t WHERE id < 1000
    ORDER BY id LIMIT 10;

            QUERY PLAN
-----------------------------------
 Limit
   ->  Sort
         Sort Key: id
         ->  Seq Scan on t
               Filter: (id < 1000)

对于具体的计划结点的介绍，可参见 查询计划 章节；

SQL命令EXPLAIN有一个参数COSTS，用来控制在输出中显示查询计划结点的代价估计值。可以输出包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为TRUE。例如：

EXPLAIN
  SELECT * FROM t WHERE id < 1000
    ORDER BY id LIMIT 10;

                             QUERY PLAN
---------------------------------------------------------------------
 Limit  (cost=22939.84..22939.87 rows=10 width=69)
   ->  Sort  (cost=22939.84..22942.48 rows=1057 width=69)
         Sort Key: id
         ->  Seq Scan on t  (cost=0.00..22917.00 rows=1057 width=69)
               Filter: (id < 1000)

输出的格式为：计划结点 (cost=启动开销..总开销 rows=计划结点输出行数 width=计划结点输出行宽)。

返回第一行的估计成本。

这个数值的单位是任意的，目的是与启动时间相关。

从该计划结点及其子结点返回所有行的估计总成本。

Postgres查询规划器通常会有几种不同的路径可以执行同一查询。它为每个潜在计划计算成本–希望与所花费的时间相关–然后选择成本最小的计划。值得记住的是，这些成本是无单位的–它们不是为了转换为时间或磁盘读取而设计的。对于较慢的操作，它们应该更大，对于较快的操作，它们应该更小。

如果查询规划器认为它可以由于上层的LIMIT操作而提前结束，则LIMIT操作的总开销将反映这一点，但其下面的子结点操作的总开销不会反映这一点。

查询规划器预计会从该计划结点返回的行数，该数值是按单次执行计算。

糟糕的行数估计可能导致次优的查询规划。通常可以通过运行ANALYZE（不是EXPLAIN参数）、增加统计信息、或者允许Postgres使用多元统计信息了解列之间的相关性，来改进它们。

如果查询规划器认为它可以由于上层的LIMIT操作而提前结束，则LIMIT操作的估计输出行数将反映这一点，但其下面的子结点操作的估计输出行数不会反映这一点。

操作返回的每行的估计的平均大小，以字节为单位。

SQL命令EXPLAIN有一个参数ANALYZE，可以执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为FALSE。

通过实际运行命令，我们可以看到查询的哪些部分是真正耗时的，并可以将这些部分与它们的估计值进行比较。例如：

EXPLAIN (analyze)
  SELECT * FROM t WHERE id < 1000
    ORDER BY id LIMIT 10;

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=22939.84..22939.87 rows=10 width=69) (actual time=55.361..55.362 rows=10 loops=1)
   ->  Sort  (cost=22939.84..22942.48 rows=1057 width=69) (actual time=55.360..55.360 rows=10 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Seq Scan on t  (cost=0.00..22917.00 rows=1057 width=69) (actual time=0.020..55.297 rows=999 loops=1)
               Filter: (id < 1000)
               Rows Removed by Filter: 999001
 Planning time: 0.061 ms
 Execution time: 55.385 ms

输出的格式为：(actual time=实际启动开销..实际总开销 rows=实际输出行数 loops=实际执行次数)。

从计划结点返回第一行所需的开销时间，以毫秒为单位。

有时，这非常接近计划结点的设置时间。例如，在返回表中所有行的顺序扫描中。

不过，其他时候，这或多或少是总时间。例如，为了返回排序的第一行，您必须对所有行进行排序，以计算哪一行先返回。

此操作及其下面的子结点操作上花费的实际总开销时间（以毫秒为单位）。它是每次执行的平均值，四舍五入到最接近的千分之一毫秒。

确定单个操作时间，特别是在涉及CTE和子计划的情况下，可能会很棘手。

每次执行该计划结点返回的行数。

需要重点注意的是，它是所有执行中单次执行的平均值，四舍五入到最接近的整数。这意味着，在大多数时候，虽然“实际执行次数”乘以“实际输出行数”是返回的总行数的一个相当好的近似值，但实际总行数可能会减少到计算值的一半。通常，当您在操作之间看到一两行出现或消失时，它只是有点混乱，但在反复循环执行的计划结点操作中，可能会出现严重的计算错误。

糟糕的行数估计（实际行数与估计行数有很大差异）可能导致次优的查询规划。通常可以通过运行ANALYZE（不是EXPLAIN参数）、增加统计信息、或者允许Postgres使用多元统计信息了解列之间的相关性，来改进它们。

执行计划结点的次数。对于许多结点，它的值将为1，但当它不是时，有三种不同的情况：

1. 某些计划结点可以多次执行。例如，“嵌套循环”为其“外部”的子结点返回的每一行运行一次其“内部”的子结点。
2. 当通常只由一次执行完成的操作跨多个进程拆分执行时，每个部分的操作都被计算为一次执行。
3. 当计划结点根本不需要执行时，执行的次数可以为零。例如，如果计划结点读取表为内部连接提供候选项，但结果发现连接的另一侧没有行，则可以有效地消除该结点。

SQL 命令EXPLAIN具有一个参数BUFFERS，用于控制要在输出中显示的查询计划节点的缓冲区使用情况。仅当同时启用了ANALYZE时，才能使用此参数。它默认为FALSE。

下面是一个简单的示例：

EXPLAIN (analyze, buffers, costs off)
  SELECT * FROM t WHERE id < 1000
    ORDER BY id LIMIT 10;

                               QUERY PLAN
------------------------------------------------------------------------
 Limit (actual time=86.844..86.845 rows=10 loops=1)
   Buffers: shared read=3473
   ->  Sort (actual time=86.842..86.843 rows=10 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared read=3473
         ->  Seq Scan on t (actual time=0.344..86.717 rows=999 loops=1)
               Filter: (id < 1000)
               Rows Removed by Filter: 999001
               Buffers: shared read=3473
 Planning time: 5.658 ms
 Execution time: 86.971 ms

输出的格式为：

Buffers: shared hit=共享块命中数 read=共享块读取数 dirtied=共享块修改数 written=共享块刷写数, local hit=本地块命中数 read=本地块读取数 dirtied=本地块修改数 written=本地块刷写数, temp read=临时块读取数 written=临时块刷写数

I/O Timings: read=读入块耗时 write=写出块耗时

只有当其中的统计数据不为零时才会显示对应的指标。

共享块命中数（shared hit），在共享缓冲区中找到的索引/表中的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

减去子计划结点的值后，共享块命中数与共享块读取数的比例，可用于度量共享缓冲区的缓存效果。

共享块命中和读取数，与处理的行数（包括输出和过滤掉的行）的比率也很有意义：高比率意味着Postgres正在读取大量数据，这可能是膨胀的迹象。

共享块读取数（shared read），从磁盘（或操作系统缓存）读取的表/索引中的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

减去子计划结点的值后，共享块命中数与共享块读取数的比例，可用于度量共享缓冲区的缓存效果。

共享块命中和读取数，与处理的行数（包括输出和过滤掉的行）的比率也很有意义：高比率意味着Postgres正在读取大量数据，这可能是膨胀的迹象。

共享块修改数（shared dirtied），由该计划结点修改的表/索引中的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

SELECT查询可能在最近被修改过的页面上设置事务状态标记，在共享缓冲区中修改块。

共享块刷写数（shared written），从共享缓冲区中逐出的表/索引中的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

本地块命中数（local hit），在本地缓冲区中找到的临时表和索引中的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

本地块读取数（local read），从临时表和索引读取的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

本地块修改数（local dirtied），由该计划结点修改的临时表和索引中的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

本地块刷写数（local written），从本地缓冲区中逐出的临时表和索引中的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

临时块读取数（temp read），从临时数据中读取的块数，用于计算哈希、排序、物化操作等。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

您的设置work_mem决定了每个计划结点有多少内存可供Postgres使用。

临时块刷写数（temp written），从工作内存区中逐出的临时数据（用于计算哈希、排序、物化操作等）中的块数。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

您的设置work_mem决定了每个计划结点有多少内存可供Postgres使用。

读入块耗时（I/O Read Time），读入块所花费的实际时间，以毫秒为单位。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

需要打开track_io_timing和BUFFERS，但不受EXPLAIN的TIMING参数的影响。

写出块耗时（I/O Write Time），写出块所花费的实际时间，以毫秒为单位。

它包括了子计划结点的值，并且是总计数值（即不是每次执行）。

需要打开track_io_timing和BUFFERS，但不受EXPLAIN的TIMING参数的影响。