@Rocky what's the expected performance difference between array_contains() for an ARRAY<VARCHAR> col and a simple string check on a VARCHAR col? Typically will only have 1 item in the array column.

For a scenario where you typically only have one item, a standard

VARCHAR

column will offer significantly better performance than an

ARRAY<VARCHAR>

column using

array_contains()

. The expected performance difference comes down to storage-level pruning and indexing: 1. Pruning and Indexing (The Biggest Difference) * VARCHAR: Standard string comparisons (

col = 'value'

) can leverage almost all of StarRocks' primary optimizations: * Prefix Index (Sort Key): If the

VARCHAR

col is a sort key, StarRocks can skip data blocks entirely.

ARRAY

columns cannot be used as sort keys. * ZoneMap: StarRocks stores min/max values for

VARCHAR

pages. It can prune data before reading it. ZoneMaps are not effective for

ARRAY

elements. * Bloom Filter: You can create a Bloom Filter index on a

VARCHAR

column to quickly check if a value exists in a data file. Bloom Filters are generally not supported for

ARRAY

types. * ARRAY: Filtering on an array element using

array_contains()

is typically treated as a function execution rather than a push-down predicate. This means StarRocks often has to scan the data first and then apply the filter, which is much slower. 2. Execution Overhead * Vectorization: While StarRocks' array functions are vectorized,

array_contains()

still incurs the overhead of processing array metadata (offsets and lengths) for every row. * Function vs. Operator:

col = 'value'

is a core operator that the storage engine understands and can use to skip I/O.

array_contains(col, 'value')

is a function that usually executes after the data is already loaded into memory. 3. Community Benchmarks Real-world usage and issues (such as GitHub #58362) have shown that

array_contains()

can be orders of magnitude slower (sometimes 100x or more) than scalar comparisons or even alternative array methods like

UNNEST

when dealing with large datasets, due to the lack of predicate pushdown. Recommendation If your business logic allows it (i.e., you almost always have one value), use a

VARCHAR

column. * If you occasionally need to store multiple values, consider if a delimited string (and using

LIKE

or

split

) is acceptable, though even then,

VARCHAR

equality remains the gold standard for performance. * If you must use

ARRAY<VARCHAR>

, ensure you are on a recent version (v3.3+) and check if Low Cardinality Optimization (

enable_low_cardinality_optimize

) is enabled, as it can provide some dictionary-based speedups for

ARRAY<VARCHAR>

. References • table_design/indexes/inverted_index.md • best_practices/query_tuning/schema_tuning.md • table_design/indexes/vector_index.md