合 GreenPlum中的GPORCA优化器
关于GPORCA
在Greenplum数据库中，默认的GPORCA优化器与传统查询优化器共存。
这些小节描述GPORCA的功能和用法：
- GPORCA概述
GPORCA扩展了Greenplum数据库传统优化器的规划和优化能力。 - 启用和禁用GPORCA
默认情况下，Greenplum数据库使用GPORCA来替代传统查询规划器。服务器配置参数可以启用或者禁用GPORCA。 - 收集根分区统计信息
对于分区表，GPORCA使用表根分区的统计信息来生成查询计划。这些统计信息用于确定联接顺序、拆分和联接聚合节点以及计算查询步骤的成本。相比之下，Postgres规划器使用每个叶分区的统计信息。 - 使用GPORCA时的考虑
用GPORCA最优化执行查询需要考虑的查询条件。 - GPORCA特性和增强
GPORCA是Greenplum的下一代查询优化器，它包括了对特定类型的查询和操作的增强： - GPORCA改变的行为
相比使用传统规划器，启用了GPORCA优化器（默认启用）的Greenplum数据库的行为有些改变。 - GPORCA的限制
在Greenplum数据库中使用默认的GPORCA优化器时有一些限制。GPORCA和传统的查询优化器当前并存于Greenplum数据库中，因为GPORCA不支持所有的Greenplum数据库特性。 - 判断被使用的查询优化器
当GPORCA被启用（默认启用）时，可以判断Greenplum数据库是在使用GPORCA还是退回到传统查询优化器。 - 关于统一多级分区表
GPORCA概述
GPORCA扩展了Greenplum数据库传统优化器的规划和优化能力。 GPORCA是可扩展的，它能在多核环境中获得更好的性能。Greenplum数据库默认使用GPORCA来生成查询计划。
GPORCA也在下列领域增强了Greenplum数据库的查询性能调优：
- 针对分区表的查询
- 包含公共表表达式（CTE）的查询
- 包含子查询的查询
在Greenplum数据库中，GPORCA与传统查询优化器并存。默认情况下，Greenplum数据库使用GPORCA。如果无法使用GPORCA，则会使用传统查询优化器。
下图展示了GPORCA如何融合到查询规划架构中。
Note: GPORCA会忽略所有的传统查询优化器服务器参数。然而，如果Greenplum数据库回退到传统查询优化器，优化器服务器配置参数将影响查询计划的生成。关于传统查询优化器的服务器参数，查看查询调节参数.
启用和禁用GPORCA
默认情况下，Greenplum数据库使用GPORCA来替代传统查询规划器。服务器配置参数可以启用或者禁用GPORCA。
虽然GPORCA默认处于启用状态，但您可以使用optimizer参数在系统，数据库，会话或查询级别配置GPORCA使用情况。 如果要更改默认行为，请参阅以下部分之一：
Note: 可以使用服务器配置参数optimizer_control禁用启用或者禁用GPORCA的能力。有关服务器配置参数的信息请见Greenplum数据库参考指南。
为一个系统启用GPORCA
为Greenplum数据库系统设置服务器配置参数optimizer。
作为gpadmin（Greenplum数据库管理员）登入到Greenplum数据库的Master主机。
设置服务器配置参数的值。下面这些Greenplum数据库gpconfig工具命令把这些参数的值设置为on：
1$ gpconfig -c optimizer -v on --masteronly重启Greenplum数据库。下面这个Greenplum数据库gpstop工具命令重新载入Master和Segment的postgresql.conf文件而不关闭Greenplum数据库。
1gpstop -u
为一个数据库启用GPORCA
用ALTER DATABASE命令为单个Greenplum数据库设置服务器配置参数optimizer。例如，这个命令为数据库test_db启用GPORCA。
1 | > ALTER DATABASE test_db SET OPTIMIZER = ON ; |
为一个会话或者查询启用GPORCA
可以使用SET命令为一个会话设置optimizer服务器配置参数。例如，在使用psql工具连接到Greenplum数据库之后，这个SET命令启用GPORCA：
1 | > set optimizer = on ; |
要为特定查询设置参数，请在运行查询之前包含SET命令。
收集根分区统计信息
对于分区表，GPORCA使用表根分区的统计信息来生成查询计划。这些统计信息用于确定联接顺序、拆分和联接聚合节点以及计算查询步骤的成本。相比之下，Postgres规划器使用每个叶分区的统计信息。
如果在分区表上执行查询，应收集根分区的统计信息，并定期更新这些统计信息，以确保gporca能够生成最佳查询计划。如果根分区统计信息不是最新的或不存在，gporca仍然对表的查询执行动态分区消除。但是，查询计划可能不是最佳的。
运行ANALYZE
默认情况下，在分区表的根分区上运行ANALYZE命令将对表中的叶分区数据进行采样， 并存储根分区的统计信息。ANALYZE 收集根分区和叶分区的统计信息，包括HyperLogLog (HLL)统计信息。ANALYZE ROOTPARTITION 只收集根分区上的统计信息。服务器配置参数optimizer_analyze_root_partition控制是否需要ROOTPARTITION关键字来收集分区表根分区的根统计信息。有关收集分区表统计信息的信息，请参阅ANALYZE命令。 .
记住，在更新根分区统计信息之前，ANALYZE总是扫描整个表。如果您的表非常大，则此操作可能需要大量时间。ANALYZE ROOTPARTITION还使用一个访问ACCESS SHARE锁，用于在执行期间阻止某些操作，如TRUNCATE和VACUUM操作。 出于这些原因，您应该定期或者当叶分区数据发生重大更改时进行ANALYZE操作。
按照以下最佳实践在系统中分区表上运行ANALYZE或ANALYZE ROOTPARTITION ：
- 运行ANALYZE 在一个新的分区表中添加初始数据后。运行ANALYZE 在新的叶分区或数据已更改的叶分区上。默认情况下，如果其他叶分区具有统计信息，则在叶分区上运行命令将更新根分区统计信息。
- 当您在表的EXPLAIN查询计划中发现查询性能衰退，或者在叶分区数据发生重大更改之后，更新根分区统计信息。 例如，如果在生成根分区统计信息后的某个时刻添加新的叶子分区，请考虑运行ANALYZE或ANALYZE ROOTPARTITION以使用从新叶子分区插入的新元组更新根分区统计信息。
- 对于非常大的表，只需每周运行ANALYZE或ANALYZE ROOTPARTITION，或者以长于每天的更新时间间隔运行。
- 避免在没有参数的情况下运行ANALYZE，因为这样做会对所有数据库表（包括分区表）执行命令。对于大型数据库，这些全局ANALYZE操作很难监控，并且很难预测完成所需的时间。
- 如果您的I/O吞吐量能够支持负载，可以考虑并行运行多个ANALYZE 或ANALYZE ROOTPARTITION 操作来加速统计数据收集的操作。
- 还可以使用Greenplum数据库实用程序analyzedb更新表统计信息。使用analyzedb确保如果没有对叶分区进行任何修改，则不会重新分析以前分析过的表。
GPORCA和叶分区统计信息
尽管创建和维护根分区统计信息对于分区表的GPORCA查询性能至关重要，但是维护叶分区统计信息也很重要。 如果GPORCA无法针对分区表生成查询计划，则使用传统查询优化器，需要叶分区统计信息来为该查询生成最佳计划。
GPORCA本身也将叶分区统计信息用于直接访问叶分区的任何查询，而不是使用带有谓词的根分区来消除分区。 例如，如果知道哪些分区包含查询所需的元组，则可以直接查询叶分区表本身；在这种情况下，GPORCA使用叶分区统计信息。
禁用自动根分区统计信息收集
如果不打算使用GPORCA对分区表执行查询（设置服务器配置参数optimizer为off)，然后你可以禁用自动收集分区表的根分区的统计信息。服务器配置参数optimizer_analyze_root_partition控制是否需要 ROOTPARTITION关键字来收集分区表根分区的根统计信息。参数的默认设置为on，ANALYZE命令可以在不使用ROOTPARTITION关键字的情况下收集根分区统计信息。通过将参数设置为off，可以禁用根分区统计信息的自动收集。当该值为off时，必须运行ANALZYE ROOTPARTITION来收集根分区统计信息。
以数据库管理员，比如gpadmin的身份登录Greenplum数据库master主机。
设置服务器配置参数的值。这些Greenplum数据库gpconfig实用程序命令将参数值设置为off：
1$ gpconfig -c optimizer_analyze_root_partition -v off --masteronly重启Greenplum数据库。这个实用程序命令gpstop在不关闭Greenplum数据库的情况下重新加载master和segments的postgresql.conf文件。
使用GPORCA时的考虑
用GPORCA最优化执行查询需要考虑的查询条件。
确保满足下列条件：
表不含有多列分区键。
多级分区表是一个统一多级分区表。请见关于统一多级分区表.
在针对只存在于Master的表（例如系统表pg_attribute）运行时，服务器配置参数optimizer_enable_master_only_queries被设置为on。有关该参数的信息，请见Greenplum数据库参考指南。
Note: 启用这一参数会降低catalog短查询的性能。为了避免这一问题，只对会话或者查询设置这一参数。
已经在分区表的根分区上收集了统计信息。
如果分区表包含超过20,000个分区，考虑重新设计该表的模式。
这些服务器配置参数影响GPORCA查询处理。
optimizer_cte_inlining_bound 控制对公共表表达式（CTE）查询（含有WHERE子句的查询）执行的内联量。
optimizer_force_multistage_agg 强制GPORCA为标量区分限制聚集选择一种3阶段聚集计划。
optimizer_force_three_stage_scalar_dqa 强制GPORCA在生成了带有多阶段聚集的计划时选择它。
optimizer_join_order为连接排序设置查询优化级别，通过指定要评估哪些类型的连接排序选项。
optimizer_join_order_threshold指定GPORCA使用基于动态编程的连接排序算法的最大连接子数。
optimizer_nestloop_factor控制查询优化时应用到嵌套循环连接的代价因子。
optimizer_parallel_union控制对于含有UNION或者UNION ALL子句的查询发生的并行量。当该值为on时，GPORCA可以生成一个查询计划，其中UNION或者UNION ALL操作的子操作在Segment实例上并行执行。
optimizer_sort_factor 控制GPORCA在查询优化时应用于排序操作的代价因子。当出现数据倾斜时可以为查询调整代价因子。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
