@Rocky explain why this query is so slow if we are using an MV

A query might be slow despite having a Materialized View (MV) for two main reasons: the query is not being rewritten to use the MV, or the MV itself is not optimized. Here is how to troubleshoot and fix it: 1. Check if the query is actually hitting the MV Use the

EXPLAIN

command to see the execution plan:

sql
EXPLAIN SELECT ...;

* Check for the MV Name: Look for the

SCAN

or

OlapScanNode

section. If it shows your base table name instead of the MV name, the query rewrite failed, and StarRocks is querying the raw data. * Check Rewrite Status: If you use

EXPLAIN LOGICAL

, you can see if the optimizer attempted a rewrite. 2. Common reasons for Query Rewrite failure If the query is not using the MV, it is usually due to one of these: * Data Staleness: By default, StarRocks only rewrites queries if the MV data is 100% consistent with the base tables. If the base table has new data but the MV hasn't refreshed, the rewrite will fail. _ _Fix:* Set

PROPERTIES('query_rewrite_consistency'='LOOSE')

on the MV or use

SET dev_mv_rewrite_staleness_second = <seconds>;

to allow slightly stale data. * Inactive State: If a schema change occurred on the base table, the MV may become

INACTIVE

. _ _Fix:* Check state with

SHOW MATERIALIZED VIEWS

and reactivate it using

ALTER MATERIALIZED VIEW <mv_name> ACTIVE;

. * Missing Columns: The MV must include all columns used in the query's

WHERE

,

GROUP BY

, and

SELECT

clauses. If you filter on a column that isn't in the MV's SELECT list, StarRocks often cannot use the MV. * Complex Logic: Some operations, like nested aggregations or complex joins after an aggregation, might not support transparent rewrite. 3. The MV is being used but is still slow If

EXPLAIN

confirms the MV is being scanned but performance is poor: * Partitioning & Bucketing: Ensure the MV is partitioned (especially if the base table is large) and that the

DISTRIBUTED BY

key matches your most frequent query filters to allow for Tablet Pruning. * Index Missing: Just like a table, an MV might need a Bitmap Index or Bloom Filter on high-cardinality columns used in filters. * MV is too large: If your MV is nearly as large as the base table (e.g., it's just a flat table with no aggregation), the performance gain will be minimal. 4. Special Case: External Tables (Data Lake) If your MV is built on an external catalog (like Hive or Iceberg), ensure you have set:

"force_external_table_query_rewrite" = "TRUE"

in the MV properties, otherwise, the optimizer might skip rewriting for safety. Next Step: Run

EXPLAIN ANALYZE <your_query>

and look at the

ScanTime

and

RowsFetched

to see exactly where the bottleneck is within the MV scan. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • knowledge/usage/best-practice.md