Case: Adjusting the GUC Parameter best_agg_plan

Symptom

The agg_t1 table is defined as follows:

1

create table agg_t1(a int, b int, c int) distribute by hash(a);

Assume that the distribution key 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.

1. setA is a subset of setB.

   In this scenario, the aggregation result of the lower-layer is correct and can be directly used by upper-level operators. Example:

   1 2 3 4 5 6 7

   gaussdb=# explain select a, count(1) from agg_t1 group by a; id | operation | E-rows | E-width | E-costs ----+------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 20 | 12 | 14.23 2 | -> HashAggregate | 20 | 12 | 13.30 3 | -> Seq Scan on agg_t1 | 20 | 4 | 13.13 (3 rows)

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).

   GaussDB provides the GUC parameter best_agg_plan to intervene the execution plan, and forces the plan to generate the corresponding execution plan. This parameter can be set to 0, 1, 2, or 3.

   • When the parameter is set to 1, the first plan is forcibly generated.
   • When the parameter is set to 2 and if the group by column can be redistributed, the second plan is forcibly generated. Otherwise, the first plan is generated.
   • When the parameter is set to 3 and if the group by column can be redistributed, the third plan is generated. Otherwise, the first plan is generated.
   • When the parameter is set to 0, the query optimizer chooses the most optimal plan by the three preceding plans' evaluation cost.

   The impact is as follows:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

   gaussdb=# set best_agg_plan to 1; SET gaussdb=# explain select b,count(1) from agg_t1 group by b; id | operation | E-rows | E-width | E-costs ----+---------------------------------+--------+---------+--------- 1 | -> HashAggregate | 10 | 12 | 14.23 2 | -> Streaming (type: GATHER) | 20 | 12 | 14.23 3 | -> HashAggregate | 20 | 12 | 13.30 4 | -> Seq Scan on agg_t1 | 20 | 4 | 13.13 (4 rows) gaussdb=# set best_agg_plan to 2; SET gaussdb=# explain select b,count(1) from agg_t1 group by b; id | operation | E-rows | E-width | E-costs ----+-----------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 20 | 12 | 14.52 2 | -> HashAggregate | 20 | 12 | 13.58 3 | -> Streaming(type: REDISTRIBUTE) | 20 | 4 | 13.42 4 | -> Seq Scan on agg_t1 | 20 | 4 | 13.13 (4 rows) gaussdb=# set best_agg_plan to 3; SET gaussdb=# explain select b,count(1) from agg_t1 group by b; id | operation | E-rows | E-width | E-costs ----+-----------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 20 | 12 | 14.97 2 | -> HashAggregate | 20 | 12 | 13.76 3 | -> Streaming(type: REDISTRIBUTE) | 20 | 12 | 13.56 4 | -> HashAggregate | 20 | 12 | 13.30 5 | -> Seq Scan on agg_t1 | 20 | 4 | 13.13 (5 rows)

Optimization

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.