starrocks: Asynchronous materialized views cannot take effect

Steps to reproduce the behavior (Required)

The goal is to establish a materialized view to optimize this SQL, mainly percentile approximate query 目标想要建立物化视图优化此sql ,主要是百分位近似查询

SELECT CAST(UNIX_TIMESTAMP(startTime) /3600 AS UNSIGNED) * 3600000 as ts ,PERCENTILE_APPROX(duration,0.5) as value from dc_span where 1 = 1 AND parent_id = ‘0’ AND df-api-key = ‘xxx’ and startTime >= ‘2024-03-11 16:33:00’ and startTime <= ‘2024-03-14 16:33:59’ group by ts

SELECT CAST(UNIX_TIMESTAMP(startTime) /60 AS UNSIGNED) * 60000 as ts ,count(0) as value from dc_span where 1 = 1 AND parent_id = ‘0’ AND df-api-key = ‘xxx’ and startTime >= ‘2024-03-13 09:40:00’ and startTime <= ‘2024-03-13 10:40:59’ and ( parent_id = ‘0’ and resource like CONCAT(‘%’,CONCAT(‘basic.ack’,‘%’))) group by ts;

CAST(UNIX_TIMESTAMP(startTime) /3600 AS UNSIGNED) * 3600000 as ts 这边会根据时间范围 去变动聚合时间 Here, the aggregation time will be changed according to the time range.

15 minutes~2 hours 60 4,6 hours 300 12 600 24 hours 900 3 days 3600 7 days 7200

The table structure is as follows CREATE TABLE dc_span ( startTime datetime NOT NULL COMMENT “开始时间”, serviceId varchar(120) NOT NULL COMMENT “”, serviceInstance varchar(50) NOT NULL COMMENT “”, resource varchar(500) NOT NULL COMMENT “”, end bigint(20) NOT NULL COMMENT “结束时间”, duration bigint(20) NULL COMMENT “耗时”, df-api-key varchar(100) NULL COMMENT “”, start bigint(20) NULL COMMENT “开始时间”, parent_id varchar(50) NULL COMMENT “”, … ) ENGINE=OLAP DUPLICATE KEY(startTime, serviceId) PARTITION BY date_trunc(‘hour’, startTime) DISTRIBUTED BY HASH(serviceId, serviceInstance, resource) PROPERTIES ( “replication_num” = “1”, “in_memory” = “false”, “enable_persistent_index” = “false”, “replicated_storage” = “true”, “partition_live_number” = “192”, “compression” = “LZ4” );

目前尝试创建物化视图如下 Currently trying to create a materialized view as follows

CREATE MATERIALIZED VIEW mv_dc_span_minute REFRESH ASYNC EVERY (interval 1 minute) PARTITION BY date_trunc(‘hour’, startTime) PROPERTIES ( “replicated_storage” = “true”, “partition_refresh_number” = “1”, “partition_ttl” = “8 DAY”, “replication_num” = “1”, “storage_medium” = “HDD”, “session.enable_spill” = “true”, “query_rewrite_consistency”=“LOOSE”, “mv_rewrite_staleness_second”=“3600” ) AS SELECT time_slice(startTime, INTERVAL 1 minute ) as startTime, PERCENTILE_APPROX(duration, 0.5) AS t_duration_50, PERCENTILE_APPROX(duration, 0.75) AS t_duration_75, PERCENTILE_APPROX(duration, 0.90) AS t_duration_90, PERCENTILE_APPROX(duration, 0.95) AS t_duration_95, PERCENTILE_APPROX(duration, 0.99) AS t_duration_99, PERCENTILE_APPROX(duration, 1.0) AS t_duration_max, df-api-key,resource,serviceId,serviceInstance FROM dc_span where parent_id=‘0’ GROUP BY time_slice(startTime, INTERVAL 1 minute), df-api-key,resource,serviceId,serviceInstance;

尝试了三种sql 都无法命中 I tried three kinds of sql but couldn’t hit it.

  1. 业务真实需要的sql SQL that the business really needs

SELECT CAST(UNIX_TIMESTAMP(startTime) /3600 AS UNSIGNED) * 3600000 as ts ,PERCENTILE_APPROX(duration,0.5) as value from dc_span where 1 = 1 AND parent_id = ‘0’ AND df-api-key = ‘xxx’ and startTime >= ‘2024-03-11 16:33:00’ and startTime <= ‘2024-03-14 16:33:59’ group by ts

trace logs mv ::

2ms| [MV TRACE] [PREPARE GLOBAL] Query input tables: [Table [id=196785, name=dc_span, type=OLAP]]" 2ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 2ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Enable/Disable Params: 2ms| [MV TRACE] [PREPARE GLOBAL] enable_experimental_mv: true 2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_rewrite: true 2ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: false 2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_union_rewrite: true 2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_view_delta_rewrite: true 2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_single_table_view_delta_rewrite: false 2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_plan_cache: true 2ms| [MV TRACE] [PREPARE GLOBAL] mv_auto_analyze_async: true 2ms| [MV TRACE] [PREPARE GLOBAL] enable_mv_automatic_active_check: true 2ms| [MV TRACE] [PREPARE GLOBAL] enable_sync_materialized_view_rewrite: true 2ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: false 2ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 2ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Limit Params: 2ms| [MV TRACE] [PREPARE GLOBAL] skip_whole_phase_lock_mv_limit: 5 2ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 2ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Config Params: 2ms| [MV TRACE] [PREPARE GLOBAL] analyze_mv: sample 2ms| [MV TRACE] [PREPARE GLOBAL] query_excluding_mv_names: 2ms| [MV TRACE] [PREPARE GLOBAL] query_including_mv_names: 2ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_rule_output_limit: 3 2ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_candidate_limit: 12 2ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_related_mvs_limit: 64 2ms| [MV TRACE] [PREPARE GLOBAL] materialized_view_rewrite_mode: DEFAULT 2ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- " 2ms| [MV TRACE] [PREPARE GLOBAL] Table/MaterializedView dc_span has related materialized views: [MvId{dbId=10005, id=198574}]" 2ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 mvs after user config 3ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 valid mvs after checking valid 3ms| [MV TRACE] [PREPARE mv_dc_span_minute] MV’ partitions to refresh: [] 3ms| [MV TRACE] [PREPARE mv_dc_span_minute] MV compensate partition predicate: null 4ms| [MV TRACE] [PREPARE mv_dc_span_minute] Prepare MV mv_dc_span_minute success " 4ms| [MV TRACE] [PREPARE GLOBAL] RelatedMVs: [mv_dc_span_minute], CandidateMVs: [mv_dc_span_minute]" 11ms| [MV TRACE] [REWRITE mv_dc_span_minute] Query Compensate partition predicate:true “12ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Try to rewrite query with mv:mv_dc_span_minute, match mode:COMPLETE” “12ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] MV predicate split:equalPredicates=, rangePredicates=19: parent_id = 0, residualPredicates=” “12ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Query predicate split:equalPredicates=, rangePredicates=14: df-api-key = xxx AND 19: parent_id = 0 AND 1: startTime >= 2024-03-11 16:33:00 AND 1: startTime <= 2024-03-14 16:33:59, residualPredicates=” 12ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Construct 1 relation id mappings from query to mv 13ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Rewrite scalar operator failed: 1: startTime <= 2024-03-14 16:33:59 cannot be rewritten “13ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] rewrite predicates from query to mv failed. isMVBased:false, useEc:true, predicate:14: df-api-key = xxx AND 1: startTime >= 2024-03-11 16:33:00 AND 1: startTime <= 2024-03-14 16:33:59” 13ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Rewrite other predicates compensation failed: 14: df-api-key = xxx AND 1: startTime >= 2024-03-11 16:33:00 AND 1: startTime <= 2024-03-14 16:33:59 13ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Success to convert query compensation predicates to MV but rewrite compensation failed “14ms| Query cannot be rewritten, please check the trace logs to find more information.” Tracer Cost: 448us

  1. 直接查询物化视图 Query materialized views directly

SELECT time_slice(startTime, INTERVAL 1 minute ) as startTime, PERCENTILE_APPROX(duration, 0.5) AS t_duration_50, PERCENTILE_APPROX(duration, 0.75) AS t_duration_75, PERCENTILE_APPROX(duration, 0.90) AS t_duration_90, PERCENTILE_APPROX(duration, 0.95) AS t_duration_95, PERCENTILE_APPROX(duration, 0.99) AS t_duration_99, PERCENTILE_APPROX(duration, 1.0) AS t_duration_max, df-api-key,resource,serviceId,serviceInstance FROM dc_span where parent_id=‘0’ and startTime >= ‘2024-03-14 15:33:00’ and startTime <= ‘2024-03-14 16:33:59’ AND df-api-key = ‘xxx’ GROUP BY time_slice(startTime, INTERVAL 1 minute), df-api-key,resource,serviceId,serviceInstance;

" 3ms| [MV TRACE] [PREPARE GLOBAL] Query input tables: [Table [id=196785, name=dc_span, type=OLAP]]" 3ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 3ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Enable/Disable Params: 3ms| [MV TRACE] [PREPARE GLOBAL] enable_experimental_mv: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_rewrite: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: false 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_union_rewrite: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_view_delta_rewrite: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_single_table_view_delta_rewrite: false 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_plan_cache: true 3ms| [MV TRACE] [PREPARE GLOBAL] mv_auto_analyze_async: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_mv_automatic_active_check: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_sync_materialized_view_rewrite: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: false 3ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 3ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Limit Params: 3ms| [MV TRACE] [PREPARE GLOBAL] skip_whole_phase_lock_mv_limit: 5 3ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 3ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Config Params: 3ms| [MV TRACE] [PREPARE GLOBAL] analyze_mv: sample 3ms| [MV TRACE] [PREPARE GLOBAL] query_excluding_mv_names: 4ms| [MV TRACE] [PREPARE GLOBAL] query_including_mv_names: 4ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_rule_output_limit: 3 4ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_candidate_limit: 12 4ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_related_mvs_limit: 64 4ms| [MV TRACE] [PREPARE GLOBAL] materialized_view_rewrite_mode: DEFAULT 4ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- " 4ms| [MV TRACE] [PREPARE GLOBAL] Table/MaterializedView dc_span has related materialized views: [MvId{dbId=10005, id=198574}]" 4ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 mvs after user config 4ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 valid mvs after checking valid 4ms| [MV TRACE] [PREPARE mv_dc_span_minute] MV’ partitions to refresh: [] 4ms| [MV TRACE] [PREPARE mv_dc_span_minute] MV compensate partition predicate: null 5ms| [MV TRACE] [PREPARE mv_dc_span_minute] Prepare MV mv_dc_span_minute success " 5ms| [MV TRACE] [PREPARE GLOBAL] RelatedMVs: [mv_dc_span_minute], CandidateMVs: [mv_dc_span_minute]" 8ms| [MV TRACE] [REWRITE mv_dc_span_minute] Query Compensate partition predicate:true " 9ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Try to rewrite query with mv:mv_dc_span_minute, match mode:COMPLETE" " 9ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] MV predicate split:equalPredicates=, rangePredicates=19: parent_id = 0, residualPredicates=" " 9ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Query predicate split:equalPredicates=, rangePredicates=14: df-api-key = xxx AND 19: parent_id = 0 AND 1: startTime >= 2024-03-14 15:33:00 AND 1: startTime <= 2024-03-14 16:33:59, residualPredicates=" 9ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Construct 1 relation id mappings from query to mv 10ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Rewrite scalar operator failed: 1: startTime <= 2024-03-14 16:33:59 cannot be rewritten “10ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] rewrite predicates from query to mv failed. isMVBased:false, useEc:true, predicate:14: df-api-key = xxx AND 1: startTime >= 2024-03-14 15:33:00 AND 1: startTime <= 2024-03-14 16:33:59” 10ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Rewrite other predicates compensation failed: 14: df-api-key = xxx AND 1: startTime >= 2024-03-14 15:33:00 AND 1: startTime <= 2024-03-14 16:33:59 10ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Success to convert query compensation predicates to MV but rewrite compensation failed “12ms| Query cannot be rewritten, please check the trace logs to find more information.” Tracer Cost: 597us

SELECT CAST(UNIX_TIMESTAMP(startTime) /60 AS UNSIGNED) * 60000 as ts ,count(0) as value from dc_span where 1 = 1 AND parent_id = ‘0’ AND df-api-key = ‘xxx’ and startTime >= ‘2024-03-13 09:40:00’ and startTime <= ‘2024-03-13 10:40:59’ and ( parent_id = ‘0’ and resource like CONCAT(‘%’,CONCAT(‘basic.ack’,‘%’))) group by ts;

" 3ms| [MV TRACE] [PREPARE GLOBAL] Query input tables: [Table [id=196785, name=dc_span, type=OLAP]]" 3ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 3ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Enable/Disable Params: 3ms| [MV TRACE] [PREPARE GLOBAL] enable_experimental_mv: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_rewrite: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: false 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_union_rewrite: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_view_delta_rewrite: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_single_table_view_delta_rewrite: false 3ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_plan_cache: true 3ms| [MV TRACE] [PREPARE GLOBAL] mv_auto_analyze_async: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_mv_automatic_active_check: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_sync_materialized_view_rewrite: true 3ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: false 3ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 3ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Limit Params: 3ms| [MV TRACE] [PREPARE GLOBAL] skip_whole_phase_lock_mv_limit: 5 3ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 3ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Config Params: 3ms| [MV TRACE] [PREPARE GLOBAL] analyze_mv: sample 3ms| [MV TRACE] [PREPARE GLOBAL] query_excluding_mv_names: 3ms| [MV TRACE] [PREPARE GLOBAL] query_including_mv_names: 3ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_rule_output_limit: 3 3ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_candidate_limit: 12 3ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_related_mvs_limit: 64 3ms| [MV TRACE] [PREPARE GLOBAL] materialized_view_rewrite_mode: DEFAULT 3ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- " 3ms| [MV TRACE] [PREPARE GLOBAL] Table/MaterializedView dc_span has related materialized views: [MvId{dbId=10005, id=198574}]" 3ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 mvs after user config 3ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 valid mvs after checking valid 4ms| [MV TRACE] [PREPARE mv_dc_span_minute] MV’ partitions to refresh: [] 4ms| [MV TRACE] [PREPARE mv_dc_span_minute] MV compensate partition predicate: null 4ms| [MV TRACE] [PREPARE mv_dc_span_minute] Prepare MV mv_dc_span_minute success " 4ms| [MV TRACE] [PREPARE GLOBAL] RelatedMVs: [mv_dc_span_minute], CandidateMVs: [mv_dc_span_minute]" 7ms| [MV TRACE] [REWRITE mv_dc_span_minute] Query Compensate partition predicate:true " 9ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Try to rewrite query with mv:mv_dc_span_minute, match mode:COMPLETE" " 9ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] MV predicate split:equalPredicates=, rangePredicates=19: parent_id = 0, residualPredicates=" " 9ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Query predicate split:equalPredicates=, rangePredicates=14: df-api-key = xxx AND 19: parent_id = 0 AND 1: startTime >= 2024-03-13 09:40:00 AND 1: startTime <= 2024-03-13 10:40:59, residualPredicates=4: resource LIKE %basic.ack%" 9ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Construct 1 relation id mappings from query to mv 10ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Rewrite scalar operator failed: 1: startTime <= 2024-03-13 10:40:59 cannot be rewritten “10ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] rewrite predicates from query to mv failed. isMVBased:false, useEc:true, predicate:14: df-api-key = xxx AND 1: startTime >= 2024-03-13 09:40:00 AND 1: startTime <= 2024-03-13 10:40:59 AND 4: resource LIKE %basic.ack%” 10ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Rewrite other predicates compensation failed: 14: df-api-key = xxx AND 1: startTime >= 2024-03-13 09:40:00 AND 1: startTime <= 2024-03-13 10:40:59 AND 4: resource LIKE %basic.ack% 10ms| [MV TRACE] [REWRITE TF_MV_AGGREGATE_SCAN_RULE mv_dc_span_minute] Success to convert query compensation predicates to MV but rewrite compensation failed “12ms| Query cannot be rewritten, please check the trace logs to find more information.” Tracer Cost: 605us

Expected behavior (Required)

命中物化视图 Hit materialized view

Real behavior (Required)

没有命中物化视图 Materialized view not hit

StarRocks version (Required)

3.2.4

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 15 (8 by maintainers)

Most upvoted comments

Thank you so much for your help with this issue. I really appreciate your quick response and solution. Your assistance has been invaluable to me.

CREATE MATERIALIZED VIEW mv_dc_span_minute REFRESH ASYNC EVERY (interval 1 minute) PARTITION BY date_trunc(‘hour’, startTime) PROPERTIES ( “replicated_storage” = “true”, “partition_refresh_number” = “1”, “partition_ttl” = “8 DAY”, “replication_num” = “1”, “storage_medium” = “HDD”, “session.enable_spill” = “true”, “query_rewrite_consistency”=“LOOSE”, “mv_rewrite_staleness_second”=“3600” ) AS SELECT time_slice(startTime, INTERVAL 1 minute ) as startTime, percentile_union(percentile_hash(duration)) as t_duration, df-api-key,resource,serviceId,serviceInstance FROM dc_span where parent_id=‘0’ GROUP BY time_slice(startTime, INTERVAL 1 minute), df-api-key,resource,serviceId,serviceInstance;

It’s not like synchronous mv, can you define mv by using percentile_union(percentile_hash(duration)) as t_duration, directly so can be used for rewrite?

https://docs.starrocks.io/docs/using_starrocks/query_rewrite_with_materialized_views/#aggregation-rewrite