Case: Adjusting the GUC Parameter best_agg_plan

Symptom

The t1 table is defined as follows:

1

create table t1(a int, b int, c int) store as orc;

Assume that the distribution column of the result set provided by the agg lower-layer operator is setA, and the group by column of the agg operation is setB, the agg operations can be performed in two scenarios in the stream framework.

Scenario 1: setA is a subset of setB.

In this scenario, the aggregation result of the lower-layer result set is the correct result, which can be directly used by the upper-layer operator.

Scenario 2: setA is not a subset of setB.

In this scenario, the Stream execution framework is classified into the following three plans:

hashagg+gather(redistribute)+hashagg

redistribute+hashagg(+gather)

hashagg+redistribute+hashagg(+gather)

DataArts Fabric SQL offers the GUC parameter best_agg_plan to intervene in the execution plan, forcing it to generate the corresponding execution plan mentioned above. The options are 0, 1, 2, and 3.

• The value 1 forces the generation of the first plan.
• The value 2 forces the generation of the second plan if the group by columns can be redistributed. Otherwise, it generates the first plan.
• The value 3 forces the generation of the third plan if the group by columns can be redistributed. Otherwise, it generates the first plan.
• The value 0 allows the optimizer to select the best plan based on the estimated costs of the above three plans.

Possible impacts are as follows:

postgres=> set best_agg_plan=1;
SET
postgres=> explain select a, count(1) from t1 group by a;
QUERY PLAN
------------------------------------------------------------------------------------------
id |                 operation                 | E-rows | E-memory | E-width | E-costs
----+-------------------------------------------+--------+----------+---------+---------
1 | ->  Row Adapter                           |    200 |          |      12 | 85.16
2 |    ->  Vector Sonic Hash Aggregate        |    200 |          |      12 | 85.16
3 |       ->  Vector Streaming (type: GATHER) |    522 |          |      12 | 85.16
4 |          ->  Vector Sonic Hash Aggregate  |    522 | 16MB     |      12 | 63.41
5 |             ->  Vector Foreign Scan on t1 |   1000 | 1MB      |       4 | 60.00

Predicate Information (identified by plan id)
---------------------------------------------
5 --Vector Foreign Scan on t1
Server Type: lf
Total files left: 0

====== Query Summary =====
------------------------------
System available mem: 262144KB
Query Max mem: 262144KB
Query estimated mem: 1872KB
(19 rows)

postgres=> set best_agg_plan=2;
SET
postgres=> explain select a, count(1) from t1 group by a;
QUERY PLAN
--------------------------------------------------------------------------------------------------
id |                     operation                     | E-rows | E-memory | E-width | E-costs
----+---------------------------------------------------+--------+----------+---------+---------
1 | ->  Row Adapter                                   |    201 |          |      12 | 78.84
2 |    ->  Vector Streaming (type: GATHER)            |    201 |          |      12 | 78.84
3 |       ->  Vector Sonic Hash Aggregate             |    201 | 16MB     |      12 | 70.47
4 |          ->  Vector Streaming(type: REDISTRIBUTE) |   1000 | 2MB      |       4 | 67.39
5 |             ->  Vector Foreign Scan on t1         |   1000 | 1MB      |       4 | 60.00

Predicate Information (identified by plan id)
---------------------------------------------
5 --Vector Foreign Scan on t1
Server Type: lf
Total files left: 0

====== Query Summary =====
------------------------------
System available mem: 262144KB
Query Max mem: 262144KB
Query estimated mem: 3965KB
(19 rows)

postgres=> set best_agg_plan=3;
SET
postgres=> explain select a, count(1) from t1 group by a;
QUERY PLAN
--------------------------------------------------------------------------------------------------
id |                     operation                     | E-rows | E-memory | E-width | E-costs
----+---------------------------------------------------+--------+----------+---------+---------
1 | ->  Row Adapter                                   |    200 |          |      12 | 82.25
2 |    ->  Vector Streaming (type: GATHER)            |    200 |          |      12 | 82.25
3 |       ->  Vector Sonic Hash Aggregate             |    200 | 16MB     |      12 | 70.25
4 |          ->  Vector Streaming(type: REDISTRIBUTE) |    522 | 2MB      |      12 | 68.03
5 |             ->  Vector Sonic Hash Aggregate       |    522 | 16MB     |      12 | 63.41
6 |                ->  Vector Foreign Scan on t1      |   1000 | 1MB      |       4 | 60.00

Predicate Information (identified by plan id)
---------------------------------------------
6 --Vector Foreign Scan on t1
Server Type: lf
Total files left: 0

====== Query Summary =====
------------------------------
System available mem: 262144KB
Query Max mem: 262144KB
Query estimated mem: 3968KB
(20 rows)

Summary

Generally, the optimizer chooses an optimal execution plan, but the cost estimation, especially that of the intermediate result set, has large deviations, which may result in large deviations in agg calculation. In this case, you need to use best_agg_plan to adjust the agg calculation model.

When the aggregation convergence ratio is very small, that is, the number of result sets does not become small obviously after the agg operation (5 times is a critical point), you can select the redistribute+hashagg or hashagg+redistribute+hashagg execution mode.