@Rocky Is it on the roadmap to eventually add index support for array values in primary key tables? Right now array_contains() functions are very slow, especially for records with large arrays. Are there any known optimizations?

As of the latest StarRocks versions (v3.x and v4.x roadmap), here is the status of index support for arrays and known optimizations for `array_contains()`: 1. Roadmap & Index Support Direct indexing of individual ARRAY elements (to accelerate

array_contains

) is primarily being addressed through the Full-text Inverted Index (GIN). * Primary Key Table Support: Full-text inverted indexes are supported on Primary Key tables starting from v4.0. In earlier versions (v3.3+), they were limited to Duplicate Key tables. * Built-in Implementation: Starting in v4.1, StarRocks introduced a "built-in" inverted index that works on both shared-nothing and shared-data clusters. * Current Limitation: While inverted indexes are evolving rapidly, they are currently optimized for

VARCHAR

columns using

MATCH

predicates. Direct acceleration of the

ARRAY

data type via

array_contains

using an inverted index is a highly requested feature and is a core part of the ongoing "Semi-structured Data Analysis" roadmap for 2025-2026. 2. Known Optimizations & Workarounds Since

array_contains()

currently often results in a full scan of the array column, you can use these strategies to improve performance: A. Low-Cardinality Optimization (Global Dictionary) If your array elements are strings (e.g.,

ARRAY<VARCHAR>

), StarRocks can apply low-cardinality optimization. This automatically establishes a global dictionary mapping strings to integers. * How it helps: It allows the engine to perform the "contains" check on integer IDs rather than raw strings, which is significantly faster and uses less memory. * Prerequisite: This is generally handled automatically by the Cost-Based Optimizer (CBO) for string-based arrays in recent versions. B. Bloom Filter & Bitmap Indexes (with a caveat) You can build Bloom Filter or Bitmap indexes on the array column in a Primary Key table. * The Caveat: These indexes treat the entire array as a single value (a "blob"). They help if you are searching for an exact match of the whole array (e.g.,

WHERE array_col = [1, 2, 3]

), but they do not accelerate

array_contains(array_col, 1)

. C. Manual "Inverted" Design (Schema Workaround) For mission-critical performance on large arrays, many users adopt a traditional relational "bridge table" or a flattened table: 1. Flattened Table: Instead of one row with a large array, use a Duplicate Key table where each array element is a separate row. You can then use standard Bitmap indexes or the new Inverted Index on that element column. 2. Materialized Views: You can create a synchronous Materialized View that uses

unnest()

to flatten the array. This allows you to query the "flattened" view while StarRocks keeps it in sync with the Primary Key table. D. Use

array_filter

for Large Arrays If you are doing complex checks on large arrays, sometimes

array_filter

combined with

array_length

can be slightly more efficient in specific execution plans, though it doesn't solve the "full scan" issue. Summary Recommendation * If you are on v3.x: Your best bet for performance is the Low-Cardinality optimization for string arrays or flattening the data into a Duplicate Key table to leverage the v3.3 Inverted Index. * If you can wait for v4.x: Watch for the official release of v4.0, which brings Inverted Index support to Primary Key tables, providing the underlying infrastructure to eventually accelerate array element lookups natively. References • table_design/indexes/inverted_index.md • best_practices/query_tuning/schema_tuning.md

@Rocky I'm currently on version 4.0.7-b75f536. Can inverted indexes help me? Seems to be experimental feature still

In version 4.0.7-b75f536, inverted indexes (GIN) are indeed supported on Primary Key tables, but their ability to help you with

array_contains()

depends on your schema and query style. 1. Current Support Status (v4.0.7) While StarRocks v4.0 introduced inverted index support for Primary Key tables, it is primarily optimized for text search (

VARCHAR

) and JSON fields. * Native Array Acceleration: Currently,

array_contains()

on a column of type

ARRAY

does not automatically leverage the inverted index in the same way a

MATCH

predicate does for strings. * Experimental Status: In v4.0.x, the CLucene-based inverted index is still considered a Beta feature. You must enable it by setting

enable_experimental_gin = true

in your FE configuration (

ADMIN SET FRONTEND CONFIG ("enable_experimental_gin" = "true");

). 2. Can it help you right now? If your arrays contain string tags, you can achieve "index speed" by using one of these two patterns instead of a raw

ARRAY

type: * The "Space-Separated String" Pattern: Convert your

ARRAY<VARCHAR>

into a single

VARCHAR

column where elements are separated by spaces. Create an inverted index with an

english

or

standard

parser. You can then use the

MATCH

operator:

sql
    -- Faster than array_contains on large records
    SELECT * FROM table WHERE tags_string MATCH 'target_tag';

* Flat JSON Pattern (v4.0+): If you store your arrays inside a JSON column, StarRocks 4.0’s "Flat JSON" optimization and inverted index support can accelerate queries on specific JSON paths. 3. Known Optimizations for

array_contains()

If you must stay with the

ARRAY

type and cannot use inverted indexes yet, consider these optimizations: 1. Global Dictionary (Low-Cardinality): Ensure your array elements are strings. StarRocks often optimizes string-based arrays using a global dictionary, which allows it to compare integer IDs instead of raw strings during the scan. 2. Bloom Filter Index: While a Bloom Filter doesn't index inside the array, it can skip entire data pages if they definitely don't contain the specific "blob" (the whole array). This only helps if you are looking for an exact match of the entire array, not an element. 3. Materialized View (Flattening): Create a synchronous Materialized View that uses

unnest()

to flatten the array into a separate table. You can then put a standard Bitmap or Inverted index on the flattened column. This is the most reliable way to get high-performance element lookups in current versions. 4. Future Roadmap Native acceleration of the

ARRAY

data type via

array_contains

using the inverted index is on the roadmap (targeting v4.1 and beyond). The goal is to allow

CREATE INDEX ... ON (array_col)

to automatically index elements so that

array_contains

behaves as a high-speed index lookup rather than a full-scan operation. Recommendation: Since you are already on v4.0.7, try the Flat JSON approach if your data is semi-structured, or the Space-Separated String workaround if you need immediate sub-second performance on millions of large arrays. References • sql-reference/sql-functions/array-functions/array_contains.md • sql-reference/sql-functions/array-functions/array_contains_all.md • sql-reference/sql-functions/array-functions/array_contains_seq.md • sql-reference/data-types/semi_structured/Array.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md