【详述】问题详细描述
starrocks 3.1版本，分区列加入到分桶列后，分桶key不能命中。
【背景】做过哪些操作
1.starrocks 2.5 测试，不存在这个问题， 分桶key可以命中。
2.将分区列从分桶列去掉后，分桶key可以命中。
【业务影响】
【是否存算分离】否
【StarRocks版本】3.1.15
【集群规模】例如：3fe + 3be（fe与be混部）
【机器信息】
【联系方式】
【附件】
建表语句：
CREATE TABLE doris_finish_episode_team_data_v3 (
dt date NOT NULL COMMENT “日期”,
teamId int(11) NOT NULL DEFAULT “0” COMMENT “teamId”,
episodeId int(11) NOT NULL DEFAULT “0” COMMENT “episodeId”,
mentorId int(11) NOT NULL DEFAULT “0” COMMENT “mentorId”,
lessonId int(11) NOT NULL DEFAULT “0” COMMENT “lessonId”,
phaseId int(11) NOT NULL DEFAULT “0” COMMENT “phaseId”,
subjectId int(11) NOT NULL DEFAULT “0” COMMENT “subjectId”,
shouldFinishedCount int(11) NOT NULL DEFAULT “0” COMMENT “shouldFinishedCount”,
attendedCount int(11) NOT NULL DEFAULT “0” COMMENT “attendedCount”,
pseudoLiveAttendedCount int(11) NOT NULL DEFAULT “0” COMMENT “pseudoLiveAttendedCount”,
replayedCountWithoutAllLive int(11) NOT NULL DEFAULT “0” COMMENT “replayedCountWithoutAllLive”
) ENGINE=OLAP
PRIMARY KEY(dt, teamId, episodeId)
COMMENT “xxx”
PARTITION BY RANGE(dt)
(PARTITION p20240828 VALUES [(“2024-08-28”), (“2024-08-29”)),
PARTITION p20241207 VALUES [(“2024-12-07”), (“2024-12-08”)),
PARTITION p20241208 VALUES [(“2024-12-08”), (“2024-12-09”)),
PARTITION p20241209 VALUES [(“2024-12-09”), (“2024-12-10”)))
DISTRIBUTED BY HASH(dt, teamId, episodeId) BUCKETS 4
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-100”,
“dynamic_partition.end” = “3”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “4”,
“dynamic_partition.history_partition_num” = “0”,
“in_memory” = “false”,
“enable_persistent_index” = “false”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);
执行sql：
explain select * from doris_finish_episode_team_data_v3 where dt = ‘2024-09-05’ and teamId = ‘12025222’ and episodeId = ‘157059699’;
执行计划：
-+
| Explain String |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:1: dt | 2: teamId | 3: episodeId | 4: mentorId | 5: lessonId | 6: phaseId | 7: subjectId | 8: shouldFinishedCount | 9: attendedCount | 10: pseudoLiveAttendedCount | 11: replayedCountWithoutAllLive |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 1:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED |
| |
| 0:OlapScanNode |
| TABLE: doris_finish_episode_team_data_v3 |
| PREAGGREGATION: ON |
| PREDICATES: 2: teamId = 12025222, 3: episodeId = 157059699 |
| partitions=1/104 |
| rollup: doris_finish_episode_team_data_v3 |
| tabletRatio=4/4 |
| tabletList=3329228,3329232,3329236,3329240 |
| cardinality=1 |
| avgRowSize=44.0 |
| numNodes=0
其中：tabletRatio=4/4，扫描了这个分区下的所有tablet应该是1/4才对。