http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> i have a select * as a create mater...
# questions-and-troubleshooting
k
@Rocky i have a select * as a create materialized view from an external iceberg table, why is this error occuring: Refresh mv my_mv ailed after 1 times, try lock failed: 0, error-msg : com.starrocks.sql.common.DmlException: Insert has filtered data, txn_id = 1905004, tracking sql = select tracking_log from information_schema.load_tracking_logs where job_id=4114068 at com.starrocks.load.InsertOverwriteJobRunner.executeInsert(InsertOverwriteJobRunner.java:404) at com.starrocks.load.InsertOverwriteJobRunner.doLoad(InsertOverwriteJobRunner.java:183) at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:163) at com.starrocks.load.InsertOverwriteJobRunner.transferTo(InsertOverwriteJobRunner.java:229) at com.starrocks.load.InsertOverwriteJobRunner.prepare(InsertOverwriteJobRunner.java:282) at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:160) at com.starrocks.load.InsertOverwriteJobRunner.run(InsertOverwriteJobRunner.java:148) at com.starrocks.load.InsertOverwriteJobMgr.executeJob(InsertOverwriteJobMgr.java:84) at com.starrocks.qe.StmtExecutor.handleInsertOverwrite(StmtExecutor.java:3058) at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:3200) at com.starrocks.qe.StmtExecutor.handleDMLStmtWithProfile(StmtExecutor.java:3067) at com.starrocks.scheduler.MVTaskRunProcessor.executePlan(MVTaskRunProcessor.java:359) at com.starrocks.scheduler.mv.pct.MVPCTRefreshProcessor.execProcessExecPlan(MVPCTRefreshProcessor.java:172) at com.starrocks.scheduler.MVTaskRunProcessor.doProcessTaskRun(MVTaskRunProcessor.java:326) at com.starrocks.scheduler.MVTaskRunProcessor.retryProcessTaskRun(MVTaskRunProcessor.java:283) at com.starrocks.scheduler.MVTaskRunProcessor.processTaskRun(MVTaskRunProcessor.java:195) at com.starrocks.scheduler.TaskRun.doExecuteTaskRun(TaskRun.java:464) at com.starrocks.scheduler.TaskRun.executeTaskRun(TaskRun.java:424) at com.starrocks.scheduler.TaskRunExecutor.lambda$executeTaskRun$1(TaskRunExecutor.java:66) at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:840)
r
This error occurs because the refresh process for your Materialized View (MV) encountered data in the source Iceberg table that does not comply with the destination MV's schema or constraints. In StarRocks, an asynchronous MV refresh is essentially an 
INSERT OVERWRITE
operation. By default, the system parameter 
mv_refresh_fail_on_filter_data
is set to 
true
, meaning the refresh task will fail if even a single row is "filtered" (rejected) during the load. 1. Identify the Specific Cause To see exactly which rows are failing and why, run the tracking SQL provided in your error message:
Copy code
sql
SELECT tracking_log 
FROM information_schema.load_tracking_logs 
WHERE job_id = 4114068;
The 
tracking_log
will show error details such as: * Column count mismatch: The source and destination columns don't align. * Data type conversion failures: e.g., a string that cannot be converted to a number. * Value too long: Data exceeding the 
VARCHAR
length defined in the MV. * Null values: A source column has 
NULL
values but the MV column was created as 
NOT NULL
. 2. Common Reasons for "Filtered Data" in MVs * Schema Evolution: If the Iceberg table schema was updated after the MV was created, the 
SELECT *
used during the MV's initial creation might no longer match the current structure of the Iceberg table. * Implicit Type Limits: When using 
SELECT *
, StarRocks infers column types for the internal storage. If the source Iceberg table contains strings longer than the inferred 
VARCHAR
limit, rows will be filtered. * Strict Mode: StarRocks enforces strict data quality checks by default during MV refreshes. 3. Recommended Solutions Option A: Recreate or Alter the MV (Best Practice) If the schema has changed, the most reliable fix is to drop and recreate the MV so it picks up the correct schema. To prevent future length issues, explicitly cast columns instead of using `SELECT *`:
Copy code
sql
CREATE MATERIALIZED VIEW my_mv 
REFRESH ASYNC 
AS 
SELECT 
  CAST(col1 AS VARCHAR(65533)) as col1, -- Use large enough limits
  col2, 
  col3 
FROM iceberg_catalog.db.table;
Option B: Allow Filtered Data (Not recommended) If you are okay with the MV skipping "bad" rows and completing the refresh anyway, you can change the FE configuration. * Global change (requires admin):
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("mv_refresh_fail_on_filter_data" = "false");
Note: This will cause the refresh to succeed, but the rows that failed validation will be missing from the MV. Option C: Fix Source Data If the tracking log indicates specific data quality issues (like malformed strings in a date column), cleaning the source Iceberg data will resolve the error. Summary of Debugging Steps 1. Run the 
SELECT ... FROM information_schema.load_tracking_logs
query. 2. Look for "column length too large" or "invalid data for type" in the log. 3. If it's a schema mismatch, recreate the MV. 4. If it's a data quality issue, fix the source data or use 
CAST
in the MV definition. References • administration/management/FE_configuration.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/System_variable.md • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
k
@Rocky a string is too long for a type, what should i use as a text field to support the column: '(length=1281577) is too long. The max length of 'api_attachments' is 1048576.
r
In StarRocks, the 
VARCHAR
and 
VARBINARY
types have a hard limit of 1,048,576 bytes (exactly 1 MB) for internal storage (OLAP engine). Since your data is 1,281,577 bytes (~1.22 MB), it exceeds this limit, causing the refresh to fail with the "filtered data" error. To support text fields larger than 1 MB (up to 16 MB), you should use the 
JSON
 data type. Recommended Solution: Use the 
JSON
Type The StarRocks 
JSON
type supports a maximum length of 16 MB. Even if your data is technically a long string and not a complex object, the 
JSON
type is the only internal data type that can accommodate more than 1 MB. How to fix your Materialized View: You need to recreate the MV and explicitly cast the problematic column to the 
JSON
type instead of using 
SELECT *
.
Copy code
sql
CREATE MATERIALIZED VIEW my_mv 
REFRESH ASYNC 
AS 
SELECT 
    column1,
    column2,
    -- Cast the long string column to JSON to support up to 16MB
    CAST(api_attachments AS JSON) AS api_attachments 
FROM iceberg_catalog.db.table;
Important Considerations * Valid JSON Format: If the data in 
api_attachments
is just raw text and not valid JSON, the 
CAST(... AS JSON)
might fail. If it is raw text, you can try wrapping it as a JSON string (e.g., 
parse_json(concat('"', api_attachments, '"'))
), but this is only recommended if the source data is already JSON-compatible. * Query Restrictions: Be aware that 
JSON
columns have some limitations: * They cannot be used in 
ORDER BY
, 
GROUP BY
, or as 
JOIN
keys. * They cannot be used as partition or bucket keys. * Truncation: If you do not actually need the full 1.2 MB of data for analysis, you can truncate the string to the 1 MB limit to keep it as a `VARCHAR`:
Copy code
sql
    SELECT SUBSTRING(api_attachments, 1, 1048576) AS api_attachments
*Why did 
SELECT *
fail?* When you use 
SELECT *
from an external Iceberg table, StarRocks automatically maps Iceberg's 
string
type to its internal 
VARCHAR
type. By default, it uses a maximum of 1,048,576 bytes. Because StarRocks does not automatically map external strings to 
JSON
, it hits the 1 MB 
VARCHAR
ceiling. References • sql-reference/data-types/string-type/VARCHAR.md • sql-reference/data-types/semi_structured/JSON.md • sql-reference/System_limit.md • faq/table_design_faq.md References • knowledge/usage/best-practice.md • sql-reference/data-types/string-type/VARCHAR.md • sql-reference/System_limit.md • faq/Others.md • faq/table_design_faq.md • sql-reference/data-types/string-type/BINARY.md • sql-reference/data-types/string-type/STRING.md
k
@Rocky how to cast that single column but do select * for the rest of the columns
r
You can achieve this using the 
EXCLUDE
 syntax if you are on StarRocks v4.0 or later. This allows you to select all columns while specifically omitting the one you want to transform, which you can then add back with a 
CAST
. For StarRocks v4.0 and later You can use the 
* EXCLUDE
syntax. This is the cleanest way to handle your Iceberg table with many columns:
Copy code
sql
CREATE MATERIALIZED VIEW my_mv 
REFRESH ASYNC 
AS 
SELECT 
    * EXCLUDE (api_attachments),                 -- Select everything except the long column
    CAST(api_attachments AS JSON) AS api_attachments -- Add it back as a JSON type
FROM iceberg_catalog.db.table;
For StarRocks versions before v4.0 Older versions do not support the 
EXCLUDE
keyword. You must explicitly list all column names in the 
SELECT
statement. Pro-tip to get the column list quickly: Run 
DESC iceberg_catalog.db.table;
in your terminal. You can copy the list of columns from the result, paste them into your 
CREATE MATERIALIZED VIEW
statement, and then just wrap the 
api_attachments
column in a 
CAST
function.
Copy code
sql
-- Manual approach for older versions
CREATE MATERIALIZED VIEW my_mv 
REFRESH ASYNC 
AS 
SELECT 
    col1, 
    col2, 
    col3, 
    CAST(api_attachments AS JSON) AS api_attachments, -- Transform only this one
    col5,
    ...
FROM iceberg_catalog.db.table;
Why this is necessary When StarRocks encounters a 
SELECT *
from an external table, it maps Iceberg strings to 
VARCHAR(1048576)
. Since your data is slightly larger than 1MB, the "Insert has filtered data" error occurs because the internal OLAP engine cannot store a 
VARCHAR
longer than that limit. Using 
CAST(... AS JSON)
bypasses this because the 
JSON
type supports up to 16MB. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/data-types/semi_structured/JSON.md • sql-reference/data-types/string-type/VARCHAR.md • sql-reference/System_limit.md • administration/management/FE_configuration.md References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md
k
@Rocky why are there instances where MVs are not rewritten correctly when they are healthy and active, and are within staleness thresholds
r
If your Materialized View (MV) is healthy, active, and within its staleness threshold, but still fails to rewrite a query, it is usually due to structural limitations or missing metadata. Here are the most common reasons why rewrite fails in these circumstances: 1. Structural Limitations (The "SPJG" Rule) StarRocks supports query rewrite primarily for SPJG (Select, Projection, Join, Group-by) patterns. Even if the MV is active, it may not rewrite if: * Nested Aggregations: The MV or query uses "aggregation on top of aggregation" (e.g., 
SELECT count(distinct user_id) FROM (SELECT user_id FROM table GROUP BY user_id)
). * Window Functions: Queries or MVs containing window functions (e.g., 
ROW_NUMBER()
) generally do not support transparent rewrite. * Join-Aggregation Order: If the MV joins two tables and then aggregates, but your query aggregates and then joins (or vice versa), the optimizer may not be able to match the structures. 2. Missing "Predicate" Columns For a query to be rewritten, the MV must "contain" the data. A common mistake is omitting columns used in the 
WHERE
or 
ORDER BY
clause from the MV’s 
SELECT
list. * Example: If your query is 
SELECT sum(sales) FROM table WHERE region = 'US'
, but your MV is 
SELECT sum(sales) FROM table GROUP BY category
, the rewrite will fail because the MV does not have the 
region
column to filter on. * Solution: Always include filtering and grouping dimensions in the MV's 
SELECT
clause. 3. Non-Deterministic Functions If your MV or your query contains non-deterministic functions, the optimizer often disables rewrite to prevent inconsistent results. * Examples: 
now()
, 
current_timestamp()
, 
uuid()
, or 
random()
. * If the MV was built using 
WHERE date > now() - interval 1 day
, it is considered non-deterministic. 4. Outer Join Complexity Transparent rewrite is highly sensitive to Join types. * Complex ON predicates: In 
LEFT JOIN
, if the join condition is more complex than simple column equality (e.g., 
ON a.id = b.id AND a.type = 'X'
), the rewrite may fail. * Nullability: If the optimizer cannot prove that a 
JOIN
in the MV produces the same null-padding behavior as the query, it will skip the rewrite. 5. Session Variables & Optimizer Settings Sometimes the optimizer is "aware" of the MV but chooses not to use it because it deems the original query faster or because of session limits. * Rewrite Enable: Ensure 
SET enable_materialized_view_rewrite = true;
is active. * Nested MV Rewrite: If you are trying to rewrite a query using an MV that is itself built on another MV, you must set 
SET enable_rewrite_nested_materialized_view = true;
. 6. External Table Metadata (Iceberg/Hive) Since you mentioned Iceberg earlier: * StarRocks relies on checking the 
snapshot_id
or last modified time of external tables to determine staleness. If the external catalog's metadata is not being updated correctly or StarRocks' metadata cache is out of sync, the optimizer might incorrectly judge the MV as "potentially inconsistent" and skip rewrite even if you think it's within the threshold. How to Debug To see exactly why the optimizer is rejecting the MV, run:
Copy code
sql
SET enable_materialized_view_rewrite_tracing = true;
EXPLAIN LOGICAL <your_query>;
The output (or the FE logs) will often contain a hint like 
Match fail: ...
or explain which part of the query plan failed to map to the MV. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • knowledge/usage/best-practice.md
k
@Rocky how to immediately see if a materizlied rewrite is being done with explain analyze SELECT, or soemthing similar
r
To see if a Materialized View (MV) rewrite is occurring, the most direct and effective tool is the 
EXPLAIN
 command. While 
EXPLAIN ANALYZE
shows execution metrics, the plan structure itself is what confirms the rewrite. 1. The Quickest Method: 
EXPLAIN
 Run 
EXPLAIN
followed by your query. Look for the 
SCAN
 or 
OlapScanNode
 section:
Copy code
sql
EXPLAIN SELECT ... FROM base_table ...;
* Rewrite Successful: You will see the name of your Materialized View in the 
TABLE
or 
SCAN
field instead of the base table. _ _Example Output:* 
OlapScanNode TABLE: my_materialized_view
or 
SCAN [my_materialized_view]
* Rewrite Failed: You will see the name of the original base table. 2. Deep Dive: 
EXPLAIN LOGICAL
 For a cleaner, high-level view of how the optimizer is mapping your query, use:
Copy code
sql
EXPLAIN LOGICAL SELECT ... ;
This output is often easier to read. Look for 
SCAN [your_mv_name]
. If you see the base table name here, the rewrite did not trigger. 3. Seeing the "Why" (Rewrite Tracing) If you are within staleness thresholds and "healthy" but the rewrite still isn't happening, you can force the optimizer to explain its reasoning. This is the most powerful debugging tool for MVs:
Copy code
sql
-- 1. Enable tracing for your current session
SET enable_materialized_view_rewrite_tracing = true;

-- 2. Run EXPLAIN (not Analyze)
EXPLAIN SELECT ... ;

-- 3. Check the "REWRITE TRACE" section at the end of the output
The REWRITE TRACE will list each candidate MV and exactly why it was rejected (e.g., "predicates not matched," "required columns missing," or "expression mapping failed"). 4. 
EXPLAIN ANALYZE
vs 
EXPLAIN
 * 
EXPLAIN
: Shows what the optimizer intends to do (including rewrites). Use this to verify the rewrite logic. * 
EXPLAIN ANALYZE
: Actually executes the query and shows where the time was spent. While it will show the MV name in the scan nodes, it is slower because it must wait for the query to finish. Summary Checklist Command | What it tells you 
EXPLAIN <query>
 | Did it rewrite? (Check 
TABLE
field) 
EXPLAIN LOGICAL <query>
 | Clearer structural view of the rewrite. 
SET ...tracing = true
 | Why did the rewrite fail? References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/cluster-management/plan_profile/EXPLAIN.md 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
k
@Rocky would materialized views be used if a query consists a mix of base tables and existing valid mvs for joins?
r
Yes, StarRocks can and will use Materialized Views (MVs) to rewrite a query even if that query already contains a mix of base tables and other MVs in its 
JOIN
clauses. The optimizer treats the query as a tree of operations and attempts to match "sub-trees" with available MVs. Here is how it handles different "mixed" scenarios: 1. Rewriting the Base Table portion If your query is 
SELECT ... FROM base_table JOIN existing_mv
, and you have another MV (let's call it 
mv_for_base
) that covers the data in 
base_table
, the optimizer can rewrite the 
base_table
part to use 
mv_for_base
instead. * Resulting Plan: 
mv_for_base JOIN existing_mv
2. Rewriting the Entire Join (Nested Rewrite) If you have a large MV (let's call it 
mv_wide
) that was built by joining 
base_table
and 
existing_mv
, StarRocks can replace the entire join in your query with a single scan of 
mv_wide
. * Requirements: You must have 
nested_mv_rewrite_max_level
set to a value greater than 1 (default is 3) to allow the optimizer to "look through" MVs to match them against other MVs. 3. View Delta Join Rewrite If your query joins 
base_table
and 
mv_A
, but you have an 
mv_B
that joins 
base_table
, 
mv_A
, and 
table_C
, StarRocks can still use 
mv_B
to fulfill the query by "dropping" the extra data from 
table_C
(provided the join to 
table_C
doesn't change the row count, such as a 1:1 inner join or a left join). Key Settings for Success To ensure these complex rewrites work correctly, check these session variables: * 
enable_materialized_view_rewrite = true
: The master switch for rewrite. * 
nested_mv_rewrite_max_level = 3
: Allows MVs built on other MVs (or queries joining MVs) to be rewritten. * 
enable_view_based_mv_rewrite = true
: (StarRocks v3.1+) Specifically helps when MVs are built on top of logical Views or other MVs. How to Verify As mentioned before, use 
EXPLAIN
to see the final plan. If the rewrite is working across the "mix," you will see multiple 
OlapScanNode
entries pointing to different MVs, or a single 
OlapScanNode
representing a larger MV that replaced the join. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
k
@Rocky what could be casuing 
Unknown thread id
 errors for my exclusive cpu resoruce group
r
The error 
Unknown thread id
 (MySQL error code 1094) typically occurs when a client application or the StarRocks Frontend (FE) attempts to perform a management action—such as 
KILL QUERY
 or a timeout-driven abort—on a connection or thread ID that is no longer recognized as active by the system. When specifically using exclusive CPU resource groups (
exclusive_cpu_cores
), this error is often caused by one of the following three scenarios: 1. Race Conditions during Query Completion Exclusive resource groups run queries in dedicated thread pools (
pip_exec_<rg_id>
) that are isolated from the shared pool. * The Cause: If a query finishes execution in the isolated pool just as a timeout or a 
KILL
command is issued, the FE may have already cleared the query from its active registry. When the command arrives, the FE looks for the ID and, finding nothing, returns "Unknown thread id." * Why it's more common in Exclusive Groups: The coordination between specialized worker pools and the FE can sometimes have slightly different timing characteristics than the standard shared pools, making race conditions more apparent. 2. Failure in Thread Binding (Environment Limitations) Exclusive resource groups use hard CPU isolation, which requires the StarRocks Backend (BE) to pin threads to specific CPU cores using Linux 
cpusets
. * The Cause: If StarRocks is running in a containerized environment (like Kubernetes or Docker) without sufficient permissions or if the 
cgroup
hierarchy is restricted, the BE may fail to correctly bind or register these threads. * The Result: The system may attempt to interact with a "thread" that was never properly initialized or tracked within that isolated group, leading to the "Unknown" error. * Verification: Check your BE logs (
<http://be.INFO|be.INFO>
) for warnings like 
failed to bind cpu
or 
resource group init failed
. 3. JDBC Driver or Connection Pooler Behavior Many applications use connection pools (like HikariCP) that automatically issue a 
KILL
command if a query exceeds a client-side timeout. * The Cause: If the resource group is heavily loaded and the query is "queued" or "throttled" within its exclusive core limit, the client might time out first. When it sends the 
KILL
command, if the query hasn't technically started or has just failed due to resource exhaustion, you will see "Unknown thread id." Troubleshooting Steps 1. Check FE Audit Logs: Look at 
fe.audit.log
to see if the query associated with that thread ID actually finished successfully or failed with a different error (like 
RESOURCES_EXCEEDED
) right before the "Unknown thread id" appeared. 2. Verify CPU Binding: Run the following SQL to see if threads are correctly bound to the expected cores for your resource group:
Copy code
sql
    SELECT * FROM information_schema.be_threads
    WHERE name LIKE '%pip_exec_<your_rg_id>%';
If 
BOUND_CPUS
is empty or 0, your environment likely doesn't support the hard isolation required by 
exclusive_cpu_cores
. 3. Check for Overloading: If your 
exclusive_cpu_cores
value is too small (e.g., 1 core) and concurrency is high, queries may be timing out before they can even register their thread IDs properly with the FE. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • sql-reference/sql-statements/cluster-management/resource-group/CREATE_RESOURCE_GROUP.md References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md
k
@Rocky i do not think exclusive resource groups are working. i have a 16 CN r8gd.4xlarge cluster, with a resource group exclusively claiming 60% of cpu capacity. while queries from the other shared 40% are heavy scans from ETL, the exclusive resource group is facing timeouts with much lower scans and QPS
r
It is common to see performance interference even with "exclusive" CPU resource groups because CPU isolation does not provide I/O or Network isolation. On an 
r8gd.4xlarge
instance (16 vCPUs), your 60% allocation (approx. 10 cores) only protects the compute cycles. If the other 40% (shared group) is running "heavy scans from ETL," the following factors are likely causing your exclusive group to time out: 1. I/O Bandwidth Contention Resource groups in StarRocks isolate CPU and Memory, but Disk I/O and PCIe bandwidth are shared across the entire node. * The Problem: Heavy ETL scans saturate the local NVMe or EBS bandwidth. Even if your exclusive group has 10 idle CPU cores, its 
pip_scan
threads will be "Blocked" waiting for the disk to return data. * How to check: Look at the query profile of a timed-out query. If the 
ScanTime
 or 
WaitIOTime
 is significantly higher than usual, you are facing I/O contention. 2. CPU Borrowing (The "Soft" Leak) By default, StarRocks allows shared resource groups to "borrow" CPU cores from exclusive groups if they are idle. * The Problem: If your exclusive group isn't 100% busy at every millisecond, the ETL group will jump into those cores. When your exclusive query starts, it has to "evict" those tasks, which causes latency spikes. * The Fix: Disable borrowing to ensure hard isolation:
Copy code
sql
    UPDATE information_schema.be_configs
    SET VALUE = "false"
    WHERE NAME = "enable_resource_group_cpu_borrowing";
3. Thread Binding Failures If you are running in a containerized environment (Kubernetes/Docker), the StarRocks BE might be unable to pin threads to specific cores due to cgroup permission restrictions. * Verification: Run this query to see if your exclusive group actually has cores bound to it:
Copy code
sql
    SELECT BE_ID, NAME, BOUND_CPUS
    FROM information_schema.be_threads
    WHERE NAME LIKE '%_your_rg_id%';
* If 
BOUND_CPUS
is 0 or empty, the isolation is not working. You likely need to grant the BE container 
SYS_ADMIN
privileges or ensure the 
cpuset
cgroup is mounted correctly. 4. The "Unknown thread id" Connection The 
Unknown thread id
error you saw earlier is a direct symptom of these timeouts. 1. The exclusive group's query hangs (likely due to I/O contention from the ETL group). 2. The query exceeds the 
query_timeout
(default 300s). 3. The FE sends a 
KILL
signal to the BE to stop the query. 4. By the time the 
KILL
signal is processed, the query might have already been partially cleaned up or moved out of the execution pool, causing the BE to report that it no longer recognizes that "thread ID." Recommended Actions 1. Check I/O: Monitor 
node_disk_io_time_seconds_total
on your CN nodes during ETL. 2. Verify Hard Limit: Ensure you used 
exclusive_cpu_cores = 10
(integer) rather than 
cpu_weight
. 3. Disable Borrowing: Set 
enable_resource_group_cpu_borrowing = false
. 4. Isolate Scans: If the ETL is against the same tables, consider if the Data Cache is being evicted by the ETL scans, forcing your exclusive group to hit the disk every time. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • administration/management/monitoring/metrics.md References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md References • administration/management/resource_management/resource_group.md
k
@Rocky does this include iceberg on s3? with slow IOTaskWaitTime
r
Yes, this absolutely includes Iceberg on S3, and your observation of high 
IOTaskWaitTime
 is the "smoking gun" that explains why the exclusive CPU group feels like it isn't working. While StarRocks creates dedicated thread pools for exclusive resource groups, it cannot isolate the global shared hardware bottlenecks that S3/Iceberg queries rely on. Here is why your exclusive group is still slow: 1. The 
pip_con_scan
Thread Pool Bottleneck For external tables (Iceberg/S3), StarRocks uses a thread pool called 
pip_con_scan_<rg_id>
. * The Problem: 
IOTaskWaitTime
represents the time a scan task sits in a queue waiting for an available thread in that pool. * Why it's high: Even if you have 60% of the CPUs, if your query is scanning thousands of small Parquet files on S3, it may be generating more "Morsels" (tasks) than your dedicated threads can handle. The tasks are waiting for a thread to become free, even though the CPU itself isn't "overloaded" by other groups. 2. Network and S3 Prefix Throttling (Shared) * The Problem: Your 16 
r8gd.4xlarge
nodes share a fixed amount of network bandwidth. * Contention: If your "heavy ETL scans" in the 40% shared group are pulling massive amounts of data from S3, they can saturate the node's network interface or hit AWS S3 prefix limits (typically 3,500/5,500 requests per second per prefix). * The Result: Your exclusive group's S3 requests get slowed down at the network layer. The 
pip_con_scan
threads remain "occupied" longer while waiting for S3 data, which prevents them from picking up new tasks from the queue, driving up 
IOTaskWaitTime
. 3. Data Cache Contention (The "d" in r8gd) The 
r8gd
instance type has local NVMe SSDs. StarRocks typically uses these for the Data Cache (
enable_scan_datacache = true
) to speed up Iceberg/S3 queries. * The Problem: I/O isolation in StarRocks does not currently extend to the local Disk Cache. * The Conflict: If your ETL group is performing "heavy scans," it is likely writing and reading from those same NVMe drives at high speed. * The Result: When your exclusive group tries to read from the cache, it has to wait for the NVMe controller to handle the ETL group's I/O requests. This manifests as high 
IOTaskWaitTime
or 
ScanTime
. How to Confirm and Fix 1. Check Thread Saturation: Run the following to see if your dedicated threads are actually busy:
Copy code
sql
    SELECT * FROM information_schema.be_threads
    WHERE name LIKE '%pip_con_scan_<your_rg_id>%';
If 
FINISHED_TASKS
is increasing rapidly, your pool is working hard. 2. Check for S3 Throttling: Look for 
HTTP 503 Slow Down
errors in your BE logs (
<http://be.INFO|be.INFO>
). If you see these, your ETL is overwhelming S3 itself. 3. Monitor Data Cache I/O: Use 
iostat -x 1
on the CN nodes during a heavy ETL run. If the local NVMe drives (where the cache is stored) show high 
%util
, the ETL is stealing your disk bandwidth. 4. Tuning: * Increase Concurrency: If the CPU is idle but 
IOTaskWaitTime
is high, try increasing the number of scan threads for that group via 
concurrency_limit
or 
pipeline_dop
. * Disable Cache for ETL: If the ETL data isn't reused, set 
SET enable_scan_datacache = false;
for the ETL jobs to stop them from "polluting" and saturating the NVMe cache disks. References • administration/management/resource_management/resource_group.md • best_practices/query_tuning/query_profile_operator_metrics.md • data_source/catalog/iceberg/iceberg_catalog.md References • administration/management/resource_management/resource_group.md
k
@Rocky why in a sql query explain logical is showing query rewrite for a simple query on a large table, but a mysql client is not:
Copy code
MV rewrite fail for my_mv_2: invalid query plan
r
The reason you see 
MV rewrite fail
 messages in 
EXPLAIN LOGICAL
but not when executing the query normally is due to StarRocks' transparent query rewrite design. 1. Why the MySQL Client doesn't show it StarRocks' Materialized View (MV) rewrite is an optimizer-level optimization. * Transparency: If the optimizer tries to rewrite a query using an MV but fails for any reason (like an "invalid query plan"), it silently falls back to the original query against the base tables. * No Error Reported: Since the original query is still valid, the query completes successfully. The MySQL client only sees the final result set, not the internal "decision-making" log of the optimizer. 2. Why 
EXPLAIN LOGICAL
shows it 
EXPLAIN
(and specifically 
EXPLAIN LOGICAL
) is a diagnostic tool. Its purpose is to show you exactly how the optimizer is thinking. * In newer versions of StarRocks (v3.x+), the optimizer was updated to include these failure reasons in the 
EXPLAIN
output specifically to help developers understand why their MVs aren't being used for acceleration. 3. What "invalid query plan" means The specific error 
MV rewrite fail for my_mv_2: invalid query plan
typically occurs when the optimizer's rewrite engine encounters one of the following: * Non-SPJG Structure: The MV or the query contains operators that the rewrite engine cannot currently handle (e.g., Window Functions, 
LIMIT
, certain nested aggregations, or 
GROUPING SETS
). The rewrite engine only supports SPJG (Select-Projection-Join-Aggregation). * Column Incompatibility: The query requires columns that are not exported by the MV, or the MV contains complex expressions that the optimizer cannot structurally map back to the query. * View Complexity: If the MV is built on top of other complex views, the resulting logical plan might become too deep or "invalid" for the pattern-matching logic used in rewrites. How to get more details If you are on StarRocks v3.2 or later, you should use the 
TRACE
command for a much clearer explanation of the failure:
Copy code
sql
TRACE REASON MV <your_sql_query>;
This will give you a concise list of reasons (e.g., "partition data is not consistent" or "missing required columns") instead of the generic "invalid query plan" message. Recommended Troubleshooting 1. Check MV Status: Ensure the MV is active: 
SHOW MATERIALIZED VIEWS LIKE 'my_mv_2';
. 2. Check Consistency: If the base table has changed but the MV hasn't refreshed, rewrite will fail unless you set 
query_rewrite_consistency = 'loose'
. 3. Simplify: If the query is complex, try rewriting a very simple 
SELECT * FROM table WHERE ...
to see if that triggers the MV. If it does, your original query likely has an unsupported operator. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-statements/cluster-management/plan_profile/EXPLAIN.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • knowledge/usage/best-practice.md References
k
@Rocky running in sql client, query rewrite was successful and was <100ms. the same query ran on etl in a python env ran a iceberg scan ~4 sec. why is one place not re-writing
r
The discrepancy between your SQL client and Python environment usually stems from how the two environments manage session variables, user permissions, or connection protocols (specifically Prepared Statements). Here are the most likely reasons why your Python ETL environment is not triggering the rewrite: 1. Server-Side Prepared Statements (Most Likely) Many Python libraries (like 
SQLAlchemy
or 
mysql-connector-python
) use Server-Side Prepared Statements by default. * The Problem: In some versions of StarRocks, the query rewrite engine for Materialized Views (MVs) is optimized for the text-based 
COM_QUERY
protocol (used by standard SQL clients) but may behave differently or be bypassed during the 
PREPARE
phase of the binary protocol. * The Fix: Force your Python driver to use "client-side" preparation (where the driver interpolates variables into a string before sending) or disable server-side prepared statements in the connection string. * SQLAlchemy/MySQL-connector: Set 
use_pure=True
or 
prepare_metadata_only=False
. * PyMySQL: This driver is "pure Python" and usually does client-side interpolation, which is more likely to trigger the rewrite. 2. Consistency Policy (
query_rewrite_consistency
) StarRocks checks if the MV data is "fresh" relative to the base table. * The Difference: Your SQL client might have a session variable set to 
loose
(allowing stale data), while your Python environment uses the default 
checked
. * The Fix: Explicitly set the consistency to 
loose
in your Python script before running the query to see if it triggers:
Copy code
sql
    SET query_rewrite_consistency = 'loose';
3. MV Status and Permissions The rewrite is "transparent," meaning if the user doesn't have access to the MV, StarRocks will silently fall back to the base table (Iceberg) without throwing an error. * Permissions: Ensure the user account used by the ETL has 
SELECT
privileges on the Materialized View, not just the Iceberg base table. * Active Status: Verify the MV is active. If a schema change occurred on the Iceberg table, the MV might have become inactive. Check this with:
Copy code
sql
    SELECT name, is_active, reason FROM information_schema.materialized_views WHERE name = 'my_mv_2';
4. Database Context (Catalog/DB) If your SQL client is connected to 
default_catalog.my_db
and your ETL script is connected to 
iceberg_catalog.some_db
, the optimizer might not "find" the MV if it isn't in the same catalog/database or if the search path is different. * The Fix: Use fully qualified names in your ETL query (e.g., 
SELECT ... FROM iceberg_catalog.db.table
) to ensure the optimizer can match the table accurately to the MV definition. How to Debug in Python To see exactly why it's failing in the Python environment, wrap your query in an 
EXPLAIN
and print the output from your Python script:
Copy code
python
# In your Python code:
cursor.execute("EXPLAIN SELECT ...")
plan = cursor.fetchall()
for line in plan:
    print(line)
Look for the 
MV rewrite fail
section at the bottom of the logical plan. If it says "partition data is not consistent," then it's a consistency/timing issue. If the MV isn't mentioned at all, it's likely a permission or prepared statement issue. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/prepared_statement.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md References • sql-reference/System_variable.md
k
@Rocky i have connections in the sql client with two users. for my root user query rewrite is working, but the other etl connection has invalid query plan. both users have access to the base table and materialized view, i checked in both connections directly querying the MV is successful. this is not a permission issue, could it be related to resource groups? my etl user is in a resource group. what else could be the issue for rewrite working for one user but not another with both users having access to the base table and MV
r
The fact that the rewrite works for 
root
but fails with an "invalid query plan" for your 
etl_user
(despite having permissions) suggests the issue isn't about whether the user can see the MV, but how the optimizer's context differs between the two sessions. Since your base table is Iceberg on S3, the rewrite logic is significantly more sensitive to metadata consistency and namespace resolution. Here are the most likely causes: 1. Catalog and Database Context (Namespace Mismatch) This is the #1 cause of user-specific rewrite failures. * The Problem: Query rewrite relies on the optimizer matching the "Fully Qualified Name" (FQN) of the tables. If the 
root
user is in the 
default_catalog
and the 
etl_user
is in the 
iceberg_catalog
, the optimizer may resolve the table names differently. * Why it fails: If the MV was created with a query like 
SELECT ... FROM iceberg_catalog.db.table
, but the ETL user is running 
SELECT ... FROM db.table
(while their session is already in the 
iceberg_catalog
), the optimizer sometimes fails to reconcile the internal IDs, leading to an "invalid query plan" during the attempt to stitch the MV into the query. * The Fix: Ensure both users are in the same catalog/database context or use Fully Qualified Names (e.g., 
catalog.db.table
) for both the MV definition and the query. 2. Difference in 
query_rewrite_consistency
 * The Problem: This session variable controls how strictly StarRocks checks if the Iceberg table has changed since the MV was last refreshed. * The Scenario: 
root
may have this set to 
loose
(globally or in a startup script), allowing the rewrite even if the Iceberg metadata is slightly out of sync. Your 
etl_user
likely defaults to 
checked
. * Why it fails: If the Iceberg table has a new snapshot that the MV hasn't processed yet, the 
checked
mode will fail the rewrite. * The Fix: Run this in the ETL session before the query:
Copy code
sql
    SET query_rewrite_consistency = 'loose';
3. Permission to 
information_schema
or Metadata * The Problem: To perform a rewrite, the optimizer must be able to read the metadata of the MV and the base table to verify versions/snapshots. * The Scenario: In some configurations, a regular user may have 
SELECT
on the tables but lacks permission to view the underlying metadata or the database's 
USAGE
property. * The Fix: Ensure the 
etl_user
has 
USAGE
permissions on the Database where the MV resides, not just the MV itself. 4. 
sql_mode
Differences * The Problem: ETL tools often set a different 
sql_mode
(e.g., 
ANSI_QUOTES
, 
PIPES_AS_CONCAT
). * Why it fails: If the 
sql_mode
is different, the logical plan generated for the ETL user might differ structurally from the one generated for 
root
(e.g., how strings or double-quotes are handled). The rewrite engine requires a structural match. * The Fix: Compare 
SELECT @@sql_mode;
in both sessions. They must be identical for the rewrite logic to behave the same way. 5. Resource Group "Short Circuit" While Resource Groups usually affect execution, they can sometimes influence session defaults if you are using Resource Group-level variables (available in some enterprise/custom versions) or if the ETL user is hitting a concurrency/queue limit that causes the FE to time out during the complex planning phase of a rewrite. ────────── How to get the exact answer Run the following specifically as the ETL user in your client:
Copy code
sql
TRACE REASON MV <your_query>;
This command was added in v3.2.8+ and is much more descriptive than 
EXPLAIN LOGICAL
. It will tell you exactly why it failed (e.g., "partition data is not consistent" or "cannot resolve table name"). If you don't have 
TRACE REASON
, compare these two outputs between Root and ETL: 1. 
SELECT @@query_rewrite_consistency, @@enable_materialized_view_rewrite, @@sql_mode;
2. 
SELECT @@catalog, @@database;
References
k
@Rocky
Copy code
SET query_rewrite_consistency = 'loose';
Query 1 ERROR at Line 40: : Getting analyzing error. Detail message: Unknown system variable 'query_rewrite_consistency', the most similar variables are {'query_timeout', 'query_history_load_interval_seconds', 'query_delivery_timeout'}.
r
The "Unknown system variable" error indicates that your StarRocks version is likely earlier than v3.2 (or you are on a specific build where this variable was renamed or not yet included). The session variable 
query_rewrite_consistency
was finalized in the v3.2.x/v3.3.x release cycles. Since your 
root
user successfully triggers the rewrite but the 
etl
user fails with an "invalid query plan", the issue is almost certainly a Metadata Access or Namespace discrepancy between the two users. 1. The "Usage" Permission Gap (Most Common) In StarRocks, query rewrite is a "behind the scenes" operation where the optimizer must inspect the definition of the Materialized View (MV) and the metadata of the base tables (Iceberg). * The Symptom: Your 
etl
user can query the MV directly because they have 
SELECT
permissions. * The Failure: When the optimizer tries to rewrite a base-table query, it must verify that the base table matches the MV. If the 
etl
user has 
SELECT
on the tables but lacks 
USAGE
permission on the Catalog or Database, the optimizer cannot "see" the structural metadata needed to validate the rewrite, resulting in an 
invalid query plan
. * The Fix: Grant the 
USAGE
privilege on the specific catalog and database to the ETL user:
Copy code
sql
    GRANT USAGE ON CATALOG <iceberg_catalog_name> TO USER 'etl_user';
    GRANT USAGE ON DATABASE <database_name> TO USER 'etl_user';
2. Namespace (Catalog/DB) Context Rewrites rely on matching the Fully Qualified Name (FQN) of tables. * If your 
root
user is working in the 
default_catalog
but your ETL connection starts inside the 
iceberg_catalog
, the optimizer may resolve the table names in the query differently than how they are stored in the MV definition. * The Fix: Ensure both users are using the same default catalog/database or use the full FQN (e.g., 
iceberg_catalog.db.table
) in the query. 3. Session Variables (Why root is different) Check if your 
root
user has different session defaults. Run this in both connections and compare:
Copy code
sql
SELECT @@enable_materialized_view_rewrite, @@sql_mode, @@query_timeout;
If 
sql_mode
differs (e.g., one has 
ANSI_QUOTES
and the other doesn't), the logical plan generated for the ETL user might be structurally different from the MV's definition, causing the rewrite to fail. 4. Resource Groups & Plan Timeouts You mentioned the 
etl
user is in a Resource Group. * Resource groups can enforce stricter timeouts. Query rewriting for large tables (especially with Iceberg metadata) is computationally expensive for the Frontend (FE). * If the 
etl
user's resource group is under heavy load or has a very short 
optimizer_materialized_view_timelimit
, the optimizer might "give up" on the rewrite mid-way, resulting in an "invalid" or "incomplete" plan error. * To test: Temporarily move the ETL user out of the resource group or increase the limit:
Copy code
sql
    SET optimizer_materialized_view_timelimit = 5000; -- increase to 5 seconds
Summary Recommendation 1. Check Version: Run 
SELECT current_version();
. If you are below 3.2, 
query_rewrite_consistency
won't work as a session variable. 2. Permissions: Double-check that the ETL user has 
USAGE
 on the Catalog and Database, not just 
SELECT
. 3. Trace: If you are on v3.2+, use 
TRACE REASON MV <your_query>;
in the ETL session. It will give you a specific reason like "user has no privilege" or "schema change detected." References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • data_source/catalog/iceberg/procedures.md
k
@Rocky i am on starrocks 4.1 and my etl use already has all those permissions
r
In StarRocks v4.1, several session variables from previous versions have been moved to Materialized View (MV) properties or replaced by more granular optimizer controls. This explains why your attempt to 
SET query_rewrite_consistency
failed. The "Invalid query plan" for your ETL user (when it works for 
root
) points to a difference in how the optimizer is constrained for that specific user. 1. Version Change: Consistency is now an MV Property In v4.1, 
query_rewrite_consistency
is no longer a session variable. To change the consistency for a query, you must modify the MV itself or ensure the MV's property allows for the data lag: * Check the current property:
Copy code
sql
    SHOW CREATE MATERIALIZED VIEW your_mv_name;
* Update the property (if you want to allow "loose" consistency for everyone):
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SET ("query_rewrite_consistency" = "loose");
2. Optimizer Time Limits (Resource Group Issue) Since your ETL user is in a specific Resource Group, they are likely hitting the 
optimizer_materialized_view_timelimit
. Rewriting queries against Iceberg is metadata-intensive. If the optimizer exceeds this limit, it may return an "invalid query plan" or fallback to the base table. * Root user: Usually bypasses strict resource group limits or has a longer default timeout. * ETL user: If their resource group has restricted CPU or a low timeout, the rewrite process is killed. * The Fix: Increase the optimizer time limit in your ETL script:
Copy code
sql
    SET optimizer_materialized_view_timelimit = 5000; -- Increase to 5 seconds
3. Namespace Resolution (Catalog/DB Context) Even with full permissions, if the ETL user's session is connected to a different default catalog or database than where the MV was created, the rewrite engine in v4.1 can sometimes fail to match the table descriptors. * The Problem: If 
root
runs the query in 
default_catalog.db1
and the MV was defined there, it works. If the ETL user connects via the 
iceberg_catalog
, the optimizer sees the base table but might fail to map it back to the MV in 
default_catalog
. * The Fix: Ensure the ETL script explicitly switches to the database where the MV lives before running the query:
Copy code
sql
    SET catalog default_catalog;
    USE your_mv_db;
4. How to diagnose in v4.1 In v4.1, use the 
materialized_view_rewrite_mode
variable to force the system to tell you exactly what is wrong. Run this as the ETL user:
Copy code
sql
SET materialized_view_rewrite_mode = 'force_or_error';
SELECT ... -- your query
Instead of falling back to a 4-second Iceberg scan, this will force an error message that explicitly states why the rewrite failed (e.g., "partition data is not consistent," "user lacks metadata access," or "optimizer timeout"). Summary Checklist 1. Increase timeout: 
SET optimizer_materialized_view_timelimit = 10000;
2. Set rewrite mode to error: 
SET materialized_view_rewrite_mode = 'force_or_error';
3. Check FQN: Use 
catalog.db.table
for all tables in the query. 4. Resource Group: Check if the ETL user's resource group has 
cpu_core_limit
or 
mem_limit
that is too restrictive for complex FE planning. References • sql-reference/System_variable.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • administration/management/resource_management/Resource_group.md References
k
@Rocky i am still getting either MV rewrite fail for my_mv: invalid query plan or Query 1 ERROR at Line 43: : no executable plan with materialized view for this sql in FORCE_OR_ERROR mode.
r
The error "no executable plan with materialized view... in FORCE_OR_ERROR mode" is the most diagnostic piece of information here. It confirms that the optimizer is explicitly rejecting the Materialized View (MV) for the ETL user's session, even though it works for 
root
. Since you are on a very recent version (you mentioned 4.1, which is likely a developer/nightly build or a typo for a 3.x stable like 3.3), and your ETL user is in a Resource Group, the issue is likely Optimizer Context or Implicit Session Constraints. Here are the four specific reasons this happens for one user but not another: 1. Catalog "Namespace" Mismatch (Most Common) Query rewrite is extremely sensitive to how table names are resolved. * The Scenario: If your 
root
user has 
default_catalog
as their default, but your 
etl
user is connected directly to the 
iceberg_catalog
(via the JDBC string or a 
USE
statement). * The Conflict: If the MV was created as 
SELECT ... FROM iceberg_catalog.db.table
, the optimizer stores the "Fully Qualified Name". If the ETL user runs 
SELECT ... FROM db.table
while already inside the Iceberg catalog, the optimizer sometimes fails to reconcile the internal IDs between the "context-relative" name and the "fully qualified" name in the MV. * The Fix: Force the ETL session to the same catalog as root, or ensure both are using Fully Qualified Names (Catalog.DB.Table) in the SQL. 2. Resource Group Session Variables Resource Groups in StarRocks can automatically override session variables for any user assigned to them. * The Check: Run 
SHOW VARIABLES;
as the ETL user and check if 
enable_materialized_view_rewrite
or 
optimizer_materialized_view_timelimit
have been modified by the group. * The Conflict: If the ETL resource group has a very low 
optimizer_materialized_view_timelimit
, the rewrite (which is expensive for Iceberg metadata) might be timing out and returning an "invalid" or "empty" candidate list. * The Fix: Increase the limit for the session:
Copy code
sql
    SET optimizer_materialized_view_timelimit = 10000;
3. S3/Iceberg Metadata Access (Credential Layer) Even if the ETL user has 
SELECT
permissions in StarRocks, the Frontend (FE) needs to fetch the Iceberg 
metadata.json
from S3 to verify consistency during the rewrite phase. * The Conflict: If your StarRocks cluster uses Session-based Impersonation (e.g., assuming an AWS IAM role based on the user's identity), the 
root
user might have a role that can access the S3 bucket, while the 
etl
user's assumed role cannot. * Why it fails: Direct query works because the Backend (BE) handles the data fetch, but Rewrite fails because the Frontend (FE) fails to fetch the Iceberg snapshot metadata to validate the "Consistency" of the MV. * The Fix: Verify the 
etl
user has permission to access the S3 path of the base table's metadata via the catalog's credential provider. 4. 
sql_mode
Structural Mismatch If your ETL tool (e.g., Airflow, dbt) sets a different 
sql_mode
(like 
PIPES_AS_CONCAT
or 
ANSI_QUOTES
) upon connection, the "Abstract Syntax Tree" generated for the query will not match the one stored in the MV definition. * The Fix: Compare 
SELECT @@sql_mode;
in both connections. If they are different, the rewrite will fail with "no executable plan." ────────── Step-by-Step Diagnosis Run these commands as the ETL user: 1. Check for "Stale" status:
Copy code
sql
    SELECT name, is_active, last_refresh_error FROM information_schema.materialized_views WHERE name = 'your_mv';
If 
is_active
is 
false
, run 
ALTER MATERIALIZED VIEW your_mv ACTIVE;
. 2. Verify Catalog Context:
Copy code
sql
    SELECT current_catalog(), current_database();
Ensure these match what 
root
uses. 3. Use the v3.3+ "Trace" tool (Crucial): Run your query with this prefix:
Copy code
sql
    TRACE REASON MV <your_sql_query>;
In v3.3+, this will output a table explaining exactly why each MV was rejected (e.g., "The data is not consistent", "The user does not have permission", or "The plan is not matched"). Summary Recommendation: The error "no executable plan... in FORCE_OR_ERROR" almost always means the optimizer looked at the MV and said, "I'm not allowed to use this for this session." This is usually due to Namespace mismatch (Catalog) or Consistency check failure (metadata access). Try using the fully qualified name for your tables in the query and see if it resolves. References • administration/user_privs/authorization/privilege_faq.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/Materialized_view.md
k
@Rocky how do i get more details from TRACE REASON MV with this error: MV rewrite fail for my_mv: invalid query plan
r
To get more detailed information when 
TRACE REASON MV
returns the generic "invalid query plan" error, you should use the more verbose tracing modes and diagnostic session variables available in StarRocks v3.x and v4.x. 1. Use 
TRACE LOGS MV
(Most Detailed) While 
TRACE REASON
gives a concise summary, 
TRACE LOGS
provides the internal decision-making process of the optimizer's rewrite engine.
Copy code
sql
TRACE LOGS MV <your_query>;
This will output a step-by-step log showing which MV candidates were considered, which rules were applied, and exactly where the logic failed (e.g., "partition column mismatch," "view lacks required output columns," or "structural mismatch"). 2. Output Traces to 
fe.log
 If the trace output is too long for your SQL client, you can redirect it to the Frontend (FE) log file for easier analysis:
Copy code
sql
SET trace_log_mode = 'file';
TRACE LOGS MV <your_query>;
Then, search your 
fe.log
for the 
FileLogTracer
entry to see the full execution stack. 3. Use 
TRACE ALL MV
 To see everything—including how much time the optimizer spent on each rewrite rule and the specific variables it was checking—use `ALL`:
Copy code
sql
TRACE ALL MV <your_query>;
4. Force a Detailed Error Message The "invalid query plan" message often means the optimizer tried to use the MV but the resulting plan was physically impossible to execute. To get the specific underlying exception, use this mode:
Copy code
sql
SET materialized_view_rewrite_mode = 'force_or_error';
-- Run your query normally (without TRACE)
SELECT ...
This mode forces the FE to return the exact exception that caused the plan to become "invalid," such as a type conversion error or a missing column in the internal plan. Common "Invalid Query Plan" Root Causes If you see this specifically with an ETL user but not root, look for these in the trace: * Mismatched 
sql_mode
: If the ETL user's session has 
ANSI_QUOTES
or 
PIPES_AS_CONCAT
enabled but the MV was created without them, the "Abstract Syntax Tree" (AST) will not match. * Namespace/Catalog Resolution: The trace might show that the ETL user is looking at 
iceberg_catalog.db.table
while the MV is looking at 
default_catalog.iceberg_db.table
. * Missing Output Columns: Ensure the columns used in your query's 
WHERE
or 
GROUP BY
are explicitly part of the 
SELECT
list in the MV definition. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • developers/trace-tools/query_trace_profile.md • sql-reference/System_variable.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • knowledge/usage/best-practice.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
k
@Rocky analyze why this isnt rewriting in my etl user but it is for root: 51ms| [MV TRACE] [PREPARE GLOBAL] Query input tables: [Table [id=104008927, name=curated_video, type=ICEBERG]] 52ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 52ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Session Variables: 52ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_rewrite: true 52ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: true 52ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_union_rewrite: true 52ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_view_delta_rewrite: true 52ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_plan_cache: true 52ms| [MV TRACE] [PREPARE GLOBAL] enable_sync_materialized_view_rewrite: true 52ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_text_match_rewrite: true 52ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_multi_stages_rewrite: true 52ms| [MV TRACE] [PREPARE GLOBAL] optimizer_materialized_view_timelimit: 1000 52ms| [MV TRACE] [PREPARE GLOBAL] materialized_view_join_same_table_permutation_limit: 5 52ms| [MV TRACE] [PREPARE GLOBAL] query_excluding_mv_names: 52ms| [MV TRACE] [PREPARE GLOBAL] query_including_mv_names: 52ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_rule_output_limit: 3 52ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_candidate_limit: 12 52ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_related_mvs_limit: 16 52ms| [MV TRACE] [PREPARE GLOBAL] materialized_view_rewrite_mode: force 52ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 52ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Config Variables: 52ms| [MV TRACE] [PREPARE GLOBAL] enable_experimental_mv: true 52ms| [MV TRACE] [PREPARE GLOBAL] mv_auto_analyze_async: true 52ms| [MV TRACE] [PREPARE GLOBAL] enable_mv_automatic_active_check: true 52ms| [MV TRACE] [PREPARE GLOBAL] skip_whole_phase_lock_mv_limit: 5 52ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_concurrent_prepare: true 52ms| [MV TRACE] [PREPARE GLOBAL] --------------------------------- 52ms| [MV TRACE] [PREPARE GLOBAL] Table curated_video has related materialized views: [starrocks.curated_video_mv_2] 52ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 mvs after user config 52ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 valid mvs after checking valid 52ms| [MV TRACE] [PREPARE GLOBAL] Choose the best 1 related mvs from all 1 mvs because related mv exceeds max config limit 16 52ms| [MV TRACE] [REWRITE curated_video_mv_2] invalid query plan 52ms| [MV TRACE] [PREPARE GLOBAL] There are no valid related mvs for the query plan 52ms| [MV TRACE] [PREPARE GLOBAL] MV rewrite strategy: MvRewriteStrategy{enableMaterializedViewRewrite=false, enableForceRBORewrite=false, enableViewBasedRewrite=false, enableSingleTableRewrite=false, enableMultiTableRewrite=false, mvStrategy=DEFAULT} 59ms| [MV TRACE] [REWRITE TF_MV_TEXT_MATCH_REWRITE_RULE] [InMemo:false] TEXT_BASED_REWRITE matched mvs: [] 59ms| [MV TRACE] [REWRITE TF_MV_TEXT_MATCH_REWRITE_RULE] [InMemo:false] OptToAstMap is empty, no try to rewrite sub-query again 65ms| Query cannot be rewritten, please check the trace logs to find more information. Tracer Cost: 7112us
r
The trace log 
[REWRITE curated_video_mv_2] invalid query plan
at the 52ms mark is the "smoking gun." In StarRocks, this specific error during the rewrite phase almost always means the Optimizer failed to build the internal logical plan of the Materialized View for that specific user. Even though the ETL user has permissions, there is a "Plan Builder" failure happening behind the scenes. Here are the three most likely reasons this happens for an ETL user but not for `root`: 1. Cloud Storage/Iceberg Metadata Access (Most Likely) When StarRocks rewrites a query against an Iceberg table, the Frontend (FE) must fetch the Iceberg metadata (snapshots/manifests) from S3/OSS/HDFS to verify if the MV is consistent with the base table. * The Problem: Your cluster likely uses User Impersonation for the Iceberg catalog. While 
root
uses the cluster's high-level IAM role, your 
etl
user might be mapped to a restricted role or account that doesn't have "Read" access to the S3 bucket where the Iceberg metadata resides. * Why it says "Invalid Plan": Because the FE cannot fetch the Iceberg snapshot, it cannot build the "Scan" part of the MV's internal plan, resulting in an "invalid query plan" exception. * Fix: Ensure the IAM role or storage credential associated with the ETL user has 
s3:GetObject
and 
s3:ListBucket
permissions for the base table's path. 2. Row-Level Security (RLS) or Masking If you have any Row Access Policies or Data Masking applied to the base table 
curated_video
for the ETL user, the rewrite will fail. * The Problem: The MV contains the "full" data. If the ETL user has a policy that says 
WHERE region = 'US'
, the Optimizer sees a structural mismatch between the query (which has a hidden filter) and the MV (which doesn't). * Fix: Check if 
SHOW ROW ACCESS POLICY
or 
SHOW MASKING POLICY
returns anything for the ETL user on the base table. Rewrite is generally disabled if RLS is active on the base table but not the MV. 3. Missing 
SELECT
on Base Table Columns StarRocks requires that the user has 
SELECT
privileges on all columns defined in the MV, even if the user's specific query only uses a few of them. * The Problem: If 
root
created the MV with 50 columns, and the ETL user only has permissions on 10 of them, the Optimizer will fail to "build the plan" for the MV's definition during the rewrite stage because it encounters a permission error for the other 40 columns. * Fix: Ensure the ETL user has 
SELECT
permission on the entire base table, not just specific columns. 4. Search Path / Catalog Context Note the trace says: 
related materialized views: [starrocks.curated_video_mv_2]
. The MV is in the 
starrocks
(internal) database, but the table is 
ICEBERG
. * The Problem: If the ETL user's default catalog is not set to 
default_catalog
, or if they are in a different database context, the name resolution for the MV's internal "Base Table" might fail for them if they don't have the same search path as 
root
. * Fix: Run 
SET catalog default_catalog; USE starrocks;
at the start of the ETL session to align the context exactly with 
root
. Recommendation To see the exact error causing the "invalid query plan," run this as the ETL user:
Copy code
sql
SET materialized_view_rewrite_mode = 'force_or_error';
SELECT ... -- your query
Instead of a generic trace message, this will force the FE to throw the actual exception (e.g., 
Access Denied to S3
, 
Access Denied to Column X
, or 
Unknown Table
). References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
• data_source/catalog/iceberg/iceberg_catalog.md • administration/user_privs/authorization/privilege_faq.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/System_variable.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • data_source/feature-support-data-lake-analytics.md • administration/management/resource_management/Resource_group.md • using_starrocks/async_mv/materialized_view_task_run_details.md • developers/trace-tools/query_trace_profile.md • release_notes/release-2.1.md • knowledge/usage/best-practice.md • data_source/External_table.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/materialized_view_task_run_details.md
k
@Rocky why is my etl user working now with set enable_materialized_view_plan_cache = false;
r
Setting 
enable_materialized_view_plan_cache = false
fixes the issue because it forces the StarRocks Optimizer to rebuild the internal logical plan of the Materialized View (MV) specifically for your ETL user's session, rather than reusing a potentially incompatible cached version. Here is why the cache was likely causing the "invalid query plan" for your ETL user but not for `root`: 1. User/Permission Context Leakage The MV plan cache (enabled by default in v3.x and v4.x) stores the parsed and analyzed logical plan of the MV's SQL definition to speed up the rewrite process. * The Conflict: If the cache was first populated by the 
root
user, that plan may contain metadata or internal column references that the 
root
user has access to, but the 
etl
user does not. * The Result: When the 
etl
user attempts a rewrite, the optimizer pulls the 
root
-generated plan from the cache. During the validation phase, it detects that the 
etl
user cannot "execute" or "see" parts of that cached plan, marks it as "invalid query plan," and aborts the rewrite. 2. Catalog and Namespace Resolution StarRocks plans are sensitive to the "current" catalog and database. * The Conflict: If 
root
has the catalog set to 
default_catalog
but your ETL user is connected via a different catalog (or has a different 
default_catalog
variable set), the cached plan's internal table IDs or resolution paths may not align with the ETL user's session context. * The Result: By disabling the cache, you force the optimizer to resolve the table names (
curated_video
) using the ETL user's specific search path, which creates a plan that is valid for that session. 3. Session Variable Sensitivity (
sql_mode
) The logical plan can change based on session variables like 
sql_mode
(e.g., how it handles double quotes or pipes). * The Conflict: If your ETL tool sets a specific 
sql_mode
that differs from the one used when the MV plan was cached, the cached plan becomes structurally "invalid" for the current session. * The Result: Disabling the cache ensures the plan is built using the ETL user's active 
sql_mode
. 4. Row-Level Security (RLS) If there are any row-level security policies on the base tables, the logical plan for a query will be different for every user. * The Conflict: A cached plan for 
root
(who sees all rows) is physically incompatible with a plan for an 
etl
user who may have a hidden 
WHERE
clause injected by a policy. * The Result: The optimizer identifies this mismatch and rejects the cached plan as "invalid." Recommendation If you want to keep the performance benefits of the cache but avoid this error, ensure the following are identical for both users: 1. Catalog Context: Both users should use the same 
SET catalog ...;
and 
USE ...;
commands. 2. Session Variables: Check 
SHOW VARIABLES LIKE 'sql_mode';
for both users. 3. Permissions: Ensure the ETL user has 
SELECT
privileges on all columns involved in the MV definition, not just the ones in their specific query. Note: If you are on a very recent version (3.3+), you can try setting 
enable_mv_query_context_cache = false
(an FE config) which is a more granular cache, though session variable 
enable_materialized_view_plan_cache = false
is the most direct way to bypass the issue you are seeing. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • administration/management/FE_configuration.md
k
@Rocky how performant is set enable_materialized_view_plan_cache = true
r
Setting 
enable_materialized_view_plan_cache = true
is highly performant because it shifts the heavy lifting of query planning from execution time to one-time setup. In high-concurrency environments or clusters with complex Materialized Views, it can mean the difference between a query spending 100ms in the optimizer vs. just 5ms. What it optimizes Every time a query runs, the StarRocks Optimizer must decide if an MV can rewrite it. Without the cache, the Frontend (FE) must: 1. Parse and Analyze the MV’s SQL definition. 2. Build a Logical Plan for the MV (handling joins, aggregations, and data types). 3. Validate Metadata (permissions, column types, and catalogs). With the cache enabled, StarRocks performs these steps once and stores the resulting logical plan in memory. Subsequent queries skip directly to the "Matching" phase. Performance Gains * Latency Reduction: For complex MVs (e.g., those with 5+ joins or multi-level nested views), building the logical plan can take 20ms to 200ms. The cache reduces this to <1ms. * CPU Efficiency: In a high-QPS (Queries Per Second) environment, re-building the same plan thousands of times consumes significant Frontend CPU. Caching frees up these resources for actual query routing and coordination. * Default Behavior: It is enabled by default in modern versions (v3.x+) precisely because the performance overhead of not having it is too high for production workloads. The Performance Trade-off (Why you had an error) While it is "faster," it is less flexible. * Memory: It consumes FE memory (controlled by 
mv_plan_cache_max_size
, default 1000 plans). * Context Sensitivity: As you discovered, the cache can store a "version" of the plan that is specific to the user who first triggered it (e.g., 
root
). If an 
etl
user has different permissions or catalog settings, the cached plan becomes "invalid" for them. When to leave it ON (Default) * Production BI: Where the same queries (or same patterns) are run thousands of times. * Large-Scale MVs: When your MVs are architecturally complex. When to turn it OFF * Multi-tenant Clusters: If users have wildly different Row-Level Security (RLS) or Catalog permissions on the same tables. * Troubleshooting: As you did, to verify if a rewrite failure is due to a stale or incompatible cached plan. Summary of FE Configurations Parameter | Default | Impact 
enable_materialized_view_plan_cache
| 
true
| Enables the plan cache in the session. 
mv_plan_cache_max_size
| 
1000
| Limits how many MV plans the FE stores in RAM. 
optimizer_materialized_view_timelimit
| 
1000ms
| Stops the rewrite attempt if it takes too long. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • sql-reference/System_variable.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md References • administration/management/FE_configuration.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • sql-reference/System_variable.md
k
@Rocky my MV is a simple select * of a very large dimension table. queries are usually point queries by id if at one point a plan was invalid because of a mv sync issue, then refreshed and succeeded, would this mv query cache invalidate all following queries made for the base table, even now the MV is valid?
r
Yes, if 
enable_materialized_view_plan_cache
is set to 
true
, a previous "invalid" state can technically persist and block your 
etl
user even after the MV becomes valid. Here is why this happens and how it interacts with your sync issues: 1. What exactly is cached? The plan cache doesn't just store the SQL text; it stores the Logical Plan. This is the post-analysis structure that includes internal Table IDs, column mappings, and metadata pointers. * The Problem: If the MV was in a "sync issue" state (e.g., Iceberg metadata was unreachable or the MV was marked 
INACTIVE
), the Optimizer might have cached a "failed" or "incomplete" logical plan. * Context Sensitivity: As discovered, if 
root
triggered the cache during a bad state, that specific "bad" plan is what the 
etl
user retrieves from memory. 2. Does it self-correct after a Refresh? In many cases, no. A 
REFRESH MATERIALIZED VIEW
updates the data and the 
last_refresh_time
, but it does not necessarily invalidate the Logical Plan Cache. _ Because the SQL definition of the MV (the 
SELECT _ ...
) hasn't changed, the FE (Frontend) may assume the cached logical plan is still valid and continue serving it. * The 
invalid query plan
error you saw in the trace is often the result of the Optimizer trying to use that cached plan and failing a "sanity check" (like checking if the current user has permissions to the specific Table IDs stored inside that cached plan). 3. Why 
set enable_materialized_view_plan_cache = false
fixed it By turning the cache off, you forced the StarRocks Frontend to: 1. Bypass the memory entry. 2. Perform a fresh "Analyze" and "Build" of the plan using the 
etl
user's current session permissions and the now-valid Iceberg metadata. 3. Since the "sync issue" was resolved, this fresh build succeeded. How to fix it without keeping the cache off If you want to re-enable the cache for performance but clear the "bad" entries: 1. Re-activate the MV: If the sync issue made the MV inactive, run:
Copy code
sql
    ALTER MATERIALIZED VIEW curated_video_mv_2 ACTIVE;
2. Clear the FE cache: There is no specific SQL command to "flush" just one MV plan from the cache, but you can force an invalidation by slightly altering the MV's properties (which triggers a metadata update):
Copy code
sql
    ALTER MATERIALIZED VIEW curated_video_mv_2 SET ("query_rewrite_consistency" = "checked");
3. Check for User-Specific Issues: Ensure your 
etl
user has 
SELECT
privileges on the entire base table. If the cache was built by 
root
and contains columns the 
etl
user can't see, the cached plan will always be "invalid" for the 
etl
user until the cache is disabled or the permissions are aligned. Summary The cache is designed to be very sticky for performance. If a sync issue or a user-context mismatch "pollutes" the cache entry, it can indeed "invalidate" following queries for that specific user until the entry is evicted or the cache is bypassed. For a simple 
SELECT *
on a large table, the performance hit of leaving 
enable_materialized_view_plan_cache = false
is likely negligible (usually <10ms per query), so keeping it off for your ETL user is a safe and valid workaround. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-functions/meta-functions/inspect_mv_plan.md • sql-reference/System_variable.md
k
@Rocky why would my mv show this with show materialized view: query_rewrite_status UNKNOWN: MV plan is not in cache, valid check is unknown
r
The status 
UNKNOWN: MV plan is not in cache, valid check is unknown
 is the direct result of you setting 
enable_materialized_view_plan_cache = false
. Here is the breakdown of why StarRocks shows this and what it means for your system: 1. Lazy Validation StarRocks does not constantly validate every Materialized View's rewrite capability in the background. Instead, it performs this validation the first time the MV is analyzed for a query and then stores that result in the MV Plan Cache. * When the cache is enabled (
true
), 
SHOW MATERIALIZED VIEWS
reads the status from memory (showing 
VALID
or a specific error). * When the cache is disabled (
false
), there is no memory entry for the FE to check. Rather than performing a heavy "Plan Build" just to show you a status in a list, it simply reports 
UNKNOWN
. 2. Impact on Query Rewrite Seeing 
UNKNOWN
does not mean the MV is broken or that rewrite will fail. * It only means the FE hasn't "pre-verified" the plan in your current session. * When you execute your ETL query, the Optimizer will still build the plan on the fly. If the SQL is valid and the data is fresh, the rewrite will still happen successfully. 3. How to make it show "VALID" If you want to see the status change from 
UNKNOWN
to 
VALID
(or see the actual error), you can force the FE to analyze the MV by running:
Copy code
sql
-- This forces a plan build and populates the status for that session
SELECT inspect_mv_plan('your_mv_name');
Note: If you still have the plan cache disabled, the status may revert to UNKNOWN in the next session because nothing is being persisted. 4. Is this a problem? For your specific case (a simple 
SELECT *
on a large dimension table): * No, it is not a problem. Since you are doing point queries by ID, the overhead of rebuilding the plan without the cache is extremely low (a few milliseconds). * The 
UNKNOWN
status is just an honest reflection that the "Optimizer's cheat sheet" (the cache) for this MV is currently empty. Summary: You are seeing this because you told the system not to cache the plan. As long as your 
EXPLAIN
shows the query is successfully hitting the MV, you can safely ignore the 
UNKNOWN
status in the 
SHOW
output. References • sql-reference/sql-functions/meta-functions/inspect_mv_plan.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/cluster-management/plan_profile/SHOW_MATERIALIZED_VIEWS.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • knowledge/usage/best-practice.md
k
@Rocky i set global enable_materialized_view_plan_cache=false, yet im still seeing queries not using the MV. how long does it take for the setting to take affect
r
Setting 
SET GLOBAL
in StarRocks follows the same logic as MySQL: it updates the global configuration for the cluster, but it does not change variables for existing, active connections. 1. How long does it take to take effect? * Existing Connections: It will never take effect for a connection that was already open when you ran the command. Those sessions keep their original 
true
value. * New Connections: It takes effect immediately for any connection established after you ran the 
SET GLOBAL
command. The Fix: To see the change immediately in your current window, run the command without the 
GLOBAL
keyword:
Copy code
sql
SET enable_materialized_view_plan_cache = false;
────────── 2. Why is the MV still not being used? If you have disabled the cache (at the session level) and the query still won't use the MV, the issue is likely no longer "stale plans" but rather Data Consistency or MV State. Check these three most common culprits: A. Data Freshness (Most Likely) By default, StarRocks is very strict: if the base table has changed by even one row since the last MV refresh, the Optimizer will refuse to use the MV to ensure you don't get "stale" results. * To verify: Try forcing a refresh: 
REFRESH MATERIALIZED VIEW your_mv_name;
* To bypass (Recommended for ETL/Large Dims): Allow the query to use the MV even if the base table has changed:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SET ("query_rewrite_consistency" = "loose");
    -- OR for just your session:
    SET query_rewrite_consistency = 'loose';
B. MV Is Inactive If a background sync failed or a schema change occurred on the base table, StarRocks may have marked the MV as 
INACTIVE
. * Check: 
SHOW MATERIALIZED VIEWS WHERE name = 'your_mv_name';
(Look at the 
is_active
column). * Fix: 
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
*C. The "Select " Limitation Even though your MV is a 
SELECT _
, if your point query refers to a column that was added to the base table _after* the MV was created, the MV technically "lacks" that column and cannot satisfy the query. * Verification tool: Use 
SELECT inspect_mv_plan('your_query_text', 'your_mv_name');
to get a detailed explanation of why the Optimizer rejected the rewrite. Summary Checklist 1. Run 
SET enable_materialized_view_plan_cache = false;
in your current session. 2. Run 
SET query_rewrite_consistency = 'loose';
to ignore data freshness gaps. 3. Run 
EXPLAIN SELECT ...
and look for an 
OlapScanNode
that points to your MV name. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
k
@Rocky should i restart my fe pods to take the new setting
r
You do not need to restart your FE pods to make the 
SET GLOBAL
setting take effect. In StarRocks, 
SET GLOBAL
persists across restarts because it is saved in the cluster's metadata. However, there is a catch regarding when it applies to your queries: 1. Why you might consider a restart (The "Connection Pool" problem) The 
SET GLOBAL
command only affects new connections. * If your application or ETL tool uses a connection pool (which keeps connections open for hours or days), those existing connections will continue to use the old 
true
value indefinitely. * Restarting the FE pods would force all existing connections to close. When they reconnect, they will be forced to pick up the new 
false
global setting. This is the "sledgehammer" approach to ensure every client is aligned. 2. A better alternative to restarting Instead of a full restart, you can do one of the following: * Force a Session Variable: In your ETL script or SQL client, add this line at the very beginning of the script:
Copy code
sql
    SET enable_materialized_view_plan_cache = false;
This overrides the global setting for that specific connection immediately, regardless of what the global value is. * Kill Existing Connections: You can see and kill active connections manually without a restart:
Copy code
sql
    SHOW PROCESSLIST;
    KILL [connection_id];
3. If it's still not working after 
SET ... = false
 If you have verified that the session variable is 
false
(by running 
SELECT @@enable_materialized_view_plan_cache;
) and the MV is still not being used, then the problem is no longer the cache. You likely have a consistency or state issue. Run this command to see exactly why the optimizer is ignoring the MV:
Copy code
sql
-- This will tell you if it's a data freshness issue or a syntax mismatch
SELECT inspect_mv_plan('SELECT ... your query here ...');
Common Reasons for rejection (besides cache): 1. Data is "Stale": If the base table has new data, StarRocks won't use the MV unless you run 
SET query_rewrite_consistency = 'loose';
. 2. MV is Inactive: Check 
SHOW MATERIALIZED VIEWS
and look at the 
is_active
column. If it's 
false
, run 
ALTER MATERIALIZED VIEW <name> ACTIVE;
. Summary: Don't restart the pods yet. First, run 
SET enable_materialized_view_plan_cache = false;
in your current session. If the query still doesn't hit the MV, the FE restart won't help—you'll need to look at 
inspect_mv_plan
to find the real culprit. References • sql-reference/System_variable.md • sql-reference/sql-functions/meta-functions/inspect_mv_plan.md • administration/management/FE_configuration.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md References • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/config_vars/ADMIN_SET_CONFIG.md • sql-reference/System_variable.md
k
@Rocky what is a relaible way to set up monitoring for when we experience materialized view query rewrite failures
r
Monitoring materialized view (MV) query rewrite failures involves three layers: Metrics (for high-level trends), Audit Logs (for specific query identification), and Trace Commands (for root cause analysis). 1. High-Level Monitoring (Prometheus & Grafana) StarRocks (v3.1+) provides specific metrics to track the health of MV rewrites. You must enable these by adding 
?with_materialized_view_metrics=all
to your Prometheus scrape configuration. Key Metrics for Rewrite Success Rate: * 
mv_query_total_count
: Total queries where an MV was considered for a rewrite. * 
mv_query_total_hit_count
: Total queries where the optimizer successfully matched the query to an MV (this doesn't guarantee it was chosen, as cost-based optimization might still prefer the base table). * 
mv_query_total_matched_count
: Total queries where an MV was actually used in the final execution plan. Alerting Logic: Create a Prometheus alert for a "Drop in Rewrite Match Rate." If the ratio of 
matched_count
to 
total_count
drops significantly, it indicates that either the MV is inactive or data is stale.
Copy code
promql
# Alert if the rewrite match rate falls below 50%
(sum(rate(mv_query_total_matched_count[5m])) / sum(rate(mv_query_total_count[5m]))) < 0.5
────────── 2. Detailed Identification (Audit Logs & AuditLoader) To find exactly which queries are failing to rewrite, the most reliable method is to use the AuditLoader plugin to store 
fe.audit.log
in a table. The audit log table (typically 
starrocks_audit_db*.starrocks_audit_tbl*
) contains two critical fields: * 
candidateMVs
: List of MVs the optimizer thought could have worked. * 
hitMVs
: List of MVs actually used in the plan. Monitoring Query: Run this periodic SQL to identify "failed rewrites" where an MV was a candidate but was not used:
Copy code
sql
SELECT 
    queryId, 
    stmt, 
    candidateMVs, 
    hitMVs 
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE candidateMVs IS NOT NULL 
  AND (hitMVs IS NULL OR hitMVs = '');
Tip: You can set up a Grafana table panel using this SQL to track the most frequent rewrite failures. ────────── 3. Root Cause Diagnosis (Manual Tools) Once you identify a query that should be hitting an MV but isn't, use the following manual tools to understand why: * 
TRACE REASON MV <your_query>
: (v3.2.8+) Provides a concise explanation (e.g., "MV is inactive" or "Aggregation mismatch"). * 
SELECT inspect_mv_plan('<your_query>', '<your_mv_name>')
: Gives a detailed breakdown of the matching process and why the optimizer rejected specific MVs. * 
EXPLAIN [LOGICAL] <your_query>
: Look for the 
SCAN [mv_name]
node. If it shows 
SCAN [base_table]
, the rewrite failed. Summary Checklist 1. Configure Prometheus with 
with_materialized_view_metrics=all
. 2. Install AuditLoader and monitor the 
candidateMVs
vs. 
hitMVs
columns. 3. Check MV State: Monitor 
mv_inactive_state
. If it's 
1
, all rewrites for that MV will fail immediately. 4. Check Data Freshness: If your MVs are "Strict" (default), monitor 
information_schema.materialized_views
to ensure 
LAST_REFRESH_STATE
is 
SUCCESS
. References • administration/management/monitoring/metrics-materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • administration/management/monitoring/metrics.md
k
@Rocky for . High-Level Monitoring (Prometheus & Grafana), does this give a breakdown of the MVs bieng used to avoid heavy audit sql queries?
r
Yes, the Prometheus metrics do provide a per-MV breakdown, which allows you to monitor usage and rewrite health without having to query the heavy audit logs for every investigation. When you enable the detailed metrics (
with_materialized_view_metrics=all
), the following metrics are exposed with labels like 
database
 and 
name
 (the MV name): 1. The Per-MV "Usage" Breakdown Instead of just a global counter, these metrics are broken down by each individual MV in your cluster: * 
mv_query_total_matched_count
: How many times this specific MV was part of the final execution plan. This is your "true" hit rate. * 
mv_query_total_considered_count
: How many times this MV successfully rewrote a query (even if the optimizer ultimately chose a different path). * 
mv_query_total_hit_count
: How many times the optimizer matched the query syntax to this MV. 2. Global Success vs. Failure (The "High-Level" view) If you want to see the "health" of the rewrite engine overall, look at: * 
mv_global_query_rewrite_queries_total
: This is a global counter that uses a 
state
label (
HIT
or 
NOT_HIT
). * Alerting Idea: If 
state="NOT_HIT"
starts spiking while your query volume is stable, it’s a sign that your MVs have become stale or inactive. 3. Monitoring "Inactive" MVs You can also monitor the status of all MVs directly via Prometheus to see which ones are failing: * 
mv_inactive_state
: A gauge metric (0 = Active, 1 = Inactive). * Grafana Tip: Create a dashboard panel showing 
mv_inactive_state{name!=""} == 1
. This immediately lists all MVs that are currently broken and cannot be used for rewrites. Comparison: Metrics vs. Audit Logs Feature | Prometheus Metrics | Audit Logs (AuditLoader) Granularity | Per MV (counts/rates) | Per Query (SQL text, QueryID) Overhead | Low (numeric scraping) | Higher (storage of query strings) Use Case | Real-time alerting & trends | Root-cause analysis for a specific user Summary Recommendation: Use the Prometheus metrics to build a "Materialized View Health" dashboard that shows you the hit rate of your top 10 MVs. Only dive into the Audit Logs when you see a specific MV's match rate drop and you need to see the exact SQL statements that are failing. References • administration/management/monitoring/metrics-materialized_view.md • administration/management/monitoring/Monitor_and_Alert.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md References • sql-reference/System_variable.md • administration/management/resource_management/be_label.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • administration/management/FE_configuration.md • sql-reference/information_schema/materialized_views.md
k
@Rocky can starrocks partition prune using date functions like:
Copy code
g.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH)
r
Yes, StarRocks can prune partitions using that expression, provided that 
g.date
is the column used for partitioning. StarRocks' optimizer performs constant folding during the planning phase. This means it evaluates dynamic but "stable" functions like 
CURRENT_DATE()
or 
DATE_SUB()
into a specific value (e.g., 
'2022-05-20'
) before it attempts to prune partitions. Why this works: 1. Column remains "Clean": Because you are applying the functions to the right-hand side of the operator (
>=
) and not to the column itself, the optimizer can directly map the resulting constant value to the metadata of your partitions. 2. Expression Support: StarRocks supports common date/time functions (like 
DATE_SUB
, 
DATE_ADD
, 
NOW()
, 
CURRENT_TIMESTAMP()
) for partition pruning. How to Verify To be 100% sure it is working for your specific table schema, run an 
EXPLAIN
on your query:
Copy code
sql
EXPLAIN SELECT * FROM your_table 
WHERE g.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH);
In the output, look for the 
OlapScanNode
. You should see a line labeled 
partitions
. * Success: It will show a subset of partitions (e.g., 
partitions=5/100
). * Failure: It will show all partitions (e.g., 
partitions=100/100
). Important Constraints * No functions on the column: If you wrote 
DATE_FORMAT(g.date, '%Y') >= '2022'
, partition pruning would fail because the optimizer cannot easily "reverse" the function to find the underlying partition ranges. * Data Types: Ensure 
g.date
is actually a 
DATE
or 
DATETIME
type. If it is a string (
VARCHAR
) and you compare it to a date function, pruning may fail due to implicit casting. Related Feature: Automatic TTL (v3.5+) If you are using this expression to manage data retention (deleting old data), StarRocks v3.5 introduced a property called 
partition_retention_condition
that uses this exact syntax to automatically drop old partitions:
Copy code
sql
PROPERTIES (
    "partition_retention_condition" = "g.date >= CURRENT_DATE() - INTERVAL 24 MONTH"
)
References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-functions/date-time-functions/date_sub.md • sql-reference/sql-statements/data-manipulation/EXPLAIN.md References • table_design/data_distribution/expression_partitioning.md
k
@Rocky i have a mv made for this table yet it is not rewrtiing 3ms| [MV TRACE] [PREPARE GLOBAL] MV rewrite strategy: MvRewriteStrategy{enableMaterializedViewRewrite=false, enableForceRBORewrite=false, enableViewBasedRewrite=false, enableSingleTableRewrite=false, enableMultiTableRewrite=false, mvStrategy=DEFAULT}
r
The trace log you provided indicates that the StarRocks optimizer has disabled all materialized view rewrite strategies for your current session. Specifically, 
enableMaterializedViewRewrite=false
means the engine isn't even attempting to match your query against the MV. To fix this, you need to enable the following session variables: 1. Enable Asynchronous MV Rewrite The primary variable must be set to 
true
(it is the default in newer versions, but may have been disabled):
Copy code
sql
SET enable_materialized_view_rewrite = true;
2. Check for Specific Rewrite Types If your query involves Joins or Views, ensure these specific flags are also enabled: * For Multi-table/Join Rewrites: 
SET enable_rule_based_materialized_view_rewrite = true;
* For Queries against Views: 
SET enable_view_based_mv_rewrite = true;
3. Other Common Reasons for "False" Strategy If you have set the variables and the trace still shows 
false
, check these conditions: * External Catalog MVs: If your MV is built on an external catalog (like Iceberg, Hive, or Glue), StarRocks may disable rewrite if it cannot guarantee data consistency. You can force it by setting this property on the MV:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SET ("force_external_table_query_rewrite" = "true");
* MV Status: If the MV is 
Inactive
, the optimizer will skip it. Check the status:
Copy code
sql
    SHOW MATERIALIZED VIEWS WHERE NAME = 'your_mv_name';
    -- Look for the 'IsActive' column. If it's 'false', use:
    ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
* Query Hint: Ensure your query doesn't have a hint like 
/_+ SET_VAR(enable_materialized_view_rewrite=false) _/
. Summary Recommendation Run the following before your query to ensure all doors are open for the optimizer:
Copy code
sql
SET enable_materialized_view_rewrite = true;
SET enable_rule_based_materialized_view_rewrite = true;
-- Then run your query with TRACE again
TRACE REASON MV <your_query>;
References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/System_variable.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/System_variable.md
k
@Rocky why is query rewirite suddently not wokring for all mvs
r
If query rewrite has suddenly stopped working for all materialized views (MVs), and your trace shows 
enableMaterializedViewRewrite=false
, it is likely due to one of the following global or system-level changes: 1. The Global Session Variable was toggled It is possible that 
enable_materialized_view_rewrite
was disabled at the global level. This would affect all new sessions and all MVs. * Fix: Run the following to re-enable it for the whole cluster:
Copy code
sql
    SET GLOBAL enable_materialized_view_rewrite = true;
_Note: You may also need to set 
SET GLOBAL enable_rule_based_materialized_view_rewrite = true;
._ 2. FE Configuration: 
skip_whole_phase_lock_mv_limit
 In StarRocks v3.2.1+, there is a specific FE configuration designed to save memory by skipping the MV rewrite path entirely for certain tables. * The Issue: If 
skip_whole_phase_lock_mv_limit
is set to a value less than 0, the planner skips recording tables into the optimizer context for MV rewrites. This effectively disables MV rewrite system-wide. * Check: Run 
ADMIN SHOW FRONTEND CONFIG LIKE 'skip_whole_phase_lock_mv_limit';
. If it is 
-1
, this is your culprit. 3. Data Staleness (Grace Period) By default, StarRocks will not rewrite a query using an MV if the data in the base tables has changed but the MV hasn't refreshed yet. If you just performed a large data load into your base tables, all MVs are now "stale." * The Test: Try setting the consistency to "loose" for your session:
Copy code
sql
    SET query_rewrite_consistency = 'loose';
If rewrite starts working again, your MVs simply need to be refreshed, or you need to increase the 
mv_rewrite_staleness_second
property on your MVs. 4. Mass Inactivation If you performed a schema change (e.g., 
ALTER TABLE
to drop or modify a column) on a base table shared by many MVs, StarRocks may have marked all dependent MVs as Inactive. * Check:
Copy code
sql
    SHOW MATERIALIZED VIEWS;
Look at the 
IsActive
column. If they are 
false
, they will never be used for rewrite. * Fix: You must manually set them back to active: 
ALTER MATERIALIZED VIEW <name> ACTIVE;
. 5. Memory / Plan Cache Limits If your cluster has a very high number of MVs, you might have hit the plan cache limit. * Check FE Config: 
mv_plan_cache_max_size
(Default is 1000). If you have more than 1000 MVs being used for rewrite, the FE may stop caching the plans necessary for the rewrite engine to function efficiently. Summary Checklist 1. Run 
SELECT @@enable_materialized_view_rewrite;
to verify the variable is actually 
1
. 2. Check if MVs are 
Active
via 
SHOW MATERIALIZED VIEWS
. 3. Check if 
query_rewrite_consistency
is blocking it by setting it to 
loose
. 4. Check FE configs if any "lock" or "limit" parameters were changed recently. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/System_variable.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
• sql-reference/System_variable.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
• using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md
• using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md
• using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md
• sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md
• sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md
• sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md
• sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md
• sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/administration/ADMIN_SET_CONFIG.md • sql-reference/sql-statements/administration/ADMIN_SHOW_CONFIG.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/data-definition/SHOW_MATERIALIZED_VIEWS.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
Open in Slack
PreviousNext
Powered by