StarRocks 3.3 Indexes: When to Use Bitmap, Bloom Filter, N-Gram & Full-Text Search

StarRocks 3.3 Index Types Overview

In StarRocks 3.3, manual index creation must consider data cardinality, query patterns, and data types. This article covers four index types: Bitmap, Bloom Filter, N-Gram Bloom Filter (preview), and Full-Text Inverted Index (preview).

1. Bitmap Index

Core Principle

Uses a bitmap (0/1 sequence) to record row positions for each unique value in a column. The i-th bit set to 1 indicates row i contains value x. Supports fast OR/AND operations to compute row sets matching a value.

Supported Columns & Data Types

Primary key and detail tables: all columns.

Aggregate and update tables: only key columns.

Data types: DATE, DATETIME; TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DECIMAL, BOOLEAN; CHAR, STRING, VARCHAR; HLL.

Basic Operations

Create at Table Creation

CREATE TABLE `lineorder_partial` (
  `lo_orderkey` int(11) NOT NULL COMMENT "",
  `lo_orderdate` int(11) NOT NULL COMMENT "",
  `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
  `lo_quantity` int(11) NOT NULL COMMENT "",
  `lo_revenue` int(11) NOT NULL COMMENT "",
  INDEX lo_orderdate_index (lo_orderdate) USING BITMAP
) ENGINE=OLAP
DUPLICATE KEY(`lo_orderkey`)
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 1;

Multiple Bitmap indexes can be defined, separated by commas.

Create After Table Creation

CREATE INDEX lo_quantity_index ON lineorder_partial (lo_quantity) USING BITMAP;

Creation Progress

Creation is asynchronous. Use SHOW ALTER TABLE COLUMN [FROM db_name]; to monitor; when State shows FINISHED, the index is ready. Only one schema change per table at a time.

View Indexes

SHOW { INDEX[ES] | KEY[S] } FROM [db_name.]table_name [FROM db_name];

Only completed indexes are visible.

Drop Index

DROP INDEX index_name ON [db_name.]table_name;

Check Query Hit

Inspect the query profile's BitmapIndexFilterRows field.

Applicable Scenarios

Low-cardinality columns (key prerequisite) : unique values ≤10,000, many duplicates (e.g., status, type, category). Example: order status (pending, paid, cancelled), gender, product category (≤100).

High-frequency equality/IN queries : conditions like col = x or col IN (x1, x2, ...) needing fast row filtering. Example: WHERE order_status IN ('paid', 'cancelled'), WHERE province = 'Guangdong'.

Non-Applicable Scenarios

High-cardinality columns (user ID, order number): bitmap size explodes, storage cost high, merge overhead large.

Range queries (e.g., col > x): Bitmap only supports exact match.

Case Study: E-commerce Order Table – Low-Cardinality Status Column

Business Scenario

Table order_info stores ~500M rows over 3 years, partitioned by day, distributed by order_id. Columns: order_id (high cardinality), user_id (~100M unique), order_status TINYINT (0-3), create_time, amount.

Slow Query

SELECT SUM(amount) FROM order_info WHERE create_time >= '2024-09-01' AND order_status = 1;

Execution ~8 seconds; full scan of 7-day partitions (~50M rows) because order_status lacked index.

Index Selection & Optimization

Analyze data : order_status has only 4 values (low cardinality), query uses equality ( order_status = 1) → fits Bitmap.

Create index :

CREATE INDEX idx_order_status ON order_info (order_status) USING BITMAP COMMENT '';

Principle : Bitmap records row positions for "paid" (1); query filters via bitmap without full scan.

Optimization Result

Query time dropped from 8s to 0.6s; scanned rows from 50M to ~30M (only paid orders); 13× speedup.

2. Bloom Filter Index

Core Principle

When a Bloom filter index on column1 is hit during SELECT ... WHERE column1 = something, two outcomes occur per data file:

If Bloom filter determines the file does NOT contain the target value, StarRocks skips the file → efficiency gain.

If Bloom filter determines the file MAY contain the target, StarRocks reads the file to verify. Bloom filter has a false positive probability ("multiple choice, no false negatives").

Usage Notes

Primary key and detail tables: all columns; aggregate and update tables: only key (dimension) columns.

Supported types: SMALLINT, INT, BIGINT, LARGEINT; CHAR, STRING, VARCHAR; DATE, DATETIME.

Only accelerates IN and = filters (e.g., WHERE xxx IN (), WHERE column = xxx).

Check hit via profile field BloomFilterFilterRows.

Create at Table Creation

CREATE TABLE table1
(
  k1 BIGINT,
  k2 LARGEINT,
  v1 VARCHAR(2048) REPLACE,
  v2 SMALLINT DEFAULT "10"
)
ENGINE = olap
PRIMARY KEY(k1, k2)
DISTRIBUTED BY HASH (k1, k2)
PROPERTIES("bloom_filter_columns" = "k1,k2");

Multiple columns separated by commas.

View Index

SHOW CREATE TABLE table1;

Modify Index (Async)

Add column:

ALTER TABLE table1 SET ("bloom_filter_columns" = "k1,k2,v1");

Remove column: ALTER TABLE table1 SET ("bloom_filter_columns" = "k1"); Drop all: ALTER TABLE table1 SET ("bloom_filter_columns" = ""); Monitor with SHOW ALTER TABLE; one modification task per table at a time.

Applicable Scenarios

High-cardinality columns (key prerequisite) : unique values ≥100,000, few duplicates (user ID, phone, order number, device ID). Example: user table user_id (100M+ unique), log table device_id (10M+ unique).

High-frequency equality queries : col = x or col IN (...), tolerating false positives (residual irrelevant rows verified later). Example: WHERE user_id = 10086, WHERE order_no IN ('ORD2024001', 'ORD2024002').

Reduce I/O on large tables : PB-scale tables; Bloom filter pre-filters irrelevant data, avoiding full scans.

Non-Applicable Scenarios

Low-cardinality columns: better served by Bitmap (no false positives, higher filter efficiency).

Range queries ( col BETWEEN x AND y) or fuzzy queries ( LIKE): Bloom filter only supports exact value judgment.

Case Study: User Behavior Log Table – High-Cardinality Device Column

Business Scenario

Table user_behavior stores 100M rows/day, 10TB total. Columns: log_id (unique), device_id STRING (~50M unique), action (low cardinality), page (medium), event_time. Partitioned by time, distributed by device_id.

Slow Query

SELECT event_time, action, page FROM user_behavior WHERE event_time >= '2024-08-15' AND device_id = 'device_12345';

Execution ~15 seconds; full scan of 30-day partitions (~3B rows) because device_id high cardinality, no index.

Index Selection & Optimization

Analyze : device_id high cardinality, equality match → Bloom filter.

Create :

ALTER TABLE user_behavior SET ("bloom_filter_columns" = "device_id");

Principle : Bloom filter quickly identifies buckets/partitions that may contain device_12345, filtering out >90% irrelevant data.

Optimization Result

Query time from 15s to 1.2s; scanned rows from 3B to ~20M; 12× speedup.

3. N-Gram Bloom Filter Index (Preview)

Core Principle

Designed for string fuzzy queries. Splits strings into fixed-length N-Grams (e.g., 2-Gram for Chinese: "StarRocks" → "St", "ta", "ar", ...). Builds a Bloom filter for each sub-string. Query matches sub-strings to locate rows containing the keyword.

Syntax

Create at Table Creation

CREATE TABLE test.table1
(
  k1 CHAR(10),
  k2 CHAR(10),
  v1 INT SUM,
  INDEX index_name (k2) USING NGRAMBF ("gram_num" = "4",
    "bloom_filter_fpp" = "0.05",
    "case_sensitive" = "false") COMMENT ''
)
ENGINE = olap
AGGREGATE KEY(k1, k2)
DISTRIBUTED BY HASH(k1);

Add After Table Creation

ALTER TABLE table1
ADD INDEX new_index_name(k1) USING NGRAMBF ("gram_num" = "4",
  "bloom_filter_fpp" = "0.05",
  "case_sensitive" = "false") COMMENT '';

Drop Index

ALTER TABLE table1 DROP INDEX new_index_name;

Applicable Scenarios

String column fuzzy queries : col LIKE '%keyword%' (contains match), especially Chinese, long strings, or strings without clear delimiters. Example: product name WHERE product_name LIKE '%phone case%', address WHERE address LIKE '%Haidian%'.

Medium-length strings : length ≥5 characters (otherwise sub-strings meaningless) and ≤1000 characters (avoid index bloat).

Non-Applicable Scenarios

Exact match queries ( col = 'xxx'): better with prefix or Bloom filter index.

Very long strings (full articles): use Full-Text Inverted Index instead.

Non-string columns: only VARCHAR, CHAR supported.

Case Study: Product Table – String Fuzzy Query

Business Scenario

Table product ~10M rows. Columns: product_id (high cardinality), product_name STRING (e.g., "iPhone 14 Pro 256G Black"), category (low), price, tags (medium). Distributed by product_id.

Slow Query

SELECT product_id, product_name, price FROM product WHERE category = 'phone' AND product_name LIKE '%apple%';

Execution ~3 seconds; full scan of 2M phone-category rows with string matching.

Index Selection & Optimization

Analyze : product_name is string, fuzzy contains ( %apple%), length 10-30 chars → N-Gram (2-Gram for Chinese).

Create :

ALTER TABLE product ADD INDEX new_index_name(product_name) USING NGRAMBF ("gram_num" = "2", "bloom_filter_fpp" = "0.05") COMMENT '';

(Chinese split by 2-char grams).

Principle : N-Gram index uses sub-strings of "apple" to quickly locate products containing the keyword, avoiding full-table string match.

Optimization Result

Query time from 3s to 0.3s; scanned rows from 2M to ~300K; 10× speedup.

4. Full-Text Inverted Index (Preview)

Core Principle

Targets long-text columns (articles, comments, descriptions). Tokenizes text into terms, records each term's document IDs and frequencies. Supports efficient full-text search: keyword match, phrase match, weight ranking.

Requires FE config:

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

Table must be a detail table with property replicated_storage = false.

Create at Table Creation

After creation, enable session variable enable_gin_filter to use index. Query support depends on parser choice.

With Tokenization (parser = 'standard|english|chinese')

Only MATCH predicate supported: <col_name> (NOT) MATCH '%keyword%' where keyword is a string literal.

CREATE TABLE `t` (
  `k` BIGINT NOT NULL COMMENT "",
  `v` STRING COMMENT "",
  INDEX idx (v) USING GIN("parser" = "english")
) ENGINE=OLAP
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES ("replicated_storage" = "false");

Parser Options

none

(default): no tokenization; entire column value as one index term. english: English tokenization at non-alphabetic chars; uppercase → lowercase. Query keywords must be lowercase. chinese: Chinese tokenization via CJK Analyzer. standard: Multi-language (Unicode Text Segmentation, UAX #29). Handles mixed languages; English lowercased.

Index column type must be CHAR, VARCHAR, STRING.

Add After Table Creation

ALTER TABLE t ADD INDEX idx (v) USING GIN('parser' = 'english');
CREATE INDEX idx ON t (v) USING GIN('parser' = 'english');

Drop Index

DROP INDEX idx on t;
ALTER TABLE t DROP index idx;

Applicable Scenarios

Long-text full-text search : column stores full text (articles, comments, product details); query matches keyword presence, frequency. Example: news body WHERE body CONTAINS 'artificial intelligence', comments WHERE comment CONTAINS 'good quality AND fast shipping'.

Complex text matching : keyword combinations (AND/OR/NOT), phrase match, fuzzy match (typo tolerance). Example: WHERE body CONTAINS 'StarRocks OR data analysis', WHERE comment CONTAINS 'damaged packaging'.

Non-Applicable Scenarios

Short-string fuzzy queries (e.g., product name LIKE '%phone%'): N-Gram lighter and faster.

Non-text columns: only string types; Chinese requires tokenizer config (e.g., jieba).

Case Study: News Content Table – Long-Text Search

Business Scenario

Table news ~5M articles, each body 1,000-5,000 chars. Columns: news_id (high cardinality), title, content TEXT, publish_time, author. Partitioned by time, distributed by news_id.

Slow Query

SELECT news_id, title FROM news WHERE publish_time >= '2024-01-01' AND content CONTAINS 'artificial intelligence AND autonomous driving';

Execution ~20 seconds; full scan of long text with keyword matching, huge I/O.

Index Selection & Optimization

Analyze : content long text (≥1000 chars), query uses keyword combination (AND) → Full-Text Inverted Index (Chinese tokenization).

Create :

CREATE INDEX idx_news_content ON news (content) USING GIN('parser' = 'chinese');

(Chinese splits "artificial intelligence" into "artificial", "intelligence").

Principle : Inverted index records article IDs for each term; query intersects posting lists for fast retrieval.

Optimization Result

Query time from 20s to 1.5s; no full-text scan, only index intersection; 13× speedup.

5. General Index Selection Decision Flow

Analyze column characteristics

Cardinality: low (≤10k unique) → Bitmap; high (≥100k) → Bloom filter.

Type: string short text + fuzzy contains → N-Gram; long text + full-text search → Full-Text Inverted.

Match query pattern

Equality/IN → Bitmap (low cardinality) or Bloom filter (high cardinality).

Fuzzy contains ( %xxx%) → N-Gram (short text) or Full-Text Inverted (long text).

Weigh costs : Indexes add storage (~5-10% of table size for Bitmap) and write overhead (index maintenance on load). Only index high-frequency query columns.

Test & verify : Use EXPLAIN to confirm index hit; compare scanned rows and latency before/after; avoid useless indexes (e.g., Bitmap on high-cardinality column).

Notes : Indexes increase storage and write overhead; avoid over-indexing.

Reference: https://docs.mirrorship.cn/zh/docs/3.3/category/indexes/

Note: Case studies are fictional.