sundry

Oceanbase distributed polymerization and depressurization

Aggregation operators in the database, such as sum, AVG, group by, sort, and so on, will consume a lot of CPU and IO resources for calculation, reading and writing. Through parallel execution, the corresponding operators are pressed down to each computing node, making full use of the computing resources of the cluster and improving the execution efficiency.

On the whole, there are two kinds of plans to depress the aggregation operator

Partition-Wise Aggregation

  1. explain select sum(c2) from t2 group by c1\G  
  2. *************************** 1.  row ***************************  
  3. Query Plan:   
  4. ============================================  
  5. |ID|OPERATOR           |NAME|EST.  ROWS|COST|  
  6. --------------------------------------------  
  7. |0 |EXCHANGE IN DISTR  |    |1000     |2834|  
  8. |1 | EXCHANGE OUT DISTR|    |1000     |2740|  
  9. |2 |  HASH GROUP BY    |    |1000     |2740|  
  10. |3 |   TABLE SCAN      |t2  |4000     |499 |  
  11. ============================================  
  12.   
  13. Outputs & filters:   
  14. -------------------------------------  
  15.   0 - output([T_FUN_SUM(t2.c2)]), filter(nil)  
  16.   1 - output([T_FUN_SUM(t2.c2)]), filter(nil)  
  17.   2 - output([T_FUN_SUM(t2.c2)]), filter(nil),   
  18.       group([t2.c1]), agg_ func([T_FUN_SUM(t2.c2)])  
  19.   3 - output([t2.c1], [t2.c2]), filter(nil),   
  20.       access([t2.c1], [t2.c2]), partitions(p[0-3])  

If the semantics of an aggregate query is to group according to the partition key and aggregate within the group, such an aggregation operation is called partition wise aggregation. All operations are pushed down into the partition for parallel execution. It should be noted that partition wise aggregation is not necessarily the optimal execution because the degree of parallelism is limited by the number of partitions. In oceanbase 1 In version x, there is no other alternative plan for this situation.

Down pressure plus two-stage polymerization

  1. explain select sum(c1) from t2 group by c2\G  
  2. *************************** 1.  row ***************************  
  3. Query Plan:   
  4. =============================================  
  5. |ID|OPERATOR            |NAME|EST.  ROWS|COST|  
  6. ---------------------------------------------  
  7. |0 |HASH GROUP BY       |    |1000     |3395|  
  8. |1 | EXCHANGE IN DISTR  |    |1000     |2834|  
  9. |2 |  EXCHANGE OUT DISTR|    |1000     |2740|  
  10. |3 |   HASH GROUP BY    |    |1000     |2740|  
  11. |4 |    TABLE SCAN      |t2  |4000     |499 |  
  12. =============================================  
  13.   
  14. Outputs & filters:   
  15. -------------------------------------  
  16.   0 - output([T_FUN_SUM(T_FUN_SUM(t2.c1))]), filter(nil),   
  17.       group([t2.c2]), agg_ func([T_FUN_SUM(T_FUN_SUM(t2.c1))])  
  18.   1 - output([T_FUN_SUM(t2.c1)], [t2.c2]), filter(nil)  
  19.   2 - output([T_FUN_SUM(t2.c1)], [t2.c2]), filter(nil)  
  20.   3 - output([T_FUN_SUM(t2.c1)], [t2.c2]), filter(nil),   
  21.       group([t2.c2]), agg_ func([T_FUN_SUM(t2.c1)])  
  22.   4 - output([t2.c1], [t2.c2]), filter(nil),   
  23.       access([t2.c1], [t2.c2]), partitions(p[0-3])  

In more general cases, the aggregation operation may not be done according to the partition key as a group. In such a case, oceanbase will adopt the two-stage aggregation method as shown in example 2. The aggregation operation is pressed down to obtain part of the aggregate results, and then the final result is obtained by summarizing. It should be noted that at present, the second phase of two-phase aggregation is not executed in parallel.

fabulous ( one hundred and seventy-three )

This paper is written by Ji Changxin Creation, article address: https://blog.isoyu.com/archives/oceanbasefenbushijuhexiaya.html
use Knowledge sharing signature 4.0 International license agreement. Except for the reprint / source, they are all original or translated by our website. Please sign before reprinting. Last editing time: September 16, 2018 at 04:34 PM

key word:

Hot articles

Comments:

4 comments, visitors: 0, bloggers: 0
  1.  Headlines
    Headlines Published on:

    The article is good. I like it very much

  2.  Dashaibi
    Dashaibi Published on:

    Good morning

  3.  Xifengli blog
    Xifengli blog Published on:

    I read it as a Book of heaven. What is the concept of downward pressure?

Comment

[required]

Please do not submit and wait three seconds later