查询画像

    Greenplum数据库为每个查询设计一个查询计划。选择正确的查询计划来匹配查询和数据结构对好的性能是必要的。一个查询计划定义Greenplum数据库将如何在并行执行环境中运行查询。

    查询优化器使用数据库维护的数据统计信息来选择具有最低可能代价的查询计划。代价以磁盘I/O来度量，磁盘I/O用取得的磁盘页面为单位。目标是最小化计划的总执行代价。

    可以用EXPLAIN命令查看一个给定查询的计划。EXPLAIN展示查询规划器对该查询计划估计的代价。例如：

    EXPLAIN ANALYZE不仅运行该语句，还会显示它的计划。这有助于判断优化器的估计与现实有多接近。例如：

    注意：在Greenplum数据库中，默认的GPORCA优化器与传统查询优化器共存。GPORCA生成的EXPLAIN输出与传统查询优化器生成的输出不同。

    默认情况下，Greenplum数据库会在可能时使用GPORCA来为查询生成执行计划。

    当EXPLAIN ANALYZE命令使用GPORCA时，EXPLAIN计划只显示被排除的分区数。被扫描的分区不会被显示。要在Segment日志中显示被扫描分区的名称，可以把服务器配置参数gp_log_dynamic_partition_pruning设置为on。这个SET命令的例子启用了该参数。

    1. SET gp_log_dynamic_partition_pruning = on;

    有关GPORCA的信息请见查询数据。

    上级主题：

    一个查询计划是一棵节点的树。计划中的每个节点表示一个操作，例如表扫描、连接、聚集或者排序。

    应该从底向上阅读计划：每个节点会把行交给直接在它上面的节点。一个计划中的底层节点通常是表扫描操作：顺序的、索引的或者位图索引扫描。如果该查询要求那些行上的连接、聚集、排序或者其他操作，就会有额外的节点在扫描节点上面负责执行这些操作。最顶层的计划节点通常是Greenplum数据库的移动节点：重新分布、显式重新分布、广播或者收集移动。这些操作在查询处理时在Segment实例之间移动行。

    • cost —以磁盘页面获取为单位度量。1.0等于一次顺序磁盘页面读取。第一个估计是得到第一行的启动代价，第二个估计是得到所有行的总代价。总代价总是假定所有的行都将被检索，但并不总是这样。例如，如果查询使用了LIMIT，并非所有的行都会被检索。
    • rows —这个计划节点输出的总行数。这个数字通常小于被该计划节点处理或者扫描的行数，它反映了任意WHERE子句条件的估计选择度。理想情况下，最顶层节点的估计近似于该查询实际返回、更新或者删除的行数。
    • width —这个计划节点输出的所有行的总字节数。

    注意以下几点：

    • 一个节点的代价包括其子节点的代价。最顶层计划节点有对于该计划估计的总执行代价。这就是优化器想要最小化的数字。
    • 代价只反映了被查询优化器加以考虑的计划执行的某些方面。例如，代价不反映将结果行传送到客户端花费的时间。

    下面的例子描述了如何阅读一个查询的EXPLAIN查询代价：

    从底向上阅读这个计划。一开始，查询优化器顺序地扫描names表。注意WHERE子句被应用为一个filter条件。这意味着扫描操作会对它扫描的每个行检查该条件并且只输出满足该条件的行。

    扫描操作的结果被传递给一个收集移动操作。在Greenplum数据库中，收集移动是Segment何时把行发送给Master。在这个例子中，我们有两个Segment实例会向一个Master实例发送。这个操作工作在并行执行计划的slice1上。查询计划会被划分成切片，这样Segment可以并行工作在查询计划的片段上。

    为这个计划估计的启动代价是00.00（没有代价）而总代价是20.88次磁盘页面获取。优化器估计这个查询将返回一行。

    EXPLAIN ANALYZE规划并且运行语句。EXPLAIN ANALYZE计划会把实际执行代价和优化器的估计一起显示。这允许用户查看优化器的估计是否接近于实际。EXPLAIN ANALYZE也展示下列信息：

    • 查询执行的总运行时间（以毫秒为单位）。
    • 查询计划每个切片使用的内存，以及为整个查询语句保留的内存。
    • 一个计划节点操作中涉及的工作者（Segment）数量。其中只统计返回行的Segment。
    • 为该操作产生最多行的Segment返回的行最大数量。如果多个Segment产生了相等的行数，EXPLAIN ANALYZE会显示那个用了最长的Segment。
    • 为一个操作产生最多行的Segment的ID。

    • 相关操作使用的内存量（work_mem）。如果work_mem不足以在内存中执行该操作，计划会显示溢出到磁盘的数据量最少的Segment的溢出数据量。例如：

      1. Work_mem used: 64K bytes avg, 64K bytes max (seg0).
      2. Work_mem wanted: 90K bytes avg, 90K byes max (seg0) to lessen 
      3. workfile I/O affecting 2 workers.

    EXPLAIN ANALYZE实例

    这个例子用同一个查询描述了如何阅读一个EXPLAIN ANALYZE查询计划。这个计划中粗体部分展示了每一个计划节点的实际计时和返回行，以及整个查询的内存和时间统计信息。

    1. EXPLAIN ANALYZE SELECT * FROM names WHERE name = 'Joelle';
    2.                      QUERY PLAN
    3. ------------------------------------------------------------
    4. Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..20.88 rows=1 width=13)
    5.     Rows out: 1 rows at destination with 0.305 ms to first row, 0.537 ms to end, start offset by 0.289 ms.
    6.         -> Seq Scan on names (cost=0.00..20.88 rows=1 width=13)
    7.              Rows out: Avg 1 rows x 2 workers. Max 1 rows (seg0) with 0.255 ms to first row, 0.486 ms to end, start offset by 0.968 ms.
    8.                  Filter: name = 'Joelle'::text
    9.  Slice statistics:
    11.       (slice0) Executor memory: 135K bytes.
    13.     (slice1) Executor memory: 151K bytes avg x 2 workers, 151K bytes max (seg0).
    15. Statement statistics:
    16.  Memory used: 128000K bytes
    17.  Total runtime: 22.548 ms

    从底向上阅读这个查询。运行这个查询花掉的总时间是22.548毫秒。

    sequential scan操作只有一个返回行的Segment（seg0），并且它只返回1行。它用了0.255毫秒找到第一行且用了0.486毫秒来扫描所有的行。这个结果接近于优化器的估计：查询优化器估计这个查询将会返回一行。收集移动（Segment向Master发送数据）接收到1行。这个操作的总消耗时间是0.537毫秒。

    判断查询优化器

    对于这两个查询计划的例子，GPORCA被启用，所以服务器配置参数OPTIMIZER为on。对于第一个计划，GPORCA生成了该EXPLAIN计划。对于第二个计划，Greenplum数据库回退到传统查询优化器来生成该查询计划。

    1. explain select count(*) from part;
    3.                        QUERY PLAN
    4. ----------------------------------------------------------------------------------------
    5.  Aggregate  (cost=3519.05..3519.06 rows=1 width=8)
    7.                ->  Seq Scan on part  (cost=0.00..3018.79 rows=100040 width=1)
    8.  Settings:  optimizer=on
    9.  Optimizer status: legacy query optimizer
    10. (5 rows)

    对于这个查询，服务器配置参数OPTIMIZER是off。

    1. explain select count(*) from part;
    3.                        QUERY PLAN
    4. ----------------------------------------------------------------------------------------
    5.  Aggregate  (cost=3519.05..3519.06 rows=1 width=8)
    6.    ->  Gather Motion 2:1  (slice1; segments: 2)  (cost=3518.99..3519.03 rows=1 width=8)
    7.          ->  Aggregate  (cost=3518.99..3519.00 rows=1 width=8)
    8.                ->  Seq Scan on part  (cost=0.00..3018.79 rows=100040 width=1)
    9.  Settings: optimizer=off
    11. (5 rows)

    如果一个查询执行得不好，检查它的查询并且提出以下问题：

    • 该计划中的操作花费了特别长的时间吗？查找消耗了多数查询执行时间的操作。例如，如果一个索引扫描花费了比预期长的时间，该索引可能过期并且需要重建索引。或者，调整enable_ 参数来看看是否能够强制传统查询优化器（规划器）来为该查询选择一个不同的计划。

    • 优化器的估计是否接近实际？ 运行EXPLAIN ANALYZE并且查看规划器估计的行数是否接近查询操作实际返回的行数。如果有很大的差别，应在相关列上收集更多统计信息。

      更多EXPLAIN ANALYZE和ANALYZE命令上的信息请见Greenplum数据库参考指南。

    • 在该计划中是否很早就应用了选择性谓词？ 在计划中早些应用最具选择性的过滤条件，这样会有较少的行在计划树中向上移动。如果查询计划没有正确地估计查询谓词的选择度，应在相关列上收集更多统计信息。更多有关收集统计信息的内容请见Greenplum数据库参考指南中的ANALYZE命令。 用户还可以尝试重新排序SQL语句中的WHERE子句。

    • 优化器是否选择了最好的连接顺序？ 当查询连接多个表时，确保优化器选择了最具选择性的连接顺序。计划中应该尽早做消除最多行的连接，这样会有较少的行在计划树中向上移动。

      如果计划没有选择最优的连接顺序，可以设置join_collapse_limit=1并且在SQL语句中使用显式的JOIN语法来强制传统查询优化器（规划器）用指定的连接顺序。还可以在相关的连接列上收集更多的统计信息。

      更多有关收集统计信息的内容请见Greenplum数据库参考指南中的ANALYZE命令。

    • 优化器是否有选择地扫描分区表？ 如果在使用表分区，优化器是否有选择地只扫描满足查询谓词所需的子表？对父表的扫描应该会返回0行，因为父表中不包含任何数据。一个显示选择性分区扫描的查询计划例子可见验证分区策略。

    • 优化器是否在适用时选择了哈希聚集和哈希连接操作？ 哈希操作通常比其他类型的连接或者聚集快很多。行比较和排序在内存中完成而不需要读写磁盘。为了让查询优化器选择哈希操作，必须由足够多的可用内存来保存估计数量的行。尝试增加工作内存来改进查询的性能。如果可能，未查询运行一次EXPLAIN ANALYZE来显示那些计划操作会溢出到磁盘、它们使用了多少工作内存以及需要多少内存来避免溢出到磁盘。例如：

      来自于EXPLAIN ANALYZE的”bytes wanted”消息是基于被写出到工作文件的数据量的而且并不准确。所需的最小work_mem可以与建议值不同。