You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2021/06/20 11:03:09 UTC
[GitHub] [incubator-doris] Userwhite opened a new issue #6066: [Feature] 针对聚合操作的查询计划优化
Userwhite opened a new issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066
相关链接：https://github.com/apache/incubator-doris/issues/4481
## 现有优化
对于Aggregate操作查询计划：默认是单节点做完Aggregate，然后shuffle进行merge finalize。
社区对单表的聚合进行了优化：Group by字段包含分桶列(都要满足)，分桶列包含分区列(只有多分区需要满足)，如果满足上述条件，说明单节点做完聚合已经是最终的结果集，不需要merge finalize。
## 期望优化
1、单表scan上的聚合：
满足条件：
* 无分区：Group by字段包含分桶列
* 有分区：Group by字段包含分桶列；**Group by字段包含分区列**(扩宽了条件，显然满足这个的时候，同一组不会在不同分区)
* **有分区但数据只命中一个分区的时候和无分区的条件一致**。
2、多表join上的聚合：
多表join下聚合优化的判断可以简化为对各个olap_scan_node的判断。
* colocate join
唯一能在一个fragment出现多个olap_scan_node的情况。
只要多张表中有一张满足单表的优化条件(需要把Group by字段分配到各个表上)，就能够去除merge finalize。
同时，由于colocate join的表存在bucket sequence，所以不需要考虑多分区(多分区可以视作单分区)。
* 其他join
至多出现一个olap_scan_node。
不考虑其他shuffle过来的，不在此fragment的表的Group by。
只需要考虑该olap_scan_node是否满足即可。
* 嵌套join
无论如何嵌套：不会出现colocate join的表和其他join的表都在一个fragment的情况。
所以要么是考虑colocate join的多表，要么是考虑其他join的单表，和上两种情况一致。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite removed a comment on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite removed a comment on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866780425
@EmmyMiao87
关于2情况：
比如这样的一张表：
plan(k1,k2,k3,day,v1) key为(k1,k2,k3)，分区列为(day)，分桶列是(k1,k2)
此时Group by k1,k2,day，是可以优化成colocate agg的，尽管不同分组数据分布不一致，但是同组数据只会出现在一个分区内，所以是可以直接进行colocate agg的。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite removed a comment on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite removed a comment on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866781933
> > 1. 。
>
> 关于2情况：
> > 1. 无分区：Group by字段包含分桶列
> > 2. 有分区但数据只命中一个分区的时候和无分区的条件一致
>
> 是这样的，之前1，3 这两种情况确实没覆盖到。后面我加了一个 PR #6029。这个PR 已经覆盖了 1，3 两种情况。你可以用 Master 代码验证一下。
>
> > 1. 有分区：Group by字段包含分桶列；Group by字段包含分区列(扩宽了条件，显然满足这个的时候，同一组不会在不同分区)
>
> 1. 的问题在于，其实这种并不能够进行 colocate agg。原因是：
> 当查询命中多分区时，尽管查询的分组列可能是分桶列的子集。但是不同分区的不同分桶可能是在不同be的。这就意味着，下层的agg 节点并没有完全合并数据。
@EmmyMiao87
关于2情况：
比如这样的一张表：
plan(k1,k2,k3,day,v1) key为(k1,k2,k3)，分区列为(day)，分桶列是(k1,k2)
此时Group by k1,k2,day，是可以优化成colocate agg的，尽管不同分组数据分布不一致，但是同组数据只会出现在一个分区内，所以是可以直接进行colocate agg的。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866825978
> > 尽管不同分组数据分布不一致，但是同组数据只会出现在一个分区内，所以是可以直接进行colocate agg的。
>
> 有道理。所以说 olap scan node 的数据分布应该加上分区列，这样考虑就对了。
好的谢谢，我看看能不能把这个加进来。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-867446261
> @EmmyMiao87
> 以及一个问题：我发现colocateAgg没有成功执行：
> 表结构如下：
> CREATE TABLE `t1` (
> `k1` int(11) NULL COMMENT "",
> `k2` int(11) NULL COMMENT "",
> `k3` int(11) NULL COMMENT "",
> `v1` int(11) NULL COMMENT "",
> `v2` int(11) NULL COMMENT ""
> ) ENGINE=OLAP
> UNIQUE KEY(`k1`, `k2`, `k3`)
> COMMENT "OLAP"
> DISTRIBUTED BY HASH(`k1`, `k2`) BUCKETS 10
> PROPERTIES (
> "replication_num" = "1",
> "colocate_with" = "group1",
> "in_memory" = "false",
> "storage_format" = "V2"
> );
> 表t2和表t1一致；
> 对于以下的sql：
> explain select count(1) from t1 group by t1.k1,t1.k2;
> explain select count(1) from t1 join t2 on t1.k1 = t2.k1 and t1.k2 = t2.k2 group by t1.k1,t1.k2;
>
> 都没有消除merge finalize这一步，可能的原因是：columnEqual的时候，由于Groupby字段的tablename没有带db，而dataPartition是带了的，所以两者不相等。
此处针对最新master代码
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 edited a comment on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 edited a comment on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866755995
> 1. 无分区：Group by字段包含分桶列
> 3. 有分区但数据只命中一个分区的时候和无分区的条件一致
是这样的，之前1，3 这两种情况确实没覆盖到。后面我加了一个 PR #6029。这个PR 已经覆盖了 1，3 两种情况。你可以用 Master 代码验证一下。
> 2. 有分区：Group by字段包含分桶列；Group by字段包含分区列(扩宽了条件，显然满足这个的时候，同一组不会在不同分区)
2. 的问题在于，其实这种并不能够进行 colocate agg。原因是：
当查询命中多分区时，尽管查询的分组列可能包含分桶列。但是不同分区的不同分桶可能是在不同be的。这就意味着，下层的agg 节点并没有完全合并数据。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-867445649
> @EmmyMiao87
> 以及一个问题：我发现colocateAgg没有成功执行：
> 表结构如下：
> CREATE TABLE `t1` (
> `k1` int(11) NULL COMMENT "",
> `k2` int(11) NULL COMMENT "",
> `k3` int(11) NULL COMMENT "",
> `v1` int(11) NULL COMMENT "",
> `v2` int(11) NULL COMMENT ""
> ) ENGINE=OLAP
> UNIQUE KEY(`k1`, `k2`, `k3`)
> COMMENT "OLAP"
> DISTRIBUTED BY HASH(`k1`, `k2`) BUCKETS 10
> PROPERTIES (
> "replication_num" = "1",
> "colocate_with" = "group1",
> "in_memory" = "false",
> "storage_format" = "V2"
> );
> 表t2和表t1一致；
> 对于以下的sql：
> explain select count(1) from t1 group by t1.k1,t1.k2;
> explain select count(1) from t1 join t2 on t1.k1 = t2.k1 and t1.k2 = t2.k2 group by t1.k1,t1.k2;
>
> 都没有消除merge finalize这一步，可能的原因是：columnEqual的时候，由于Groupby字段的tablename没有带db，而dataPartition是带了的，所以两者不相等。
此处是针对master的最新代码
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866783531
@EmmyMiao87
关于2情况：
比如这样的一张表：
plan(k1,k2,k3,day,v1) key为(k1,k2,k3)，分区列为(day)，分桶列是(k1,k2)
此时Group by k1,k2,day，是可以优化成colocate agg的，尽管不同分组数据分布不一致，但是同组数据只会出现在一个分区内，所以是可以直接进行colocate agg的。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866795016
> 尽管不同分组数据分布不一致，但是同组数据只会出现在一个分区内，所以是可以直接进行colocate agg的。
有道理。所以说 olap scan node 的数据分布应该加上分区列，这样考虑就对了。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-947652704
@EmmyMiao87
目前聚合作为两阶段执行，update serialize + merge finalize
在实际的一些业务测试发现对于小查询，去掉聚合优化有小幅度的提升，对于某些大数据情况，效果会变差。
原因：
1、数据聚合程度较差，优化前会在一阶段聚合的时候发现聚合程度较差，然后将数据shuffle到多台BE上进行；
2、数据倾斜
例子：
三台机器：a,b,c; 查询sql的时候, a需要获取10条数据,b需要获取210w条，c需要获取10条
在优化前，a/b/c会shuffle得到70w条数据去处理；
在优化后，a/b/c会单独处理自己的数据，即使减少了一层fragment，但是b花费的时间过多。
我理解这样会导致优化的效果对于大查询可能起到副作用，或者说优化可能和数据分布相关性较大。
可能的解决办法？
1、也许需要进行一个数据量的预估, 需要一些数据统计？
2、该功能可以改成session variable，方便用户针对某些查询单独进行处理
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-867445844
> > @EmmyMiao87
> > 以及一个问题：我发现colocateAgg没有成功执行：
> > 表结构如下：
> > CREATE TABLE `t1` (
> > `k1` int(11) NULL COMMENT "",
> > `k2` int(11) NULL COMMENT "",
> > `k3` int(11) NULL COMMENT "",
> > `v1` int(11) NULL COMMENT "",
> > `v2` int(11) NULL COMMENT ""
> > ) ENGINE=OLAP
> > UNIQUE KEY(`k1`, `k2`, `k3`)
> > COMMENT "OLAP"
> > DISTRIBUTED BY HASH(`k1`, `k2`) BUCKETS 10
> > PROPERTIES (
> > "replication_num" = "1",
> > "colocate_with" = "group1",
> > "in_memory" = "false",
> > "storage_format" = "V2"
> > );
> > 表t2和表t1一致；
> > 对于以下的sql：
> > explain select count(1) from t1 group by t1.k1,t1.k2;
> > explain select count(1) from t1 join t2 on t1.k1 = t2.k1 and t1.k2 = t2.k2 group by t1.k1,t1.k2;
> > 都没有消除merge finalize这一步，可能的原因是：columnEqual的时候，由于Groupby字段的tablename没有带db，而dataPartition是带了的，所以两者不相等。
此处是针对master的最新代码
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite removed a comment on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite removed a comment on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-867445844
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 edited a comment on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 edited a comment on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866761688
> 多表join下聚合优化的判断可以简化为对各个olap_scan_node的判断。
其实 colocate 的本质在于，当前 fragment 的分布方式和 group by 的分组方式相同或小于。
所以只要能精确的判断 fragment 的分布方式，就能正确的判断是否能进行colocate。
确实可以简化为直接判断 olap scan node。但这种判断会缩小 colocate 的范围。
因为当 fragment 中并不包含 olap scan node 的时候其实也有可能可以 colocate agg。
如果简单的看olap scan node 的数据分布，一些可以被 colocate 的聚合就会被误判成不能。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-867466967
@EmmyMiao87
我选择在GroupByClause的anlayze函数中加入选中的这一句，能够成功work，这样ok吗？(ok我就提个pr)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-867443476
@EmmyMiao87
以及一个问题：我发现colocateAgg没有成功执行：
表结构如下：
CREATE TABLE `t1` (
`k1` int(11) NULL COMMENT "",
`k2` int(11) NULL COMMENT "",
`k3` int(11) NULL COMMENT "",
`v1` int(11) NULL COMMENT "",
`v2` int(11) NULL COMMENT ""
) ENGINE=OLAP
UNIQUE KEY(`k1`, `k2`, `k3`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`k1`, `k2`) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"colocate_with" = "group1",
"in_memory" = "false",
"storage_format" = "V2"
);
表t2和表t1一致；
对于以下的sql：
explain select count(1) from t1 group by t1.k1,t1.k2;
explain select count(1) from t1 join t2 on t1.k1 = t2.k1 and t1.k2 = t2.k2 group by t1.k1,t1.k2;
都没有消除merge finalize这一步，可能的原因是：columnEqual的时候，由于Groupby字段的tablename没有带db，而dataPartition是带了的，所以两者不相等。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-868361438
@EmmyMiao87
主要感觉问题集中于存在Group by字段没有带dbName；
以及a join b group by b的字段的时候，由于Fragment只存了a表的分布，所以datapartition的表是a而不是b，这个时候无法做优化。
explain select count(1) from (select k1,k2 from test.t1 group by t1.k1,t1.k2) a,t2 b where a.k1 = b.k1 and a.k2 = b.k2 group by b.k1,b.k2;
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-867274452
> 好的谢谢，我看看能不能把这个加进来。
只要改 OlapScanNode 的 constructInputPartitionByDistributionInfo 就可以了~
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite closed issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite closed issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite edited a comment on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite edited a comment on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866780425
@EmmyMiao87
关于2情况：
比如这样的一张表：
plan(k1,k2,k3,day,v1) key为(k1,k2,k3)，分区列为(day)，分桶列是(k1,k2)
此时Group by k1,k2,day，是可以优化成colocate agg的，尽管不同分组数据分布不一致，但是同组数据只会出现在一个分区内，所以是可以直接进行colocate agg的。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866780425
> 1. 。
关于2情况：
比如这样的一张表：
plan(k1,k2,k3,day,v1) key为(k1,k2,k3)，分区列为(day)，分桶列是(k1,k2)
此时Group by k1,k2,day，是可以优化成colocate agg的，尽管不同分组数据分布不一致，但是同组数据只会出现在一个分区内，所以是可以直接进行colocate agg的。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866755995
> 1. 无分区：Group by字段包含分桶列
> 3. 有分区但数据只命中一个分区的时候和无分区的条件一致
是这样的，之前1，3 这两种情况确实没覆盖到。后面我加了一个 PR #6029。这个PR 已经覆盖了 1，3 两种情况。你可以用 Master 代码验证一下。
> 2. 有分区：Group by字段包含分桶列；Group by字段包含分区列(扩宽了条件，显然满足这个的时候，同一组不会在不同分区)
2. 的问题在于，其实这种并不能够进行 colocate agg。原因是：
当查询命中多分区时，尽管查询的分组列可能是分桶列的子集。但是不同分区的不同分桶可能是在不同be的。这就意味着，下层的agg 节点并没有完全合并数据。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866781933
> > 1. 。
>
> 关于2情况：
> > 1. 无分区：Group by字段包含分桶列
> > 2. 有分区但数据只命中一个分区的时候和无分区的条件一致
>
> 是这样的，之前1，3 这两种情况确实没覆盖到。后面我加了一个 PR #6029。这个PR 已经覆盖了 1，3 两种情况。你可以用 Master 代码验证一下。
>
> > 1. 有分区：Group by字段包含分桶列；Group by字段包含分区列(扩宽了条件，显然满足这个的时候，同一组不会在不同分区)
>
> 1. 的问题在于，其实这种并不能够进行 colocate agg。原因是：
> 当查询命中多分区时，尽管查询的分组列可能是分桶列的子集。但是不同分区的不同分桶可能是在不同be的。这就意味着，下层的agg 节点并没有完全合并数据。
@EmmyMiao87
关于2情况：
比如这样的一张表：
plan(k1,k2,k3,day,v1) key为(k1,k2,k3)，分区列为(day)，分桶列是(k1,k2)
此时Group by k1,k2,day，是可以优化成colocate agg的，尽管不同分组数据分布不一致，但是同组数据只会出现在一个分区内，所以是可以直接进行colocate agg的。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] Userwhite removed a comment on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
Userwhite removed a comment on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-867466967
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on issue #6066: [Feature] 针对聚合操作的查询计划优化
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on issue #6066:
URL: https://github.com/apache/incubator-doris/issues/6066#issuecomment-866761688
> 多表join下聚合优化的判断可以简化为对各个olap_scan_node的判断。
其实 colocate 的本质在于，当前 fragment 的分布方式和 group by 的分组方式相同或大于。
所以只要能精确的判断 fragment 的分布方式，就能正确的判断是否能进行colocate。
确实可以简化为直接判断 olap scan node。但这种判断会缩小 colocate 的范围。
因为当 fragment 中并不包含 olap scan node 的时候其实也有可能可以 colocate agg。
如果简单的看olap scan node 的数据分布，一些可以被 colocate 的聚合就会被误判成不能。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org