MogDB 排序列裁剪

原创 MogDB 2024-08-07

218

可获得性

本特性自MogDB 5.0.6版本开始引入。

特性简介

本特性允许当父查询存在聚合、分组、排序操作时，子查询按需对排序列进行裁剪，减少不必要的性能浪费。

客户价值

消除SQL子查询内无用的ORDER BY语句，提高查询性能、简化查询逻辑，并减少不必要的开销，从而提高数据库系统的整体效率和性能。

特性描述

当父查询不存在有序性需求的时候，SQL子查询内无用的ORDER BY语句可以被裁剪掉以提升查询性能和执行效率，生成更好的执行计划。

例如：

SELECT a, b FROM (SELECT * FROM t1 ORDER BY a, b, c) s1 GROUP BY a, b;

以上SQL语句中，s1子查询对a、b、c进行了排序，但是上层查询中仅对a、b进行了分组操作，c列不会被输出，子查询中对于c列的排序在上层查询中并没有被用到，因此可以被裁剪。

对子查询中的排序键进行裁剪需要满足以下条件：

父查询为有效SQL且可以正常生成执行计划。
父查询内没有改变子查询输出顺序的操作时，子查询不做裁剪。
子查询不能为未inline的CTE或者表达式子查询。
子查询不能有顺序敏感操作（如：for update、limit等）

注意，当父查询的自然顺序全部来自数据源子查询的时候，子查询中的排序不会被剪裁。

本特性的启用通过GUC参数sort_key_pruning_level控制，取值为balanced/aggressive时生效，默认开启。

参数说明

新增GUC参数sort_key_pruning_level，用于控制优化器对子查询中无用排序键的裁剪规则。

该参数属于USERSET类型参数，取值范围为off、balanced、aggressive。取值对应不同的优化程度。默认值为balanced。

off：关闭子查询中无用排序键裁剪功能。
balanced：保守剪裁策略。数据库优化器仅尝试裁剪完全无用的排序键，且不会裁剪集合操作中的子查询。
aggressive：相对激进的剪裁策略。数据库优化器会尝试裁剪掉所有可能用不到的排序键，集合操作中的排序键也会被裁剪。

特性约束

rewrite_rule参数开启reduce_orderby时，reduce_orderby会优先生效。
对于旧版本的beta特性，需要关闭sql_beta_feature中的canonical_pathkey选项，本特性仅支持剪裁正则化后的Pathkey。
对于集合操作，在balance模式下，只有UNION ALL不会被裁剪，其他集合操作需要依赖sort或者hash进行去重，而UNION ALL不需要；在aggressive模式下，所有集合操作都会被裁剪。

示例

-- 创建测试表
MogDB=# CREATE TABLE t1(a int, b int, c int, d int);
CREATE TABLE

-- 查看计划中是否存在排序
MogDB=# EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM (SELECT * FROM t1 ORDER BY 1, 2) AS s1;
      QUERY PLAN
----------------------
 Aggregate
   ->  Seq Scan on t1
(2 rows)

-- 关闭rewrite_rule中的reduce_orderby选项
MogDB=# set rewrite_rule = 'magicset';
SET

-- 关闭sql_beta_feature中的canonical_pathkey选项
MogDB=# set sql_beta_feature = 'none';
SET

-- 保守排序键裁剪
MogDB=# SET sort_key_pruning_level TO balanced;
SET

-- 部分裁剪
MogDB=# EXPLAIN (COSTS OFF) SELECT a FROM (SELECT * FROM t1 ORDER BY a, b) s1 GROUP BY a;
         QUERY PLAN
----------------------------
 Group
   Group By Key: t1.a
   ->  Sort
         Sort Key: t1.a
         ->  Seq Scan on t1
(5 rows)

-- 部分裁剪，增量排序
MogDB=# EXPLAIN (COSTS OFF) SELECT * FROM (SELECT * FROM t1 ORDER BY a, b) ORDER BY a, c;
         QUERY PLAN
----------------------------
 Incremental Sort
   Sort Key: t1.a, t1.c
   Presorted Key: t1.a
   ->  Sort
         Sort Key: t1.a
         ->  Seq Scan on t1
(6 rows)

-- 全部裁剪
MogDB=# EXPLAIN (COSTS OFF) SELECT * FROM (SELECT * FROM t1 ORDER BY a, b) ORDER BY b, c;
       QUERY PLAN
------------------------
 Sort
   Sort Key: t1.b, t1.c
   ->  Seq Scan on t1
(3 rows)

-- 保留归并键
MogDB=# EXPLAIN (COSTS OFF) SELECT * FROM (SELECT * FROM t1 ORDER BY a, b) s1 LEFT JOIN t1 ON s1.a = t1.a;
                QUERY PLAN
------------------------------------------
 Hash Right Join
   Hash Cond: (public.t1.a = public.t1.a)
   ->  Seq Scan on t1
   ->  Hash
         ->  Sort
               Sort Key: public.t1.a
               ->  Seq Scan on t1
(7 rows)

-- 不裁剪append中的排序
MogDB=# EXPLAIN (COSTS OFF) SELECT v.b FROM (SELECT * FROM (SELECT a, b FROM t1 ORDER BY b) UNION ALL (SELECT a, b FROM t1 ORDER BY a)) v GROUP BY 1;
                    QUERY PLAN
---------------------------------------------------
 HashAggregate
   Group By Key: __unnamed_subquery__.b
   ->  Append
         ->  Subquery Scan on __unnamed_subquery__
               ->  Sort
                     Sort Key: public.t1.b
                     ->  Seq Scan on t1
         ->  Subquery Scan on "*SELECT* 2"
               ->  Sort
                     Sort Key: public.t1.a
                     ->  Seq Scan on t1
(11 rows)

-- 激进排序键裁剪
MogDB=# SET sort_key_pruning_level TO aggressive;
SET

-- 全部裁剪
MogDB=# EXPLAIN (COSTS OFF) SELECT a FROM (SELECT * FROM t1 ORDER BY a, b) s1 GROUP BY a;
         QUERY PLAN
----------------------------
 HashAggregate
   Group By Key: s1.a
   ->  Subquery Scan on s1
         ->  Seq Scan on t1
(4 rows)

-- 全部裁剪
MogDB=# EXPLAIN (COSTS OFF) SELECT * FROM (SELECT * FROM t1 ORDER BY a, b) ORDER BY a, c;
       QUERY PLAN
------------------------
 Sort
   Sort Key: t1.a, t1.c
   ->  Seq Scan on t1
(3 rows)

-- 全部裁剪
MogDB=# EXPLAIN (COSTS OFF) SELECT * FROM (SELECT * FROM t1 ORDER BY a, b) ORDER BY b, c;
       QUERY PLAN
------------------------
 Sort
   Sort Key: t1.b, t1.c
   ->  Seq Scan on t1
(3 rows)

-- 不保留归并键
MogDB=# EXPLAIN (COSTS OFF) SELECT * FROM (SELECT * FROM t1 ORDER BY a, b) s1 LEFT JOIN t1 ON s1.a = t1.a;
                QUERY PLAN
------------------------------------------
 Hash Left Join
   Hash Cond: (public.t1.a = public.t1.a)
   ->  Seq Scan on t1
   ->  Hash
         ->  Seq Scan on t1
(5 rows)

-- 裁剪append中的排序
MogDB=# EXPLAIN (COSTS OFF) SELECT v.b FROM (SELECT * FROM (SELECT a, b FROM t1 ORDER BY b) UNION ALL (SELECT a, b FROM t1 ORDER BY a)) v GROUP BY 1;
                    QUERY PLAN
---------------------------------------------------
 HashAggregate
   Group By Key: __unnamed_subquery__.b
   ->  Append
         ->  Subquery Scan on __unnamed_subquery__
               ->  Seq Scan on t1
         ->  Subquery Scan on "*SELECT* 2"
               ->  Seq Scan on t1
(7 rows)