postgres query plan

落日映苍穹つ 2021-09-22 01:10 313阅读 0赞

PostgreSQL数据库针对它收到的每一个sql查询都会设计一个query plan-查询计划。要想拥有良好的查询性能performance， 选择正确的query计划来匹配查询的结构和数据本身的特性绝对是至关重要的，因此postgres数据库系统有一个复杂的计划器planner用来为sql查询选择好的query plan。 我们可以使用 EXPLAIN 命令查看规划器为任何查询创建的查询计划。

query plan查询计划的结构是计划节点树plan node。 树底层的节点是数据库表扫描节点：它们从数据库表中返回数据的原始行。 不同的表访问方法有不同类型的扫描节点：顺序扫描sequential scan、index scan索引扫描和位图索引扫描bitmap scan。 如果查询需要对原始行进行join、aggregation聚合、排序或其他操作，那么在扫描节点之上会有额外的节点来执行这些操作。 同样，通常有不止一种可能的方法来执行这些操作，因此这里也可能出现不同的节点类型。 EXPLAIN 的输出当中对计划树中的每个节点都有一行，显示基本节点类型以及计划器为执行该计划节点所做的成本估计。 第一行（最上面的节点）是计划的估计总执行成本； sql规划寻求最小化的正是这个数字。

下面是一个例子:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvcGRldmVsb3BlcnI_size_16_color_FFFFFF_t_70][]

EXPLAIN 生成的query plan中有几个数字，他们的意思是（从左到右）：

*   估计启动成本（输出扫描开始前花费的时间，例如在排序节点中进行排序的时间）
 *  估计总成本（如果所有行都被检索时的花费，当然也有可能不会检索所有行带有 LIMIT 子句的查询将停止支付限制计划节点的输入节点的总成本）
 *  此执行计划节点输出的估计行数（仅当执行完成时）
 *  此执行计划节点输出的行的估计平均宽度（以字节为单位）

执行成本以由计划者的成本参数确定的任意单位进行衡量。 传统的做法是以磁盘页面获取为单位来衡量成本； 也就是说，seq\_page\_cost 通常设置为 1.0，而其他成本参数则相对于此设置。

需要注意的是，上层节点的成本包括其所有子节点的成本。 同样重要的是要意识到成本只反映了计划者关心的事情。 特别是，成本不考虑将结果行传输到客户端所花费的时间，这可能是实际经过时间的一个重要因素； 但是计划者忽略了它，因为它不能通过改变计划来改变它。 （我们相信，每个正确的计划都会输出相同的行集。）

行值有点棘手，因为它不是计划节点处理或扫描的行数。 它通常较小，反映了在节点上应用的任何 WHERE 子句条件的估计选择性。 理想情况下，顶级行估计将近似于查询实际返回、更新或删除的行数。

回到我们的例子：

EXPLAIN SELECT * FROM tenk1;
    
                             QUERY PLAN
    -------------------------------------------------------------
     Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

这很简单。 如果你这样做：

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

你会发现tenk1有358个磁盘页和10000行。 估计成本计算为（读取的磁盘页数 \* seq\_page\_cost）+（扫描的行数 \* cpu\_tuple\_cost）。 默认情况下，seq\_page\_cost 为 1.0，cpu\_tuple\_cost 为 0.01，因此估计成本为 (358 \* 1.0) + (10000 \* 0.01) = 458。

现在让我们修改原始查询以添加 WHERE 条件：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
    
                             QUERY PLAN
    ------------------------------------------------------------
     Seq Scan on tenk1  (cost=0.00..483.00 rows=7033 width=244)
       Filter: (unique1 < 7000)

请注意，EXPLAIN 输出显示 WHERE 子句作为“过滤器”条件应用； 这意味着计划节点检查它扫描的每一行的条件，并只输出通过条件的那些。 由于 WHERE 子句，输出行的估计已减少。 但是，扫描仍然需要访问所有 10000 行，因此成本并没有降低； 事实上，它已经上升了一点（准确地说是 10000 \* cpu\_operator\_cost）以反映检查 WHERE 条件所花费的额外 CPU 时间。

此查询将选择的实际行数为 7000，但行估计值只是近似值。 如果您尝试重复此实验，您可能会得到略有不同的估计值； 此外，它会在每个 ANALYZE 命令之后发生变化，因为 ANALYZE 产生的统计数据来自表的随机样本。

现在，让我们使条件更具限制性：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
    
                                      QUERY PLAN
    ------------------------------------------------------------------------------
     Bitmap Heap Scan on tenk1  (cost=2.37..232.35 rows=106 width=244)
       Recheck Cond: (unique1 < 100)
       ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
             Index Cond: (unique1 < 100)

这里规划器决定使用两步计划：底部计划节点访问索引以查找匹配索引条件的行的位置，然后上部计划节点实际从表本身中获取这些行。 单独获取行比顺序读取要昂贵得多，但由于不必访问表的所有页面，因此这仍然比顺序扫描便宜。 （使用两个计划级别的原因是上计划节点在读取它们之前将索引标识的行位置按物理顺序排序，以最小化单独获取的成本。节点名称中提到的“位图”是一种机制 进行排序。）

如果 WHERE 条件足够有选择性，规划器可能会切换到“简单”索引扫描计划：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
    
                                      QUERY PLAN
    ------------------------------------------------------------------------------
     Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.00 rows=2 width=244)
       Index Cond: (unique1 < 3)

在这种情况下，表行是按索引顺序获取的，这使得它们的读取成本更高，但数量太少以至于对行位置进行排序的额外成本是不值得的。 对于仅获取单行的查询以及具有与索引顺序匹配的 ORDER BY 条件的查询，您最常看到这种计划类型。

向 WHERE 子句添加另一个条件：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
    
                                      QUERY PLAN
    ------------------------------------------------------------------------------
     Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.01 rows=1 width=244)
       Index Cond: (unique1 < 3)
       Filter: (stringu1 = 'xxx'::name)

添加的条件 stringu1 = 'xxx' 减少了输出行估计，但不会降低成本，因为我们仍然必须访问相同的行集。 请注意，stringu1 子句不能用作索引条件（因为此索引仅位于 unique1 列上）。 相反，它被用作索引检索到的行的过滤器。 因此，成本实际上略有增加以反映这种额外的检查。 如果 WHERE 中引用的多个列上有索引，规划器可能会选择使用索引的 AND 或 OR 组合：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
    
                                         QUERY PLAN
    -------------------------------------------------------------------------------------
     Bitmap Heap Scan on tenk1  (cost=11.27..49.11 rows=11 width=244)
       Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
       ->  BitmapAnd  (cost=11.27..11.27 rows=11 width=0)
             ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                   Index Cond: (unique1 < 100)
             ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..8.65 rows=1042 width=0)
                   Index Cond: (unique2 > 9000)

但这需要访问两个索引，因此与仅使用一个索引并将另一个条件视为过滤器相比，这不一定是胜利。 如果您改变所涉及的范围，您会看到计划相应地发生变化。

让我们尝试使用我们一直在讨论的列连接两个表：

EXPLAIN SELECT *
    FROM tenk1 t1, tenk2 t2
    WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
    
                                          QUERY PLAN
    --------------------------------------------------------------------------------------
     Nested Loop  (cost=2.37..553.11 rows=106 width=488)
       ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
             Recheck Cond: (unique1 < 100)
             ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                   Index Cond: (unique1 < 100)
       ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244)
             Index Cond: (unique2 = t1.unique2)

在这个嵌套循环连接中，外部（上）扫描与我们之前看到的位图索引扫描相同，因此它的成本和行数是相同的，因为我们在该节点应用了 WHERE 子句 unique1 < 100。 t1.unique2 = t2.unique2 子句尚不相关，因此它不会影响外部扫描的行数。对于内部（下）扫描，当前外部扫描行的 unique2 值被插入内部索引扫描以产生一个索引条件，如 unique2 = constant。所以我们得到了与我们从 EXPLAIN SELECT \* FROM tenk2 WHERE unique2 = 42 中得到的相同的内部扫描计划和成本。 然后根据外部扫描的成本设置循环节点的成本，加上每个外行的内部扫描重复一次（106 \* 3.01，这里），加上一点 CPU 时间用于连接处理。

在此示例中，连接的输出行数与两次扫描的行数的乘积相同，但并非在所有情况下都是如此，因为您可以使用 WHERE 子句来提及两个表，因此只能在连接点应用，不要输入扫描。例如，如果我们添加了 WHERE ... AND t1.hundred < t2.hundred，这将减少连接节点的输出行数，但不会改变任一输入扫描。

查看变体计划的一种方法是使用第 18.7.1 节中描述的启用/禁用标志，强制计划器忽略它认为最便宜的任何策略。 （这是一个粗略的工具，但很有用。另见第 14.3 节。）

SET enable_nestloop = off;
    EXPLAIN SELECT *
    FROM tenk1 t1, tenk2 t2
    WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
    
                                            QUERY PLAN
    ------------------------------------------------------------------------------------------
     Hash Join  (cost=232.61..741.67 rows=106 width=488)
       Hash Cond: (t2.unique2 = t1.unique2)
       ->  Seq Scan on tenk2 t2  (cost=0.00..458.00 rows=10000 width=244)
       ->  Hash  (cost=232.35..232.35 rows=106 width=244)
             ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
                   Recheck Cond: (unique1 < 100)
                   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                         Index Cond: (unique1 < 100)

该计划建议使用相同的旧索引扫描提取 Tenk1 的 100 行有趣的行，将它们存储到内存中的哈希表中，然后对tenk2 进行顺序扫描，探查哈希表中可能匹配的 t1.unique2 = 每个tenk2行的t2.unique2。 读取tenk1 和设置哈希表的成本是哈希连接的启动成本，因为在我们开始读取tenk2 之前不会有输出。 连接的总时间估计还包括 CPU 时间来探测哈希表 10000 次的高额费用。 但是请注意，我们不会收取 10000 次 232.35； 在此计划类型中，哈希表设置仅完成一次。 可以使用 EXPLAIN ANALYZE 检查计划员估计成本的准确性。 该命令实际执行查询，然后显示每个计划节点内累积的真实运行时间以及与普通 EXPLAIN 显示的估计成本相同的估计成本。 例如，我们可能会得到这样的结果：

EXPLAIN ANALYZE SELECT *
    FROM tenk1 t1, tenk2 t2
    WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
    
                                                                QUERY PLAN
    ----------------------------------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
       ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
             Recheck Cond: (unique1 < 100)
             ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
                   Index Cond: (unique1 < 100)
       ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
             Index Cond: (unique2 = t1.unique2)
     Total runtime: 14.452 ms

请注意，“实际时间”值以实时毫秒为单位，而成本估计以任意单位表示；所以他们不太可能匹配。需要注意的是实际时间和估计成本的比例是否一致。

在一些查询计划中，一个子计划节点可能被执行多次。例如，在上面的嵌套循环计划中，内部索引扫描对每个外部行执行一次。在这种情况下，循环值报告节点的总执行次数，显示的实际时间和行值是每次执行的平均值。这样做是为了使数字与显示成本估算的方式具有可比性。乘以循环值以获得实际在节点中花费的总时间。

EXPLAIN ANALYZE 显示的总运行时间包括执行程序启动和关闭时间，但不包括解析、重写或计划时间。对于 INSERT、UPDATE 和 DELETE 命令，应用表更改所花费的时间将记入顶级插入、更新或删除计划节点。 （此节点下的计划节点表示定位旧行和/或计算新行的工作。）执行 BEFORE 触发器所花费的时间（如果有）计入相关的插入、更新或删除节点，尽管执行所花费的时间AFTER 触发器不是。在每个触发器中花费的时间（之前或之后）也单独显示并包含在总运行时间中。但是请注意，延迟约束触发器在事务结束之前不会执行，因此不会由 EXPLAIN ANALYZE 显示。

由 EXPLAIN ANALYZE 测量的运行时间可以通过两种重要方式偏离同一查询的正常执行。首先，由于没有将输出行传送到客户端，因此不包括网络传输成本和 I/O 格式化成本。其次，EXPLAIN ANALYZE 增加的开销可能很大，尤其是在 gettimeofday() 内核调用速度较慢的机器上。

值得注意的是，EXPLAIN 结果不应外推到您实际测试的情况以外的情况；例如，不能假定玩具大小的表上的结果适用于大表。计划器的成本估算不是线性的，因此它可能会为更大或更小的表选择不同的计划。一个极端的例子是，在一个只占用一个磁盘页面的表上，无论索引是否可用，您几乎总是会得到一个顺序扫描计划。规划器意识到在任何情况下都需要读取一个磁盘页面来处理表，因此花费额外的页面读取来查看索引是没有价值的。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvcGRldmVsb3BlcnI_size_16_color_FFFFFF_t_70]: /images/20210920/dffcee75d5cd4118bbb573c16d5dc13d.png