http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I have two partitioned tables that ...
# questions-and-troubleshooting
s
@Rocky I have two partitioned tables that are joined in an MV, They are partitioned on day of created_at column. The partition column values are not same across both tables meaning same key in first table can have different created_at value then the table on right side of join. Now when Async Scheduled Mv with 10 Partition TTL refreshes it reads 10 partitions from left tables always and all the data from right table. Can anything be done here so that it reads from delta data from left and right side always instead reading entire data
r
This behavior occurs because StarRocks treats the table specified in the 
PARTITION BY
clause as the reference table. For the other table (the right side of the join), if it is not joined on the partition column, StarRocks cannot determine which of its partitions correspond to the partitions being refreshed in the Materialized View (MV). Consequently, it defaults to scanning the entire right table to ensure no data is missed for the join. To achieve incremental/delta reading from both sides, you can use the following strategies: 1. Add a Temporal Constraint to the Join (Recommended) Even if you cannot join on the exact partition key values because they differ, you can often define a time window relationship. If you know that records in the right table (
t2
) will always have a 
created_at
within a certain range of the left table (
t1
), add this to your 
JOIN
condition:
Copy code
sql
CREATE MATERIALIZED VIEW my_mv
PARTITION BY t1_created_at
AS
SELECT ...
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id 
-- Add a range filter to enable partition pruning on t2
AND t2.created_at BETWEEN t1.created_at - INTERVAL 7 DAY AND t1.created_at + INTERVAL 7 DAY;
When StarRocks refreshes a specific partition of 
t1
(e.g., 
'2023-10-01'
), the optimizer will see the 
BETWEEN
predicate and prune the scan of 
table2
to only the relevant 14 days of partitions instead of the whole table. 2. Upgrade to StarRocks v3.3+ (Align Multiple Base Tables) Starting from version 3.3, StarRocks supports multi-table partition alignment. This allows the MV to track partition-level changes for multiple base tables. * Requirement: You must include the partition columns of both tables in the join and potentially in the 
SELECT
list. * Effect: If StarRocks can verify that the partitions of 
t2
align with the partitions of the MV, it will only refresh the partitions that have changed in 
t2
, rather than scanning the whole table. 3. Use 
excluded_trigger_tables
 If the right table is updated frequently but you don't want those updates to trigger a refresh of the entire MV, you can exclude it from the trigger list. This doesn't solve the "scan all" problem during a scheduled refresh, but it prevents unnecessary refreshes:
Copy code
sql
PROPERTIES (
  "excluded_trigger_tables" = "table2"
)
4. Materialized View on Multiple Partition Columns (v3.5+) If your use case requires keeping track of deltas on both tables independently regardless of how they join, StarRocks v3.5+ supports Multiple Partition Columns. You could theoretically partition the MV by both 
t1.created_at
and 
t2.created_at
. However, this significantly increases the number of partitions in the MV and may not be suitable for simple 1:1 join views. Summary Checklist * Check Version: Ensure you are on at least v3.1.x for stable partitioned MV features, and ideally v3.3+ for multi-table alignment. * Explain Plan: Run 
EXPLAIN REFRESH MATERIALIZED VIEW my_mv PARTITION START (...) END (...)
to see the physical plan. Look for the 
SCAN
on the right table to see if 
partitionRatio
shows pruning (e.g., 
1/1000
vs 
1000/1000
). * Tighten Predicates: The most effective way to stop a full scan is to give the optimizer a hint about which partitions of the right table are "logically" related to the left table's partition. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/materialized_view_task_run_details.md
s
this is my MV- create MATERIALIZED VIEW 
picked_shipment_12_day_mv
PARTITION BY date_trunc('day', created_at) DISTRIBUTED BY HASH(
group_id
) BUCKETS 128 ORDER BY (created_at) REFRESH ASYNC EVERY(INTERVAL 30 SECOND) PROPERTIES ( "storage_medium" = "SSD", "storage_cooldown_time" = "9999-12-31 212959", "replicated_storage" = "true", "replication_num" = "2", "mv_rewrite_staleness_second" = "30", "partition_ttl" = "30 DAYS", "partition_refresh_number" = "30", "partition_refresh_strategy" = "strict", "query_rewrite_consistency" = "CHECKED" ) AS SELECT t1.group_id, t2.external_reference_id, t1.status as shipment_status, flow as shipment_flow, t1.created_at, t2.created_at as picked_item_created_at FROM shipments t1 JOIN picked_items t2 ON t1.group_id = t2.group_id @Rocky create MATERIALIZED VIEW 
mv_name
PARTITION BY date_trunc('day', created_at) DISTRIBUTED BY HASH(
group_id
) BUCKETS 128 ORDER BY (created_at) REFRESH ASYNC EVERY(INTERVAL 30 SECOND) PROPERTIES ( "replicated_storage" = "true", "replication_num" = "2", "mv_rewrite_staleness_second" = "30", "partition_ttl" = "30 DAYS", "partition_refresh_number" = "30", "partition_refresh_strategy" = "strict", "query_rewrite_consistency" = "CHECKED" ) AS SELECT t1.id, t2.status, t2.created_at as table_2_created_at FROM table_1 t1 JOIN table_2 t2 ON t1.group_id = t2.group_id i am already on starrocks 4.0
r
Since you are on StarRocks 4.0, you have access to Multi-table Partition Alignment. The reason StarRocks scans the entire right table is that your current 
JOIN
only uses 
group_id
. Without a join condition on the partition column (
created_at
), the optimizer cannot "map" which partitions of 
table_2
correspond to the 10 partitions of 
table_1
you are refreshing. Logically, a row in 
table_2
from a year ago could still match a 
group_id
in today's 
table_1
partition. To stop the full scan and enable incremental/delta reading from both sides, you must provide a link between the two tables' partition columns. 1. The Temporal Join Solution (Best for Performance) Even if the 
created_at
values are not identical, there is usually a business limit on how far apart they can be. By adding a range predicate on the partition column, StarRocks can prune the partitions of the right table. Update your MV query to include a time-window join:
Copy code
sql
AS
SELECT t1.group_id, t2.external_reference_id, ...
FROM shipments t1
JOIN picked_items t2 ON t1.group_id = t2.group_id
-- Add this to enable partition pruning on t2
AND t2.created_at >= t1.created_at - INTERVAL 7 DAY 
AND t2.created_at <= t1.created_at + INTERVAL 7 DAY
2. Explicit Partition Alignment (v3.3+) StarRocks 4.0 supports treating both tables as reference tables. For this to work, the optimizer needs to see a direct join on the partition columns. You can use 
date_trunc
in the join condition to align them at the day level:
Copy code
sql
AS
SELECT t1.group_id, ...
FROM shipments t1
JOIN picked_items t2 ON t1.group_id = t2.group_id
-- Join on the day-truncated version of the partition key
AND date_trunc('day', t1.created_at) = date_trunc('day', t2.created_at)
Note: This only works if your business logic guarantees that matching records always fall within the same UTC day. 3. Check for "Reference Table" Status Once you add a join condition on the partition column, you can verify if StarRocks is tracking both tables by checking the MV metadata:
Copy code
sql
SHOW MATERIALIZED VIEWS LIKE 'picked_shipment_12_day_mv';
Look for the 
RefBaseTable
column. If both 
shipments
and 
picked_items
are listed as reference tables, StarRocks will track deltas for both and only scan the necessary partitions from either side during refresh. Summary of properties Your existing properties are mostly fine, but ensure the following: * Version: You are on 4.0, so multi-table alignment is enabled by default. * Join Condition: This is the missing link. Without a predicate on 
created_at
(either a range or a 
date_trunc
equality), the right table will always be fully scanned. If you absolutely cannot join on the 
created_at
column because the dates are completely unrelated, StarRocks has no choice but to scan the right table to ensure data correctness. Inconsistency isn't missing any matches for the refreshed 
t1
partitions. In that case, you might consider making 
table_2
a Primary Key table if updates are frequent, as this can sometimes optimize join performance, but the join scan remains the primary bottleneck. References • faq/Others.md • knowledge/trouble_shooting/fe_memory_problems.md • administration/Meta_recovery.md • administration/management/FE_configuration.md • faq/fe_mem_faq.md • faq/operation_maintenance_faq.md • sql-reference/sql-functions/JAVA_UDF.md
s
@Rocky added this on the join condition- ON t1.group_id = t2.group_id AND date_trunc('day', t1.created_at) = date_trunc('day', t2.created_at) it still reads entire data from table_1. From MV Details- {table_2=[p20260325, p20260402, p20260326, p20260403, p20260327, p20260404, p20260328, p20260405, p20260329, p20260406, p20260407, p20260408, p20260409, p20260320, p20260321, p20260322, p20260323, p20260324, p20260401, p20260512, p20260513, p20260514, p20260515, p20260516, p20260517, p20260518, p20260519, p20260430, p20260510, p20260511, p20260509, p20260424, p20260501, p20260425, p20260502, p20260426, p20260503, p20260427, p20260504, p20260428, p20260505, p20260429, p20260506, p20260507, p20260508, p20260420, p20260421, p20260422, p20260423, p20260413, p20260414, p20260415, p20260416, p20260417, p20260418, p20260419, p20260330, p20260410, p20260411, p20260412], table_1=[p20260424, p20260501, p20260425, p20260502, p20260426, p20260503, p20260427, p20260504, p20260428, p20260505, p20260429, p20260506, p20260507, p20260508, p20260420, p20260421, p20260422, p20260423, p20260512, p20260513, p20260514, p20260515, p20260516, p20260517, p20260419, p20260518, p20260519, p20260430, p20260510, p20260511, p20260509]}
r
Since you are on StarRocks 4.0, you are likely seeing the difference between Partition-level Incremental Refresh (PCT) and Row-level Incremental Refresh (IVM). By adding the join condition 
date_trunc('day', t1.created_at) = date_trunc('day', t2.created_at)
, you have successfully enabled Multi-table Partition Alignment. This is why your "MV Details" now shows a list of partitions for 
table_2
instead of a full table scan. However, it is still reading 31 partitions for 
table_1
and 60 for 
table_2
. Here is why this is happening and how to fix it: 1. Verify "Reference Table" Status For an MV to refresh incrementally from both sides, StarRocks must recognize both tables as "Reference Tables." * Check: Run 
SHOW MATERIALIZED VIEWS LIKE 'your_mv_name' \G
and look at the 
RefBaseTable
field. * Fix: It should list both 
table_1
and 
table_2
. If it only lists one, it means the partition alignment failed. In your 
PARTITION BY
clause, explicitly use the table alias:
Copy code
sql
    PARTITION BY date_trunc('day', t1.created_at) -- Use the alias!
2. Why so many partitions (60 for Table 2)? The list you shared shows 
table_2
tracking partitions back to March (
p20260325
), while 
table_1
only goes back to April. * Potential Issue: If your business logic allows 
table_2.created_at
to be significantly different from 
table_1.created_at
, but you used an equality join (
=
), you might be dropping data or causing the optimizer to check a wider range of partitions to ensure consistency. * Alignment Logic: If you join on 
date_trunc('day', t1.created_at) = date_trunc('day', t2.created_at)
, StarRocks maps 1 partition of 
table_1
to exactly 1 partition of 
table_2
. If it's listing 60 partitions for 
table_2
, it means it detected data changes in those 60 days. 3. Move from "Partition Refresh" to "Row-level Delta" (IVM) StarRocks 4.0 supports Incremental View Maintenance (IVM), which updates only the changed rows rather than re-reading entire partitions. * Problem: Your current MV is using 
PARTITION BY
, which defaults to "Partition-level" (if 1 row changes in a day, the whole day is re-read). * Solution: To use row-level deltas, you can try adding the property 
"enable_incremental_refresh" = "true"
. _ _Note*: IVM has stricter requirements (e.g., usually requires both tables to be Primary Key tables or have specific join types). If your tables are 
Duplicate Key
, IVM might not be supported for this specific join yet. 4. Optimize the Join for Pruning If you cannot use IVM and must stick to Partition-level refresh, use a Range Join if the dates aren't perfectly aligned. This is often more "pruning-friendly" than 
date_trunc
equality:
Copy code
sql
ON t1.group_id = t2.group_id 
-- This allows StarRocks to prune table_2 to a 7-day window around t1's partition
AND t2.created_at BETWEEN t1.created_at - INTERVAL 3 DAY AND t1.created_at + INTERVAL 3 DAY
Summary of Recommended Changes 1. Be Explicit: Change 
PARTITION BY date_trunc('day', created_at)
to 
PARTITION BY date_trunc('day', t1.created_at)
. 2. Verify: Check 
RefBaseTable
in 
SHOW MATERIALIZED VIEWS
. If both tables aren't there, the "delta" tracking is only working for one table. 3. Check Data Changes: If it reads 30 partitions, it’s because it thinks those 30 partitions are "stale" (data was updated in them). If you only want to refresh today, ensure you aren't accidentally updating historical data in the base tables. 4. Strategy: Change 
partition_refresh_strategy
to 
loose
if you want to allow the MV to refresh even if some historical partitions in the right table are slightly out of sync. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md
s
@Rocky none of the suggestion worked. even when doing-
Copy code
t2.created_at BETWEEN t1.created_at - INTERVAL 3 DAY AND t1.created_at + INTERVAL 3 DAY
OR AND date_trunc('day', t1.created_at) = date_trunc('day', t2.created_at) it still 10 partitions from left table and all the partitions from right table
r
In StarRocks 4.0, the behavior you're seeing—where one table tracks specific partitions while the other seems to "read all" (or a much larger set)—usually stems from how the Partition Alignment logic tracks data changes. Based on the "MV Details" you provided: * 
table_1
has 31 partitions listed (matching your 30-day TTL). * 
table_2
has 60 partitions listed (going back to March, 
p20260325
). This confirms that StarRocks is tracking 
table_2
as a reference table (otherwise it would show "ALL"). However, it's reading 60 partitions because it has detected data changes (new versions) in those 60 partitions of 
table_2
. Here is how to fix this and achieve the desired incremental behavior: 1. Enable Row-Level Incremental Refresh (IVM) Since you are on version 4.0, you should try the Incremental View Maintenance (IVM) feature. Unlike standard MVs that refresh entire partitions (Partition-level), IVM only processes the specific rows that changed. * Requirement: This works best if your base tables are Primary Key tables. * Action: Add this property to your MV:
Copy code
sql
    PROPERTIES (
      "enable_incremental_refresh" = "true"
    )
2. Force Explicit Partition Alignment StarRocks needs to be 100% certain of the mapping between 
t1
and 
t2
to prune the scan of 
t2
when refreshing a partition of 
t1
. The 
date_trunc
expression in the join is supported for alignment, but it can be sensitive. Try the following "cleanest" alignment join:
Copy code
sql
AS
SELECT t1.group_id, t1.created_at, t2.created_at as t2_at, ...
FROM table_1 t1
JOIN table_2 t2 ON t1.group_id = t2.group_id
-- Ensure the expression exactly matches the PARTITION BY expression
AND date_trunc('day', t1.created_at) = date_trunc('day', t2.created_at)
Crucial Check: Run 
SHOW MATERIALIZED VIEWS LIKE 'mv_name' \G
. Look at the 
RefBaseTable
 field. It must list both 
table_1
and 
table_2
. If it only lists 
table_1
, the alignment failed, and 
table_2
will always be fully scanned during a refresh. 3. Use 
excluded_trigger_tables
if Table 2 is a "Lookup" If 
table_2
is a dimension/lookup table and you don't want its historical updates to trigger a refresh of the MV, add it to this list. This prevents the "60 partitions" problem where old data updates in 
t2
force the MV to re-run those days.
Copy code
sql
PROPERTIES (
  "excluded_trigger_tables" = "table_2"
)
With this, only changes in 
table_1
will trigger a refresh. When `table_1`'s today's partition refreshes, it will still use the join to 
table_2
, and the 
date_trunc
join will allow it to prune 
table_2
to just today's data. 4. Verify with 
EXPLAIN
 To see exactly what partitions will be scanned, run an explain plan on a manual refresh:
Copy code
sql
EXPLAIN REFRESH MATERIALIZED VIEW mv_name PARTITIONS (p20260519);
Look for the 
OlapScanNode
for 
table_2
. * If you see 
partitionRatio: 1/60
, it is pruning correctly. * If you see 
partitionRatio: 60/60
, it is a full scan. Why was it reading 60 partitions? In your metadata output, StarRocks listed 60 partitions for 
table_2
because those partitions have a newer data version than the last time the MV refreshed. In a partitioned MV, StarRocks' goal is consistency. If data changes in 
p20260325
of 
table_2
, StarRocks thinks the corresponding partition in the MV is now "stale" and adds it to the refresh list. Because your TTL is 30 days, it might be trying to refresh partitions that are already expired or about to expire. Summary Recommendation: 1. Check 
RefBaseTable
to ensure both tables are aligned. 2. Use 
excluded_trigger_tables
if you only want the "Left" table to drive the refresh schedule. 3. Try 
"enable_incremental_refresh" = "true"
for row-level deltas. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
s
@Rocky there is no 
enable_incremental_refresh
config. also there is no RefBaseTable in query output?
r
It sounds like there are two points of confusion: where to find the metadata and which property name to use for StarRocks 4.0's incremental features. 1. Correct Property for Incremental Refresh The property name is not 
enable_incremental_refresh
. In StarRocks 4.0, the property to enable row-level incremental refresh (IVM) is:
Copy code
sql
PROPERTIES (
  "refresh_mode" = "INCREMENTAL"
)
*Note: This typically requires your base tables to be Primary Key tables. If you are using Duplicate Key tables, StarRocks must fall back to "Partition-level" refresh (PCT).* 2. Finding Metadata (
RefBaseTable
) The 
RefBaseTable
field is not in the standard 
SHOW MATERIALIZED VIEWS
output. To see the dependencies and which tables are tracked, use these instead: * For Table Dependencies: 
SELECT * FROM sys.object_dependencies WHERE object_name = 'mv_name';
* For Detailed Refresh Info: Query the 
EXTRA_MESSAGE
column in 
information_schema.task_runs
. This is where the 
refBasePartitionsToRefreshMap
you saw comes from. Why it still reads 60 partitions of Table 2 Your "MV Details" show 
table_2=[p20260325, ..., p20260412]
. This proves that Partition Alignment is working (it's not scanning every partition in the table), but StarRocks has detected data changes in 60 different partitions of 
table_2
. Even with a perfect join, if 
table_2
has any new data or updates in those 60 days, StarRocks considers those 60 partitions of the MV "stale" and will refresh them to maintain consistency. How to restrict the refresh to just Table 1's changes If you want the MV to only refresh when 
table_1
(your "shipments") changes, and you want to ignore historical updates in 
table_2
, use the 
excluded_trigger_tables
property:
Copy code
sql
PROPERTIES (
  "excluded_trigger_tables" = "table_2",
  "partition_ttl" = "30 DAYS"
)
What this does: 1. Triggers: Only data changes in 
table_1
will trigger an automatic refresh. 2. Pruning: When the refresh runs for a specific day (e.g., today), the 
date_trunc
join condition will still allow StarRocks to prune the scan of 
table_2
to only that specific day. 3. Result: You stop "re-running" the last 60 days every time a small change happens in an old 
table_2
record. Summary Checklist for 4.0 1. Check Alignment: Run 
SHOW CREATE MATERIALIZED VIEW mv_name;
. Ensure the 
PARTITION BY
uses the alias: 
PARTITION BY date_trunc('day', t1.created_at)
. 2. Exclude Table 2: Add 
"excluded_trigger_tables" = "table_2"
to your properties. 3. Verify Scan: Run 
EXPLAIN REFRESH MATERIALIZED VIEW mv_name;
. Look for 
OlapScanNode
for 
table_2
and check the 
partitionRatio
. It should now reflect a much smaller number of partitions. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/sys/object_dependencies.md
Open in Slack
PreviousNext
Powered by