@Rocky how to support secondary indexes on materialized views for shared data cluster for sorting

In StarRocks, optimization for sorting and filtering on materialized views (MVs) in a shared-data cluster involves two distinct mechanisms: the Sort Key (for sorting/range performance) and Secondary Indexes (for filtering performance). 1. Support for Sorting: The Sort Key Sorting performance is primarily driven by the Sort Key (also known as the Prefix Index), not by secondary indexes. When you create an asynchronous materialized view, you define the sort key using the

ORDER BY

clause. * Mechanism: Data is physically stored in the order of the columns specified. This creates a "Prefix Index" that allows for extremely fast range scans and prefix filtering. * How to implement:

sql
    CREATE MATERIALIZED VIEW mv_name
    DISTRIBUTED BY HASH(col1)
    ORDER BY (col1, col2) -- This defines the Sort Key
    AS
    SELECT col1, col2, col3 FROM base_table;

* Shared-data Note: The Sort Key works identically in shared-data clusters, as it is part of the underlying segment file structure. 2. Support for Secondary Indexes If you need additional acceleration for columns that are not part of the Sort Key (to avoid full scans when filtering), you can use secondary indexes. Bitmap & Bloom Filter Indexes Asynchronous MVs support Bitmap and Bloom Filter indexes just like regular tables. * Bitmap Index: Best for filtering on low-to-medium cardinality columns. * Bloom Filter: Best for high-cardinality columns (like IDs) for equality checks. * Syntax:

sql
    -- Create a Bitmap index on an existing MV
    CREATE INDEX idx_name ON mv_name(column_name) USING BITMAP;

    -- Create a Bloom Filter index
    ALTER MATERIALIZED VIEW mv_name SET ("bloom_filter_columns" = "col1,col2");

Inverted Index (GIN) Starting from v4.1, shared-data clusters support built-in Inverted Indexes. * Note: In shared-data clusters, you must use the built-in implementation (based on StarRocks' native bitmap format). The CLucene-based implementation is not supported in shared-data mode. * Syntax:

sql
    -- On an MV in a shared-data cluster (v4.1+)
    CREATE INDEX gin_idx ON mv_name(col_text) USING GIN;

3. Summary Table for Shared-Data MVs Feature | Support Status | Best For Sort Key (

ORDER BY

) | Supported (v3.0+) | Sorting, range scans, and prefix filtering. Bitmap Index | Supported | Filtering on columns with few distinct values. Bloom Filter Index | Supported | Point lookups on high-cardinality columns. Inverted Index (GIN) | v4.1+ (Built-in only) | Text search and complex keyword filtering. Sync MVs | v3.4+ | Simple aggregate acceleration (treated as base table indexes). Recommendation: To optimize for sorting, ensure the columns you sort or filter by most frequently are the first columns in the

ORDER BY

clause of your

CREATE MATERIALIZED VIEW

statement. Use secondary indexes (Bitmap/GIN) only as a supplement for columns that cannot be part of the prefix sort key. References • table_design/indexes/indexes.md • table_design/indexes/Prefix_index_sort_key.md • best_practices/table_clustering.md

@Rocky I want to add indexes on individual columns so that I can performantly sort on any individual column

In StarRocks, secondary indexes (Bitmap, Bloom Filter, and Inverted Index) are designed to accelerate filtering (WHERE clauses), but they cannot be used to provide a pre-sorted order for

ORDER BY

operations. Because StarRocks is a columnar storage engine, the data is physically ordered on disk according to a single Sort Key. To achieve high-performance sorting on any individual column, you should use the following strategies: 1. The Materialized View Strategy (Best for multiple sort orders) Since a single table can only have one physical sort order, the standard way to support high-performance sorting on different columns is to create Asynchronous Materialized Views. Each MV can have its own

ORDER BY

clause. * How it works: When you query a column and

ORDER BY

it, the StarRocks optimizer can automatically route the query to the MV that is already physically sorted by that column. * Example:

sql
    -- Base table sorted by 'id'
    CREATE TABLE base_table (id INT, price INT, created_at DATETIME)
    DUPLICATE KEY(id)
    DISTRIBUTED BY HASH(id);

    -- MV for performant sorting by 'price'
    CREATE MATERIALIZED VIEW mv_sort_price
    DISTRIBUTED BY HASH(id)
    ORDER BY (price) -- Physical sorting on disk
    AS SELECT id, price, created_at FROM base_table;

    -- MV for performant sorting by 'created_at'
    CREATE MATERIALIZED VIEW mv_sort_time
    DISTRIBUTED BY HASH(id)
    ORDER BY (created_at)
    AS SELECT id, price, created_at FROM base_table;

2. The "Decoupled Sort Key" (For Primary Key Tables) If you are using the Primary Key model (v3.0+), you can decouple the sorting order from the Primary Key. While this still only allows one sort order per table, you can change it dynamically without re-creating the table. * Syntax:

ALTER TABLE my_table ORDER BY (new_column);

* Behavior: This is an asynchronous background operation. It will eventually re-sort the data on disk to make

ORDER BY new_column

extremely fast (using the Prefix Index). 3. Why Secondary Indexes don't work for Sorting * Bitmap/Bloom Filter: these are essentially "checklists" used to skip data blocks that don't match a filter. They contain no information about the relative order of the values. * Inverted Index (GIN): Designed for keyword searching in text. While it helps find rows quickly, it does not store data in a way that assists the execution engine in avoiding a "Top-N" sort step. Summary of Recommendations Goal | Strategy Filter by any column | Add Bitmap or Inverted Index. Sort by any column | Create Materialized Views with different

ORDER BY

keys. Point Lookup | Use Bloom Filter or Primary Key. Note for Shared-Data Clusters: The Materialized View approach is highly recommended for shared-data clusters because the data for MVs is stored in remote storage (S3/OSS), making it cost-effective to maintain a few extra "sorted copies" of your hot data for performance. References • knowledge/usage/best-practice.md • table_design/indexes/Prefix_index_sort_key.md • faq/Sql_faq.md • table_design/table_types/primary_key_table.md • best_practices/table_clustering.md

@Rocky I have a 50 million row table I need to support sorting on individual 10 columns. Is the best way to really crease 10 individual mvs? I can't use one mv with additional configuration?

For a table with 50 million rows, creating 10 individual materialized views (MVs) is generally not recommended. 50 million rows is a relatively small dataset for StarRocks, and the overhead of maintaining 10 MVs (storage, write amplification, and background refresh) will likely outweigh the benefits. There is no "additional configuration" that allows a single MV to have 10 different physical sort orders. However, you can achieve excellent performance using the following approach: 1. Leverage "Top-N" Optimization (Use

LIMIT

) StarRocks is highly optimized for

ORDER BY column LIMIT N

queries. * How it works: Instead of sorting the entire 50 million rows, StarRocks uses a heap-based "Top-N" algorithm that only maintains the best $N$ records in memory. * Performance: For a 50M row table, a Top-N sort on a single column typically completes in sub-second or low-second range, even if that column is not in the Sort Key. * Recommendation: Simply query the base table. As long as you are using a

LIMIT

clause (common for dashboards and pagination), the performance is usually sufficient without an MV. 2. Choose a Smart "Base" Sort Key You can't sort by all 10 columns perfectly, but you can optimize for the most frequent ones. * Sort Key: Place the 1-2 most frequently sorted/filtered columns at the beginning of your

ORDER BY

clause in the base table. * Effect: This creates a Prefix Index for those columns, making their sorts virtually instantaneous. 3. Use Z-ORDER (For Filtering, not Sorting) If your goal was to filter on any of those 10 columns, you could use Z-ORDER clustering (available since v3.1) in your table or MV definition:

sql
-- This helps with WHERE col1=x OR col2=y, but NOT with ORDER BY col1
CREATE TABLE my_table (...)
ORDER BY ZORDER (col1, col2, col3, ..., col10);

Warning: Z-ORDER improves multi-column filtering but does not provide a linear physical sort for sorting. It will actually make a pure

ORDER BY

on a single column slightly slower than a standard sort key. 4. When to actually use an MV? Only create an MV for an individual column if: 1. High Frequency: That specific sort is triggered hundreds of times per minute. 2. No Limit: You are performing a "Global Sort" (sorting all 50M rows and returning them all, which is rare for analytical apps). 3. Complex Queries: The sort is part of a very complex join or aggregation that is already struggling. Summary Strategy 1. Do nothing first: Test the query

SELECT ... FROM table ORDER BY col_X LIMIT 100

. 2. Monitor: If the query takes more than 1-2 seconds and your application requires 100ms, only then consider an MV for that specific column. 3. Shared-Data Benefit: In a shared-data cluster, ensure your Data Cache is large enough. Once the column data is cached in the BE's local SSD/Memory, sorting 50M rows becomes extremely fast. Crucial Note: Secondary indexes (Bitmap, GIN, Bloom Filter) will not help with the

ORDER BY

clause; they only assist with the

WHERE

clause. References • table_design/indexes/Prefix_index_sort_key.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • best_practices/table_clustering.md • knowledge/usage/best-practice.md References • best_practices/table_clustering.md • table_design/indexes/Bitmap_index.md

@Rocky I had a large mv performing well and added new compute nodes, then performance degraded. What is the best way to ensure all compute nodes have caches mv data

Performance degradation after scaling a shared-data cluster is usually caused by cache misses. In a shared-data architecture, data is routed to nodes based on consistent hashing of tablet IDs. When you add new nodes, the hash ring changes, and many tablets are reassigned to the new nodes, which have "cold" local caches. To ensure all compute nodes (including new ones) have the Materialized View (MV) data cached, use the following methods: 1. Proactive Cache Warmup (

CACHE SELECT

) Introduced in v3.3.0, this is the most efficient way to "warm up" specific data across the cluster without running full heavy queries. * Command: Run a

CACHE SELECT

on the MV. This forces the cluster to fetch the data from remote storage and populate the local disks of the nodes currently responsible for those tablets.

sql
    CACHE SELECT * FROM mv_name;

* Why it helps: Unlike a standard

SELECT

, this is optimized to populate the cache across all relevant nodes for the table's distribution. 2. Enable "Cache Sharing" If you are on v3.3+, you can enable Cache Sharing. This allows a new node to fetch cached data from an old node over the network rather than downloading it from remote storage (S3/HDFS), which is typically much faster. * Configuration:

sql
    -- Enable on FE
    ADMIN SET FRONTEND CONFIG ("enable_trace_historical_node" = "true");

    -- Enable for the session or globally
    SET GLOBAL enable_datacache_sharing = true;

* Effect: This significantly reduces "performance jitter" during elastic scaling by leveraging the existing caches on the original nodes. 3. Disable Asynchronous Cache Population (Temporary) By default, StarRocks populates the cache asynchronously to avoid slowing down the first query. This means one query might only cache a portion of the data. To force a full, immediate cache populate during your warmup: * Command:

sql
    SET enable_datacache_async_populate_mode = false;
    CACHE SELECT * FROM mv_name;
    -- Set it back to true after warmup
    SET enable_datacache_async_populate_mode = true;

4. Verify Cache Status You can check if the data is being read from the local cache or remote storage by looking at the Query Profile. * Metrics to watch: * `DataCacheReadBytes`: Data read from local disk cache. * `CompressedBytesReadRemote`: Data read from remote storage (higher values indicate a cache miss). * `DataCacheReadPeerBytes`: Data read from other nodes (if Cache Sharing is enabled). Summary Checklist 1. Check Version: Ensure you are on v3.3+ for

CACHE SELECT

and Cache Sharing. 2. Run Warmup: Execute

CACHE SELECT * FROM your_mv

after adding nodes. 3. Monitor: Use the Query Profile to confirm

CompressedBytesReadRemote

drops to near zero after the warmup. References • data_source/data_cache.md • deployment/shared_data/feature-support-shared-data.md • data_source/data_cache_troubleshooting.md • faq/shared_data_faq.md References • deployment/shared_data/feature-support-shared-data.md • data_source/data_cache_troubleshooting.md • data_source/data_cache.md • using_starrocks/caching/block_cache.md