http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> What optimizations can I make for ...
# questions-and-troubleshooting
k
@Rocky What optimizations can I make for queries against tokens in array columns in a multi billion row table in a shared data cluster? I have a materialized view to replicate the iceberg table on disk
r
To optimize queries against tokens in array columns for a multi-billion row table in a StarRocks shared-data cluster, you should focus on indexing strategies, data caching, and schema design within your Materialized View (MV). 1. Leverage Inverted Indexes (GIN) Since StarRocks 3.3, Full-text Inverted Indexes (GIN) are the most effective way to search for tokens. However, there are specific considerations for shared-data clusters and array types: * Transform Array to String: Currently, Inverted Indexes in StarRocks are primarily optimized for `STRING`/`VARCHAR` columns. In your MV, consider creating an additional column that converts the 
ARRAY<STRING>
into a space-separated 
STRING
(e.g., using 
array_join(col, ' ')
). * Create the Index: Create a GIN index on this string column with a parser (e.g., 
english
or 
standard
).
Copy code
sql
    -- Example for the Materialized View
    CREATE MATERIALIZED VIEW array_token_mv
    ...
    AS SELECT
        id,
        array_join(tags, ' ') as tags_string, -- Join tokens into a searchable string
        tags
    FROM iceberg_table;

    -- Add the index to the MV (if supported by your version) or the underlying table
    ALTER TABLE mv_underlying_table ADD INDEX idx_tags (tags_string) USING GIN("parser" = "english");
* Shared-data Requirement: If you are on v4.1+, you must use the built-in inverted index implementation for shared-data clusters. Earlier versions only supported CLucene-based GIN on shared-nothing clusters. * Querying: Use the 
MATCH
predicate instead of 
array_contains
to trigger the index:
Copy code
sql
    SELECT * FROM array_token_mv WHERE tags_string MATCH 'token1';
2. Optimize the Data Cache (Shared-Data Specific) In a shared-data architecture, your performance is bound by object storage latency unless the data is cached. * Data Cache Warmup: Use the 
CACHE SELECT
command to proactively load the MV's data into the local NVMe/SSD cache of your CN/BE nodes. * Increase Cache Persistence: Set the table property 
datacache.partition_duration
for the MV to a high value to prevent "hot" token data from being evicted. * Check for Cache Hits: Monitor your query profile for 
CompressedBytesReadRemote
. If this is high, your cache is too small or data is being evicted. 3. Bitmap Indexing for Low-Cardinality Tokens If your "tokens" belong to a relatively small set of unique values (e.g., categories, status codes): * Bitmap Index: Add a bitmap index to the array column or a pre-unnested column. While StarRocks supports bitmap indexes, they are most effective on scalar columns. * Global Dictionary: For multi-billion rows, converting string tokens to integer IDs using a Global Dictionary and then using Bitmap functions (e.g., 
bitmap_union
, 
bitmap_contains
) can be orders of magnitude faster than string-based array searches. 4. Partitioning and Bucketing * Partition Alignment: Ensure the MV's partitions are aligned with the Iceberg table's partitions (e.g., by 
event_date
). This allows the MV to perform incremental refreshes and enables partition pruning, skipping billions of rows during the initial scan. * Bucketing: Use a high-cardinality column for bucketing (e.g., 
user_id
or 
request_id
) to ensure data is evenly distributed across your cluster nodes, maximizing parallelism. 5. Efficient Querying Patterns * Avoid Unnesting Early: Use 
array_contains(col, 'token')
or 
any_match
predicates. Avoid 
UNNEST
in the 
WHERE
clause or joining against unnested rows, as this creates a massive intermediate result set on billion-row tables. * Predicate Pushdown: Ensure your token filters are pushed down to the storage layer. Check the Query Profile to see if 
PushDownPredicates
includes your array filters. Summary Recommendation If you need sub-second performance on billions of rows: 1. MV Design: Materialize the array as a space-separated string.
2. Indexing: Apply a built-in Inverted Index (if on v4.1+) or a standard GIN index to that string. 3. Cache: Warm up the MV data with 
CACHE SELECT
. 4. Query: Query using 
MATCH
rather than 
array_contains
. References • sql-reference/sql-functions/array-functions/any_match.md • sql-reference/sql-functions/array-functions/all_match.md • sql-reference/data-types/semi_structured/Array.md • sql-reference/sql-functions/array-functions/array_contains_seq.md • sql-reference/sql-functions/array-functions/array_contains_all.md
k
@Rocky why am I getting cache misses for materialized view being refreshed, I want to avoid going to s3 as much as possible
r
In a StarRocks shared-data cluster, a Materialized View (MV) refresh is essentially a background write operation that creates new segment files in S3/Object Storage. Because the Data Cache uses file-level and block-level keys, these new files will not be in the cache of your compute nodes (CN/BE) until they are accessed or explicitly warmed up. Here is why you are seeing cache misses and how to fix them: 1. New Files = No Cache When an MV refreshes, it generates new data. Even if the underlying data in Iceberg didn't change much, the MV's internal storage has changed. The first query after a refresh will always trigger a "Cold Read" from S3 because the local NVMe/SSD cache doesn't recognize the new file IDs/offsets. Solution: Active Warmup Use the 
CACHE SELECT
command immediately after your MV refresh completes. This proactively pulls the new data into the local cache so the first user query hits the disk instead of S3.
Copy code
sql
-- Warm up the entire MV
CACHE SELECT * FROM your_mv_name;

-- Or warm up only the most recent partitions to save cache space
CACHE SELECT * FROM your_mv_name WHERE event_date >= '2023-10-01';
You can automate this by submitting it as a task:
Copy code
sql
SUBMIT TASK warmup_mv AS CACHE SELECT * FROM your_mv_name;
2. Check 
datacache.partition_duration
 If your MV is partitioned, StarRocks uses the 
datacache.partition_duration
property to decide which partitions are "hot" enough to stay in the cache. If the refreshed data falls outside this window, it won't be cached at all. * Recommendation: Set this to a duration that covers your queryable window (e.g., 
30d
for 30 days).
Copy code
sql
    ALTER TABLE your_mv_name SET ("datacache.partition_duration" = "30d");
3. Warehouse/Node Mismatch In shared-data clusters, if you have multiple warehouses or if the warehouse that refreshes the MV is different from the warehouse that queries it, the cache will be empty on the querying nodes. * Solution: Enable Cache Sharing. This allows nodes to fetch cache blocks from other nodes over the network instead of going back to S3.
Copy code
sql
    -- Set these globally or in your session
    SET GLOBAL enable_datacache_sharing = true;
    ADMIN SET FRONTEND CONFIG ("enable_trace_historical_node" = "true");
4. Asynchronous Cache Population By default, StarRocks populates the cache asynchronously during a query to avoid slowing down the initial response. This means a single query might only cache 10–20% of the data it reads, requiring 4 or 5 consecutive runs to reach a 100% hit rate. * Solution: If you want the very first query to be fast and fully cached, set 
enable_datacache_async_populate_mode = false
during your warmup query, or simply use 
CACHE SELECT
which is synchronous by design. 5. Disk I/O Adaptation If your BE nodes are under heavy I/O load (common during a massive MV refresh), the I/O Adapter might skip writing to the local cache to prioritize the query/write itself. * Diagnostic: Check your Query Profile for 
CompressedBytesReadRemote
. If it's high despite repeated queries, check if the cache disk is full or if 
enable_datacache_io_adapter
is skipping writes due to high latency. Summary Checklist 1. Run 
CACHE SELECT
 after every refresh. 2. Set 
datacache.partition_duration
 high enough. 3. Enable 
enable_datacache_sharing
 if using multiple warehouses/large clusters. 4. Verify Cache Status: Run 
SHOW BACKENDS
or 
SHOW COMPUTE NODES
and check the 
CacheCapacity
and 
CacheUsedCapacity
columns to ensure you haven't run out of local disk space. References • deployment/shared_data/feature-support-shared-data.md • data_source/block_cache_warmup.md
k
@Rocky tables are not generally partitioned these are dimensional tables. I need to ensure there will never be cold reads and that materialized views will always be used during query rewrite
r
To ensure that your materialized views (MVs) always provide sub-second performance without cold reads from S3, you need to configure both the Data Cache (Shared-data architecture) and the Query Rewrite logic. 1. Guaranteeing Hot Reads (Avoiding S3 Latency) In a shared-data cluster, an MV refresh creates new files in S3. By default, these are cold to your compute nodes. Use the following settings to ensure the cache stays hot: * Cache-on-Write: Ensure the MV is created with 
"datacache.enable" = "true"
. This is the default, but it ensures that when the MV is refreshed (written), the data is simultaneously pushed to the local NVMe/SSD cache of the node performing the refresh. * Proactive Warmup: Immediately after your MV refresh job finishes, run a Cache Warmup command. Since your table is not partitioned, you must warm up the whole table:
Copy code
sql
    CACHE SELECT * FROM your_mv_name;
Tip: You can automate this by adding a second step to your refresh pipeline or using StarRocks' 
SUBMIT TASK
to run it on a schedule. * Disable Eviction for Dim Tables: Since these are dimensional tables (even at billion-row scales), you want them to persist in cache. Set a very high 
datacache.partition_duration
(e.g., 100 years) so the cache logic never considers the data "cold" based on time.
Copy code
sql
    ALTER TABLE your_mv_name SET ("datacache.partition_duration" = "1000 MONTH");
* Enable Cache Sharing: In a multi-node cluster, the data might be cached on Node A, but your query might land on Node B. Enable cache sharing to allow nodes to fetch data from each other's local disks rather than S3:
Copy code
sql
    SET GLOBAL enable_datacache_sharing = true;
2. Guaranteeing MV Query Rewrite StarRocks is strict about data consistency. If the underlying Iceberg table changes (even a single row), StarRocks will, by default, stop using the MV for query rewrite to avoid returning stale data. To force the use of the MV: * Relax Consistency Checks: Add these properties to your MV definition. 
LOOSE
consistency tells the optimizer to use the MV even if the base table has newer data.
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SET PROPERTIES (
        "query_rewrite_consistency" = "LOOSE",
        "force_external_table_query_rewrite" = "true"
    );
* Staleness Grace Period: Alternatively, if you want to allow rewrite only if the MV is "fresh enough" (e.g., refreshed within the last hour), use:
Copy code
sql
    "mv_rewrite_staleness_second" = "3600"
* Text-Based Rewrite (v3.3+): If your queries are complex or involve the 
ARRAY
tokens discussed earlier, ensure text-based rewrite is active. This allows the optimizer to match the query's AST directly to the MV's definition.
Copy code
sql
    SET enable_materialized_view_text_match_rewrite = true;
3. Optimizer Configuration for Large Scales Because you are dealing with multi-billion row tables, the optimizer might sometimes time out while trying to find a rewrite path. * Increase Rewrite Timeout: If you see in 
EXPLAIN
that rewrite was skipped, increase the time limit:
Copy code
sql
    SET optimizer_materialized_view_timelimit = 2000; -- default is 1000ms
* Verify with Explain: Always verify that your queries are actually hitting the MV by running:
Copy code
sql
    EXPLAIN SELECT ... -- Look for 'MaterializedView: [your_mv_name]' in the plan
Summary of Properties for your MV
Copy code
sql
CREATE MATERIALIZED VIEW your_mv_name
DISTRIBUTED BY HASH(id) -- Use a high cardinality column
PROPERTIES (
    "datacache.enable" = "true",
    "datacache.partition_duration" = "1000 MONTH",
    "query_rewrite_consistency" = "LOOSE",
    "force_external_table_query_rewrite" = "true"
)
AS SELECT ... FROM iceberg_table;
References
k
@Rocky how does query rewrite consistency interact with staleness second If a query is within staleness second, but rewrite consistency is NOT loose, will it go to iceberg?
r
The interaction between 
query_rewrite_consistency
and 
mv_rewrite_staleness_second
determines whether the optimizer prioritizes data freshness or query performance (via the Materialized View). The Interaction Logic If a query is within the 
mv_rewrite_staleness_second
window, it will NOT go to Iceberg; it will use the Materialized View, even if 
query_rewrite_consistency
is set to 
checked
(the default). Think of these properties as two different ways to satisfy the "Consistency Check": 1. 
query_rewrite_consistency
: * 
checked
(Default): The optimizer checks if the base table (Iceberg) has been updated since the last MV refresh. If the base table is newer, the MV is considered "stale" and the rewrite fails unless a staleness window is defined. * `loose`: The optimizer skips all consistency checks. It will use the MV regardless of how old it is or how much the base table has changed. 2. 
mv_rewrite_staleness_second
: * This property acts as a grace period for the 
checked
consistency level. * If you set 
mv_rewrite_staleness_second = 3600
, you are telling StarRocks: "For the next hour after a refresh, treat this MV as consistent even if the Iceberg table changes." * Within the window: Rewrite is successful (Uses MV). * Outside the window: StarRocks reverts to the strict 
checked
logic. If Iceberg has changed, it will go to Iceberg (Cold Read). Summary Table 
query_rewrite_consistency
 | Within 
staleness_second
window? | Iceberg Data Changed? | Resulting Action 
checked
 | Yes | Yes/No | Uses MV (Override triggered by staleness) 
checked
 | No | Yes | Goes to Iceberg (Cold Read) 
checked
 | No | No | Uses MV (Up to date) 
loose
 | N/A | Yes/No | Uses MV (Always) Recommendation for Dimensional Tables Since you want to avoid cold reads at all costs for your dimensional tables: * Option A (Time-based): Set 
mv_rewrite_staleness_second
to a value slightly longer than your refresh interval (e.g., if you refresh every hour, set it to 
3600
). This ensures that during the refresh cycle, queries always hit the MV. * Option B (Safest): Set 
query_rewrite_consistency = 'loose'
. This is common for dimensional data where slightly older labels/tokens are acceptable in exchange for guaranteed sub-second performance. One final note: If you find the query still goes to Iceberg, check the session variable 
optimizer_materialized_view_timelimit
. If the optimizer takes too long to find the rewrite path on your billion-row schema, it will give up and default to the base table. Increase it to 
2000
or 
3000
ms if necessary. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
3 Views
Open in Slack
PreviousNext
Powered by