http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> i have a shared data cluster agains...
# questions-and-troubleshooting
k
@Rocky i have a shared data cluster against an external catalog s3 table bucket with a table containing an array column, for example called "multi_values". my use case is to support the fastest possible reads on "multi_values". i have an MV against the table, and currently in application we are calling "array_overlap" to find rows whose "multi_values" column contains at least one value. can generated queries support making query rewrites invisible against an 'array_overlap' query? this is to ensure queries will always be successful against the external catalog if there is an issue with the MV
r
To support the fastest possible reads on an 
ARRAY
column from an external S3 table while ensuring a transparent fallback to the data lake, StarRocks provides several mechanisms. 1. Transparent Query Rewrite and Fallback The "invisible" rewrite you are looking for is the default behavior of Asynchronous Materialized Views in StarRocks. * Invisible Rewrite: When you create an asynchronous MV against your external S3 table, StarRocks' cost-based optimizer (CBO) will automatically detect if a query can be satisfied by the MV. If it can, the query is transparently rewritten to use the MV's local, optimized storage without requiring changes to your application SQL. * Automatic Fallback: If there is an issue with the MV—such as it being stale (data inconsistency exceeds 
mv_rewrite_staleness_second
), inactive, or *manually disabled*—the optimizer will simply skip the rewrite and route the query directly to the external catalog (S3). This ensures your application queries remain successful. 2. Supporting 
array_overlap
in Rewrites StarRocks supports query rewrite for functions like 
array_overlap
. To ensure this works effectively: * Column-based Rewrite: If your MV includes the raw 
multi_values
column (e.g., 
SELECT multi_values, ... FROM external_table
), StarRocks can rewrite any query that filters using 
arrays_overlap(multi_values, [...])
to use the MV. * Expression-based Rewrite (v3.1+): You can also include the specific boolean expression in the MV definition. If your application always checks for a specific set of values, including that expression in the MV allows the optimizer to map the query directly to the pre-computed result. 3. Achieving "Fastest Possible Reads" For the best performance on 
ARRAY
columns in a shared-data cluster: * Use an Asynchronous MV: This caches the S3 data into StarRocks' native storage (StarRocks Shared-data / StarOS). * Inverted Index (v3.3+): You can build an Inverted Index on the 
ARRAY
column within the Materialized View. This significantly accelerates membership checks like 
array_contains
and 
arrays_overlap
by avoiding full scans of the array data. _ _Note: In shared-data clusters, you must use the built-in inverted index implementation (available from v4.1, or utilize the standard bitmap/index acceleration in earlier versions).* 4. Recommendation for your Setup To implement this: 1. Create the MV with the necessary columns and set it to refresh at your desired interval. 2. Enable Rewrite: Ensure 
force_external_table_query_rewrite
is set to 
true
if you want to allow rewrites even when StarRocks cannot perfectly guarantee the freshness of the S3 metadata (common for S3-backed tables). 3. Optimize: If performance is still not sufficient, add an inverted index to the 
multi_values
column on the MV.
Copy code
sql
CREATE MATERIALIZED VIEW mv_multi_values_accel
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "force_external_table_query_rewrite" = "true",
    "mv_rewrite_staleness_second" = "3600" -- Tolerate 1 hour staleness for rewrite
)
AS 
SELECT id, multi_values, other_cols
FROM s3_catalog.db.table;

-- Optional: Add index for even faster array lookups (v3.3+)
ALTER TABLE mv_multi_values_accel ADD INDEX idx_array (multi_values) USING INVERTED;
If the MV refresh fails or you manually drop/disable it, your application's 
SELECT ... WHERE arrays_overlap(...)
will continue to run against the 
s3_catalog
automatically. References • table_design/indexes/inverted_index.md • best_practices/query_tuning/schema_tuning.md • release_notes/release-2.4.md
k
@Rocky where is explicit documentation stating inverted index supports array columns? the current docs say it does not support it: https://docs.starrocks.io/docs/table_design/indexes/inverted_index/#built-in-inverted-index The data type of the indexed column must be CHAR, VARCHAR, or STRING.
r
You are correct that the built-in inverted index (introduced in v4.1 for shared-data clusters) currently lists 
CHAR
, 
VARCHAR
, and 
STRING
as the only supported data types. The documentation for the CLucene-based implementation (used in shared-nothing clusters) has historically been more flexible with 
ARRAY
elements, but since you are on a shared-data cluster, you are restricted to the built-in implementation. To achieve your goals of "fastest possible reads" and "invisible rewrites with fallback," here is the recommended approach: 1. Transparent Query Rewrite & Fallback StarRocks' Asynchronous Materialized Views provide exactly the "invisible" behavior you described. * Invisible Rewrite: The Cost-Based Optimizer (CBO) automatically rewrites queries against the S3 table to use the MV if it contains the necessary data. * Automatic Fallback: If the MV is stale, inactive, or dropped, the optimizer will route the query directly to the external S3 catalog without your application needing to change the SQL. * Requirement: Ensure 
force_external_table_query_rewrite
is set to 
true
to allow rewrites against external catalogs. 2. Accelerating 
arrays_overlap
(Workarounds for Index Limits) Since the built-in inverted index does not yet support the 
ARRAY
type directly in shared-data clusters, you can use one of these two high-performance strategies: Option A: The "Generated Column" approach (Recommended) Add a Generated Column to your Materialized View that converts the array into a searchable string, and then put the inverted index on that string. 1. Define the MV with a column like 
array_join(multi_values, ' ')
. 2. Add the built-in inverted index to that new string column. 3. Queries using 
MATCH
on the string column will be extremely fast. _Note: This might require slightly changing the query to use 
MATCH
instead of 
arrays_overlap
if you want the index hit, but it remains invisible to the underlying S3 table structure._ Option B: Materialized View with 
UNNEST
 If the "overlap" check is the primary bottleneck, create an MV that expands (unnests) the array into a flat table.
Copy code
sql
CREATE MATERIALIZED VIEW mv_flattened_values
AS SELECT id, unnested_val 
FROM s3_catalog.db.table, UNNEST(multi_values) as unnested_val;
You can then place a standard index (or even a Bitmap index) on 
unnested_val
. The optimizer can sometimes rewrite membership queries to use this flattened MV. 3. Summary of Status * Inverted Index on ARRAY: You are correct; the official documentation for the built-in inverted index (shared-data) does not currently support 
ARRAY
types. * Query Rewrite: This is fully supported and provides the "invisible" fallback you need. * Performance: Even without an inverted index, StarRocks' Low Cardinality Optimization (v3.3+) significantly accelerates scans on 
ARRAY<STRING>
columns by using global dictionaries. Ensure 
array_low_cardinality_optimize
is set to 
true
(default). For the absolute fastest performance in a shared-data environment, the most robust path is using an Asynchronous MV to cache the S3 data locally, which allows the engine to use vectorized execution and local data cache (StarOS) even if a secondary index is not present. References • table_design/indexes/inverted_index.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/data-types/semi_structured/Array.md • table_design/indexes/indexes.md References • sql-reference/data-types/semi_structured/Array.md
k
@Rocky For option b, if I make an mv with an unnest, and I query my base table with array overlap, query rewrite will occur?
r
For Option B (creating an MV with 
UNNEST
), the short answer is: No, the query rewrite will not occur automatically if your application continues to query the base table using 
array_overlap
. Here is the detailed breakdown of why, and the recommended solution to achieve your "invisible" rewrite and "fastest" read goals. 1. Why Option B fails for invisible rewrites The StarRocks Cost-Based Optimizer (CBO) handles transparent rewrites by matching the structure and columns of your query to the Materialized View (MV). * Structural Difference: A query using 
array_overlap(multi_values, ...)
expects a result at the original row grain. An MV created with 
UNNEST(multi_values)
changes the data grain (it expands one row into many). * Optimizer Logic: Currently, the StarRocks optimizer does not have a built-in rule to transform a scalar array function (like 
array_overlap
) on a base table into a join/filter operation against a flattened (unnested) MV. * Result: Since the MV doesn't "match" the query structure, the optimizer will skip the MV and query the external S3 catalog directly, which fails your performance goal. 2. The Recommended Solution: "Array-to-Array" MV To achieve an invisible rewrite while getting the fastest possible reads, you should create an MV that keeps the 
ARRAY
column as-is.
Copy code
sql
CREATE MATERIALIZED VIEW mv_multi_values_accel
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "force_external_table_query_rewrite" = "true" -- Critical for external catalog rewrites
)
AS 
SELECT id, multi_values, other_columns
FROM s3_catalog.db.table;
How this works for you: 1. Invisible Rewrite: When you run 
SELECT ... FROM s3_catalog.db.table WHERE array_overlap(multi_values, [1,2])
, the optimizer sees that the 
multi_values
column is pre-cached in the local 
mv_multi_values_accel
. It transparently redirects the query to the MV. 2. Performance: Reads from the MV are significantly faster than S3 because: * The data is stored in StarRocks' native segment format (optimized for vectorized execution). * In a shared-data cluster, the data is cached in the local disk cache (StarOS), avoiding S3 network latency. * StarRocks applies Low Cardinality Optimization to 
ARRAY<STRING>
columns automatically, which can accelerate scans even without an index. 3. Invisible Fallback: If the MV becomes stale or is deleted, the optimizer will simply route the query back to the S3 catalog. Your application SQL never changes and never fails. 3. Summary of Inverted Index for Arrays To clarify your earlier point: You are correct. The built-in inverted index (required for shared-data clusters) does not support ARRAY types at this time (it supports 
CHAR
, 
VARCHAR
, and 
STRING
). Alternative for maximum speed: If 
array_overlap
is still too slow on the MV, you can use a Generated Column in the MV to create a "searchable string" from the array and index that: 1. MV definition: 
SELECT id, array_join(multi_values, ' ') as search_str FROM s3_table
. 2. Add inverted index to 
search_str
. 3. Note: This would require your application to use a 
MATCH
query instead of 
array_overlap
, which would mean the rewrite is no longer "invisible" to your current SQL. Conclusion: The most robust and "invisible" way to speed up your use case is to create an Async MV containing the raw ARRAY column. This provides the best balance of transparent fallback and high-performance local execution. References • sql-reference/sql-functions/array-functions/unnest.md • using_starrocks/Lateral_join.md • sql-reference/sql-functions/bitmap-functions/unnest_bitmap.md
k
@Rocky how can I observe the effectiveness of low cardinality optimizer against my array column queries for an mv based on an external base table
r
To observe the effectiveness of the low-cardinality optimization (Global Dictionary) on your 
ARRAY<STRING>
column within an Async Materialized View, you can use the Query Profile and the EXPLAIN plan. Since the Materialized View (MV) is stored in StarRocks' native format, the engine automatically builds and maintains a global dictionary for low-cardinality string elements within your arrays. 1. Verify Configuration First, ensure the optimization is enabled in your session:
Copy code
sql
-- This is enabled by default in v3.3+, but worth verifying
SET array_low_cardinality_optimize = true;
SET enable_profile = true;
2. Check the Execution Plan (EXPLAIN) Before running the query, look for the Decode operator in the physical plan. If the optimization is working, the optimizer will plan to work with integer dictionary IDs rather than raw strings.
Copy code
sql
EXPLAIN SELECT * FROM your_mv WHERE array_overlap(multi_values, ['val1', 'val2']);
What to look for: * In the 
PLAN FRAGMENT
section, look for a 
Decode
 node or a 
DecodeCollector
. * In the 
SCAN
node description, look for attributes like 
dict_optimize=true
. This indicates the engine is filtering or processing based on dictionary IDs. 3. Analyze the Query Profile (The "Evidence") After running your query, retrieve the profile using 
SELECT get_query_profile();
(or via the StarRocks Manager UI). Navigate to the Scan Node corresponding to your Materialized View. Look for the following specific metrics under the 
OLAP_SCAN_NODE
or `VOlapScanNode`: * 
DictDecodeRows
: The number of rows where the engine successfully used dictionary-based decoding. If this is high, the optimization is active. * 
DictDecodeTime
: The time spent converting dictionary IDs back to strings. If the query is fast and this time is low, it means the engine processed most of the logic (like 
array_overlap
) in the "integer domain" before decoding at the last second. * 
PushDownDictFilter
: (If applicable) Indicates if the predicate was pushed down to the scan level using dictionary IDs. 4. Comparison Test (Optional) To see the actual performance delta, you can run the query twice: once with the optimization on and once with it off.
Copy code
sql
-- Test 1: Optimization ON
SET array_low_cardinality_optimize = true;
SELECT ... -- check execution time

-- Test 2: Optimization OFF
SET array_low_cardinality_optimize = false;
SELECT ... -- check execution time
Why this works for your MV Even though your base table is an external S3 table, an Asynchronous Materialized View in StarRocks is a local, native table. When StarRocks ingests data from S3 into the MV, it analyzes the data. If the elements in 
multi_values
have a low number of unique values, it creates a global dictionary. When you run 
array_overlap
, StarRocks converts your input 
['val1', 'val2']
into their integer dictionary IDs and performs a highly optimized integer comparison against the stored array IDs, which is significantly faster than string matching than string comparisons on raw strings. Summary of Indicators Feature | Where to look | Indicator of Success Plan | 
EXPLAIN
| Presence of 
Decode
node or 
dict_optimize=true
Profile | 
SCAN_NODE
| 
DictDecodeRows
> 0 Logic | 
array_overlap
| Faster execution vs. 
array_low_cardinality_optimize = false
References • sql-reference/System_variable.md • administration/management/FE_configuration.md • using_starrocks/query_acceleration_with_auto_increment.md • sql-reference/sql-functions/array-functions/arrays_overlap.md
k
@Rocky analyze this query profile:
Copy code
[0mSummary[0m
    [0mQueryId: 019e53bb-ece7-7f54-a559-c7a4265ca7a3[0m
    [0mVersion: 4.0.5-000efef[0m
    [0mState: Finished[0m
    [0mTotalTime: 33ms[0m
        [0mExecutionTime: 21.300ms [Scan: 4.530ms (21.27%), Network: 2.703ms (12.69%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 20.196ms (94.82%)][0m
        [0mCollectProfileTime: 0[0m
        [0mFrontendProfileMergeTime: 2.757ms[0m
    [0mQueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 521.143 MB[0m
    [0mTop Most Time-consuming Nodes:[0m
        [1m[31m1. AGGREGATION (id=2) [serialize, update]: 12.334ms (55.47%)[0m
        [1m[38;2;250;128;114m2. OLAP_SCAN (id=0) : 5.661ms (25.46%)[0m
        [0m3. MERGE_EXCHANGE (id=6) [GATHER]: 1.974ms (8.88%)[0m
        [0m4. EXCHANGE (id=3) [SHUFFLE]: 1.699ms (7.64%)[0m
        [0m5. PROJECT (id=1) : 313.299us (1.41%)[0m
        [0m6. AGGREGATION (id=4) [finalize, merge]: 119.706us (0.54%)[0m
        [0m7. TOP_N (id=5) [ROW_NUMBER, TOP-N]: 80.450us (0.36%)[0m
        [0m8. RESULT_SINK: 53.250us (0.24%)[0m
    [0mTop Most Memory-consuming Nodes:[0m
    [0mNonDefaultVariables:[0m
        [0mcatalog: default_catalog -> iceberg[0m
        [0menable_adaptive_sink_dop: false -> true[0m
        [0menable_async_profile: true -> false[0m
        [0menable_profile: false -> true[0m
        [0minteractive_timeout: 3600 -> 28800[0m
        [0mmaterialized_view_rewrite_mode: DEFAULT -> force[0m
[0mFragment 0[0m
│   [0mBackendNum: 1[0m
│   [0mInstancePeakMemoryUsage: 78.969 KB, InstanceAllocatedMemoryUsage: 440.281 KB[0m
│   [0mPrepareTime: ?[0m
└──[0mRESULT_SINK[0m
   │   [0mTotalTime: 53.250us (0.24%) [CPUTime: 53.250us][0m
   │   [0mOutputRows: 34[0m
   │   [0mSinkType: MYSQL_PROTOCAL[0m
   └──[0mMERGE_EXCHANGE (id=6) [GATHER][0m
          [0mEstimates: [row: 34, cpu: 173.22, memory: 173.22, network: 173.22, cost: 147725404.61][0m
          [0mTotalTime: 1.974ms (8.88%) [CPUTime: 455.963us, NetworkTime: 1.518ms][0m
          [0mOutputRows: 34[0m
          [0mPeakMemory: ?, AllocatedMemory: ?[0m
[0m
[0mFragment 1[0m
│   [0mBackendNum: 10[0m
│   [0mInstancePeakMemoryUsage: 959.405 KB, InstanceAllocatedMemoryUsage: 13.058 MB[0m
│   [0mPrepareTime: ?[0m
└──[0mDATA_STREAM_SINK (id=6)[0m
   │   [0mPartitionType: UNPARTITIONED[0m
   └──[0mTOP_N (id=5) [ROW_NUMBER, TOP-N][0m
      │   [0mEstimates: [row: 34, cpu: 173.22, memory: 173.22, network: 173.22, cost: 147724711.73][0m
      │   [0mTotalTime: 80.450us (0.36%) [CPUTime: 80.450us][0m
      │   [0mOutputRows: 34[0m
      │   [0mPeakMemory: ?, AllocatedMemory: ?[0m
      │   [0mOrderByExprs: [<slot 18> 18: primary_category][0m
      └──[0mAGGREGATION (id=4) [finalize, merge][0m
         │   [0mEstimates: [row: 34, cpu: 173.22, memory: 173.22, network: 0.00, cost: 147724018.86][0m
         │   [0mTotalTime: 119.706us (0.54%) [CPUTime: 119.706us][0m
         │   [0mOutputRows: 34[0m
         │   [0mPeakMemory: ?, AllocatedMemory: ?[0m
         │   [0mGroupingExprs: [18: primary_category][0m
         └──[0mEXCHANGE (id=3) [SHUFFLE][0m
                [0mEstimates: [row: 34, cpu: 17.32, memory: 0.00, network: 17.32, cost: 147723585.81][0m
                [0mTotalTime: 1.699ms (7.64%) [CPUTime: 513.949us, NetworkTime: 1.185ms][0m
                [0mOutputRows: 340[0m
                [0mPeakMemory: ?, AllocatedMemory: ?[0m
[0m
[0mFragment 2[0m
│   [0mBackendNum: 10[0m
│   [0mInstancePeakMemoryUsage: 9.138 MB, InstanceAllocatedMemoryUsage: 507.656 MB[0m
│   [0mPrepareTime: ?[0m
└──[0mDATA_STREAM_SINK (id=3)[0m
   │   [0mPartitionType: HASH_PARTITIONED[0m
   │   [0mPartitionExprs: [18: primary_category][0m
   └──[1m[31mAGGREGATION (id=2) [serialize, update][0m
      │   [1m[31mEstimates: [row: 34, cpu: 26858821.19, memory: 17.32, network: 0.00, cost: 147723551.17][0m
      │   [1m[31mTotalTime: 12.334ms (55.47%) [CPUTime: 12.334ms][0m
      │   [1m[31mOutputRows: 340[0m
      │   [1m[31mPeakMemory: ?, AllocatedMemory: ?[0m
      │   [1m[31mGroupingExprs: [18: primary_category][0m
      │   [1m[31mSubordinateOperators: [0m
      │       [1m[31mLOCAL_EXCHANGE [Passthrough][0m
      │   [1m[31mDetail Timers: [0m
      │       [1m[31mAggComputeTime: 11.024ms [min=9.147ms, max=11.566ms][0m
      └──[0mPROJECT (id=1) [0m
         │   [0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?][0m
         │   [0mTotalTime: 313.299us (1.41%) [CPUTime: 313.299us][0m
         │   [0mOutputRows: 14.551M (14550915)[0m
         │   [0mExpression: [102: primary_category][0m
         └──[1m[38;2;250;128;114mOLAP_SCAN (id=0) [0m
                [1m[38;2;250;128;114mEstimates: [row: 14766266, cpu: 268588211.87, memory: 0.00, network: 0.00, cost: 134294105.93][0m
                [1m[38;2;250;128;114mTotalTime: 5.661ms (25.46%) [CPUTime: 1.131ms, ScanTime: 4.530ms][0m
                [1m[38;2;250;128;114mOutputRows: 14.551M (14550915)[0m
                [1m[38;2;250;128;114mTable: : core_channels_mv[0m
                [1m[38;2;250;128;114mSubordinateOperators: [0m
                    [1m[38;2;250;128;114mCHUNK_ACCUMULATE[0m
                [1m[38;2;250;128;114mDetail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime][0m
                    [1m[38;2;250;128;114mIOTaskExecTime: 3.977ms [min=3.736ms, max=4.504ms][0m
                        [1m[38;2;250;128;114mDictDecode: 1.758ms [min=1.612ms, max=2.152ms][0m
                    [1m[38;2;250;128;114mIOTaskWaitTime: 32.836us [min=24.973us, max=49.917us][0m
[0m
r
Based on the query profile provided, here is the analysis of how the low-cardinality (Global Dictionary) optimization and your Materialized View (MV) are performing. 1. Evidence of Successful MV Rewrite The profile confirms that your "invisible rewrite" strategy is working perfectly: * Table Hit: The 
OLAP_SCAN (id=0)
node explicitly shows 
Table: core_channels_mv
. * Rewrite Mode: Under 
NonDefaultVariables
, we see 
materialized_view_rewrite_mode: force
. * The Result: Instead of scanning an external Iceberg table, StarRocks successfully routed the query to your local native MV. 2. Effectiveness of Low-Cardinality Optimization The optimization is highly effective and active. You can see this in the 
OLAP_SCAN
detail timers: * 
DictDecode: 1.758ms
: This is the "smoking gun." This metric only appears when StarRocks is using the Global Dictionary. It indicates the time spent mapping dictionary IDs back to strings. * Throughput: The engine scanned 14.55 million rows in just 5.66ms. * This is an incredible scan speed (approx. 2.5 billion rows per second). * This level of performance is only possible because the engine is processing integer-based dictionary IDs rather than raw strings. If it were scanning raw strings from S3 or even a local unoptimized table, this would likely take an order of magnitude longer. 3. Bottleneck Analysis Since the scan is now so fast (5.6ms), the bottleneck has shifted "up" the plan: * `AGGREGATION (id=2)`: 12.334ms (55.47%) * Most of the query time is spent on 
AggComputeTime
(11.024ms). This is the CPU overhead of grouping the 14.5 million rows into the 34 unique categories. * Because the scan was so fast, the actual computation of the 
GROUP BY
is now the most "expensive" part of the query. 4. Summary of Indicators Metric | Value | Significance Rows Scanned | 14,550,915 | Massive data volume processed. Scan Time | 5.66ms | Extremely fast; indicates local cache + dictionary usage. DictDecode | 1.758ms | Confirmed: Low-cardinality optimization is active. IOTaskWaitTime | 32.836us | Very low; indicates data was already in the StarOS local cache. Conclusion Your setup is performing optimally. The query is hitting the local MV, it is pulling data almost instantly from the local cache (avoiding S3), and it is using the Global Dictionary to process the 
primary_category
column. One Final Tip: If you want to make this even faster, ensure your MV is sorted by 
primary_category
in the 
ORDER BY
clause of the MV definition. This would allow StarRocks to use the prefix index and potentially reduce the Aggregation time.
k
@Rocky I’m running 10 cn nodes on r8gd.4xlarge machines. Would it be more effective to scale up or scale out against large, wide column materialized views
r
For large, wide-column Materialized Views on 
r8gd
instances, Scaling Up (moving to larger instance sizes like 
r8gd.8xlarge
or 
16xlarge
) is generally more effective than scaling out to more 
4xlarge
nodes. Here is why scaling up is the preferred strategy for this specific workload: 1. Memory Headroom for Wide Columns Wide columns (especially many 
ARRAY
or 
STRING
columns) significantly increase the memory footprint of a single "chunk" of data during processing. * The Problem: StarRocks processes data in vectorized chunks. With wide columns, each chunk consumes more RAM. On a 
4xlarge
(128GB), a high degree of parallelism (DOP) combined with wide rows can lead to memory pressure or frequent spills to disk. * The Scale-Up Advantage: Larger instances (e.g., 
8xlarge
with 256GB) provide more memory per CPU core, allowing the engine to keep more wide rows in memory during complex aggregations and joins without hitting safety limits. 2. Efficiency of Local NVMe (r8gd series) Since you are using the 
d
variant (local NVMe), your MV data and StarOS cache reside on these disks. * Scale Out: Adding more 
4xlarge
nodes increases your total aggregate disk space, but it also increases the "fragmentation" of your data. * Scale Up: AWS scales NVMe performance (IOPS and Throughput) linearly with the instance size. A 
16xlarge
will typically have much higher sequential read/write throughput than a 
4xlarge
. For wide-column scans, sequential I/O throughput is usually the primary bottleneck. 3. Reduced Shuffle Overhead Wide columns are expensive to move across the network. * The Problem: In an MPP (Massively Parallel Processing) architecture, data is often "shuffled" (sent over the network) between nodes for Joins or Group-Bys. If your columns are wide, the network cost of these shuffles becomes a major part of your query latency. * The Scale-Up Advantage: By having more CPU and memory on a single node, StarRocks can perform more of the computation locally within the same machine. This reduces the amount of data that needs to be serialized, sent over the wire, and deserialized on another node. 4. Better CPU-to-Network Ratio The 
r8gd.4xlarge
has 25 Gbps network bandwidth. While scaling out to 20 nodes increases aggregate bandwidth, the per-query performance is often limited by how fast a single fragment can process and send data. Larger instances often have higher network caps (up to 50 or 100 Gbps), which helps when transferring wide rows between stages. When should you Scale Out instead? You should only prioritize Scaling Out over Scaling Up if: * High Concurrency: You have hundreds of users running small queries simultaneously, and you need more "slots" to handle the query volume. * Storage Bound: You have run out of local NVMe capacity for your cache/MVs and need more physical disks to hold the data. Summary Recommendation For Wide-Column MV performance: 1. Upgrade to 
r8gd.8xlarge
or `16xlarge`: This provides the memory and I/O density required to handle wide rows efficiently. 2. Monitor `PeakMemoryUsage`: Check your query profiles. If 
PeakMemoryUsage
is approaching the limit of your 
4xlarge
nodes, scaling up is mandatory. 3. Adjust Parallelism: After scaling up, ensure you increase 
pipeline_dop
(or let the engine auto-set it) to take advantage of the additional cores on the larger machines. References • administration/management/Best_practices_for_instance_selection.md • administration/Scale_up_StarRocks.md • introduction/StarRocks_architecture.md • administration/Scale_out_StarRocks.md
k
@Rocky Based on the query profile i shared, how much faster or reduced data scanned would adding an inverted index on a generated string column for an array be, or a bitmap index for a 15 million row mv
r
Based on the query profile you provided, adding an index (Inverted or Bitmap) to your 15 million row Materialized View would have a specific, measurable impact on performance. Here is the technical breakdown of the expected gains: 1. The "Data Scanned" Impact Your profile shows the 
OLAP_SCAN
currently outputs 14.55 million rows in 5.66ms. * Current State: You are already benefitting from the Global Dictionary (
DictDecode: 1.758ms
). This means StarRocks is not "scanning" raw strings; it's scanning integer IDs. * With an Index: An index (Bitmap or Inverted) allows the engine to skip rows entirely at the storage layer rather than scanning them and then filtering them in memory. * Reduction: If your 
WHERE
clause is selective (e.g., filtering for a specific category that only appears in 1% of the data), the "Rows Scanned" and 
OutputRows
would drop from 14.5M to ~145k. 2. The "Speed" Impact (Aggregation is the Bottleneck) In your profile, the Scan (5.6ms) is not the bottleneck—the Aggregation (12.3ms) is. * How Indexes Help Here: Indexes don't make the math of aggregation faster, but they reduce the volume of data the aggregator has to touch. * Estimated Gain: Since the 
AggComputeTime
(11.02ms) is directly tied to processing 14.5M rows, if an index reduces the rows passed to the aggregator by 90%, your aggregation time would likely drop from 11ms to ~1ms. * Overall Latency: Your total query time could drop from 33ms to ~10-15ms. 3. Which Index to Choose? Index Type | Best Use Case for your MV | Expected Effectiveness Bitmap Index | Used for 
WHERE category = 'value'
. Best for columns with cardinality between 10k and 100k. | High, but only if you have a selective filter. If you filter for a very common category, it may be slower due to the overhead of loading the index. Inverted Index (GIN) | Used for 
ARRAY
element searches (e.g., 
array_contains
) or 
MATCH
queries on your generated string column. | Highest for array queries. StarRocks v4.1+ built-in inverted indexes can filter row IDs for specific array elements much faster than a full scan. Summary Recommendation * Add an Inverted Index (GIN) if you are frequently querying specific elements inside the 
ARRAY
(e.g., 
WHERE array_contains(tags, 'search_term')
). * Add a Bitmap Index if you are filtering by the 
primary_category
column and that column has more than a few hundred unique values. * Don't expect much change in "Data Volume Read": Because you have a local MV and Global Dictionary, your I/O is already minimal. The win here is CPU cycles saved in the Aggregation stage by feeding it fewer rows. How to verify: After adding the index, look for 
BitmapIndexFilterRows
 or 
InvertedIndexFilterRows
 in the 
OLAP_SCAN
node of the query profile. If these numbers are high, the index is doing the heavy lifting. References • table_design/indexes/Bitmap_index.md • table_design/indexes/inverted_index.md • best_practices/query_tuning/schema_tuning.md • unloading/Spark_connector.md • table_design/indexes/Prefix_index_sort_key.md • using_starrocks/distinct_values/Using_bitmap.md References • table_design/indexes/Bitmap_index.md • unloading/Spark_connector.md
k
@Rocky is it better to apply a bloom filter index or bitmap index against a column with 1000 unique values, but many rows can have the same value
r
Against a column with 1000 unique values, a Bitmap Index is generally the better choice. While both indexes help skip data at the storage layer, they are optimized for different cardinality levels. Here is the breakdown for your specific scenario: 1. Bitmap Index (Recommended for 1,000 Unique Values) In StarRocks, a bitmap index is highly effective for "medium" cardinality columns like yours. * The 1/1000 Rule: StarRocks documentation specifically recommends bitmap indexes when a filter can eliminate at least 999/1,000 of the data. With 1,000 unique values, a query for a single value (e.g., 
WHERE col = 'A'
) hits this exact threshold. * Determinism: Unlike Bloom Filters, Bitmaps are deterministic. They tell the engine exactly which rows to read, which can significantly speed up the 
OLAP_SCAN
node in your profile. * Operator Support: Bitmaps support 
=
, 
IN
, 
IS NULL
, and even range queries like 
>
, 
<
, and 
BETWEEN
. * Multi-Column Queries: If you often filter by this column and another low-cardinality column together, StarRocks can perform a very fast bitwise 
AND/OR
on the indexes before even touching the data. 2. Bloom Filter Index (Better for >100,000 Unique Values) Bloom Filters are designed for high-cardinality columns with very low duplication (like 
order_id
or 
user_id
). * Probabilistic Nature: Bloom Filters can have "false positives." With only 1,000 unique values, the filter might occasionally tell the engine a value exists in a data block when it doesn't, leading to unnecessary I/O. * Limited Operators: They only support 
=
and 
IN
queries. If you ever need to perform a range scan on that column, a Bloom Filter provides zero benefit. * High Cardinality Focus: They are best used when the cardinality is so high that a Bitmap Index would become too large and expensive to maintain. Summary Comparison Feature | Bitmap Index | Bloom Filter Index Ideal Cardinality | Low to Medium (10 - 10,000) | High (>100,000) Your Case (1,000) | Optimal | Sub-optimal Operators | 
=
, 
IN
, 
>
, 
<
, 
BETWEEN
| 
=
, 
IN
only Storage | Increases with cardinality | Relatively fixed/small Performance | Best for filtering many rows | Best for skipping entire files Recommendation Apply a Bitmap Index. It is specifically tuned for the 1,000-cardinality range and will provide more consistent performance across a wider variety of query types. To add it:
Copy code
sql
ALTER TABLE your_mv_name ADD INDEX index_name (your_column_name) USING BITMAP;
Note: After adding, check your query profile for the 
BitmapIndexFilterRows
metric to verify the index is being used. References • knowledge/usage/best-practice.md • table_design/indexes/Bloomfilter_index.md • table_design/indexes/Bitmap_index.md • best_practices/query_tuning/schema_tuning.md
k
@Rocky what if 10% of rows will match the query? for example the column is a category classification, where many channels can have the same classification, even though total possible unique values is 1000
r
If 10% of your rows match the query (e.g., 1.5M out of 15M), the effectiveness of both Bitmap and Bloom Filter indexes drops significantly. In fact, they might even slightly degrade performance due to index loading overhead. Here is why 10% selectivity changes the game: 1. The "Scattered Page" Problem StarRocks reads data in Pages (typically 64KB - 1MB blocks). * How indexes work: For an index to actually speed up a query, it must allow StarRocks to skip reading entire pages from disk. * The 10% Reality: If 10% of your rows match the query and those rows are randomly distributed, nearly every page in your table will contain at least one matching row. * Result: Because the index can't prove that a page is "empty" of your target value, StarRocks still has to load every single page. You end up reading 100% of the data anyway, plus the extra time spent loading and processing the index. 2. Bitmap Index at 1,000 Cardinality * Threshold: By default, StarRocks uses a threshold for Bitmaps: 
(values in filter / total cardinality) < 1/1000
. With 1000 unique values, a single-value filter is exactly 
1/1000
. This is on the razor's edge of whether the optimizer will even choose to use the index. * Efficiency: Even if used, a bitmap for a value that covers 10% of the table is "dense." The CPU cost to decode that bitmap and then find the rows can be higher than just doing a linear scan of the column (especially since you have a Global Dictionary on that string column). 3. Bloom Filter Index at 10% Selectivity * No Benefit: Bloom Filters are designed to skip files when a value is absent. If a category appears in 10% of the rows, it is almost certainly present in every data file (tablet) and every large segment. * False Positives: At this cardinality, the Bloom Filter will provide almost zero "skips," making it useless for this specific column. What should you do instead? If this category filter is your most frequent query, there are two better ways to speed it up: 1. Change the Sort Key (Best Performance): If you make this category column the first column in your 
ORDER BY
(Sort Key) for the MV, StarRocks will store all rows with the same category physically together. _ This ensures that 90% of the pages will contain _zero* matching rows. * The 
OLAP_SCAN
will skip those pages entirely using the Prefix Index. This is much faster than any Bitmap or Bloom Filter. 2. Inverted Index (GIN): If you cannot change the sort order, a StarRocks Inverted Index is more robust at higher selectivity. While it still struggles with page-skipping if data is scattered, it is generally more efficient at row-level filtering than Bitmaps for complex string/array data. Final Verdict: For a 10% match rate on 1,000 unique values: * Bitmap Index: ❌ Not recommended (likely no page-skipping). * Bloom Filter: ❌ Not recommended (useless at this cardinality). * Sort Key / Prefix Index: ✅ Highly Recommended (the only way to truly skip 90% of the I/O). * Do Nothing: ⚠️ Since you have a Global Dictionary and a small MV (15M rows), a raw scan is already very fast (~5ms in your profile). The overhead of managing an index might not be worth the 1-2ms gain. References • table_design/indexes/Bitmap_index.md • table_design/indexes/Prefix_index_sort_key.md • table_design/indexes/Bloomfilter_index.md • table_design/indexes/inverted_index.md References • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md
k
@Rocky Example statement to remove existing bloom filter from an MV that was created during the CREATE MATERIALIZED VIEW command
r
To remove a bloom filter index from a Materialized View (MV), the statement depends on which type of bloom filter was created. 1. Standard Bloom Filter (Property-based) If you defined the bloom filter using the 
PROPERTIES("bloom_filter_columns" = "...")
syntax during the 
CREATE MATERIALIZED VIEW
command, you remove it by setting the property to an empty string. To remove all bloom filters:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET ("bloom_filter_columns" = "");
To remove one specific column but keep others: If you had 
col1, col2
and only want to remove `col2`:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET ("bloom_filter_columns" = "col1");
2. N-gram Bloom Filter (Index-based) If you created an N-gram Bloom Filter (used for fuzzy string matching/`LIKE` queries), it was likely created as a named index. You remove these using the 
DROP INDEX
syntax. To remove an N-gram index:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name DROP INDEX index_name;
Important Notes: * Asynchronous Operation: Removing an index is an asynchronous background task. The index will not disappear instantly. * Checking Progress: You can monitor the progress of the removal by running:
Copy code
sql
    SHOW ALTER TABLE FROM your_database_name;
(Note: Even for MVs, the internal storage alteration is tracked as a table-level alter job). * Verification: Once finished, run 
SHOW CREATE MATERIALIZED VIEW your_mv_name;
to verify that the 
bloom_filter_columns
property is gone or empty. References • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • table_design/indexes/Bloomfilter_index.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/indexes/Ngram_Bloom_Filter_Index.md References • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md
3 Views
Open in Slack
PreviousNext
Powered by