< Rocky> For the built in inverted index in v4 1 is it more StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> For the built in inverted index in ...

Minn Fong

07/14/2026, 2:57 PM

@Rocky For the built in inverted index in v4.1, is it more efficient to set the dict_gram_num parameters to larger values if I know I don't need smaller ones? Currently I have a column called deal_ids. They are an array of strings representing deal_ids attached to a request. I am thinking of testing out the new indexes. Current indexes won't work because my deal_ids are not strictly alphanumeric. Many of them contain dashes ('-') and underscores ('_'). For instance, MGNI-ABC-123. I am thinking of making either a new column that is just a varchar of comma-separated or space-separated deal_id values and using the built-in indexes with dict_gram_num specifications on top. Given my situation, recommend step by step how I should proceed. Currently, the issue is that array_contains(deal_ids, '<deal_id>') is a very cpu intensive operation.

Rocky

07/14/2026, 2:58 PM

For the built-in inverted index in StarRocks v4.1, your understanding of dict_gram_num
is mostly correct, but its "efficiency" depends on whether you are doing exact matches or wildcard/substring searches. 1. Is a larger
dict_gram_num
more efficient? Yes, in terms of storage and dictionary filtering speed, but with a strict limitation. * How it works: The

dict_gram_num

builds an n-gram index on the dictionary entries themselves. If you search for

%ABC-123%

, StarRocks uses the n-grams to quickly find which dictionary entries contain that string, rather than scanning the entire dictionary. * The "Larger is Better" Rule: A larger value (e.g.,

) creates fewer n-grams, resulting in a smaller index size and less overhead during ingestion. * The Catch: If your search pattern is shorter than the

dict_gram_num

, the n-gram index cannot be used at all, and StarRocks will fall back to a full dictionary scan. * Recommendation: If you know your search strings (deal IDs) are always long (e.g., 8+ characters) and you only ever search for patterns at least 4 characters long, setting

dict_gram_num

is more efficient than

. ────────── 2. Step-by-Step Recommendation for Deal IDs Since

array_contains

is currently your bottleneck and your IDs contain special characters like

and

, follow these steps to migrate to the v4.1 built-in index. Step 1: Decide on Column Type (Keep the Array) You do not need to convert the array to a comma-separated string. StarRocks' inverted index (GIN) supports

ARRAY<STRING>

natively. Keeping it as an array is generally more efficient for storage and prevents issues with delimiter collisions. Step 2: Choose the Right Parser This is the most important step for your specific IDs (

MGNI-ABC-123

). * Do NOT use `english`: The English parser splits strings at

and

. It would turn

MGNI-ABC-123

into three tokens:

MGNI

ABC

, and

. You wouldn't be able to easily search for the exact ID. * Use
parser
= `none`: This treats each element in your array as a single, exact token. The inverted index will store

MGNI-ABC-123

as one entry. Step 3: Create the Index Since you are on v4.1, use the

builtin

implementation. If you only perform exact matches, you don't even need

dict_gram_num

. If you need to search for partial IDs (e.g., IDs containing

ABC

), add it.

Copy code

sql
-- Assuming your table is 'requests' and column is 'deal_ids'
ALTER TABLE requests ADD INDEX idx_deal_ids (deal_ids) 
USING GIN (
    "imp_lib" = "builtin", 
    "parser" = "none",
    "dict_gram_num" = "4" -- Only if you plan to use LIKE '%ABC%'
);

Step 4: Update Your Queries Once the index is built,

array_contains

can be accelerated by the inverted index. However, to ensure maximum performance and use the full-text capabilities, you can also use the

MATCH

predicate: * Option A (Exact match in Array):

Copy code

sql
    SELECT * FROM requests WHERE array_contains(deal_ids, 'MGNI-ABC-123');

(In recent versions, the optimizer can use the GIN index to accelerate this). * Option B (Full-text style):

Copy code

sql
    SELECT * FROM requests WHERE deal_ids MATCH 'MGNI-ABC-123';

Step 5: Verify Acceleration Run your query and check the Query Profile. Look for the following metrics in the Scan node: * GinFilterRows
: This should show a large number of rows were filtered out by the index. * GinFilterTime
: This tells you how long the index lookup took. Summary * Keep the Array. * Use
parser
=
none
to keep your IDs intact (ignoring dashes/underscores). * Set
dict_gram_num
to 3 or 4 if you need substring searches; leave it disabled (

-1

) if you only do exact ID lookups for the full deal ID. References • table_design/indexes/inverted_index.md

Minn Fong

07/14/2026, 3:00 PM

@Rocky Is this GIN not available prior to v4.1? I am aware of the CLucene one but it's limitations is working strictly on alphanumeric values only? So v4.1 solves this?

Rocky

07/14/2026, 3:00 PM

The GIN (Generalized Inverted Index) has been available since v3.3.0, but v4.1 introduces significant changes that specifically address the performance and flexibility issues you are facing. To clarify your specific questions: 1. Is GIN available prior to v4.1? Yes, but with different "engines": * v3.3.0 to v4.0: Only the CLucene-based implementation exists (

"imp_lib" = "clucene"

). * v4.1+: Adds the built-in implementation (

"imp_lib" = "builtin"

). This is a native StarRocks implementation that is generally faster and is the only version that supports the

dict_gram_num

parameter. 2. Is the "alphanumeric" limitation real? The limitation you mentioned is actually a function of the Parser, not the library itself. * The Issue: If you use the

english

parser (default in many examples), it treats non-alphanumeric characters like

and

as delimiters. It would split

MGNI-ABC-123

into

MGNI

ABC

, and

. * The Solution: In any version (v3.3+), you can set

"parser" = "none"

. This treats the entire string (including dashes and underscores) as a single token. This allows you to perform exact matches or

LIKE

searches on the full string. 3. Does v4.1 solve your specific problem? Yes, in two major ways: 1. Substring Acceleration (
dict_gram_num
): Prior to v4.1, if you used

parser

none

and searched for

LIKE '%ABC%'

, the index could only filter by the full token. It would still have to scan the dictionary of tokens. In v4.1, with

builtin

and

dict_gram_num

, StarRocks builds an additional n-gram index on the dictionary itself, making those wildcard searches much faster. 2. Native Array Acceleration: While older versions support GIN, v4.1 has significantly improved how the built-in index handles

ARRAY

types. Using the built-in index in v4.1 will much more effectively accelerate

array_contains

MATCH

queries on arrays compared to the older CLucene implementation. Updated Recommendation Since you are on (or moving to) v4.1, you should proceed with the built-in index using the none
parser. Example for your
deal_ids
array:

Copy code

sql
ALTER TABLE your_table ADD INDEX idx_deal_ids (deal_ids) 
USING GIN (
    "imp_lib" = "builtin", 
    "parser" = "none",     -- This keeps 'MGNI-ABC-123' as one token
    "dict_gram_num" = "3"  -- This allows fast LIKE '%ABC%' searches
);

Why this is better than a comma-separated string: * Correctness: If you use a comma-separated string, a search for

ABC

might accidentally match

ABC-123

and

X-ABC-Y

. Arrays are "cleaner." * Efficiency: StarRocks v4.1 built-in index indexes each element of the array individually. Searching

array_contains(deal_ids, 'MGNI-ABC-123')

will point directly to the row ID via the index, turning a CPU-intensive scan into a O(1) or O(log N) lookup. Summary: v4.1 doesn't just "fix" the alphanumeric issue (which was solvable via parsers); it provides the Built-in engine which makes those searches orders of magnitude more efficient for complex strings and arrays. References • table_design/indexes/inverted_index.md

Minn Fong

07/14/2026, 3:04 PM

@Rocky If I dont need substring lookups, what actual benefit does v4.1 bring?

Rocky

07/14/2026, 3:04 PM

If you only need exact matches (no substring

LIKE

or wildcards), the primary benefits of the v4.1 built-in inverted index (

imp_lib

builtin

) over the older CLucene implementation are architectural efficiency and future-proofing: 1. Shared-Data Architecture Support The biggest technical differentiator is that CLucene does not work on Shared-Data (Lakehouse) clusters. * If you are running StarRocks on object storage (S3/GCS/Azure) using the Shared-Data mode, the built-in index is your only option. * It is designed to work natively with StarRocks' remote storage and local caching mechanisms. 2. Native Vectorization and Performance While CLucene is a powerful library, it is an external dependency wrapped by StarRocks. The "built-in" version is a native StarRocks implementation built directly on top of their Bitmap Index technology. * Vectorized Execution: Because it is native code, it is more tightly integrated with StarRocks' SIMD (Single Instruction, Multiple Data) vectorized query engine. For exact matches in large arrays, this can lead to lower CPU overhead during the "filtering" phase of a query compared to calling out to the CLucene library. * Faster Ingestion: The built-in index typically has lower overhead during data ingestion (writing) because it doesn't have to manage the complex internal structures required by a full search engine like Lucene. 3. Primary Key Table Maturity If you are using the Primary Key (PK) storage engine, v4.1's built-in index is significantly more robust. While PK tables added inverted index support in v4.0, the v4.1 native implementation is better optimized for the way PK tables handle updates and deletes. 4. Simpler Maintenance and Stability CLucene is a mature but older C++ port of Lucene. It can occasionally be a "black box" when debugging memory issues or crashes. * The built-in engine uses StarRocks’ native memory management. * It avoids the "double-buffering" or external memory tracking issues that can sometimes occur with third-party libraries. Should you switch if you only need exact matches? If you are currently on a Shared-Nothing (local disk) cluster and your exact matches are already fast with CLucene, you don't have to switch immediately. However, for your specific "CPU intensive"
array_contains
problem: The v4.1 built-in index is designed specifically to solve this. When you use

parser

none

, StarRocks treats each

deal_id

as a single value in a bitmap. Checking if an array contains a specific ID becomes a highly efficient bitwise operation rather than a string-parsing operation. Recommendation: Since you are investigating v4.1 anyway, use

imp_lib

builtin

. Even without

dict_gram_num

, the native integration will likely give you the best "performance-per-CPU-cycle" for those

array_contains

filters. References • table_design/indexes/inverted_index.md References • table_design/indexes/inverted_index.md

Minn Fong

07/15/2026, 4:46 PM

@Rocky MySQL [requests]> CREATE INDEX idx_deal_ids ON requests (deal_ids) -> USING GIN ( -> "parser" = "none", -> "imp_lib" = "builtin" -> ) -> COMMENT 'Index for array elements'; ERROR 1064 (HY000): Getting analyzing error. Detail message: The inverted index can only be build on column with type of CHAR/STRING/VARCHAR type.. MySQL [requests]>

Rocky

07/15/2026, 4:46 PM

I apologize for the confusion in my previous response. I previously stated that

ARRAY<STRING>

is natively supported for the inverted index, but as you discovered from the error message, StarRocks (including v4.1) currently only supports GIN indexes on scalar
CHAR
,
VARCHAR
, and
STRING
types. The reason

array_contains

is CPU intensive for you is that it performs a full scan of the array data for every row without index acceleration. To achieve the performance of the new v4.1 built-in index for your deal IDs, you should proceed with a "flattened string" approach. Here is the step-by-step recommendation: Step 1: Create a Flattened String Column Since you cannot index the array directly, you must create a string column that contains all the IDs. The best way to do this in StarRocks is using a Generated Column, which will automatically keep the string in sync with your array without changing your ingestion logic. Add a column that joins your array elements with a delimiter (e.g., a space):

Copy code

sql
ALTER TABLE requests ADD COLUMN deal_ids_flattened STRING 
AS (array_join(deal_ids, ' ')) MATERIALIZED;

Note: Using a space as a delimiter is usually better for text-based indexing than a comma. Step 2: Create the Built-in Inverted Index Now, create the index on this new string column. Because your IDs contain special characters like

and

, you must use

parser

none

. If you use the English parser, it will split

MGNI-ABC-123

into three separate words, making it impossible to search for the full ID.

Copy code

sql
CREATE INDEX idx_deal_ids_flattened ON requests (deal_ids_flattened)
USING GIN (
    "imp_lib" = "builtin",
    "parser" = "none",
    "dict_gram_num" = "4" -- Use a value like 4 or 5 for deal IDs
);

Step 3: Why
dict_gram_num
matters here Since you are using

parser

none

, the inverted index dictionary will store the entire "joined" string of IDs for each row as a single entry. * Without
dict_gram_num
: A search for one ID would require a slow full scan of the dictionary entries to see which ones contain your ID. * With
dict_gram_num
: StarRocks builds an n-gram index on the dictionary itself. It will break your search ID (e.g.,

MGNI-ABC-123

) into 4-character chunks and use them to immediately pinpoint which dictionary entries (and thus which rows) match. Is a larger
dict_gram_num
more efficient? Yes. For your deal IDs, which are likely 10+ characters long, a value of 4 or 5 is more efficient than the default (or a low value like 2). It will result in a smaller index size and faster lookup, provided your search strings are always longer than the

dict_gram_num

value. Step 4: Update Your Queries Instead of using

array_contains

, you will now use the

MATCH

predicate (or

LIKE

) on the flattened column:

Copy code

sql
-- This will be accelerated by the built-in GIN + n-gram dictionary index
SELECT * FROM requests 
WHERE deal_ids_flattened MATCH 'MGNI-ABC-123';

Summary of the Workflow 1. Flatten: Use a Generated Column to turn the

ARRAY

into a

STRING

. 2. Index: Use the

builtin

GIN index with

parser

none

to preserve your dashes/underscores. 3. Accelerate: Set

dict_gram_num

to 4 or 5 to make the substring search on that flattened string extremely fast. 4. Query: Switch from

array_contains(deal_ids, ...)

deal_ids_flattened MATCH '...'

. This approach bypasses the current array limitation while providing the high-speed filtering you need for IDs with special characters. References • table_design/indexes/inverted_index.md

Minn Fong

07/15/2026, 4:49 PM

@Rocky Is it more efficient if my application stream loading the data to starrocks format the data into space separated first? instead of using generated columns. Also, if i just want the index to be built using the space as a delimiter, do i need to specify dict_gram_num?

Rocky

07/15/2026, 4:49 PM

To answer your questions about efficiency and index configuration: 1. Is application-side formatting more efficient? Yes. Formatting the data into a space-separated string in your application before streaming it into StarRocks is more efficient for the cluster. * Ingestion CPU: StarRocks "Generated Columns" perform the calculation (e.g.,

array_join

) on the Backend (BE) nodes during ingestion. If you have high-throughput streams, offloading this logic to your application saves CPU cycles on the database, allowing it to focus on indexing and storage. * Simplicity: It avoids the extra metadata management of a generated column and ensures the data is in the final format the moment it hits the system. 2. If I use space as a delimiter, do I need
dict_gram_num
? Yes, you still need it. Here is why: StarRocks does not currently have a "space-only" parser. Its parsers behave as follows: * parser
=
english
: Splits by any non-alphabetic character. It would split your ID

MGNI-ABC-123

into three separate tokens:

mgni

abc

, and

. This makes it impossible to search for the specific full ID. * parser
=
none
: Treats the entire row (all your space-separated IDs) as one single entry in the index dictionary. The catch: Because

parser = "none"

stores the whole concatenated string as one item, searching for a single ID inside that string (e.g.,

MATCH '%MGNI-ABC-123%'

) is technically a substring search. * Without
dict_gram_num
: The index has to perform a slow scan of all unique concatenated strings in the dictionary to find which ones contain your ID. * With
dict_gram_num
: StarRocks builds an n-gram index on the dictionary itself. It breaks your long string into small chunks (grams), allowing it to jump directly to the rows containing your specific ID without scanning the whole dictionary. Final Recommended Configuration For IDs that contain non-alphanumeric characters like

, the only way to get high performance in v4.1 is: 1. Format in App: Send a string like

'ID1 ID2 ID3'

. 2. Index Setup:

Copy code

sql
    ALTER TABLE requests ADD INDEX idx_ids (ids_string)
    USING GIN (
        "imp_lib" = "builtin",
        "parser" = "none",
        "dict_gram_num" = "4" -- Optimizes the substring lookup
    );

3. Query:

Copy code

sql
    SELECT * FROM requests WHERE ids_string MATCH 'MGNI-ABC-123';

Note: When using

parser = "none"

MATCH

is semantically equivalent to

LIKE

. The

dict_gram_num

ensures that this "LIKE" operation is accelerated by the index rather than being a brute-force search. References • table_design/indexes/inverted_index.md • sql-reference/sql-statements/generated_columns.md References • table_design/indexes/inverted_index.md

Minn Fong

07/15/2026, 4:59 PM

@Rocky Will the built in index be a significant improvement over array_contains function?

Rocky

07/15/2026, 5:03 PM

Yes, the improvement will be massive. In a typical production environment, you can expect the query time to drop from seconds to milliseconds for large datasets. The performance difference comes from a fundamental change in how StarRocks finds your data: 1. `array_contains`: The "Brute Force" Way When you use

array_contains(deal_ids, 'XYZ')

, StarRocks has to perform a Full Table Scan: * Deserialization: For every single row in your table, the BE (Backend) must pull the

ARRAY

data from disk and deserialize it into memory. * CPU Loop: It then runs a loop over every element in that array to check if it matches

'XYZ'

. * Result: This is extremely CPU-intensive and the execution time grows linearly with your row count. 2. Built-in Inverted Index: The "Shortcut" Way By using a flattened string with the v4.1 built-in GIN index (

imp_lib

builtin

), you switch to an Index-Only Lookup: * Row Skipping: Instead of reading all rows, StarRocks looks at the index (which is essentially a map of

ID -> [RowID1, RowID2, ...]

). It immediately knows exactly which row numbers contain the ID. * No Deserialization: Rows that do not contain the ID are never even read from disk or processed in CPU. * Bitmap Acceleration: Since the built-in index is built on StarRocks' native Bitmap technology, it uses highly optimized bitwise operations (SIMD) to merge results if you have multiple filters. How to verify the improvement Once you implement the new approach, you can run a query and check the Query Profile. Look for these specific metrics in the Scan node: * GinFilterRows
: This shows how many rows the inverted index successfully filtered out before they were even read. * GinFilterTime
: This shows how much time was spent searching the index. Why
dict_gram_num
is the "Secret Sauce" In v4.1, if you use

parser

none

on a long string of IDs, the

dict_gram_num

makes the substring search behave like an exact match. It prevents the system from having to scan a massive dictionary of unique "joined strings," which was a bottleneck in earlier versions of the index. In summary: You are moving from a $O(N)$ complexity (where $N$ is the number of rows) to nearly $O(1)$ or $O(\log N)$ for the filtering phase. For tables with millions or billions of rows, this is the single most impactful optimization you can make. References • table_design/indexes/inverted_index.md References • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md index) • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md

Rocky

07/15/2026, 5:03 PM

Rocky

07/15/2026, 5:03 PM

Rocky

07/15/2026, 5:03 PM

Rocky

07/15/2026, 5:03 PM

Rocky

07/15/2026, 5:03 PM

Rocky

07/15/2026, 5:03 PM

Rocky

07/15/2026, 5:03 PM

Rocky

07/15/2026, 5:03 PM

Rocky

07/15/2026, 5:03 PM

Minn Fong

07/15/2026, 5:28 PM

@Rocky how do i track index addition progress? I dont see it in show create table yet

Rocky

07/15/2026, 5:28 PM

In StarRocks, creating an index (GIN, Bitmap, etc.) is an asynchronous operation. The command returns immediately after the task is submitted, but the actual building of the index on existing data happens in the background. You can track the progress using the following command:

Copy code

sql
SHOW ALTER TABLE COLUMN;

Tips for tracking progress: 1. Check the
State
column: * RUNNING
: The index is currently being built. * FINISHED
: The index creation is complete, and it should now appear in

SHOW CREATE TABLE

. * CANCELLED
: The task failed (check the

Msg

column for the error). 2. Filter by Table: If you have many operations running, filter for your specific table:

Copy code

sql
    SHOW ALTER TABLE COLUMN WHERE TableName = 'requests' ORDER BY CreateTime DESC;

3. Check the
Progress
column: This typically shows the percentage of completion (e.g.,

1/3

33%

), representing the progress of re-indexing existing data files. Why you don't see it in
SHOW CREATE TABLE
yet StarRocks only adds the index definition to the table metadata and

SHOW CREATE TABLE

output after the background job reaches the

FINISHED

state. Once the state changes to

FINISHED

, the index is active and will be used to accelerate your queries. References • sql-reference/sql-statements/SHOW_ALTER_TABLE.md • table_design/indexes/inverted_index.md

Minn Fong

07/15/2026, 6:52 PM

@Rocky So once index created, MATCH AND LIKE are same? select sum(num_requests) from requests where deal_ids_v2 match '<deal>' How to verify gin being used

Rocky

07/15/2026, 6:52 PM

With the built-in inverted index (

imp_lib

builtin

) and

parser

none

, here is how

MATCH

and

LIKE

behave and how you can verify their performance. 1. Are
MATCH
and
LIKE
the same? Yes. When you set

"parser" = "none"

, the inverted index treats the entire column value as a single token. In this mode: *

MATCH

and

LIKE

are semantically equivalent. * To find a single ID inside your space-separated string (e.g.,

'ID1 ID2 ID3'

), you must use wildcards:

Copy code

sql
    -- This uses the index to find any row where the string contains the ID
    WHERE deal_ids_v2 MATCH '%<deal>%'
    -- OR
    WHERE deal_ids_v2 LIKE '%<deal>%'

* Crucial Note: If you use

MATCH '<deal>'

(without the

), StarRocks will look for an exact match of the entire column. If your column contains

'ID1 ID2'

, then

MATCH 'ID1'

will return nothing. 2. How to verify the Index is being used You can verify index usage through the Query Profile. Follow these steps: 1. Enable the index filter (it is usually on by default in newer versions, but good to be sure):

Copy code

sql
    SET enable_gin_filter = true;

2. Run your query and then get the profile (using

GET_QUERY_PROFILE

or via the Manager UI). 3. Look for the Scan Node (e.g.,

OLAP_SCAN_NODE

) and find these specific metrics: * GinFilterRows
: This is the most important metric. It tells you exactly how many rows were filtered out by the inverted index before reading the actual data. If this is a large number, the index is working. * GinFilterTime
: Shows how much time was spent traversing the index. * InvertedIndexFilterRows
: (In some versions, it may appear under this name). 3. Will
dict_gram_num
help here? Absolutely. Since you are doing a substring search (

%<deal>%

), the

dict_gram_num

you configured is what makes the index fast. * Without it, the system would have to scan every unique concatenated string in the index dictionary to see if it contains your ID. * With it, the system uses the n-grams to jump directly to the correct dictionary entries. Summary Checklist * Query: Use

MATCH '%ID%'

LIKE '%ID%'

. * Config: Ensure

enable_gin_filter = true

. * Verify: Check

GinFilterRows

in the Query Profile. References • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md • table_design/indexes/inverted_index.md References • table_design/indexes/inverted_index.md • table_design/indexes/Prefix_index_sort_key.md

2 Views

Open in Slack

Previous Next