AntDB的Cluster Plan与PGXC的Remote Query Plan区别

最新推荐文章于 2024-09-13 08:07:38 发布

constzl

最新推荐文章于 2024-09-13 08:07:38 发布

阅读量493

点赞数

分类专栏： AntDB 文章标签： AntDB PostgreSQL PGXC Cluster Plan

本文链接：https://blog.csdn.net/constzl/article/details/79203063

版权

本文对比分析了AntDB 3.1版本引入的Cluster Plan与PGXC的Remote Query Plan的区别。Cluster Plan通过Reduce Plan支持实时动态数据分布，优化了无法在Datanode执行的计划，减轻Coordinator节点压力并提高SQL执行效率。文章列举了不同场景下的SQL示例，展示了两种执行计划在select从句、agg、with子句、子查询及in、not in条件下的执行差异。

摘要由CSDN通过智能技术生成

比较AntDB Cluster Plan与PGXC Remote Query Plan的区别

AntDB 3.1版本引入Cluster Plan，区别于原PGXC的执行计划，通过Reduce Plan支持数据的实时动态分布，将原本PGXC无法下沉到Datanode执行的执行计划做了优化，使得执行计划的执行压力分散到各个Datanode节点，一方面减轻Coordinator节点的性能压力，另一方面提高了SQL的执行效率。

鉴于表的实际情况，Cluster Plan不一定是最优的执行计划，本文仅列举部分用例以示Cluster Plan与Remote Query Plan的区别

select从句、agg

SQL

SELECT unique2
    , (
        SELECT COUNT(1)
        FROM onek
        WHERE odd > 10
    ) AS cnt
FROM tenk1;

Cluster Plan

                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Cluster Gather  (cost=974.46..4089.21 rows=10000 width=12)
   Plan id: 0
   ->  Seq Scan on tenk1  (cost=0.00..114.75 rows=2500 width=12)
         Plan id: 1
         InitPlan 1 (returns $0)
           ->  Cluster Reduce  (cost=10.25..11.76 rows=1 width=8)
                 Plan id: 2
                 ->  Finalize Aggregate  (cost=18.51..18.52 rows=1 width=8)
                       Plan id: 3
                       ->  Cluster Reduce  (cost=17.89..18.50 rows=4 width=8)
                             Plan id: 4
                             ->  Partial Aggregate  (cost=21.11..21.12 rows=1 width=8)
                                   Plan id: 5
                                   ->  Seq Scan on onek  (cost=0.00..20.88 rows=95 width=0)
                                         Plan id: 6
                                         Filter: (odd > 10)
(16 rows)

Remote Query Plan

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Result  (cost=974.46..10433.46 rows=10000 width=12)
   Plan id: 0
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=974.45..974.46 rows=1 width=8)
           Plan id: 2
           ->  Data Node Scan on onek "_REMOTE_TABLE_QUERY__1"  (cost=0.00..973.50 rows=379 width=0)
                 Plan id: 3
                 Node/s: dn1, dn2, dn3, dn4
   ->  Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..9359.00 rows=10000 width=4)
         Plan id: 1
         Node/s: dn1, dn2, dn3, dn4
(11 rows)

select从句、agg、not in

SQL

SELECT unique2
    , (
        SELECT COUNT(1)
        FROM onek
        WHERE odd > 10
    ) AS cnt
FROM tenk1
WHERE even NOT IN (
    SELECT COUNT(1)
    FROM tenk2
    WHERE even < 100
);

Cluster Plan

                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Cluster Gather  (cost=975.64..7458.39 rows=20000 width=12)
   Plan id: 0
   ->  Seq Scan on tenk1  (cost=1.18..483.93 rows=5000 width=12)
         Plan id: 1
         Filter: (NOT (hashed SubPlan 2))
         InitPlan 1 (returns $0)
           ->  Cluster Reduce  (cost=10.25..11.76 rows=1 width=8)
                 Plan id: 2
                 ->  Finalize Aggregate  (cost=18.51..18.52 rows=1 width=8)
                       Plan id: 3
                       ->  Cluster Reduce  (cost=17.89..18.50 rows=4 width=8)
                             Plan id: 4
                             ->  Partial Aggregate  (cost=21.11..21.12 rows=1 width=8)
                                   Plan id: 5
                                   ->  Seq Scan on onek  (cost=0.00..20.88 rows=95 width=0)
                                         Plan id: 6
                                         Filter: (odd > 10)
         SubPlan 2
           ->  Cluster Reduce  (cost=3.21..4.71 rows=1 width=