http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> We have a lot of queries with a sha...
# questions-and-troubleshooting
s
@Rocky We have a lot of queries with a shared JOIN snippet like
Copy code
FROM interactions i
LEFT JOIN enrichments e ON e.account_key = i.account_key
    AND e.entity_id = i.conversation_id
    AND e.entity_type = 'zzzzzzzzzzzzz'
    AND e.event_time >= '%s'
    AND e.event_time < '%s'
    WHERE i.account_key = %d
and wondering if async materialized view could help. What the create materialized view look like
r
Yes, an Asynchronous Materialized View (MV) is an excellent fit for this scenario. It can pre-calculate the expensive 
LEFT JOIN
and store it as a "wide table," allowing StarRocks' optimizer to transparently rewrite your queries to hit the MV instead of re-joining the base tables. Here is how you would structure the `CREATE MATERIALIZED VIEW`: 1. Recommended MV Definition To make this MV reusable for many queries with different 
account_key
and 
event_time
values, you should include the join logic and the static filters (like 
entity_type
), but keep the dynamic fields in the 
SELECT
list so the optimizer can apply your specific filters to the MV.
Copy code
sql
CREATE MATERIALIZED VIEW interactions_enrichment_mv
-- Partition by time to allow incremental refreshes and partition pruning
PARTITION BY (i_event_time) 
-- Distribute by join keys to avoid shuffles during the initial build/refresh
DISTRIBUTED BY HASH(account_key, conversation_id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Adjust refresh frequency as needed
AS
SELECT 
    i.account_key,
    i.conversation_id,
    i.event_time AS i_event_time, -- Assuming interactions has a time column
    e.event_time AS e_event_time,
    e.entity_type,
    -- Include all other columns used in your shared queries
    i.col1, 
    e.attr1,
    e.attr2
FROM interactions i
LEFT JOIN enrichments e ON e.account_key = i.account_key
    AND e.entity_id = i.conversation_id
    AND e.entity_type = 'zzzzzzzzzzzzz';
2. Why this works * Transparent Query Rewrite: Once created and refreshed, the StarRocks optimizer can detect that a query uses the same 
LEFT JOIN
logic. It will automatically route the query to 
interactions_enrichment_mv
. * Partitioning: By using 
PARTITION BY
, StarRocks only needs to refresh the specific partitions where data has changed (e.g., the last 24 hours). This makes maintaining a massive join much more efficient. * Predicate Pushdown: Your dynamic filters (
WHERE i.account_key = %d
) will be pushed down into the MV scan, making the query extremely fast as it will only read the relevant buckets and partitions. 3. Important Considerations * The 
ON
Clause vs `WHERE`: In your snippet, the 
event_time
filter is in the 
ON
clause of the 
LEFT JOIN
. This means you want to see the 
interactions
row even if there is no 
enrichment
in that specific time window. _ If you define the MV _without* the time filter (as shown above), the MV will contain the enrichment data for all time. * For the optimizer to rewrite this correctly, it must ensure that adding the time filter to the query results in the same "NULL-filling" behavior. StarRocks' optimizer is very sophisticated with Join Derivability, but if you find it isn't rewriting automatically, you can query the MV directly using a 
CASE
statement or by moving the filter to the 
WHERE
clause if your logic allows. * Static vs. Dynamic Filters: I included 
e.entity_type = 'zzzzzzzzzzzzz'
in the MV definition because it seems to be a constant. This reduces the size of the MV. If 
entity_type
actually changes between queries, move it from the 
ON
clause in the MV to the 
SELECT
list. * Memory/Storage: Since this is a 
LEFT JOIN
between what sounds like two large tables, ensure your 
DISTRIBUTED BY
key (usually the 
account_key
or 
conversation_id
) provides good data distribution to avoid skew. How to verify it's working After creating the MV, run your original query and check the execution plan:
Copy code
sql
EXPLAIN SELECT ... FROM interactions i LEFT JOIN enrichments e ...;
If you see 
interactions_enrichment_mv
in the plan instead of the base tables, the rewrite was successful. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
s
@Rocky We have about 60000 rows per min going in interactions and 20000 per minute in enrichments. Would 
REFRESH ASYNC EVERY (INTERVAL 30 SECOND)
be acceptable ? We want to limit time before data gets queryable
r
A 30-second refresh interval is technically acceptable in StarRocks, but its success depends entirely on whether your refresh task can complete within that window. Given your ingestion rate (~80k rows/min combined), a full join every 30 seconds would be expensive, but an incremental partition refresh makes this very feasible. Here is how to optimize for that 30-second requirement: 1. Partitioning is Mandatory For a 30-second refresh to be efficient, the Materialized View (MV) must be partitioned (typically by the same time column as your 
interactions
table). * How it works: StarRocks tracks data versions at the partition level. If only the "current hour" partition in 
interactions
received new data, the 30-second refresh task will only re-calculate the join for that specific partition, rather than the entire table. * Result: The refresh might only process the ~40k rows that arrived in the last 30 seconds, taking only a few seconds to complete. 2. 
REFRESH ASYNC
vs. 
EVERY (INTERVAL 30 SECOND)
 * 
REFRESH ASYNC EVERY (INTERVAL 30 SECOND)
: Triggers strictly every 30 seconds. If a refresh is still running when the next 30s mark hits, the new task is queued or merged. * 
REFRESH ASYNC
(Triggered): If you omit the 
EVERY
clause, StarRocks triggers a refresh immediately after a data load into a base table is committed. _ _Recommendation:* If your data is arriving in small batches every few seconds, the 30-second interval is better to prevent "refresh storms." If you load data once per minute, use the triggered mode for lower latency. 3. Tuning for High Frequency If you see the MV falling behind, check these settings: * 
partition_refresh_number
: In v3.3+, this defaults to 
1
. For very frequent refreshes, you might set this to a higher number (or 
-1
for all changed partitions) to ensure the refresh doesn't get stuck processing partitions one-by-one. * Resource Groups: High-frequency refreshes can "flicker" CPU usage. You can assign the MV to a specific resource group to ensure it doesn't starve your frontend queries:
Copy code
sql
    PROPERTIES (
        "resource_group" = "your_mv_resource_group"
    )
* Check for Backlogs: Monitor the refresh duration to see if it's exceeding 30 seconds:
Copy code
sql
    SELECT * FROM information_schema.task_runs
    WHERE task_name = 'mv_name'
    ORDER BY create_time DESC LIMIT 10;
4. What happens if a refresh takes > 30s? StarRocks handles this gracefully. It will not run multiple refresh tasks for the same MV simultaneously. If Task A is still running at the 30s mark, Task B will wait. If Task C is triggered while B is waiting, B and C are often merged. Your data will simply be slightly more "stale" (e.g., 45s lag instead of 30s) during heavy load periods. Summary Recommendation Yes, use the partitioned approach. If your join is on 
account_key
and 
conversation_id
, ensure the MV is distributed by those keys to make the join as fast as possible (colocated join). With 80k rows/min, a well-tuned incremental refresh should easily finish in under 10 seconds. References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md References • using_starrocks/async_mv/Materialized_view.md • data_source/feature-support-data-lake-analytics.md
s
@Rocky Assume I use a less agressive refresh period of 1 hour in your your example and the query cover last 5 hours. Will StarRocks be able to route the first 4 hours toward the MV and last hour in the base tables not yet covered by the MV ?
r
Yes, StarRocks can do exactly that. This feature is called Partition Union Rewrite. When you have a partitioned asynchronous Materialized View (MV), StarRocks' optimizer is smart enough to "fill the gap" between the data in the MV and the fresh data in your base tables. How it works If your query requests a time range (e.g., last 5 hours) that is broader than what the MV currently contains (e.g., last 4 hours because the 5th hour hasn't been refreshed yet), StarRocks will: 1. Read the first 4 hours from the pre-joined Materialized View (fast). 2. Read the last 1 hour by performing the actual join on the base tables (
interactions
and 
enrichments
). 3. Union the results transparently. Requirements for this to work To enable this specific behavior, ensure the following: 1. Partition Alignment: Both your base table (
interactions
) and the MV must be partitioned by the same time column (e.g., 
event_time
). 2. Property Setting: The MV must be created with the property 
"query_rewrite_consistency" = "loose"
(default is usually 
checked
, which might prevent the rewrite if the MV is considered stale). However, for Partition Union Rewrite specifically, StarRocks is designed to handle this even in 
checked
mode if it can prove the older partitions are consistent. 3. Query Structure: The query must use the partition key in the 
WHERE
clause so the optimizer can identify which partitions are missing from the MV. Example logic If your MV was refreshed at 10:00 AM and you run a query at 10:30 AM for data since 5:00 AM: * MV Data: 5:00 AM to 10:00 AM (Partitions are consistent). * Base Table Data: 10:00 AM to 10:30 AM (New data not yet in MV). * Plan: 
UNION ALL(Scan MV [5-10], Join(Scan Base [10-10:30]))
. This gives you the best of both worlds: the performance of a wide table for historical data and the "real-time" accuracy of your base tables for the most recent data. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md (See "Partition Union rewrite" section) References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
s
@Rocky Are there any limitation of this union process, for example, using DISTINCT in the select part ?
r
The Partition Union Rewrite process in StarRocks is specifically designed to handle complex queries, including those with 
DISTINCT
. How 
DISTINCT
is handled When you run a 
SELECT DISTINCT
query that spans both the Materialized View (MV) and fresh partitions in the base tables: 1. Partial Deduplication: StarRocks fetches unique rows from the MV and unique rows from the base tables (for the partitions not yet in the MV). 2. Union: It performs a 
UNION ALL
of these two unique sets. 3. Global Deduplication: The optimizer adds a final deduplication step on top of the unioned results to ensure that if the same value appeared in both the MV and the new base table data, it only appears once in the final output. Mathematically, it treats the query as an aggregation. Since 
SELECT DISTINCT
is functionally equivalent to a 
GROUP BY
on all selected columns, the optimizer uses the same logic it uses for 
SUM
or 
MAX
to ensure correctness across the boundary. Limitations to watch for While 
DISTINCT
in your query is supported, there are specific limitations on the Materialized View definition if you want it to support transparent query rewrite: * No 
UNION
or 
LIMIT
in MV Definition: If you define the MV using a 
UNION
, 
LIMIT
, or 
ORDER BY
clause, the optimizer currently cannot use that MV for automatic query rewriting. * Window Functions: Queries using window functions (e.g., 
ROW_NUMBER()
) generally do not support Partition Union Rewrite because the "window" could overlap between the MV and the base table, leading to incorrect results. * Consistency Mode: For the union to occur, the partitions in the MV that are being used must be "consistent" (up to date). If the MV is partially stale, the optimizer will only use the fresh partitions of the MV and fill the rest from the base tables. * External Tables: If your base tables are in an external catalog (like Iceberg or HIVE), Partition Union Rewrite is supported, but ensure your StarRocks version is recent (v3.1+ is recommended for the most robust rewrite support). Summary for your use case In your scenario where you are joining 
interactions
and `enrichments`: * Query: 
SELECT DISTINCT account_key FROM ...
* Behavior: StarRocks will scan the unique `account_key`s from the MV (last 4 hours) and join the fresh `interactions`/`enrichments` data (last 1 hour) to find new `account_key`s, then merge and deduplicate the two sets. This is a standard and supported path for the StarRocks optimizer. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md (See "Union rewrite" and "Limitations" sections) References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • using_starrocks/caching/query_cache.md
s
@Rocky About the enrichments event_time join: this was likely added to enable time prune of partition in the original query. The gist of it is that enrichments.event_time is always >= interaction.start_time and these enrichments are usually at most a few hours after that start time. You'll never have an enrichment found before the interaction start time. They correlate mostly time wise.
r
The correlation you've described (
enrichments.event_time >= interaction.start_time
) is very beneficial for StarRocks, particularly for the Union Rewrite process. Here is how StarRocks uses that time relationship to keep the "fallback" part of your query fast: 1. Partition Pruning via Runtime Filters When StarRocks executes the join for the "fresh" data (the 1 hour not yet in the MV), it uses Runtime Filters. * The Mechanism: As StarRocks scans the 
interactions
table for the last 1 hour (e.g., 
12:00
to 
13:00
), it calculates the min/max range of 
start_time
. * The Optimization: It then pushes this range down to the 
enrichments
scan. Because you have the join condition 
enrichments.event_time >= interactions.start_time
, StarRocks knows it can prune every partition in the 
enrichments
table that ends before 
12:00
. * Result: Even though you are querying the base tables, you aren't doing a full scan of 
enrichments
. You are only scanning the partitions that could possibly contain matching events. 2. Efficiency in the "Gap" In your example (MV covers 4 hours, Query covers 5 hours): 1. MV Part: Scans the pre-joined data for the first 4 hours. This is near-instant. 2. Base Part: Joins 
interactions
(last 1 hour) with 
enrichments
. 3. Pruning: Because of your correlation, the 
enrichments
scan will only touch the last ~1-3 partitions (depending on your partition size). It won't look at the historical data in the base table at all. 3. Key Configuration for this Setup To ensure this works optimally with your 1-hour refresh strategy: * Partition your base tables: Both 
interactions
and 
enrichments
should be partitioned by their respective `event_time`/`start_time` columns. * Colocated Join (Optional but Recommended): If you bucket both tables by a shared ID (like 
interaction_id
) and put them in the same Colocation Group, the join for that last hour of data will be a "local join," avoiding expensive data shuffling across the network. * Enable Runtime Filters: Ensure 
runtime_filter_mode
is set to 
GLOBAL
(this is the default in recent versions). A small tip on the Join Condition If you know for a fact that enrichments happen within, say, 6 hours of the interaction, adding an upper bound to your join condition can help StarRocks prune even more aggressively:
Copy code
sql
ON enrichments.interaction_id = interactions.id
AND enrichments.event_time >= interactions.start_time
AND enrichments.event_time <= interactions.start_time + INTERVAL 6 HOUR
While the 
>=
prunes older partitions, the 
<=
allows StarRocks to also prune partitions that are too far in the future, which can be helpful if you have future-dated data or very large tables. References • using_starrocks/Runtime_filter.md • using_starrocks/Colocated_join.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md (Partition Union rewrite section) References • using_starrocks/Runtime_filter.md • using_starrocks/Colocated_join.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • administration/management/resource_management/resource_group.md • using_starrocks/Table_design.md • administration/Query_planning.md References
s
@Rocky when running EXPLAIN, what is the indicator showing if there was network shuffling done or not to assess if colocation was effective
r
When running 
EXPLAIN
, there are three primary indicators to check if colocation is effective and if network shuffling is being avoided: 1. The 
colocate: true
Flag Look for the 
HASH JOIN
 node in the execution plan. If the tables are correctly colocated and the optimizer is using that fact, you will see an explicit property: * Effective: 
colocate: true
* Failed: 
colocate: false, reason: ...
(e.g., 
reason: group is not stable
or 
reason: tables not in same group
) 2. Absence of 
EXCHANGE
Nodes An 
EXCHANGE
 node represents a network shuffle (data being sent between BE nodes). * Colocated (No Shuffle): You will see the 
OlapScanNode
of both tables feeding directly into the 
HASH JOIN
within the same Plan Fragment, without an 
EXCHANGE
node between them. * Shuffled (Network Overhead): You will see an 
EXCHANGE
node sitting above one or both scan nodes. This indicates that StarRocks is re-partitioning the data across the network to ensure matching keys land on the same machine. 3. Plan Fragment Structure * Successful Colocation: Both tables are scanned and joined within a single 
PLAN FRAGMENT
. * Non-Colocated: The join usually spans multiple fragments, with 
STREAM DATA SINK
and 
EXCHANGE
nodes connecting them. ────────── Example Comparison Colocated (Fastest): Notice there is no 
EXCHANGE
between the scan and the join.
Copy code
text
PLAN FRAGMENT 1
  ...
  2:HASH JOIN
  |  join op: INNER JOIN (COLOCATE)
  |  colocate: true                 <-- INDICATOR 1
  |  ...
  |-0:OlapScanNode                  <-- Directly under Join
  |    TABLE: table_a
  +-1:OlapScanNode                  <-- Directly under Join
       TABLE: table_b
Non-Colocated (Shuffled): The 
EXCHANGE
node indicates data is moving over the network.
Copy code
text
PLAN FRAGMENT 1
  ...
  2:HASH JOIN
  |  colocate: false                <-- INDICATOR 1
  |  ...
  |-3:EXCHANGE                      <-- INDICATOR 2 (Network Shuffle)
  |  0:OlapScanNode
  |    TABLE: table_a
  +-1:OlapScanNode
       TABLE: table_b
Pro-Tip: Check the "Stable" Status If you expect colocation but see 
colocate: false
, check the status of your colocation group:
Copy code
sql
SHOW PROC '/colocation_group';
If the 
IsStable
column is 
false
, StarRocks is currently rebalancing data (perhaps after a node failure), and it will temporarily fall back to network shuffling until the data is realigned. References • using_starrocks/Colocate_join.md (See "Query" section for EXPLAIN examples) • best_practices/query_tuning/query_hint.md (Covers viewing join execution methods) References • using_starrocks/Colocate_join.md • best_practices/query_tuning/query_hint.md • administration/Query_planning.md • administration/management/resource_management/resource_group.md • using_starrocks/Table_design.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/Runtime_filter.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • using_starrocks/caching/query_cache.md • using_starrocks/Colocated_join.md
• administration/Maintenance.md • administration/Configuration.md • administration/Monitor_and_Manage.md • benchmarking/SSB_Benchmarking.md • benchmarking/TPC-H_Benchmarking.md • deployment/helm.md • faq/loading/Loading_faq.md • faq/Queries_faq.md • get_started/introduction.md • integrations/authenticate_to_aws.md • introduction/StarRocks_intro.md • loading/Etl_in_starrocks.md • quick_start/shared_storage.md • releasenotes/release-3.0.md • sql-reference/sql-functions/array-functions/array_agg.md • sql-reference/sql-functions/aggregate-functions/approx_count_distinct.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • using_starrocks/table_types/primary_key_table.md • using_starrocks/data_loading/loading_introduction.md • using_starrocks/external_table/External_table.md • using_starrocks/query_acceleration/Alter_view.md • using_starrocks/backup_and_restore.md • using_starrocks/Distinct_values.md • using_starrocks/Materialized_view.md • using_starrocks/Partition.md • using_starrocks/Zorder_and_Short_key_index.md • unsupported_sqls/unsupported_sqls.md • administration/cluster_management/upgrade/upgrade_overview.md • administration/Query_analysis.md • administration/Resource_management.md • administration/Statistic.md • administration/User_privilege.md • deployment/deploy_starrocks_with_docker.md • deployment/Deployment_recommendation.md • faq/Installation_faq.md • loading/RoutineLoad.md • quick_start/starrocks_intro.md
• releasenotes/release-1.19.md • releasenotes/release-2.0.md • releasenotes/release-2.1.md • releasenotes/release-2.2.md • releasenotes/release-2.3.md • releasenotes/release-2.4.md • releasenotes/release-2.5.md • releasenotes/release-3.1.md • sql-reference/sql-functions/array-functions/array_contains.md • sql-reference/sql-functions/aggregate-functions/avg.md • sql-reference/sql-functions/Cast.md • sql-reference/sql-statements/account-management/CREATE_USER.md • sql-reference/sql-statements/data-definition/CREATE_TABLE.md • sql-reference/sql-statements/data-manipulation/INSERT.md • using_starrocks/table_types/aggregate_table.md • using_starrocks/table_types/duplicate_key_table.md • using_starrocks/table_types/unique_key_table.md • using_starrocks/data_loading/Loading_faq.md • using_starrocks/data_loading/RoutineLoad.md • using_starrocks/external_table/External_catalog.md • using_starrocks/external_table/Hive_catalog.md • using_starrocks/query_acceleration/Bitmap_index.md • using_starrocks/query_acceleration/Bloom_filter_index.md • using_starrocks/Continuous_loading.md • using_starrocks/Cost_optimization.md • using_starrocks/Hadoop_catalog.md • using_starrocks/Iceberg_catalog.md • using_starrocks/Join_optimization.md • using_starrocks/Object_storage_catalog.md • using_starrocks/Table_types.md • using_starrocks/Vector_search.md • using_starrocks/Z-order_index.md • administration/cluster_management/Elastic_scaling.md • administration/management/Data_recovery.md
• administration/management/Monitor_and_Alert.md • administration/Configuration_file.md • administration/HDFS_configuration.md • administration/Resource_isolation.md • deployment/Deployment_planning.md • faq/Queries_FAQ.md • loading/loading_introduction.md • loading/SparkLoad.md • loading/StreamLoad.md • quick_start/deployment_with_docker.md • releasenotes/release-1.18.md • sql-reference/sql-functions/array-functions/array_length.md • sql-reference/sql-functions/aggregate-functions/count.md • sql-reference/sql-statements/account-management/CREATE_ROLE.md • sql-reference/sql-statements/data-definition/CREATE_VIEW.md • sql-reference/sql-statements/data-manipulation/UPDATE.md • using_starrocks/data_loading/Etl_in_starrocks.md • using_starrocks/data_loading/SparkLoad.md • using_starrocks/data_loading/StreamLoad.md • using_starrocks/external_table/Hadoop_catalog.md • using_starrocks/external_table/Iceberg_catalog.md • using_starrocks/query_acceleration/Bitmap_index_usage.md • using_starrocks/query_acceleration/Bloom_filter_index_usage.md • using_starrocks/Delta_Lake_catalog.md • using_starrocks/Elastic_scaling.md • using_starrocks/Hudi_catalog.md • using_starrocks/Resource_Group.md • using_starrocks/Table_design_overview.md • using_starrocks/User_privilege.md • using_starrocks/Using_StarRocks_with_Grafana.md • using_starrocks/Working_with_Iceberg.md • administration/cluster_management/upgrade/upgrade_starrocks.md • administration/management/Control_execution_timeout.md
• administration/management/Resource_Group.md • administration/FE_configuration.md • administration/Partition_management.md • deployment/deploy_starrocks_with_operator.md • faq/Data_loading_faq.md • loading/BrokerLoad.md • loading/Load_to_Primary_Key_Table.md • quick_start/starrocks_kubernetes.md • releasenotes/release-1.11.md • sql-reference/sql-functions/array-functions/array_max.md • sql-reference/sql-functions/aggregate-functions/group_concat.md • sql-reference/sql-statements/account-management/GRANT.md • sql-reference/sql-statements/data-definition/CREATE_INDEX.md • sql-reference/sql-statements/data-manipulation/DELETE.md • using_starrocks/data_loading/BrokerLoad.md • using_starrocks/data_loading/Load_to_Primary_Key_Table.md • using_starrocks/external_table/Delta_Lake_catalog.md • using_starrocks/external_table/Hudi_catalog.md • using_starrocks/query_acceleration/Bitmap_Index.md • using_starrocks/query_acceleration/Bloom_Filter_Index.md • using_starrocks/audit_loader.md • using_starrocks/Broker_load.md • using_starrocks/CBO_stats.md • using_starrocks/Data_recovery.md • using_starrocks/Hive_catalog.md • using_starrocks/Monitor_and_Alert.md • using_starrocks/Partition_management.md • using_starrocks/Resource_isolation.md • using_starrocks/Stream_load.md • using_starrocks/Working_with_Delta_Lake.md • using_starrocks/Working_with_Hudi.md • administration/cluster_management/deployment/deploy_starrocks_with_docker.md • administration/management/Control_execution_plan.md
• administration/management/Manage_queries.md • administration/BE_configuration.md • administration/Step-by-step_optimizing_slow_queries.md • deployment/k8s_operator.md • faq/System_administration_faq.md • loading/Data_loading_faq.md • loading/InsertInto.md • loading/Loading_introduction.md • quick_start/starrocks_docker.md • releasenotes/release-1.10.md • sql-reference/sql-functions/array-functions/array_min.md • sql-reference/sql-functions/aggregate-functions/max.md • sql-reference/sql-statements/account-management/REVOKE.md • sql-reference/sql-statements/data-definition/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/data-manipulation/TRUNCATE_TABLE.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/data_loading/Data_loading_faq.md • using_starrocks/data_loading/InsertInto.md • using_starrocks/data_loading/Loading_introduction.md • using_starrocks/external_table/JDBC_catalog.md • using_starrocks/external_table/Paimon_catalog.md • using_starrocks/query_acceleration/bitmap_index.md • using_starrocks/query_acceleration/bloom_filter_index.md • using_starrocks/Backup_and_restore.md • using_starrocks/Colocate_join.md • using_starrocks/JDBC_catalog.md • using_starrocks/Paimon_catalog.md • using_starrocks/Primary_key_table.md • using_starrocks/StarRocks_Audit_Loader.md • using_starrocks/Statistics.md • using_starrocks/Working_with_Paimon.md • administration/cluster_management/deployment/deploy_starrocks_with_operator.md
• administration/management/FE_BE_Configuration.md • administration/management/Session_variables.md • administration/Common_Slow_Query_Scenarios.md • administration/Execution_Plan.md • deployment/on-premise_deployment.md • faq/Loading_faq.md • loading/Etl_in_StarRocks.md • loading/Loading_faq.md • loading/Routine_Load.md • quick_start/starrocks_manual.md • releasenotes/release-1.12.md • sql-reference/sql-functions/array-functions/array_sum.md • sql-reference/sql-functions/aggregate-functions/min.md • sql-reference/sql-statements/account-management/SET_PASSWORD.md • sql-reference/sql-statements/data-definition/DROP_TABLE.md • sql-reference/sql-statements/data-manipulation/EXPLAIN.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/data_loading/Etl_in_StarRocks.md • using_starrocks/data_loading/Loading_faq.md • using_starrocks/data_loading/Routine_Load.md • using_starrocks/external_table/Kudu_catalog.md • using_starrocks/query_acceleration/Bitmap_Index_usage.md • using_starrocks/query_acceleration/Bloom_Filter_Index_usage.md • using_starrocks/Aggregate_table.md • using_starrocks/Colocated_join.md • using_starrocks/Duplicate_key_table.md • using_starrocks/Iceberg_catalog_Iceberg.md • using_starrocks/Kudu_catalog.md • using_starrocks/Unique_key_table.md • using_starrocks/Working_with_HDFS.md • using_starrocks/Working_with_Kudu.md • administration/cluster_management/deployment/on-premise_deployment.md • administration/management/Query_Queuing.md
• administration/management/Session_Variables.md • administration/Check_slow_queries.md • administration/Query_Profiling_Overview.md • deployment/on-prem_deployment.md • faq/Loading_FAQ.md • loading/Data_Loading_FAQ.md • loading/Load_to_Unique_Key_Table.md • loading/Spark_Load.md • quick_start/starrocks_quickstart.md • releasenotes/release-1.13.md • sql-reference/sql-functions/array-functions/array_sort.md • sql-reference/sql-functions/aggregate-functions/sum.md • sql-reference/sql-statements/account-management/SET_ROLE.md • sql-reference/sql-statements/data-definition/DROP_VIEW.md • sql-reference/sql-statements/data-manipulation/SELECT.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/data_loading/Data_Loading_FAQ.md • using_starrocks/data_loading/Load_to_Unique_Key_Table.md • using_starrocks/data_loading/Spark_Load.md • using_starrocks/external_table/Hudi_catalog_Hudi.md • using_starrocks/external_table/Iceberg_catalog.md • using_starrocks/query_acceleration/Bitmap_index.md • using_starrocks/query_acceleration/Bloom_filter_index.md • using_starrocks/Cost-based_Optimizer.md • using_starrocks/Full_text_search.md • using_starrocks/Hudi_catalog_Hudi.md • using_starrocks/Load_to_Primary_Key_Table.md • using_starrocks/Runtime_filter.md • using_starrocks/Short_Key_Index.md • using_starrocks/Working_with_S3.md • administration/cluster_management/deployment/on-prem_deployment.md • administration/management/Resource_Isolation.md • administration/management/Variables.md
• administration/Optimize_slow_queries.md • administration/Query_Profile.md • deployment/starrocks_aws_ami.md • faq/Table_design_faq.md • loading/Broker_Load.md • loading/Load_to_Aggregate_Table.md • loading/Stream_Load.md • quick_start/starrocks_setup.md • releasenotes/release-1.14.md • sql-reference/sql-functions/array-functions/arrays_overlap.md • sql-reference/sql-functions/aggregate-functions/variance.md • sql-reference/sql-statements/account-management/SHOW_GRANTS.md • sql-reference/sql-statements/data-definition/DROP_MATERIALIZED_VIEW.md • sql-reference/sql-statements/data-manipulation/SHOW_DATABASES.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/data_loading/Broker_Load.md • using_starrocks/data_loading/Load_to_Aggregate_Table.md • using_starrocks/data_loading/Stream_Load.md • using_starrocks/external_table/Delta_Lake_catalog_Delta_Lake.md • using_starrocks/external_table/Lake_Formation_configuration.md • using_starrocks/query_acceleration/Bitmap_Index.md • using_starrocks/query_acceleration/Bloom_Filter_Index.md • using_starrocks/Bitmap_index.md • using_starrocks/Bloom_filter_index.md • using_starrocks/Delta_Lake_catalog_Delta_Lake.md • using_starrocks/Load_to_Unique_Key_Table.md • using_starrocks/StarRocks_Audit_Loader_Usage.md • using_starrocks/Working_with_Azure_Storage.md • using_starrocks/Working_with_GCS.md • administration/cluster_management/deployment/starrocks_aws_ami.md • administration/management/Statistics.md • administration/management/User_privilege.md
• administration/Slow_Query_Analysis_and_Optimization.md • administration/System_Variables.md • deployment/starrocks_operator.md • faq/Upgrade_faq.md • loading/Load_to_Duplicate_Key_Table.md • loading/Loading_FAQ.md • loading/Spark_connector.md • quick_start/starrocks_training.md • releasenotes/release-1.15.md • sql-reference/sql-functions/array-functions/cardinality.md • sql-reference/sql-functions/aggregate-functions/stddev.md • sql-reference/sql-statements/account-management/SHOW_ROLES.md • sql-reference/sql-statements/data-definition/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/data-manipulation/SHOW_TABLES.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/data_loading/Load_to_Duplicate_Key_Table.md • using_starrocks/data_loading/Loading_FAQ.md • using_starrocks/data_loading/Spark_connector.md • using_starrocks/external_table/External_table_configuration.md • using_starrocks/external_table/Glue_catalog.md • using_starrocks/query_acceleration/Bitmap_Index_usage.md • using_starrocks/query_acceleration/Bloom_Filter_Index_usage.md • using_starrocks/Data_recovery_Data.md • using_starrocks/Duplicate_Table.md • using_starrocks/Load_to_Aggregate_Table.md • using_starrocks/Partition_Management.md • using_starrocks/Primary_Key_Table.md • using_starrocks/StarRocks_Audit_Loader_Installation.md • using_starrocks/Vector_Index.md • administration/cluster_management/deployment/starrocks_operator.md • administration/management/System_Variables.md
s
@Rocky Can you select star 
*
both joined tables in a materialized such that when we add a new field to either one of the joined tables, it becomes available to the materialized view and optimizer ? What happens if 2 table have the same column name
r
Yes, you can use 
SELECT *
in an asynchronous Materialized View (MV) definition, but it does not behave as a dynamic schema that automatically inherits new columns in real-time. 1. New Fields and Schema Propagation When you create an MV with 
SELECT *
, StarRocks expands the star into a fixed list of columns based on the base table's schema at the moment of creation. * Static Schema: If you add a new column to a base table later, it will not automatically appear in the MV. The MV's physical schema remains unchanged. * How to Update: To make the new column available in the MV, you must use the following command:
Copy code
sql
    ALTER MATERIALIZED VIEW <mv_name> ACTIVE;
This command "re-activates" the MV by re-parsing its original SQL definition. Because the original definition contained 
SELECT *
, the reactivation process will see the new column and add it to the MV's schema. Note: After this, the MV will likely need a refresh to populate data for that new column. 2. Duplicate Column Names If you join two tables that share a column name (e.g., both have an 
id
or 
event_time
column) and use 
SELECT *
, the 
CREATE MATERIALIZED VIEW
command will fail. * Reason: An asynchronous MV is physically stored as a table. Just like a standard table, it cannot have two columns with the exact same name. * The Fix: You cannot use a naked 
SELECT *
on joined tables with overlapping names. You must either: 1. Explicitly list and alias the conflicting columns: 
SELECT t1.id AS t1_id, t2.id AS t2_id, ...
2. Use 
SELECT t1.*, t2.specific_col
to avoid the overlap. Summary of Best Practices While 
SELECT *
is convenient for one-time setup, it is generally not recommended for Materialized Views in production for two reasons: 1. Optimizer Precision: The StarRocks optimizer works best when it knows exactly which columns are in the MV. Including every column (especially large strings or unused data) increases the MV's storage and refresh cost without benefit. 2. Explicit Mapping: Using explicit column names prevents the MV from breaking if a column is added to a base table that happens to conflict with a name in another joined table. References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md (See "Schema change" section) • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md (See 
ACTIVE
parameter) • using_starrocks/async_mv/Materialized_view.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md
s
@Rocky Explicit name disambiguation in the MV definition will not confuse the optimizer if the user query selects both table1.some_field and table2.some_field when both are joined ?
r
Explicit name disambiguation (aliasing) in the Materialized View (MV) definition will not confuse the optimizer. In fact, it is the standard and required way to handle joining tables with overlapping column names. StarRocks' optimizer tracks column lineage (provenance). It doesn't just look at the names of the columns in the MV; it understands that "column 
t1_id
in this MV actually contains the data from 
table1.id
." How the Optimizer Handles It When you define your MV:
Copy code
sql
CREATE MATERIALIZED VIEW my_mv AS
SELECT 
    t1.id AS t1_id, 
    t2.id AS t2_id,
    t1.event_time
FROM table1 t1 
JOIN table2 t2 ON t1.id = t2.ref_id;
And then you run a query:
Copy code
sql
SELECT table1.id, table2.id FROM table1 JOIN table2 ...
The optimizer performs a mapping process: 1. Linage Matching: It sees the query is asking for 
table1.id
. 2. Lookup: It checks its internal map of the MV and finds that 
t1_id
in 
my_mv
originates from 
table1.id
. 3. Transparent Rewrite: It transparently replaces the base table scan with a scan of the MV, pulling 
t1_id
to satisfy the request for 
table1.id
. Why This is Safe * Expression Tracking: The optimizer matches based on the underlying expression. If you had 
SELECT (price _ tax) AS total_price
in the MV, a query asking for 
price _ tax
would be rewritten to use the 
total_price
column from the MV regardless of the alias name. * Disambiguation Requirement: Because the MV is a physical table, it must have unique column names. If you didn't alias 
t1.id
and 
t2.id
, the 
CREATE MATERIALIZED VIEW
command would fail immediately with a "duplicate column name" error. Summary Using aliases like 
t1_some_field
and 
t2_some_field
is the best practice for joined MVs. It ensures the MV is valid and allows the optimizer to uniquely identify which base-table data to retrieve for query rewrite. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md (Covers Join rewrite logic) • using_starrocks/async_mv/Materialized_view.md • administration/Query_planning.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
s
got it
@Rocky in the original 
CREATE MATERIALIZED VIEW
of this thread, can we optimize its LEFT JOIN to account for the fact that enrichments happen strictly after the interactions.start_time or it's not important ? The enrichment cannot be before the interaction....
r
In StarRocks, adding a temporal inequality like 
enrichment.time >= interactions.start_time
to your 
LEFT JOIN
is a tradeoff between logical correctness and optimizer flexibility. Here is how to assess if it is "important" or "optimizing" for your Materialized View: 1. Performance: Equi-Join vs. Non-Equi-Join StarRocks is highly optimized for Equi-Joins (using 
=
). * The Hash Join: When you join on an ID (
ON interactions.id = enrichment.interaction_id
), StarRocks builds a hash table. This is extremely fast. * The Inequality Filter: If you add 
AND enrichment.time >= interactions.start_time
, this becomes a Non-Equi-Join component. In StarRocks, this is typically executed as a post-join filter. It doesn't usually speed up the "lookup" process; instead, the system performs the hash join on the ID first and then discards rows that don't meet the time criteria. * Conclusion: It won't necessarily make the refresh faster unless it prevents a massive "fan-out" (where one interaction ID matches thousands of enrichment rows, and the time filter narrows it down significantly). 2. Query Rewrite Impact (Critical) The most important factor for an MV is whether the optimizer can use it to rewrite user queries. * Strict Matching: If your MV definition contains the time inequality, the optimizer can generally only rewrite queries that also contain that exact same logic. * The Risk: If a user runs a simple query like 
SELECT * FROM interactions LEFT JOIN enrichment ON id
, the optimizer cannot use your MV. Why? Because the optimizer doesn't know your business logic ("enrichment always follows interaction"). It only sees that the MV has an extra filter that the query doesn't have, making the MV's data a potential subset of the truth. * Recommendation: If you want the MV to be "universal" for all joins between these two tables, omit the inequality in the MV definition and let the user add it in their query. The optimizer is smart enough to pull the data from the wide MV and then apply the filter. 3. Partition Alignment (The "Real" Optimization) If you want to optimize the performance of the join during the MV refresh, the best practice isn't adding a 
WHERE
or 
AND
condition—it's Partition Alignment: * Ensure both 
interactions
and 
enrichment
are partitioned by time (e.g., daily). * If both tables have data for 
2023-10-01
in the same partitions, StarRocks can perform a Colocate Join or Local Join during the refresh. * This avoids moving data across the network (shuffling), which is a much larger performance win than adding a 
>=
filter. Summary: Should you add it? * Add it if: It is required for correctness (e.g., an interaction ID is reused over time, and you must link to the enrichment that happened specifically after the start time to avoid joining with old, stale enrichment data). * Avoid it if: You are doing it just for "speed." It may actually hinder the optimizer's ability to rewrite simpler queries and won't significantly change the join performance in a distributed hash-join architecture. Best Practice: Define the MV using the simplest Equi-Join possible to maximize its "Query Rewrite" potential, and ensure your base tables use the same partitioning and distribution keys to enable Colocated Joins. References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
s
@Rocky If a user add a column not in the materialized view in its select statement, that will totally prevent query rewrite ?
r
Yes, if a user selects a column that is not included in the Materialized View (MV) definition, it will prevent the query rewrite for that query. The StarRocks optimizer requires the MV to be a "covering" source for the portion of the query it intends to rewrite. Here is a breakdown of why this happens and how to handle it: 1. The "Covering" Requirement For a transparent rewrite to occur, the MV must contain: * All columns in the 
SELECT
list: If your query asks for 
table1.new_col
but the MV only has 
table1.old_col
, the optimizer cannot fulfill the request from the MV's physical storage. * All columns in the 
WHERE
clause: Even if you don't select a column, if you filter by it (e.g., 
WHERE status = 'active'
), that column must be present in the MV's 
SELECT
list so the optimizer can apply the filter to the MV data. * All columns in 
ORDER BY
or `GROUP BY`: Any column used for sorting or grouping must also be available in the MV. 2. No "Join-Back" Capability Unlike some other database engines, StarRocks does not currently support a "Join-Back" (or "Back-Join") rewrite. * What this means: The optimizer will not say: "I'll get 9 columns from the MV and then perform a join back to the base table just to get that 1 missing column." * The Result: If even one required column is missing from the MV, the optimizer will abandon the MV rewrite entirely and fall back to scanning the original base tables. *3. Impact of `SELECT ` As discussed earlier, if you used 
SELECT *
in your MV: * The MV's schema is static (fixed at the time of creation). * If you add a column to the base table, the MV does not see it yet. * A user query selecting that new column will not be rewritten to use the MV. * The Fix: You must run 
ALTER MATERIALIZED VIEW <mv_name> ACTIVE
to refresh the MV's schema and include the new column. Summary of Best Practices * Include Common Filters: Always include columns that are frequently used in 
WHERE
clauses in your MV definition, even if they aren't "business" results. * Use `EXPLAIN`: You can verify if a query is successfully using an MV by running 
EXPLAIN <query>
. If you see the name of your MV in the scan nodes, the rewrite worked. If you see the base table names, it failed. * Schema Evolution: When base table schemas change, remember to re-activate and refresh your MVs to keep them eligible for query rewrites. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md (See "Check whether the query statement... lacks output columns") • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
s
@Rocky What does EXPLAIN display when the query rewrite using the MV was done ?
r
When you run 
EXPLAIN
on a query that has been successfully rewritten to use a Materialized View (MV), there are three key indicators to look for in the output: 1. The Table Name in 
OlapScanNode
 The most direct indicator is that the 
TABLE
field under the 
OlapScanNode
(the part of the plan that reads data) will show the name of your Materialized View instead of the base tables.
Copy code
text
0:OlapScanNode
   TABLE: my_materialized_view  <-- This shows the MV name
   PREAGGREGATION: ON
   partitions=1/1
   rollup: my_materialized_view
2. 
MaterializedView: true
(in Logical Explain) If you use 
EXPLAIN LOGICAL <query>
, StarRocks explicitly adds a flag to the scan node to confirm it is using a materialized view:
Copy code
text
- SCAN [my_materialized_view]
      MaterializedView: true    <-- Confirms rewrite was active
3. Trace the Rewrite (Advanced) If you want to see how the optimizer made the decision or why it chose a specific MV, you can use: 
SET variable trace_log_mode = 1;
Then run your query. This is often too verbose for daily use, but for standard verification, the 
EXPLAIN
output above is the source of truth. Summary of what to look for: * Target Table: In the plan tree, look at the bottom-most nodes (Scans). If they list your MV name instead of 
table1
or 
table2
, the rewrite was successful. * Join Elimination: You will notice that the 
HASH JOIN
or 
NESTED LOOP JOIN
nodes present in a normal plan might disappear entirely if the MV already contains the joined results. If you don't see the MV name: It means the rewrite failed. Common reasons include missing columns in the MV, data not being up-to-date (if 
query_rewrite_consistency
is set to strict), or the MV being in an inactive state. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
s
@Rocky Do we need to enable a setting to get query rewrites ?
r
Yes, query rewrite is generally enabled by default in StarRocks (for version 2.5+), but there are specific scenarios where you must toggle certain settings or properties to make it work. 1. The Main Global Switch * 
enable_materialized_view_rewrite
: This is the primary session variable. It is set to 
true
by default. If it is somehow disabled, no query rewrites will occur.
Copy code
sql
    SET enable_materialized_view_rewrite = true;
2. When Rewriting Views (Disabled by default) If your Materialized View is built on top of Logical Views (not just base tables), rewrite is disabled by default for performance reasons. You must enable it:
Copy code
sql
SET enable_view_based_mv_rewrite = true;
3. When Using External Catalogs If your MV points to data in an external catalog (like Hudi, Iceberg, or JDBC), StarRocks is more cautious about consistency. * Property: You must set 
"force_external_table_query_rewrite" = "true"
in the 
PROPERTIES
block when you create the MV (or via 
ALTER
). * Note: For Hive catalogs, rewrite is typically enabled by default, but for others, it often requires this explicit property. 4. For 
INSERT
Statements (Disabled by default) By default, StarRocks only rewrites 
SELECT
queries. If you want a command like 
INSERT INTO target_table SELECT ...
to be accelerated by an MV, you must enable:
Copy code
sql
SET enable_materialized_view_for_insert = true;
5. Data Consistency Settings Even if rewrite is "enabled," the optimizer will ignore the MV if it thinks the data is stale. You can control how "brave" the optimizer is with this session variable: * 
materialized_view_rewrite_mode
 (v3.2+): * `DEFAULT`: Rewrites only if the MV is determined to be consistent with base tables. * `STALE`: Allows rewrite even if the MV data is not yet synchronized (useful if you prioritize speed over perfect real-time accuracy). Summary Checklist 1. Ensure 
enable_materialized_view_rewrite
is 
true
. 2. If using external tables (except Hive), ensure the MV has the property 
force_external_table_query_rewrite = true
. 3. If your query uses a View, set 
enable_view_based_mv_rewrite = true
. 4. Ensure the MV status is 
ACTIVE
(check via 
SHOW MATERIALIZED VIEWS
). References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • administration/management/FE_configuration.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
s
@Rocky
Copy code
CREATE MATERIALIZED VIEW mv_interaction_enrichment_keyed_on_conversation
    PARTITION BY date_trunc('day', i_start_time)
    DISTRIBUTED BY HASH(i_account_key) BUCKETS 10
    REFRESH ASYNC EVERY (INTERVAL 5 MINUTE)
AS
SELECT
    i.interaction_id    as i_interaction_id,
    i.conversation_id   as i_conversation_id,
    i.account_key       as i_account_key,
    i.start_time        as i_start_time,
    i.participant_type  as i_participant_type,
    i.user_key          as i_user_key,
    i.channel_type      as i_channel_type,
    i.is_originator     as i_is_originator,
    i.end_time          as i_end_time,
    i.direction         as i_direction,

    e.account_key                       as e_account_key,
    e.event_time                        as e_event_time,
    e.entity_id                         as e_entity_id,
    e.entity_type                       as e_entity_type,
    e.channel_type                      as e_channel_type,
    e.csat_result                       as e_csat_result,
    e.csat_score                        as e_csat_score,
    e.outcome                           as e_outcome,
    e.aiqm_score                        as e_aiqm_score,
    e.profanity_detected                as e_profanity_detected,
    e.appointment_opportunity_detected  as e_appointment_opportunity_detected,
    e.appointment_set_detected          as e_appointment_set_detected,
    e.sale_pitch_detected               as e_sale_pitch_detected,
    e.sale_close_detected               as e_sale_close_detected,
    e.ai_sentiment                      as e_ai_sentiment,
    e.aiqm_evaluation_outcome           as e_aiqm_evaluation_outcome,
    e.conversation_direction            as e_conversation_direction
FROM
    interactions i
    LEFT JOIN enrichments e ON
        e.account_key = i.account_key
        AND e.entity_id = i.conversation_id;


explain select count(*) FROM
    interactions i
    LEFT JOIN enrichments e ON
        e.account_key = i.account_key
        AND e.entity_id = i.conversation_id;
The explain does not show MV usage. Why is that ?
r
There are several reasons why the optimizer might skip your Materialized View (MV) even if the SQL structure looks identical. In your case, the most likely culprits are data consistency and MV status. Here are the specific things to check: 1. Data Consistency (The most common reason) By default, StarRocks is very strict about data consistency. Since your MV is set to 
REFRESH ASYNC EVERY 5 MINUTE
, it is almost always "stale" compared to the base tables if they are receiving new data. * The Problem: If even one row was added to 
interactions
or 
enrichments
after the last MV refresh, the optimizer will skip the MV to ensure your query results are 100% accurate. * The Test: Try running your query with this session variable:
Copy code
sql
    SET query_rewrite_consistency = 'loose';
    -- or for newer versions
    SET materialized_view_rewrite_mode = 'stale';
If the 
EXPLAIN
now shows the MV, you know the issue was that the MV data wasn't perfectly synchronized with the base tables. 2. Is the MV "Active"? If an asynchronous refresh fails (e.g., due to a timeout or schema change), StarRocks often marks the MV as Inactive. Inactive MVs are never used for query rewrite. * Check: Run 
SHOW MATERIALIZED VIEWS WHERE name = 'mv_interaction_enrichment_keyed_on_conversation'\G
* Look for: 
is_active
. If it is 
false
, you must re-activate it:
Copy code
sql
    ALTER MATERIALIZED VIEW mv_interaction_enrichment_keyed_on_conversation ACTIVE;
3. Check the Last Refresh State If the MV has never successfully finished its first refresh, it contains no data and cannot be used for a rewrite. * Check: In the 
SHOW MATERIALIZED VIEWS
output, look at 
last_refresh_status
. If it says 
PENDING
or 
FAILED
, the MV is not ready. 4. `count()` and Aggregate Rewrite* While StarRocks can rewrite a 
count(*)
query using a "flat" (non-aggregate) join MV, the optimizer is sometimes more sensitive to this. * The Logic: To count the results of a join using the MV, the optimizer needs to be certain that the MV represents the exact same join result set. * Wait for Refresh: If the MV is currently in the middle of a refresh, it might be locked or considered inconsistent. 5. Partitioning Constraints Your MV is partitioned by 
date_trunc('day', i_start_time)
. * If your query scans the entire table (as your example does, with no 
WHERE
clause), the optimizer has to ensure that all partitions in the MV are refreshed and valid. * If some partitions are "stale" but others are "fresh," the optimizer might decide it's safer to just query the base table. Summary Checklist to fix it: 1. Verify Status: 
SHOW MATERIALIZED VIEWS
(ensure 
is_active
is true). 2. Force Rewrite: 
SET query_rewrite_consistency = 'loose';
and run 
EXPLAIN
again. 3. Check Variable: Ensure 
SET enable_materialized_view_rewrite = true;
(though it's usually on by default). 4. Check for "No-Data": If the tables are empty or the MV hasn't run yet, the optimizer will ignore it. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/System_variable.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
s
@Rocky Would replacing JOIN ON clause with WHERE clause help ?
r
Replacing the 
JOIN ON
clause with a 
WHERE
clause (comma-style join) is unlikely to help and will likely make it harder for the optimizer to rewrite the query. Here is why: 1. Semantic Difference (Inner vs. Outer Join) Your Materialized View is defined using a 
LEFT JOIN
. * If you change your query to use 
FROM interactions i, enrichments e WHERE ...
, you are changing the query to an INNER JOIN. _ While StarRocks _can* sometimes rewrite an Inner Join query using a Left Join MV, it requires the optimizer to perform extra steps (like adding "IS NOT NULL" predicates) to ensure the results are identical. * Matching a 
LEFT JOIN
query to a 
LEFT JOIN
MV is much more direct and reliable for the optimizer. 2. ANSI Syntax is Preferred StarRocks' Cost-Based Optimizer (CBO) is designed to work best with standard ANSI 
JOIN ... ON
syntax. It uses this structure to identify the join graph and match it against the Materialized View's definition. Using the older 
FROM A, B WHERE
syntax requires an extra normalization step that can occasionally lead to the optimizer failing to see the equivalence. 3. Join Predicate vs. Filter Predicate In a 
LEFT JOIN
, the 
ON
clause and the 
WHERE
clause behave very differently: * 
ON
: Determines which rows from the right table are matched. * 
WHERE
: Filters the entire result set after the join. If you put join conditions for the right table in the 
WHERE
clause of a 
LEFT JOIN
, it effectively converts the query into an 
INNER JOIN
, which may not be what you want. Recommendation If you want to "help" the optimizer, you should: 1. Keep the syntax identical to the MV definition. 2. Check for "Data Drift": The most common reason for a rewrite failing in StarRocks (especially with 
ASYNC
MVs) is that the base tables have changed since the last refresh. * Try running: 
SET query_rewrite_consistency = 'loose';
3. Check MV Status: Ensure the MV is 
ACTIVE
by running 
SHOW MATERIALIZED VIEWS
. If a refresh fails, the MV often becomes 
INACTIVE
and will be ignored by the optimizer. In summary: Do not move the join logic to the 
WHERE
clause. Instead, ensure the MV is Active, the data is Refreshed, and your session variables allow for Stale rewrites if the base tables are constantly changing. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
s
@Rocky A create materialized view over tables failed and that was the output:
Copy code
SR StressLoad [root]> show materialized views\G
*************************** 1. row ***************************
                                  id: 40531122
                       database_name: insights
                                name: mv_interaction_enrichment_keyed_on_conversation
                        refresh_type: ASYNC
                           is_active: true
                     inactive_reason:
                      partition_type: RANGE
                             task_id: 40531125
                           task_name: mv-40531122
             last_refresh_start_time: 2026-10-20 02:30:49
          last_refresh_finished_time: 2026-10-20 02:30:51
               last_refresh_duration: 1.018
                  last_refresh_state: FAILED
          last_refresh_force_refresh: false
        last_refresh_start_partition: NULL
          last_refresh_end_partition: NULL
last_refresh_base_refresh_partitions: {}
  last_refresh_mv_refresh_partitions: [p20261019_20261020]
             last_refresh_error_code: -1
          last_refresh_error_message: Refresh mv mv_interaction_enrichment_keyed_on_conversation failed after 1 times, try lock failed: 0, error-msg : com.starrocks.common.DdlException: fail to create tablet: 10002: [create tablet failed. empty store limit in request]
	at com.starrocks.task.TabletTaskExecutor.waitForFinished(TabletTaskExecutor.java:522)
	at com.starrocks.task.TabletTaskExecutor.sendCreateReplicaTasksAndWaitForFinished(TabletTaskExecutor.java:355)
	at com.starrocks.task.TabletTaskExecutor.buildPartitionsSequentially(TabletTaskExecutor.java:114)
	at com.starrocks.server.LocalMetastore.buildPartitions(LocalMetastore.java:2032)
	at com.starrocks.server.LocalMetastore.createTempPartitionsFromPartitions(LocalMetastore.java:5428)
 [wrapped] java.lang.RuntimeException: create partitions failed: fail to create tablet: 10002: [create tablet failed. empty store limit in request]
	at com.starrocks.server.LocalMetastore.createTempPartitionsFromPartitions(LocalMetastore.java:5434)
	at com.starrocks.load.PartitionUtils.createAndAddTempPartitionsForTable(PartitionUtils.java:65)
What does this error mean?
r
The error message 
fail to create tablet: 10002: [create tablet failed. empty store limit in request]
indicates that the Frontend (FE) cannot find any suitable storage locations (Backends/BEs) to create the data shards (tablets) for your Materialized View. In StarRocks, an asynchronous MV refresh often involves creating temporary partitions and tablets. If all your BE nodes are considered "full" or "unhealthy" by the FE's resource manager, the request fails with this "empty store limit" error. Common Causes 1. Disk Space Threshold Reached (Most Common) StarRocks has safety limits to prevent disks from filling up completely. By default: * 
storage_flood_stage_usage_percent
: If a BE disk is more than 95% full, it enters a "flood stage" and will reject all new tablet creation and load requests. * 
storage_flood_stage_left_capacity_bytes
: If a BE disk has less than 100 GB left, it will also trigger this state. * 
storage_usage_soft_limit_percent
: If disk usage exceeds 90%, the FE will stop balancing new data into that directory. 2. High Replica Count or Low BE Node Count If you have a high replication factor (e.g., 
REPLICATION_NUM = 3
) but only a few BE nodes, and some of those nodes are over the threshold, the FE may not be able to find enough distinct nodes to satisfy the replica requirements. 3. BE Nodes are Offline If BE nodes are down or flapping, they are removed from the pool of available storage, leading to an "empty" list of valid stores. How to Fix 1. Check BE Storage Status Run this command to see the disk usage of your backends:
Copy code
sql
SHOW BACKENDS\G
Look for these columns: * 
AvailCapacity
: How much space is left. * 
UsedPct
: Percentage of disk used. * 
Alive
: Must be 
true
. If 
UsedPct
is near or above 90-95%, you must either delete old data, add more disk space/BE nodes, or temporarily adjust the safety thresholds. 2. Clean up Disk Space * Delete unused tables or old partitions. * Check if there are many large logs in the BE 
log/
directory. * Check for "trash" files that haven't been cleaned up yet in the BE storage path. 3. Temporarily Adjust Thresholds (Emergency Only) If you have a bit of headroom and just need the MV to finish, you can increase the limit (not recommended for long-term use):
Copy code
sql
-- Increase the flood stage limit on FE (runtime change)
ADMIN SET FRONTEND CONFIG ("storage_usage_hard_limit_percent" = "98");
_Note: You may also need to adjust 
storage_flood_stage_usage_percent
in the 
be.conf
of each BE node and restart them if the BE-side hard limit is being triggered._ 4. Verify MV Replication Property Check if your MV was created with a replication number higher than your available "healthy" BE count:
Copy code
sql
SHOW CREATE MATERIALIZED VIEW mv_interaction_enrichment_keyed_on_conversation;
If you only have 3 BEs and one is full, and your replication is set to 3, the FE cannot fulfill the request. Summary Checklist 1. Run 
SHOW BACKENDS
to confirm if any disks are > 90% full. 2. If disks are full, add storage or delete data. 3. Check 
FE.log
for lines containing 
is filtered by
to see the exact reason why the FE rejected specific BE nodes. References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • administration/management/resource_management/resource_group.md • faq/Others.md References • administration/management/FE_configuration.md • administration/management/BE_configuration.md
s
@Rocky Our BE nodes have 384 GiB volume attached and we have 
storage_flood_stage_left_capacity_bytes = 107374182400
and 
storage_min_left_capacity_bytes = 214748364800
Can we lower those to say 64 GiB so we can accomodate more tablets
r
Yes, you can and should lower these values for your specific hardware. On a 384 GiB volume, the default 
storage_min_left_capacity_bytes
of 200 GiB is extremely aggressive—it effectively prevents StarRocks from using more than 48% of your disk space. Once you have used ~184 GiB, the system will refuse to create new tablets (like those needed for your Materialized View), leading to the 
empty store limit
error you saw. Recommended Settings for 384 GiB Disks Lowering these to 64 GiB (approx. 16% of your disk) is a safe and common practice for volumes of this size. Parameter | Purpose | Recommended for 384GB Soft Limit (
storage_min_left_capacity_bytes
) | Stops creating new tablets or balancing data here. | 68719476736 (64 GiB) Hard Limit (
storage_flood_stage_left_capacity_bytes
) | Rejects all Loads/MVs (Flood Stage). | 42949672960 (40 GiB) How to apply the changes You must update these settings on both the Frontends (FE) and Backends (BE) to keep them in sync. 1. Update the Frontends (Runtime) Run these commands in your SQL client. These take effect immediately without a restart:
Copy code
sql
-- Set Soft Limit (default was 200GB)
ADMIN SET FRONTEND CONFIG ("storage_usage_soft_limit_reserve_bytes" = "68719476736");

-- Set Hard Limit (default was 100GB)
ADMIN SET FRONTEND CONFIG ("storage_usage_hard_limit_reserve_bytes" = "42949672960");
2. Update the Backends Modify the 
conf/be.conf
file on every BE node so the changes persist after a restart:
Copy code
properties
storage_min_left_capacity_bytes = 68719476736
storage_flood_stage_left_capacity_bytes = 42949672960
Note: While these are technically mutable via the BE's HTTP API, updating the config file and restarting is the most reliable method. Important: Why do we keep a buffer? Do not set these to 0. StarRocks requires free disk space for Compaction. * When StarRocks "compacts" data, it reads multiple old files and writes a new, merged file. * If your disk is 99% full, there won't be enough room to write the new merged file, compaction will fail, and eventually, your queries will fail with a "too many versions" error. * 64 GiB is plenty of headroom for compaction on a 384 GiB drive. One Final Check Check your percentage-based limits as well. Ensure they aren't also blocking you: * 
storage_usage_soft_limit_percent
(Default 90% = ~38GB left) * 
storage_usage_hard_limit_percent
(Default 95% = ~19GB left) Since your 64 GiB target is larger than these percentage thresholds, the byte-based limits you are changing now are the ones currently causing your MV failure. References • administration/management/FE_configuration.md • administration/management/BE_configuration.md • faq/Others.md References • administration/management/BE_configuration.md • administration/management/FE_configuration.md
s
@Rocky Do we need to change those config parameter on BOTH the BE and FE node types ?
r
Yes, you must change these configurations on both the Frontend (FE) and the Backend (BE). In StarRocks, these two node types work together to manage storage, and if their settings are out of sync, the system may still block operations. Why both? * FE (The Planner): The FE decides where to create new tablets and which nodes are "healthy" enough to receive data. If the FE’s threshold is reached, it will stop scheduling tasks (like Materialized View refreshes) to those BEs, even if the BEs themselves think they have room. * BE (The Executor): The BE performs the actual writing and compaction. It has its own safety checks to protect the local file system. If the FE sends a task but the BE's local threshold is reached, the BE will reject the write. The Parameter Mapping Note that the parameter names differ slightly between the FE and BE. To ensure your 64 GiB change works, set them as follows: Limit Type | ***Frontend (FE) Parameter*** | ***Backend (BE) Parameter*** Soft Limit (Stops balancing) | 
storage_usage_soft_limit_reserve_bytes
| 
storage_min_left_capacity_bytes
Hard Limit (Stops all writes) | 
storage_usage_hard_limit_reserve_bytes
| 
storage_flood_stage_left_capacity_bytes
How to apply the changes 1. On the FE (via SQL):
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("storage_usage_soft_limit_reserve_bytes" = "68719476736");
    ADMIN SET FRONTEND CONFIG ("storage_usage_hard_limit_reserve_bytes" = "42949672960");
2. On each BE (in 
be.conf
):
Copy code
properties
    storage_min_left_capacity_bytes = 68719476736
    storage_flood_stage_left_capacity_bytes = 42949672960
After updating 
be.conf
, you must restart the BE service for the changes to be fully recognized. Pro-Tip: Check the Status After making the changes, you can verify if the FE now sees the nodes as available by running:
Copy code
sql
SHOW BACKENDS\G
If the BE was previously in a "Flood Stage," the error message in the 
last_refresh_error_message
of your Materialized View should clear on the next refresh attempt. References • administration/management/FE_configuration.md • administration/management/BE_configuration.md • faq/Others.md References • administration/management/BE_configuration.md
s
@Rocky For the FE, can we set that in 
fe.conf
file (this is a cluster running on k8s with pod rotations so I don't want to loose any config it is not permanent)
r
Yes, you absolutely can (and should) set these in the 
fe.conf
file to ensure they persist across pod rotations in Kubernetes. In StarRocks, while 
ADMIN SET FRONTEND CONFIG
allows you to change parameters at runtime, these changes are not persistent across restarts. If your FE pod restarts or rotates, it will revert to the values defined in its 
fe.conf
file. How to set this in Kubernetes Depending on how you deployed StarRocks, you should apply the changes to your configuration manifest rather than editing files inside a running pod. If using the StarRocks Operator or Helm You should add the parameters to the 
config
section of your 
StarRocksCluster
custom resource or your Helm 
values.yaml
. Example for Helm/Operator:
Copy code
yaml
starrocksFESpec:
  config: |
    # Existing configs...
    storage_usage_soft_limit_reserve_bytes = 68719476736
    storage_usage_hard_limit_reserve_bytes = 42949672960
If using a ConfigMap manually If you are managing your own 
ConfigMap
for 
fe.conf
, add the lines there:
Copy code
properties
storage_usage_soft_limit_reserve_bytes = 68719476736
storage_usage_hard_limit_reserve_bytes = 42949672960
Summary of Persistence * 
ADMIN SET FRONTEND CONFIG
: Immediate effect, no restart required, but lost on restart. * 
fe.conf
: Requires a restart to take effect, but permanent. Recommendation: 1. Run the 
ADMIN SET FRONTEND CONFIG
commands via SQL first to unblock your Materialized View immediately. 2. Update your Kubernetes YAML (Helm values or Operator CRD) with the same values so that the next time a pod rotates, it starts with the correct settings. References • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/config_vars/ADMIN_SET_CONFIG.md • quick_start/helm.md References • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/config_vars/ADMIN_SET_CONFIG.md • quick_start/helm.md • administration/management/BE_configuration.md References • administration/management/FE_configuration.md • quick_start/helm.md
s
@Rocky About 
storage_usage_hard_limit_reserve_bytes
and 
storage_usage_soft_limit_reserve_bytes
it seems like 
storage_flood_stage_left_capacity_bytes
and 
storage_min_left_capacity_bytes
are aliases according to
Copy code
StarRocks [root]> ADMIN SHOW FRONTEND CONFIG LIKE 'storage_usage%';
+----------------------------------------+-------------------------------------------+--------------+------+-----------+---------+
| Key                                    | AliasNames                                | Value        | Type | IsMutable | Comment |
+----------------------------------------+-------------------------------------------+--------------+------+-----------+---------+
| storage_usage_hard_limit_percent       | [storage_flood_stage_usage_percent]       | 95           | int  | true      |         |
| storage_usage_hard_limit_reserve_bytes | [storage_flood_stage_left_capacity_bytes] | 107374182400 | long | true      |         |
| storage_usage_soft_limit_percent       | [storage_high_watermark_usage_percent]    | 90           | int  | true      |         |
| storage_usage_soft_limit_reserve_bytes | [storage_min_left_capacity_bytes]         | 214748364800 | long | true      |         |
+----------------------------------------+-------------------------------------------+--------------+------+-----------+---------+
Can you confirm I could use 
storage_usage_soft_limit_percent
/ 
storage_usage_hard_limit_percent
on BOTH the FE and BE configs ?
r
Based on the 
ADMIN SHOW FRONTEND CONFIG
output you shared and the StarRocks documentation, here is the confirmation regarding the percentage-based parameters: Summary: Can you use these names on both? No, not exactly. While the FE uses the names you listed, the BE (Backend) uses different names for some and lacks a direct equivalent for others. Limit Type | ***Frontend (FE) Parameter*** | ***Backend (BE) Parameter*** Soft Limit % (90%) | 
storage_usage_soft_limit_percent
| None (BE only uses the byte-based limit) Hard Limit % (95%) | 
storage_usage_hard_limit_percent
| 
storage_flood_stage_usage_percent
────────── Key Details for your BE Config If you want to lower your percentage thresholds on the BE nodes (via 
be.conf
), you must use the BE-specific names: 1. Hard Limit (Flood Stage): Use 
storage_flood_stage_usage_percent
. * On the FE, this is 
storage_usage_hard_limit_percent
. _ _Effect:* If disk usage exceeds this percentage AND the remaining bytes are less than the byte-limit, the BE will reject new loads. 2. Soft Limit: There is no percentage-based parameter in 
be.conf
for the soft limit. * The BE only respects the byte-based 
storage_min_left_capacity_bytes
. * The FE-side 
storage_usage_soft_limit_percent
(90%) is a scheduling policy. The FE uses it to decide which BEs are "too full" to receive new tablet replicas or clones. Since the FE makes these decisions, the BE doesn't need its own percentage-based soft limit. Recommended Configuration Pattern To lower your thresholds to roughly 85% usage (64 GiB free) on your 384 GiB disks, update your configs as follows: 1. FE Config (
fe.conf
)
Copy code
properties
# Byte-based limits
storage_usage_soft_limit_reserve_bytes = 68719476736
storage_usage_hard_limit_reserve_bytes = 42949672960

# Percentage-based limits (Optional, defaults are 90/95)
storage_usage_soft_limit_percent = 85
storage_usage_hard_limit_percent = 90
2. BE Config (
be.conf
)
Copy code
properties
# Byte-based limits
storage_min_left_capacity_bytes = 68719476736
storage_flood_stage_left_capacity_bytes = 42949672960

# Percentage-based limit (Only Hard limit exists on BE)
storage_flood_stage_usage_percent = 90
Why the names are different The "AliasNames" column in your FE output shows the names that the FE uses when it communicates with the BEs. For example, the FE knows that when it talks about its 
storage_usage_hard_limit_percent
, it is referring to the BE's 
storage_flood_stage_usage_percent
. References • administration/management/FE_configuration.md • administration/management/BE_configuration.md • sql-reference/sql-statements/cluster-management/config_vars/ADMIN_SHOW_CONFIG.md References • administration/management/BE_configuration.md • administration/management/FE_configuration.md
Open in Slack
PreviousNext
Powered by