Profiling Query Compilation Time with GPORCA

拼搏现实的明天。 2022-12-31 07:21 131阅读 0赞

GPORCA是Pivotal针对greenplum数据库和apache HAWQ（孵化）的查询优化器。在这篇文章中，我们将描述用户如何使用GPORCA分析查询编译。这将有助于用户了解GPORCA的哪些步骤是资源密集型的，以及正在触发哪些转换。基于这些信息，用户可以提供查询提示，以减少或增加搜索空间，查看时间和内存的使用情况，并了解如何影响其决策。

Pivotal的查询优化器（PQO）旨在寻找在分布式环境（如Pivotal的Greenplum数据库）中执行SQL查询的最快方式。PQO的开源版本名为GPORCA。在分布式环境中处理大量数据时，一个简单的查询计划可能比最佳计划花费的时间多几个数量级。在某些情况下，查询计划甚至在几个小时后也无法完成，如我们的实验研究\[1\]所示。为了生成最优计划，GPORCA考虑了数千个可选的查询执行计划，并做出基于成本的决策。

在本文中，我们将描述GPDB用户可以设置的参数：

*  影响GPORCA的决策过程来选择某类执行计划。
 *  在查询优化阶段打印GPORCA状态。
 *  枚举查询优化期间应用的转换列表。
 *  报告查询编译时间在GPORCA中花费的位置。

Pivotal的Greenplum数据库（GPDB）基于Postgres。因此，我们使用PostgreSQL中的grand unified configuration（GUC）子系统将特定于优化器的参数设置传递给GPORCA。

## Running example ##

在本博客中，我们使用来自TPC-H基准的查询Q1作为示例。

-- tpch q1
    SELECT 
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
    FROM lineitem
    WHERE l_shipdate <= date '1998-12-01' - interval '108 day'
    GROUP BY (l_returnflag, l_linestatus)
    ORDER BY (l_returnflag, l_linestatus);

## GPORCA Workflow ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg_size_16_color_FFFFFF_t_70]  
GPORCA及其主机系统（GPDB或HAWQ）使用数据交换语言（DXL）来传递诸如查询、元数据信息和数据库设置等信息。

*  Algebrizer (Query To DXL Translator) 将解析后的GPDB或HAWQ查询对象作为输入，并返回其DXL表示形式。然后将序列化的DXL发送到GPORCA进行优化。DXL假设having子句被转换成select谓词，groupby逻辑运算符有一个项目列表，其中只包含分组列和聚合，而不包含表达式。代数化将输入查询变为规范化的查询形式，以确保DXL符合这些假设。
 *  DXL To Logical Expression Translator 将DXL查询对象作为输入，并将其转换为内部逻辑树表示形式，并对其进行优化。
 *  Expression Pre-Processing 将逻辑表达式树作为输入，并在应用一些启发式方法（如将select推到扫描、删除不必要的计算列和检测矛盾）后生成等效的逻辑表达式树。
 *  GPORCA Optimization Phases GPORCA使用搜索机制在可能的备选方案空间中导航，并以最小的估计成本确定计划。搜索机制由一个专门的作业调度器启用，该调度器创建依赖的或并行的工作单元，以在三个主要步骤中执行查询优化。首先，探索阶段通过为每个子表达式生成等价的逻辑表达式来增加搜索空间。第二，在实现阶段，GPORCA考虑不同运营商的替代物理实现。例如，它可以选择是通过嵌套循环联接实现联接，还是通过哈希联接实现联接。或者，它可以选择通过流实现聚合操作还是基于哈希的实现。最后，在优化阶段，我们强制执行所需的物理属性（如分布和排序顺序），并对不同的计划替代方案进行成本计算，以选择最佳的执行计划。
 *  Statistics Derivation 将逻辑表达式和统计信息作为输入，以返回输出基数。
 *  Physical Expression To DXL Translation 将表示物理计划的表达式树作为输入，并将其转换为DXL表示形式。
 *  DXL to PlannedStmt Translator 获取物理计划的DXL表示形式，并将其转换为特定于主机（GPBD或HAWQ）的planned语句对象。
 *  Metadata Translator 转换查询优化期间所需的所有数据库对象，如表架构、索引、约束和列统计信息。  
    我们的白皮书\[2\]和GPDB文档\[3\]中详细介绍了使用GPROCA优化查询所涉及的步骤。

## How does one know where GPORCA is spending its time? ##

有些情况下，用户可能希望了解查询编译最耗时的方面是什么。基于此，用户可以决定向GPORCA提供提示，以选择特定形状的平面或更快地生成平面。  
为了表达记录GPORCA度量的意图，GPDB用户必须在查询级别启用这两个guc。  
set optimizer\_print\_optimization\_stats = on;  
set client\_min\_messages=‘log’;  
对于TPC-H查询Q1，启用上述GUCs将生成以下GPORCA度量以供显示。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg_size_16_color_FFFFFF_t_70 1]  
衡量的GPORCA指标包括：

*  GPORCA在哪里消磨时间
 *  GPORCA从主机系统获取有关数据库对象的相关信息（如模式、统计信息、可用索引和约束）所需的时间。
 *  用于生成计划的转换规则列表

\[1\] [New Benchmark Results: Pivotal Query Optimizer Speeds Up Big Data Queries Up To 1000x][New Benchmark Results_ Pivotal Query Optimizer Speeds Up Big Data Queries Up To 1000x].  
\[2\] [Orca: A Modular Query Optimizer Architecture for Big Data][Orca_ A Modular Query Optimizer Architecture for Big Data].  
\[3\] [Enabling PQO in GPDB][].

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg_size_16_color_FFFFFF_t_70]: /images/20221120/3cd0f0eb3a67448d934a1dccfd22b96b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201228231843260.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg==,size_16,color_FFFFFF,t_70
[New Benchmark Results_ Pivotal Query Optimizer Speeds Up Big Data Queries Up To 1000x]: https://blog.pivotal.io/big-data-pivotal/products/new-benchmark-results-pivotal-query-optimizer-speeds-up-big-data-queries-up-to-1000x
[Orca_ A Modular Query Optimizer Architecture for Big Data]: https://pivotal.io/big-data/white-paper/orca-a-modular-query-optimizer-architecture-for-big-data
[Enabling PQO in GPDB]: http://gpdb.docs.pivotal.io/4390/admin_guide/query/topics/query-piv-opt-enable.html