@Rocky I used to have a query plan use a materialized view instead of a table because the view matched the conditions perfectly. After I started adding updates to the table the view depended on that does not seem to be the case anymore. Would updates to a table (that a materialized view depends on) stop the materialized view from being used in the query planning? And how can I fix that or work around it?

Hi, I'm Rocky! It sounds like you've run into a common scenario with Asynchronous Materialized Views in StarRocks. The short answer is yes: by default, updates to a base table will prevent StarRocks from using a materialized view (MV) for query rewrite. This is because StarRocks prioritizes data consistency. If the base table has been updated but the MV hasn't been refreshed yet, the MV is considered "stale," and the optimizer will fall back to the base table to ensure your query results are accurate. Here is how you can fix or work around this behavior: 1. The Recommended Fix: Refresh the MV The most straightforward solution is to refresh the materialized view so it matches the base table data again.

sql
REFRESH MATERIALIZED VIEW your_mv_name;

You can also set up an automatic refresh strategy (e.g.,

REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)

) so the window of "staleness" is minimized. 2. The "Loose" Consistency Workaround If you don't mind querying slightly stale data in exchange for the performance of the MV, you can change the consistency requirements. You can do this by altering the MV properties: * Set Consistency to LOOSE: This tells StarRocks to use the MV for query rewrite even if the base table has changed since the last refresh.

sql
    ALTER MATERIALIZED VIEW your_mv_name SET ("query_rewrite_consistency" = "loose");

Note: The default is

checked

. * Set a Staleness Threshold: If you want to allow the MV to be used only if it's "not too old," you can use the staleness property (introduced in v3.0+). For example, to allow rewrite if the MV was refreshed within the last 5 minutes:

sql
    ALTER MATERIALIZED VIEW your_mv_name SET ("mv_rewrite_staleness_second" = "300");

3. How to verify the rewrite To see exactly why the optimizer is (or isn't) choosing your MV, you can run an

EXPLAIN

on your query:

sql
EXPLAIN REWRITE SELECT ... FROM your_table;

Look for the

Materialized View Rewrite Success

or

Failure

section in the output. If it fails, it will often provide a reason, such as "materialized view is outdated." Summary of properties Property | Description

query_rewrite_consistency

| Set to

loose

to allow rewrite even if data is inconsistent.

mv_rewrite_staleness_second

| Defines a "grace period" (in seconds) where the MV is still eligible for rewrite after a base table update. If you're on a version later than v3.2, you can also experiment with the session variable

SET materialized_view_rewrite_mode = 'force';

to encourage the optimizer to prioritize the MV, though the consistency properties mentioned above are usually the most effective way to handle update-driven staleness. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/System_variable.md • administration/management/FE_configuration.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md