对随机分布表进行多列group by操作时,集群默认以group by的第一列对表进行hash重分布。当表数据量大,而group by的第一列唯一值较少时,会造成数据重分布到少数的几个节点,达不到多节点并行处理的效果,影响性能。
原始sql语句,t1是随机分布表:
select c1,c2,count(distinct c3) from t1 group by c1,c2;
默认计划:
gbase> explain select c1,c2,count(distinct c3) from t1 group by c1,c2;
+----+--------------+-----------+---------+---------------------+
| ID | MOTION | OPERATION | TABLE | CONDITION |
+----+--------------+-----------+---------+---------------------+
| 01 | [RESULT] | Step | <00> | |
| | | GROUP | | GROUP BY c1, c2 |
| 00 | [REDIST(c1)] | Table | t1[DIS] | |
| | | GROUP | | GROUP BY c1, c2, c3 |
+----+--------------+-----------+---------+-------------------