@Rocky I have some findings for query re-write using materialized views. Please confirm my findings are correct: When MV is created on top of StarRocks iceberg tables, here is the findings of query re-write: 1. If MV is complicated, usually query rewrite does not work even if the query is very similar to the definition of the MV. In this case, query rewrite using MV works only when the query is exactly the same as the definition of the MV. 2. If MV is simple, the query rewrite works when the queries are not very different from the definition of the MV. 3. The following factors do not change the results of the findings: A. table or MV is partition or not; B. table and MV are in the same or different catalogs; C. MV joins tables or not; When MV is created on top of StarRocks native tables, the findings of query re-write as below: 1. MV is created on top of StarRocks native tables, so query rewrite works. 2. MV and tables are all in default_catalog. 3. Join sequence does not matter. 4. LEFT JOIN or INNER JOIN matters, must match with the definition of MV to make query rewrite work.

Hi there! I'm Rocky. I have reviewed your findings regarding query re-write using Materialized Views (MVs) in StarRocks and compared them with the official documentation and technical behavior. Here is a confirmation and some refinement of your findings: 1. MVs on StarRocks Iceberg Tables * Finding 1 (Complicated MVs): You are largely correct. For complex queries (e.g., those containing window functions, CTEs, or multi-layer aggregations), StarRocks often relies on Text-based Rewrite (introduced in v3.3.0). This requires the query's abstract syntax tree (AST) to exactly match the MV's definition or a subquery within it. The documentation explicitly recommends splitting complex MVs into simple nested ones to improve the success rate of the more flexible SPJG (Select-Project-Join-GroupBy) rewrite algorithm. * Finding 2 (Simple MVs): Correct. If the MV follows the SPJG pattern, StarRocks can perform flexible rewrites, such as using a subset of columns, applying additional filters, or performing further aggregations on the MV's results. * Factors (Partitioning, Catalog, Joins): Correct. These factors do not inherently block the rewrite logic, though they affect data consistency checks. For Iceberg, StarRocks can detect partition-level changes (starting from v3.1.4) to ensure the MV is "fresh" enough for a rewrite. 2. MVs on StarRocks Native Tables * Finding 1 (Native Tables): Correct. This is the most optimized path for query rewrite with the strongest consistency guarantees. * Finding 2 (Catalog): Correct. While MVs can reference external catalogs, the asynchronous MVs themselves are internal StarRocks objects typically created within the

default_catalog

. * Finding 3 (Join Sequence): Correct. The StarRocks optimizer is generally robust to join reordering. As long as the tables and join conditions are logically equivalent, the sequence in the query does not need to match the MV definition. * Finding 4 (Join Type - INNER vs LEFT): Actually, they do not always have to match. StarRocks supports a feature called Join Derivability. * An MV defined with a

LEFT OUTER JOIN

can rewrite a query using an

INNER JOIN

(StarRocks will automatically add a

NOT NULL

predicate to the result). * Conversely, an

INNER JOIN

MV can sometimes rewrite a

LEFT OUTER JOIN

query if there are filters in the query that effectively nullify the outer join's behavior. * However, if your MV is "complex" (falling back to text-based rewrite), then the join types must match exactly because the ASTs must match. Summary Comparison Table Feature | Finding | Status | Technical Detail Complex MV Rewrite | Exact Match only | Confirmed | Complex queries fall back to "Text-based Match" which requires AST equivalence. Simple MV Rewrite | Flexible | Confirmed | Uses SPJG algorithm for partial matches, predicate pushdown, etc. Join Sequence | Doesn't matter | Confirmed | Optimizer handles join reordering before matching. Join Type (Inner/Left) | Must match | Refined | For simple SPJG MVs, they can differ (Join Derivability). For complex/Text-based MVs, they must match. For the best results with complex logic, we recommend the Nested MV approach (building simple MVs on top of other MVs), as this allows the flexible SPJG rewriter to work at multiple levels. _Sources: StarRocks Docs - Query rewrite with materialized views_ References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md

Thanks Rocky for confirming my findings. Anyone else has similar findings or experience about StarRocks query re-write using MVs?