StarRocks Materialized Views and Automatic Query Rewrite

You built an asynchronous materialized view in StarRocks for a dashboard that runs the same join every minute. The view refreshes, but EXPLAIN still shows the base table, and TRACE REASON MV prints MV rewrite fail for mv1: Rewrite rollup aggregate failed. StarRocks rewrites a query only when the view's definition matches the query's shape, the view is active, and its data passes the freshness check. This guide covers StarRocks 3.5, 4.0, and 4.1: synchronous or asynchronous views, refresh strategies, views over Hive, Iceberg, Paimon, and Hudi, and how to prove a rewrite with EXPLAIN. For the engine basics, start with our StarRocks architecture and table models guide.

Scope and review date: November 2026. This post covers StarRocks 3.5, 4.0, and 4.1; the latest patches are 4.1.7 (26 October 2026) and 4.0.17 (24 October 2026), per the StarRocks 4.1 release notes. Syntax, defaults, and version numbers were checked against the StarRocks documentation on 9 November 2026. The commands were not run in a lab, so test them on a staging cluster first.

Synchronous or asynchronous materialized view: which one do you need?

Both kinds use CREATE MATERIALIZED VIEW. The synchronous materialized view, also called a rollup, is a special index on one native table. It is updated in the same load that changes the base table. The asynchronous materialized view, available since 2.4, is a separate physical table that a refresh task fills.

-- Synchronous view: no REFRESH or DISTRIBUTED BY clause
CREATE MATERIALIZED VIEW web.pv_site_day AS
SELECT site_id, view_date, SUM(bytes_sent)
FROM web.page_views
GROUP BY site_id, view_date;

-- Building a synchronous view is asynchronous; check its progress
SHOW ALTER MATERIALIZED VIEW FROM web;

Interpretation: the statement only submits the build job. The view can serve queries once SHOW ALTER MATERIALIZED VIEW reports the job as FINISHED. StarRocks builds one synchronous view at a time, so wait for this job before you create the next one.

Synchronous views gained a WHERE clause in 3.1.8 and shared-data support in 3.4.0, according to the CREATE MATERIALIZED VIEW reference. The rest of this post covers asynchronous views.

How do you create an asynchronous materialized view that StarRocks can rewrite?

Write the definition in SPJG form: scans, filters, projections, joins, and one aggregation. Keep every column a query filters or groups on in the SELECT list. A view that groups by region alone cannot answer a query filtered on order_date, because that column is not in the view. Do not put ORDER BY in the defining query. The reference says it blocks SPJG rewrite; use the separate ORDER BY (sort_key) clause to set a sort key instead.

CREATE MATERIALIZED VIEW sales.orders_daily_mv
DISTRIBUTED BY HASH(region)
REFRESH DEFERRED ASYNC
PARTITION BY order_date
PROPERTIES (
    "partition_refresh_number" = "3",
    "excluded_trigger_tables" = "sales.customers"
)
AS
SELECT o.order_date,
       c.region,
       COUNT(*)      AS order_count,
       SUM(o.amount) AS revenue
FROM sales.orders o
JOIN sales.customers c ON o.customer_id = c.customer_id
GROUP BY o.order_date, c.region;

Interpretation: sales.orders must be range-partitioned on order_date so view partitions map to base partitions. DEFERRED skips the first refresh that the default, IMMEDIATE, starts at creation. Loads into customers do not trigger a refresh, but its changes are included when orders next triggers one.

Which refresh strategy should you use: ASYNC, scheduled, or MANUAL?

The CREATE MATERIALIZED VIEW reference defines three refresh schemes. If you omit the clause, the default is MANUAL.

• ASYNC: refresh when a base table's data changes (native tables only).
• ASYNC [START (...)] EVERY (INTERVAL n unit): refresh on a fixed interval, in DAY, HOUR, MINUTE, or SECOND. From 4.1.1, SCHEDULE is accepted as a synonym, and SHOW CREATE MATERIALIZED VIEW prints the SCHEDULE form.
• MANUAL: refresh only when someone runs REFRESH MATERIALIZED VIEW.

-- Change an existing view to an hourly schedule (works on 3.x and 4.x)
ALTER MATERIALIZED VIEW sales.orders_daily_mv
    REFRESH ASYNC START('2026-11-10 00:00:00') EVERY (INTERVAL 1 HOUR);

-- Re-run two days of partitions even if no base data changed
REFRESH MATERIALIZED VIEW sales.orders_daily_mv
    PARTITION START ("2026-10-22") END ("2026-10-24")
    FORCE
    WITH SYNC MODE;

Interpretation: without FORCE, StarRocks compares base partition versions and refreshes only partitions whose data changed. Unchanged partitions appear as SKIPPED task runs. The partition range is ignored for external catalog views: the REFRESH MATERIALIZED VIEW reference says they refresh all partitions.

Partition-level refresh settings

A partitioned view on a partitioned base table refreshes only the partitions that changed. Four properties control how much work one refresh does:

How do you accelerate Hive, Iceberg, Paimon, and Hudi queries with materialized views?

An asynchronous view can read from an external catalog, but the view itself must live in default_catalog. Its data sits in StarRocks storage, so queries get local indexes, partitioning, and bucketing instead of reading files from object storage. The data lake acceleration guide sets the rules: external catalog views cannot refresh on base table change, only on a fixed interval or by hand.

CREATE MATERIALIZED VIEW lake_mv.events_daily_mv
DISTRIBUTED BY HASH(event_type)
REFRESH ASYNC START('2026-11-10 00:00:00') EVERY (INTERVAL 1 HOUR)
PARTITION BY event_date
PROPERTIES (
    "mv_rewrite_staleness_second" = "3600"
)
AS
SELECT event_date, event_type, COUNT(*) AS events
FROM iceberg_catalog.analytics.events
GROUP BY event_date, event_type;

Interpretation: this assumes the Iceberg table is identity-partitioned on event_date. For a days(ts) transform, partition the view by date_trunc('day', ts); bucket and truncate cannot be mapped. The staleness setting keeps the view usable between hourly refreshes.

For Hive, change detection relies on the FE setting enable_background_refresh_connector_metadata (default true since 3.0), which polls the metastore every 10 minutes by default. Rewrite for lake views is controlled by force_external_table_query_rewrite, default true since 3.3. StarRocks does not promise strong consistency between a lake table and its view, so the staleness rules below matter more here.

The StarRocks pages disagree on Paimon partitions (the feature table says "to be supported") and on Hudi rewrite (unsupported in one guide, off by default in another). Rely on EXPLAIN on your version.

When does StarRocks rewrite a query to use a materialized view?

The optimizer compares the structure of the query with the structure of each active view, not the SQL text. The query rewrite guide lists the patterns it can match:

• Join rewrite: inner, cross, outer, semi, and anti joins. A query that joins fewer tables than the view can match if unique_constraints and foreign_key_constraints describe the extra joins (View Delta Join).
• Aggregation rollup: a view grouped by a, b can answer a query grouped by a. Grouping sets, ROLLUP, and CUBE queries are not rewritten.
• Union rewrite: if the view covers only part of the query's range or partitions, StarRocks reads that part from the view and the rest from the base table.
• Nested views: up to nested_mv_rewrite_max_level levels, default 3.
• Text-based rewrite (3.3 and later): matches a query or subquery whose syntax tree equals the view definition, including UNION, window, LIMIT, and CTE shapes.

The guide also lists what blocks a rewrite: non-deterministic functions such as rand() and uuid() or window functions in the query, and views defined with LIMIT, ORDER BY, UNION, EXCEPT, INTERSECT, GROUPING SETS, WITH CUBE, or WITH ROLLUP. Session variables such as enable_materialized_view_union_rewrite and enable_materialized_view_text_match_rewrite (both default true) switch individual rewrite types, so compare them between a BI tool's session and your SQL client.

How do you confirm a query was rewritten with EXPLAIN?

The troubleshooting guide gives the test: if the TABLE field of the OlapScanNode names the view, the query was rewritten. For a synchronous view, the scan node keeps the base table name and shows the view in the rollup: field.

If the plan names orders instead, ask the optimizer why. TRACE REASON MV (3.2.8 and later) gives one line per rejected view. TRACE LOGS MV (3.2 and later) prints the full matching log.

Each line starts with MV rewrite fail for <view>: and a reason, such as a missing column, a rollup function that cannot be derived, or a view that is not fresh enough. If no line mentions your view, it was not a candidate at all: check that it is active and that rewrite is enabled.

How stale can a materialized view be and still answer queries?

By default, a view is used only when its data matches every base table. Two properties change that, and both are set per view:

• query_rewrite_consistency (3.2 and later): checked (default) applies the freshness check; loose skips it; disable turns rewrite off for this view; force_mv (3.5 and later) always uses the view for partitions inside its partition_retention_condition.
• mv_rewrite_staleness_second: with checked, the view stays eligible while the gap between the latest base table change and the view's last complete refresh is within this many seconds.

-- Accept up to 10 minutes of lag for this view
ALTER MATERIALIZED VIEW sales.orders_daily_mv
    SET ("mv_rewrite_staleness_second" = "600");

Interpretation: the comparison is against the latest base table change, not the wall clock. A view whose base tables have not changed stays eligible however old it is. A partial refresh, such as a PARTITION START ... END refresh or one capped by auto_refresh_partitions_limit, does not reset the baseline. 4.1.5 fixed how this baseline is tracked, so older patches can behave differently.

How do you keep refresh jobs away from interactive queries?

Every refresh is an INSERT OVERWRITE. Unless you set the resource_group property, it runs in the system group default_mv_wg. The resource group documentation gives its defaults: cpu_weight 1, mem_limit 80%, concurrency_limit 0, and spill_mem_limit_threshold 80%.

-- Give background refreshes more CPU share on every BE
ALTER RESOURCE GROUP default_mv_wg WITH ('cpu_weight' = '4');

-- Or move one heavy view to its own group (created beforehand)
ALTER MATERIALIZED VIEW sales.orders_daily_mv
    SET ("resource_group" = "rg_mv_heavy");

-- Let a large refresh spill and run longer than the default
ALTER MATERIALIZED VIEW sales.orders_daily_mv
    SET ("session.enable_spill" = "true", "session.insert_timeout" = "7200");

Interpretation: cpu_weight is a CPU share under contention, not a hard cap (called cpu_core_limit before 3.3.5). CREATE RESOURCE GROUP needs at least one classifier, so give a dedicated group one that matches no interactive traffic. The refresh timeout defaults to 1 hour from 3.2. Only one refresh per view runs at a time; a newer pending task shows as MERGED.

How do you monitor and troubleshoot materialized view refreshes?

Two information schema views cover most checks. information_schema.materialized_views holds one row per view with its state and last refresh. information_schema.task_runs holds the history of each refresh task run.

-- Views that cannot serve rewrites, or whose last refresh failed
SELECT TABLE_SCHEMA, TABLE_NAME, IS_ACTIVE, INACTIVE_REASON,
       LAST_REFRESH_STATE, LAST_REFRESH_FINISHED_TIME,
       LAST_REFRESH_ERROR_MESSAGE, TASK_NAME
FROM information_schema.materialized_views
WHERE IS_ACTIVE = 'false' OR LAST_REFRESH_STATE = 'FAILED';

Interpretation: an inactive view is neither refreshed nor used for rewrite, usually after a base table schema change or re-creation. Newer patches add columns, such as LAST_REFRESH_TIME (4.0.14) and LAST_FRESHNESS_CONFIRMED_AT (4.1.4), so run DESC information_schema.materialized_views first and adjust the filter types to match.

-- Refresh history for one view (TASK_NAME from the query above)
SELECT CREATE_TIME, FINISH_TIME, STATE, PROGRESS, ERROR_MESSAGE
FROM information_schema.task_runs
WHERE TASK_NAME = 'mv-112517'
ORDER BY CREATE_TIME DESC
LIMIT 20;

Interpretation: states are PENDING, RUNNING, SUCCESS, FAILED, MERGED, and SKIPPED (no base partition changed). Repeated memory errors point to an unpartitioned or oversized view; timeouts point to session.insert_timeout.

-- Re-activate after fixing the base table schema
ALTER MATERIALIZED VIEW sales.orders_daily_mv ACTIVE;

-- Stop a refresh that is hurting the cluster
CANCEL REFRESH MATERIALIZED VIEW sales.orders_daily_mv;

Interpretation: if ACTIVE does not take effect, drop and re-create the view. To alert on refresh failures and lag, see our StarRocks monitoring and alerting guide.

Frequently asked questions

Key takeaways

Review your StarRocks materialized views with JusDB

JusDB's StarRocks performance tuning team can review your slow dashboard queries, design partitioned materialized views that the optimizer can rewrite, and check refresh schedules and resource groups against your load pattern.

Bring your slowest queries, their EXPLAIN output, your view definitions, and your StarRocks version. Your team approves every production change.

Related reading

• StarRocks vs ClickHouse: architecture, table models, and when to choose each
• StarRocks 4.1: adaptive tablets, range distribution, and the upgrade trade-offs
• StarRocks monitoring and alerting: the production guide
• StarRocks documentation: Query rewrite with materialized views