分析型数据库 PostgreSQL提供两种优化器,默认优化器为 ORCA 优化器。两个优化器在不同的场景下,各有优势。
Legacy 优化器:SQL 优化耗时较短,适合高并发的简单查询场景(3 表以内关联),或者高并发的数据写入或更新场景(INSERT/UPDATE/DELTE)。
ORCA 优化器:面向复杂 SQL 语句的优化器,会遍历更多执行路径,制定最优执行计划,但 SQL 优化过程相对耗时稍长。建议对复杂查询(3 表以上关联为主的场景)为主的 ETL 场景和报表场景采用此外,ORCA 优化器具有相关子查询的解关联优化及动态分区裁剪优化等能力,含有相关子查询的语句及含有带参数化过滤条件的分区表的语句建议使用 ORCA 优化器。
查看当前的优化器的方式
show optimizer;
如执行结果值为 on:表示当前优化器为 ORCA 优化器。
如执行结果值为 off:表示当前优化器为 Legacy 优化器。
Session 会话级设置方式
使用 Legacy 优化器,执行以下命令:
set optimizer = off;
使用 ORCA 优化器,执行以下命令:
set optimizer = on;
用 ORCA 最优化执行查询需要考虑的查询条件
确保满足下列条件:
- 表不含有多列分区键。
- 多级分区表是一个统一多级分区表。
- 在针对只存在于 Master 的表(例如系统表 pg_attribute)运行时,服务器配置参数 optimizer_enable_master_only_queries 被设置为 on。
注意
启用这一参数会降低 catalog 短查询的性能。为了避免这一问题,只对会话或者查询设置这一参数。
- 已经在分区表的根分区上收集了统计信息。
- 如果分区表包含超过 20,000 个分区,考虑重新设计该表的模式。
这些服务器配置参数会影响 ORCA 查询处理,建议在会话中设置:
-
optimizer_cte_inlining_bound 控制对公共表表达式(CTE)查询(含有 WHERE 子句的查询)执行的内联量。
-
optimizer_force_multistage_agg 强制 ORCA 为标量区分限制聚集选择一种 3 阶段聚集计划。
-
optimizer_force_three_stage_scalar_dqa 强制 ORCA 在生成了带有多阶段聚集的计划时选择它。
-
optimizer_join_order 为连接排序设置查询优化级别,通过指定要评估哪些类型的连接排序选项。
-
optimizer_join_order_threshold 指定 ORCA 使用基于动态编程的连接排序算法的最大连接子数。
-
optimizer_nestloop_factor 控制查询优化时应用到嵌套循环连接的代价因子。
-
optimizer_parallel_union 控制对于含有 UNION 或者 UNION ALL 子句的查询发生的并行量。当该值为 on 时,ORCA 可以生成一个查询计划,其中 UNION 或者 UNION ALL 操作的子操作在 Segment 实例上并行执行。
-
optimizer_sort_factor 控制 ORCA 在查询优化时应用于排序操作的代价因子。当出现数据倾斜时可以为查询调整代价因子。
-
gp_enable_relsize_collection 控制 ORCA(和传统查询优化器)处理一个没有统计信息的表的方式。默认情况下,如果统计信息不可用,ORCA 使用默认值估计行数。当该值为 on,ORCA 使用表的估计大小(如果没有统计数据)。
对于分区表的根分区,此参数将被忽略。如果根分区没有统计信息,ORCA 总是使用默认值。你可以使用 ANALZYE ROOTPARTITION 收集根分区的统计信息。
这些服务器配置参数控制信息的显示和记录,建议在会话中设置:
- optimizer_print_missing_stats 控制有关对查询缺失统计信息的列的信息显示(默认是 true)。
- optimizer_print_optimization_stats 控制 ORCA 查询优化度量对于查询的记录(默认为 off)。