ERROR 1064 (HY000): StarRocks planner use long time 10000 ms in logical phase

【详述】联表查询大概率出现超时
【是否存算分离】否
【StarRocks版本】3.2.13
【集群规模】3fe(1 leader+2follower)+3be(fe与be混部)

联表查询时极大概率超时,报StarRocks planner use long time 10000 ms in logical phase

starrocks查询语句:
SELECT a.integer_01 AS a.integer_01 FROM (SELECT query_data_olap.__time, query_data_olap.count, query_data_olap.string_01, query_data_olap.string_02, query_data_olap.string_03, query_data_olap.string_04, query_data_olap.string_05, query_data_olap.string_06, query_data_olap.string_07, query_data_olap.string_08, query_data_olap.string_09, query_data_olap.string_10, query_data_olap.integer_01, query_data_olap.integer_02, query_data_olap.integer_03, query_data_olap.integer_04, query_data_olap.integer_05, query_data_olap.integer_06, query_data_olap.integer_07, query_data_olap.integer_08, query_data_olap.integer_09, query_data_olap.integer_10, query_data_olap.long_01, query_data_olap.long_02, query_data_olap.long_03, query_data_olap.long_04, query_data_olap.long_05, query_data_olap.long_06, query_data_olap.long_07, query_data_olap.long_08, query_data_olap.long_09, query_data_olap.long_10, query_data_olap.float_01, query_data_olap.float_02, query_data_olap.float_03, query_data_olap.float_04, query_data_olap.float_05, query_data_olap.float_06, query_data_olap.float_07, query_data_olap.float_08, query_data_olap.float_09, query_data_olap.float_10, query_data_olap.double_01, query_data_olap.double_02, query_data_olap.double_03, query_data_olap.double_04, query_data_olap.double_05, query_data_olap.double_06, query_data_olap.double_07, query_data_olap.double_08, query_data_olap.double_09, query_data_olap.double_10 FROM dte_druid_catalog.default_db.ODAEDATASET__DEFAULT_query_data_olap__DEFAULT AS query_data_olap) AS a INNER JOIN (SELECT query_data_jdbc.STRING_01 AS STRING_01, query_data_jdbc.STRING_02 AS STRING_02, query_data_jdbc.STRING_03 AS STRING_03, query_data_jdbc.STRING_04 AS STRING_04, query_data_jdbc.STRING_05 AS STRING_05, query_data_jdbc.INTEGER_01 AS INTEGER_01, query_data_jdbc.INTEGER_02 AS INTEGER_02, query_data_jdbc.INTEGER_03 AS INTEGER_03, query_data_jdbc.INTEGER_04 AS INTEGER_04, query_data_jdbc.INTEGER_05 AS INTEGER_05 FROM dte_jdbc_catalog_query_data_jdbc_connection._default.QUERY_DATA_JDBC AS query_data_jdbc) AS b ON a.integer_01 = b.INTEGER_01 WHERE a.__time >= 1745982000000 AND a.__time <= 1745982299999 GROUP BY a.integer_01

执行报错:
ERROR 1064 (HY000): StarRocks planner use long time 10000 ms in logical phase, This probably because 1. FE Full GC, 2. Hive external table fetch metadata took a long time, 3. The SQL is very com
olex. You could 1. adjust FE JVM config, 2. try query again, 3. enlarge new_planner_optimize_timeout session variable

目前通过query profile定位到是starrocks在计划阶段应该有一个步骤是会尝试用已有的物化视图匹配目标物理数据集,从而提升查询效率。

在把查询超时限制放开之后,查询总耗时44秒里面,有41秒是在计划阶段,并且在匹配物化视图的时候消耗了大量时间

IsProfileAsync: true
Planner:
Total[1] 41s742ms
– Analyzer[1] 4s972ms
— Lock[1] 0
— AnalyzeTable[2] 52ms
— AnalyzeTable[2] 4s917ms
– Transformer[1] 2ms
– Optimizer[1] 33s517ms
— MVPreprocess[1] 33s509ms
— MVChooseCandidates[1] 33s506ms
— MVGenerateMVPlan[1] 0
— MVValidateMV[1] 0
— MVProcessWithView[1] 0
– RuleBaseOptimize[1] 3ms
– CostBaseOptimize[1] 2ms
– PhysicalRewrite[1] 0
– PlanValidate[1]
— InputDependenciesChecker[1] 0
— TypeChecker[1] 0
— CTEChecker[1] 0
— ColumnReuseChecker[1] 0
– ExecPlanBuild[1] 4ms
— Pending[1] 0
– Prepare[1] 0
– Deploy[1] 15ms
— DeployInternalTime[1] 15ms
— DeployLockSerializeConcurrentTime[3] 1ms
— DeployStageByStageTime[9] 1ms
— DeployAsyncSendTime[4] 0
— DeployAsyncTime[9] 11ms
DeployDataSize: 12784
Reason:
MV rewrite fail for dte_DEFAULT_test_scan_all_add_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_IMF_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_1h_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_1y_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_MaxTimeOut_11ge_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_TimeOutIllege_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_time_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_time_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_group_concat_default_sep_file_bbbKey: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_test_scan_all_1d_test_2: MV contains extra tables besides FK-PK
MV rewrite fail for dte_DEFAULT_JSON_QUERYOlap_test: MV contains extra tables besides FK-PK

暴力一点,set new_planner_optimize_timeout=600000; 试试

这个应该是mv 改写的问题 。 可以先关闭 mv改写 set global enable_materialized_view_rewrite=false;

这个确实暴力了些

禁用后可以了,thx~