Greenplum - 优化SQL语句

注：本文翻译自官网https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/best_practices-tuning_queries.html

Greenplum数据库基于成本的优化器评估用于运行查询的许多策略，并选择成本最低的方法。

与其他RDBMS优化器一样，在计算备选执行计划的成本时，Greenplum优化器会考虑要连接的表中的行数、索引的可用性和列数据的基数等因素。优化器还考虑数据的位置，它倾向于在段上执行尽可能多的工作，并尽量减少为完成查询而必须在段之间传输的数据量。

当查询的运行速度低于预期时，您可以查看优化器选择的计划以及它为计划的每个步骤计算的成本。这将帮助您确定哪些步骤消耗了最多的资源，然后修改查询或模式，为优化器提供更有效的替代方案。您可以使用SQL EXPLAIN语句查看查询的计划。

优化器根据为表生成的统计信息生成计划。有准确的统计数据来制定最佳计划是很重要的。有关更新统计信息的信息，请参阅本指南中使用ANALYZE更新统计信息。

如何生成执行计划

EXPLAIN和EXPLAIN ANALYZE语句是确定改进查询性能的机会的有用工具。EXPLAIN显示查询计划和查询的估计成本，但不运行查询。除了显示查询计划外，EXPLAIN ANALYZE还会运行查询。EXPLAIN ANALYZE放弃SELECT语句的输出;但是，执行语句中的其他操作(例如INSERT、UPDATE或DELETE)。要在DML语句上使用EXPLAIN ANALYZE而不让命令影响数据，请在事务(BEGIN;解释分析…;回滚)。

EXPLAIN ANALYZE运行语句，并显示带有附加信息的计划，如下所示:

• 运行查询的总运行时间(以毫秒为单位)
• 参与计划节点操作的worker(段)的数目
• 为操作生成最多行的段(及其段ID)返回的最大行数
• 操作所使用的内存
• 从产生最多行的段中检索第一行所花费的时间(以毫秒为单位)，以及从该段中检索所有行所花费的总时间。

如何阅读执行计划

解释计划是一个报告，详细说明Greenplum Database优化器确定运行查询时要遵循的步骤。该计划是一个节点树，从下到上读取，每个节点将其结果传递给正上方的节点。每个节点表示计划中的一个步骤，每个节点的一行标识在该步骤中执行的操作，例如，扫描、连接、聚合或排序操作。节点还标识用于执行操作的方法。例如，扫描操作的方法可以是顺序扫描或索引扫描。连接操作可以执行散列连接或嵌套循环连接。

下面是一个简单查询的解释计划。该查询查找存储在每个段上的贡献表中的行数。

gpadmin=# EXPLAIN SELECT gp_segment_id, count(*)
                  FROM contributions 
                  GROUP BY gp_segment_id;
                                 QUERY PLAN                        
--------------------------------------------------------------------------------
 Gather Motion 2:1  (slice2; segments: 2)  (cost=0.00..431.00 rows=2 width=12)
   ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=12)
         Group By: gp_segment_id
         ->  Sort  (cost=0.00..431.00 rows=1 width=12)
               Sort Key: gp_segment_id
               ->  Redistribute Motion 2:2  (slice1; segments: 2)  (cost=0.00..431.00 rows=1 width=12)
                     Hash Key: gp_segment_id
                     ->  Result  (cost=0.00..431.00 rows=1 width=12)
                           ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=12)
                                 Group By: gp_segment_id
                                 ->  Sort  (cost=0.00..431.00 rows=7 width=4)
                                       Sort Key: gp_segment_id
                                       ->  Seq Scan on table1  (cost=0.00..431.00 rows=7 width=4)
 Optimizer status: Pivotal Optimizer (GPORCA) version 2.56.0
(14 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

这个计划有8个节点——Seq Scan, Sort, GroupAggregate, Result, Redistribute Motion, Sort, GroupAggregate，最后是Gather Motion。每个节点包含三个成本估计:成本(按顺序页面读取)、行数和行宽度。

费用分为两部分估算。成本为1.0等于顺序读取一个磁盘页面。估算的第一部分是启动成本，也就是获取第一行的成本。第二个估计是总成本，获取所有行的成本。

行估计值是计划节点输出的行数。该数字可能低于计划节点处理或扫描的实际行数，反映了WHERE子句条件的估计选择性。总成本假设所有行都将被检索，但情况可能并非总是如此(例如，如果使用LIMIT子句)。

宽度估计是计划节点输出的所有列的总宽度(以字节为单位)。

节点中的成本估计包括其所有子节点的成本，因此计划的最顶层节点(通常是Gather Motion)具有该计划的估计总执行成本。这是查询规划器寻求最小化的数字。

扫描操作符扫描表中的行以找到一组行。对于不同类型的存储，有不同的扫描操作符。它们包括以下内容:

• 对表进行扫描——扫描表中的所有行。
• 索引扫描-遍历索引以从表中获取行。
• 位图堆扫描-从索引收集指向表中行的指针，并按磁盘上的位置排序。(该操作符被称为位图堆扫描，即使对于只追加的表也是如此。)
• 动态Seq扫描-选择分区扫描使用分区选择功能。

连接操作符包括:

• 哈希连接——用连接列作为哈希键，从较小的表构建一个哈希表。然后扫描更大的表，计算连接列的哈希键，并探测哈希表以查找具有相同哈希键的行。哈希连接通常是Greenplum数据库中最快的连接。解释计划中的Hash Cond标识要连接的列。
• 嵌套循环——遍历较大数据集中的行，在每次迭代时扫描较小数据集中的行。嵌套循环连接需要广播其中一个表，以便可以将一个表中的所有行与另一个表中的所有行进行比较。对于小型表或受索引限制的表，它的性能很好。它也用于笛卡尔连接和范围连接。当对大型表使用嵌套循环连接时，会有性能影响。对于包含嵌套循环连接操作符的计划节点，请验证SQL并确保结果符合预期。将enable_nestloop服务器配置参数设置为OFF(默认)以支持散列连接。
• 合并连接-对两个数据集进行排序并将它们合并在一起。合并连接对于预先排序的数据来说速度很快，但在现实世界中非常罕见。为了支持合并连接而不是散列连接，请将enable_mergejoin系统配置参数设置为ON。

一些查询计划节点指定Motion操作。当需要处理查询时，Motion操作在段之间移动行。节点标识用于执行Motion操作的方法。Motion操作符包括:

• Broadcast motion-每个段发送自己的，单独的行到所有其他段，这样每个段实例都有一个完整的表的本地副本。Broadcast运动可能不如Redistribute运动最优，所以优化器通常只选择小表的Broadcast运动。Broadcast motion对于大表是不可接受的。如果数据没有分布在连接键上，则执行从一个表到另一个段的所需行的动态重新分布。
• Redistribute motion——每个段重新散列数据，并根据散列键将行发送到适当的段。
• Gather motion-结果数据，从所有部分组装成一个单一的流。这是大多数查询计划的最后一个操作。

在查询计划中出现的其他操作符包括:

• Materialize—规划器将子选择具体化一次，这样就不必为每个顶级行重复工作。
• InitPlan -一个预查询，用于动态分区消除，当规划器需要识别要扫描的分区的值在执行时间之前未知时执行。
• Sort—对行进行排序，为另一个需要有序行(如聚合或合并连接)的操作做准备。
• Group by-按一个或多个列对行进行分组。
• Group/Hash Aggregate -使用散列聚合行。
• Append-连接数据集，例如在合并从分区中扫描的行时。
• Filter-使用WHERE子句中的条件选择行。
• Limit-限制返回的行数。

优化语句

介绍在某些情况下可用于提高系统性能的Greenplum Database特性和编程实践。

要分析查询计划，首先要确定执行操作的估计成本非常高的计划节点。确定相对于所执行操作的行数，估计的行数和成本是否合理。

如果使用分区，验证是否实现了分区消除。要实现分区消除，查询谓词(WHERE子句)必须与分区标准相同。此外，WHERE子句不能包含显式值，也不能包含子查询。

查看查询计划树的执行顺序。查看估计的行数。您希望在较小的表或散列连接结果上构建执行顺序，并对较大的表进行探测。最理想的情况是，将最大的表用于最后的连接或探测，以减少通过树传递到最顶层计划节点的行数。如果分析显示执行构建和/或探测的顺序不是最优的，请确保数据库统计信息是最新的。运行ANALYZE可能会解决这个问题，并生成一个最佳的查询计划。

寻找计算偏差的证据。当执行Hash Aggregate和Hash Join等操作符导致在段上执行不均匀时，会在查询执行期间发生计算倾斜。在某些段上使用的CPU和内存比其他段多，从而导致执行不理想。原因可能是对基数低或分布不均匀的列进行连接、排序或聚合。您可以在查询的EXPLAIN ANALYZE语句的输出中检测计算偏差。每个节点包括任何一个段处理的最大行数和所有段处理的平均行数。如果最大行数远高于平均值，则至少有一个段比其他段执行了更多的工作，并且应该怀疑该操作符存在计算倾斜。

标识执行排序或聚合操作的计划节点。隐藏在聚合操作内部的是排序操作。如果Sort或Aggregate操作涉及大量行，则有机会提高查询性能。当需要对大量行进行排序时，HashAggregate操作比Sort和Aggregate操作更受欢迎。通常排序操作是由优化器根据SQL结构选择的;也就是说，由于SQL的编写方式。如果重写查询，大多数Sort操作都可以用HashAggregate替换。要使HashAggregate操作优于排序和聚合操作，请确保将enable_groupagg服务器配置参数设置为ON。

当解释计划显示具有大量行的广播运动时，您应该尝试消除广播运动。实现这一点的一种方法是使用gp_segments_for_planner服务器配置参数来增加运动的成本估算，从而使备选方案得到青睐。gp_segments_for_planner变量告诉查询规划器在其计算中要使用多少主段。默认值为零，它告诉规划器在估计中使用主段的实际数量。增加主段的数量会增加运动的成本，从而有利于重分配运动而不是广播运动。例如，设置gp_segments_for_planner = 100000告诉规划器有100000个段。相反，要影响优化器广播表而不是重新分发表，可以将gp_segments_for_planner设置为较低的数字，例如2。

Grouping扩展

数据库聚合扩展到GROUP BY子句可以比在应用程序或过程代码中更有效地执行数据库中的一些常见计算:

GROUP BY ROLLUP(*col1*, *col2*, *col3*)
GROUP BY CUBE(*col1*, *col2*, *col3*)
GROUP BY GROUPING SETS((*col1*, *col2*), (*col1*, *col3*))
1
2
3

ROLLUP分组创建汇总小计，这些小计按照分组列(或表达式)列表从最详细的级别累积到总汇总。ROLLUP获取分组列的有序列表，计算GROUP BY子句中指定的标准聚合值，然后从右向左依次创建更高级别的小计。最后，它创造了一个总额。
CUBE分组为给定的分组列(或表达式)列表的所有可能组合创建小计。在多维分析术语中，CUBE生成可以为具有指定维度的数据立方体计算的所有小计。
可以使用GROUPING SETS表达式选择性地指定要创建的组集。这允许跨多个维度进行精确规范，而无需计算整个ROLLUP或CUBE。
有关这些条款的详细信息，请参阅Greenplum数据库参考指南。

窗口函数

窗口函数对结果集的分区应用聚合或排序函数，例如，sum(population) over (partition by city)。窗口函数功能强大，因为它们在数据库中完成所有工作，所以它们比前端工具具有性能优势，后者通过从数据库中检索详细行并对其进行重新处理来产生类似的结果。
row_number()窗口函数为分区中的行生成行号，例如，row_number() over(order by id)。
当查询计划表明在多个操作中扫描一个表时，您可以使用窗口函数来减少扫描次数。
通常可以通过使用窗口函数来消除自连接。