@Rocky Regarding query rewrite using MVs, after testing numerous scenarios, MVs, queries, and parameter changes, we conclude that query rewrite does not work reliably unless the MV is very simple or the query is an exact match to the MV definition. For real-world analytics workloads with varied query patterns, query rewrite is not a dependable optimization strategy. The UI/upper layer can consume MVs directly as source objects instead of base tables, making the routing explicit and deterministic rather than relying on the optimizer to choose the correct rewrite path. Is this correct or not? What is your comment?

Your observation aligns with the experiences of many users handling complex, high-concurrency production workloads. While StarRocks has invested heavily in a sophisticated transparent query rewrite optimizer, the "reliability" you are questioning is often a trade-off between optimizer flexibility and execution determinism. 1. Why Rewrite Can Feel "Unreliable" Query rewrite is not a simple string match; it is a cost-based optimization that must prove semantic equivalence. It often fails to trigger in real-world scenarios due to: * Consistency Requirements: By default, StarRocks will not rewrite a query if the MV is "stale" relative to the base tables. In high-frequency ingestion scenarios, MVs are often in a state of "refreshing," causing the optimizer to fall back to base tables to ensure correctness. * Optimizer Timeouts: The variable

optimizer_materialized_view_timelimit

(default 1000ms) can cause the optimizer to abandon the rewrite path if the search space (number of candidate MVs vs. query complexity) is too large. * Structural Sensitivity: Standard SPJG rewrites are sensitive to join types and predicates. If a query adds a join or a filter not present in the MV, the optimizer must use "Join Delta" or "Join Derivability" logic, which has stricter mathematical requirements (e.g., 1:1 cardinality preservation). * Unsupported Operators: Operations like window functions (pre-v3.3) or non-deterministic functions (e.g.,

now()

) traditionally block rewrite. 2. Is Your Conclusion Correct? Technically, yes. If your goal is 100% deterministic routing for a critical UI/Application layer, explicitly querying the MV as a source object is a valid and common design pattern. * Explicit Routing (Direct Access): Ensures the query always hits the pre-aggregated data. It shifts the "intelligence" from the StarRocks Optimizer to your application/modeling layer (e.g., dbt or a custom API). * Transparent Rewrite: Is designed for "discovery" and ad-hoc BI tools where users don't know which MVs exist. It is intended to be a "best-effort" acceleration rather than a hard guarantee. 3. Recent Improvements (v3.3+) If you are on an older version, your experience may be improved by Text-based Rewrite (introduced in v3.3.0). This feature compares the Abstract Syntax Tree (AST) of the query against the MV definition: * It supports complex operators like Union, Window functions, and CTEs that standard rewrites often miss. * It is much faster and less sensitive to the "structural proofs" required by the SPJG rewriter. 4. Recommendation * For Fixed Dashboards: If you have a known set of queries and a known set of MVs, explicitly querying the MV is the most robust approach. It eliminates optimizer overhead and avoids the "surprise" of a query suddenly hitting base tables because of a minor metadata change. * For Ad-hoc Analytics: Continue to rely on transparent rewrite, but tune the following to increase "reliability": * Set

query_rewrite_consistency = 'loose'

(if you can tolerate slightly stale data). * Increase

optimizer_materialized_view_timelimit

if your queries are very complex. * Use

EXPLAIN

to debug why a rewrite was rejected (look for

MaterializedView: true

in the plan). In summary: Your approach of using MVs as explicit source objects is a mature production strategy used to guarantee performance when the complexity of the workload outpaces the optimizer's "transparent" heuristics. 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/Materialized_view.md