300 倍的性能提升！PieCloudDB Database 优化器「达奇」又出新“招”啦

PieCloudDB 技术派 2023-08-24

310

当前数字化进程不断加快，全球数据量呈指数级增长，对数据库系统的性能提出了巨大的挑战。优化器作为数据库管理系统中的关键技术，对数据库性能和效率具有重要影响，它通过对用户的查询请求进行解析和优化来生成高效的执行计划，进而快速返回查询结果，同一条 SQL 语句在不同的优化决策下可能会产生数量级的性能差异。

随着数据规模和查询复杂性的增加，优化器的不断发展和创新将成为数据库系统持续提升性能的重要方向，也是数据库系统的最强有力的竞争力之一。

针对云原生和分布式场景，云原生虚拟数仓 PieCloudDB 设计与打造了新一代优化器：「达奇」。这个命名灵感来自于游戏《荒野大镖客》中的一个 NPC 角色，他的口头禅“I have a plan”与优化器的功能非常契合。

拓数派吉祥物「派派」

优化器「达奇」在 PieCloudDB 中实现了许多优化特性，通过生成高质量的查询计划，充当数据库系统的“智囊团”，确保用户的查询性能和效率。「达奇」通过智能地分析查询语句、优化查询计划以及利用分布式数据存储和计算能力，实现了更快速、更高效的查询处理。

01. 聚集操作与聚集下推

今天，我们将详细介绍「达奇」的全新功能：聚集下推。在现代的数据库系统中，聚集操作（Aggregate）是一种非常常见的操作类型。通过聚集操作，可以把多行数据合并成一行，并对其进行统计、求和、求均值等计算。

在传统的查询执行过程中，聚合操作通常在查询结果返回后进行，需要将所有数据传输至查询引擎进行聚合计算。然而，对于大规模数据集和复杂查询，这种方式可能导致查询性能低下、计算负载过高和计算成本昂贵等问题。

为了解决这些问题，PieCloudDB Database 优化器「达奇」打造了聚集下推（Aggregate Pushdown）这一功能，经测试，在很多场景下，聚集下推功能会取得百倍甚至千倍的性能提升。

02. 聚集下推原理

聚集下推（Pushdown Aggregation）是一种数据库查询优化技术，通过将聚合操作下推至数据源，减少数据传输和处理的开销，从而提高查询性能和效率。

聚集下推的实现原理是在查询执行过程中尽可能早地进行聚合操作，并将聚合操作下推至数据源进行处理。具体而言，当查询计划生成时，优化器会将聚合操作下推至数据源，以便在数据源处进行聚合计算。这样可以减少数据传输量，减轻查询引擎的负担，并利用数据源的计算能力进行局部聚合。

「聚集下推」原理演示

下面我们用一个简单的图示，来讲解聚集下推的主要实现原理：

有无「聚集下推」对比

上图中，左图是没有聚集下推的执行流程，右图是有聚集下推时的执行流程。在左图没有聚集下推的情况下，表 T1 和 T2 会先做连接操作，并在连接之后的数据集上完成聚集操作。

而在右图有聚集下推的情况下，会在对表 T1 和 T2 进行连接操作之前，先对表 T1 做一次聚集操作。这样可以在某些场景下使得连接操作时表 T1 中参与的数据量极大的减少，从而显著提高查询性能。为了能准确地提高查询效率，以上两种执行方案在「达奇」中都会生成，最后会根据代价模型的估算选择代价较小的执行方案。

当有更多的表参与连接时，「达奇」会尝试把聚集操作下推到不同的连接层，通过代价的比较来选定最优的下推位置。

03. 聚集下推演示实例

下面，我们通过一个查询实例来看一下聚集下推的效果。首先，我们通过下面语句来创建测试用表：

CREATE TABLE t (x int, y int);
INSERT INTO t SELECT i % 30, i % 30 FROM generate_series(1, 10240) i;
ANALYZE t;

测试所用的查询如下：

SELECT t1.x, sum(t2.y + t3.y), count(*) FROM t t1 
JOIN t t2 ON t1.x = t2.x JOIN t t3 ON t2.x = t3.x 
GROUP BY t1.x;

当没有聚集下推时，查询计划如下：

EXPLAIN (ANALYZE, COSTS OFF) 
SELECT t1.x, sum(t2.y + t3.y), count(*) FROM t t1 JOIN t t2 ON t1.x = t2.x JOIN t t3 ON t2.x = t3.x GROUP BY t1.x; 

                                                        QUERY PLAN 

--------------------------------------------------------------------------------------------------------------------------- 

Gather Motion 3:1  (slice1; segments: 3) (actual time=153884.859..274102.066 rows=30 loops=1) 
   ->  HashAggregate (actual time=274100.004..274100.011 rows=12 loops=1) 
         Group Key: t1.x 
         Peak Memory Usage: 0 kB 
         ->  Hash Join (actual time=38.717..100579.782 rows=477571187 loops=1) 
               Hash Cond: (t1.x = t3.x) 
               Extra Text: (seg0)   Hash chain length 341.4 avg, 342 max, using 12 of 131072 buckets. 
               ->  Hash Join (actual time=2.088..429.203 rows=1398787 loops=1) 
                     Hash Cond: (t1.x = t2.x) 
                     Extra Text: (seg0)   Hash chain length 341.4 avg, 342 max, using 12 of 131072 buckets. 
                     ->  Redistribute Motion 3:3  (slice2; segments: 3) (actual time=0.044..4.590 rows=4097 loops=1) 
                           Hash Key: t1.x 
                           ->  Seq Scan on t t1 (actual time=1.382..32.683 rows=3496 loops=1) 
                     ->  Hash (actual time=1.760..1.761 rows=4097 loops=1) 
                           Buckets: 131072  Batches: 1  Memory Usage: 1185kB 
                           ->  Redistribute Motion 3:3  (slice3; segments: 3) (actual time=0.049..0.922 rows=4097 loops=1) 
                                 Hash Key: t2.x 
                                 ->  Seq Scan on t t2 (actual time=1.628..32.837 rows=3496 loops=1) 
               ->  Hash (actual time=36.153..36.153 rows=4097 loops=1) 
                     Buckets: 131072  Batches: 1  Memory Usage: 1185kB 
                     ->  Redistribute Motion 3:3  (slice4; segments: 3) (actual time=3.918..35.169 rows=4097 loops=1) 
                           Hash Key: t3.x 
                           ->  Seq Scan on t t3 (actual time=1.380..30.316 rows=3496 loops=1) 

Planning Time: 8.810 ms 
   (slice0) Executor memory: 257K bytes. 
   (slice1) Executor memory: 2484K bytes avg x 3 workers, 2570K bytes max (seg0).  Work_mem: 1185K bytes max. 
   (slice2) Executor memory: 32840K bytes avg x 3 workers, 32841K bytes max (seg0). 
   (slice3) Executor memory: 32860K bytes avg x 3 workers, 32860K bytes max (seg0). 
   (slice4) Executor memory: 32860K bytes avg x 3 workers, 32860K bytes max (seg0). 

Memory used:  128000kB 
Optimizer: Postgres query optimizer 
Execution Time: 274130.589 ms
(32 rows)

而当有聚集下推时，查询计划如下：

EXPLAIN (ANALYZE, COSTS OFF) 
SELECT t1.x, sum(t2.y + t3.y), count(*) FROM t t1 JOIN t t2 ON t1.x = t2.x JOIN t t3 ON t2.x = t3.x GROUP BY t1.x; 

                                                                      QUERY PLAN 

---------------------------------------------------------------------------------------------------------------------------------------------------------- 

Gather Motion 3:1  (slice1; segments: 3) (actual time=835.755..836.406 rows=30 loops=1) 
   ->  Finalize GroupAggregate (actual time=834.227..835.432 rows=12 loops=1) 
         Group Key: t1.x 
         ->  Sort (actual time=834.031..834.441 rows=4097 loops=1) 
               Sort Key: t1.x 
               Sort Method:  quicksort  Memory: 1266kB 
               ->  Redistribute Motion 3:3  (slice2; segments: 3) (actual time=812.139..830.706 rows=4097 loops=1) 
                     Hash Key: t1.x 
                     ->  Hash Join (actual time=810.536..828.097 rows=3496 loops=1) 
                           Hash Cond: (t1.x = t2.x) 
                           Extra Text: (seg0)   Hash chain length 1.0 avg, 1 max, using 30 of 131072 buckets. 
                           ->  Seq Scan on t t1 (actual time=1.689..16.674 rows=3496 loops=1) 
                           ->  Hash (actual time=808.497..808.498 rows=30 loops=1) 
                                 Buckets: 131072  Batches: 1  Memory Usage: 1026kB 
                                 ->  Broadcast Motion 3:3  (slice3; segments: 3) (actual time=461.065..808.466 rows=30 loops=1) 
                                       ->  Partial HashAggregate (actual time=810.026..810.033 rows=12 loops=1) 
                                             Group Key: t2.x 
                                             Peak Memory Usage: 0 kB 
                                             ->  Hash Join (actual time=28.070..331.181 rows=1398787 loops=1) 
                                                   Hash Cond: (t2.x = t3.x) 
                                                   Extra Text: (seg0)   Hash chain length 341.4 avg, 342 max, using 12 of 262144 buckets. 
                                                   ->  Redistribute Motion 3:3  (slice4; segments: 3) (actual time=0.040..1.270 rows=4097 loops=1) 
                                                         Hash Key: t2.x 
                                                         ->  Seq Scan on t t2 (actual time=1.449..19.963 rows=3496 loops=1) 
                                                   ->  Hash (actual time=27.834..27.835 rows=4097 loops=1) 
                                                         Buckets: 262144  Batches: 1  Memory Usage: 2209kB 
                                                         ->  Redistribute Motion 3:3  (slice5; segments: 3) (actual time=3.836..27.025 rows=4097 loops=1) 
                                                               Hash Key: t3.x 
                                                               ->  Seq Scan on t t3 (actual time=1.537..23.654 rows=3496 loops=1) 

Planning Time: 14.425 ms 
   (slice0)    Executor memory: 328K bytes. 
   (slice1)    Executor memory: 408K bytes avg x 3 workers, 514K bytes max (seg0).  Work_mem: 450K bytes max. 
   (slice2)    Executor memory: 33951K bytes avg x 3 workers, 33952K bytes max (seg0).  Work_mem: 1026K bytes max. 
   (slice3)    Executor memory: 2298K bytes avg x 3 workers, 2341K bytes max (seg0).  Work_mem: 2209K bytes max. 
   (slice4)    Executor memory: 32860K bytes avg x 3 workers, 32860K bytes max (seg0). 
   (slice5)    Executor memory: 32860K bytes avg x 3 workers, 32860K bytes max (seg0). 

Memory used:  128000kB 
Optimizer: Postgres query optimizer 
Execution Time: 865.305 ms 
(39 rows)

通过两个查询计划的对比，可以看到，当没有聚集下推时（查询计划 1），需要先完成所有的连接操作，再在连接的数据集上进行聚集操作。而聚集下推（查询计划 2）可以把聚集操作下推到最优的连接层，从而减少后续连接操作的数据量，可以显著地提升性能。

在这个例子中，「达奇」把聚集操作下推到了 t2 和 t3 连接之后，与 t1 连接之前。通过对比这两个执行时间，我们可以看到执行时间从之前的 274130.589 ms 降低到了 865.305 ms ，实现三百多倍的性能提升。当数据量进一步增大，或是查询涉及的表进一步增多时，聚集下推的性能提升会更加明显。

无聚集下推 VS 有聚集下推对比

PieCloudDB 的全新优化器「达奇」为用户提供了一系列全面的逻辑优化功能，包括谓词下推、子查询子连接提升和外连接消除等。优化器「达奇」不仅支持聚集下推功能，还具备 Data Skipping、预计算等高级特性，以满足各种复杂查询的需求。在未来的文章中，我们将逐一介绍这些功能，为您带来更深入的了解。敬请关注！

pieclouddb openpie 拓数派性能优化聚集下推

最后修改时间：2023-08-25 17:18:19

文章转载自PieCloudDB 技术派，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

300 倍的性能提升！PieCloudDB Database 优化器「达奇」又出新“招”啦

评论