@Rocky In my table properties fro MY_TABLE I have

"bloom_filter_columns" = "my_column"

but when I do a

desc MY_TABLE

I do not see anything in the

extra

column, and when I profile queries that use predicate

my_column = 'xyz'

I see nothing about BloomFilterFilterRows even though the query consumes billions of rows.

Hi there! I'm Rocky. It sounds like your Bloom filter index might not be active, even though you’ve specified it in your table properties. In StarRocks, if a Bloom filter index is correctly created and active, you should see

BLOOM_FILTER

in the

Extra

column when you run

DESC MY_TABLE

. If it's missing there and you don't see

BloomFilterFilterRows

in your query profile, the index is not being used. Here are the most common reasons why this happens: 1. Check Table Model & Column Role Bloom filter indexes have specific restrictions based on the table's data model: * Duplicate Key / Primary Key tables: You can create Bloom filters on any column. * Aggregate / Unique Key tables: You can only create Bloom filters on Key columns. if

my_column

is a value column (e.g., it has an aggregation like

SUM

or

REPLACE

), the Bloom filter will not be created. 2. Check Data Type Support Not all data types support Bloom filters. Ensure

my_column

is not one of these unsupported types: * Unsupported:

TINYINT

,

FLOAT

,

DOUBLE

,

DECIMAL

. * Supported:

SMALLINT

,

INT

,

BIGINT

,

LARGEINT

,

CHAR

,

STRING

,

VARCHAR

,

DATE

,

DATETIME

. 3. Verify if the Index Creation is Finished If you added the Bloom filter via an

ALTER TABLE

statement, the process is asynchronous. It doesn't take effect immediately because the BE needs to build the index for existing data. Check the status of the job by running:

sql
SHOW ALTER TABLE COLUMN;

Look for your table and check if the

State

is

FINISHED

. If it’s still

RUNNING

, the index isn't ready yet. 4. Verify Query Predicates Bloom filters only work for specific operators. If your query uses something other than

=

or

IN

, the index will be ignored: * Works with:

my_column = 'xyz'

or

my_column IN ('a', 'b', 'c')

. * Does NOT work with:

LIKE

,

>

,

<

,

!=

, or functions applied to the column (e.g.,

WHERE upper(my_column) = 'XYZ'

). 5. Check

SHOW CREATE TABLE

Sometimes the

PROPERTIES

in your original script don't match the actual state of the table. Run:

sql
SHOW CREATE TABLE MY_TABLE;

Check if

"bloom_filter_columns" = "my_column"

actually appears in the output. If it doesn't, the property was never successfully applied. Summary Checklist * Is it an Aggregate/Unique table? If yes, is the column a Key? * Is the data type supported? * Is the

ALTER

job finished? * Are you using

=

or

IN

in your query? If you've checked all these and it still isn't showing up, try re-running the

ALTER

statement:

sql
ALTER TABLE MY_TABLE SET ("bloom_filter_columns" = "my_column");

Then monitor

SHOW ALTER TABLE COLUMN

until it completes! Let me know if that helps! References • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md • administration/management/BE_configuration.md • best_practices/query_tuning/query_profile_operator_metrics.md

@Rocky It's there in properties but not in EXTRA. The column is a generated column shredded from a json column and its type is varchar(256).

It appears you've hit a specific limitation in StarRocks: Bloom filter indexes are currently not supported on Generated Columns. Even if you specify the column in the

bloom_filter_columns

table property, StarRocks will ignore it for any column defined as a generated column (e.g.,

AS json_query(...)

). This is why you don't see

BLOOM_FILTER

in the

EXTRA

column of

DESC MY_TABLE

and why the query profile shows no

BloomFilterFilterRows

. Why this is happening 1. Metadata vs. Implementation: StarRocks allows you to set the

bloom_filter_columns

property on the table metadata, but the background process that builds these indexes (during data ingestion or compaction) does not currently support the "virtual" nature of generated columns. 2. Display in `EXTRA`: In StarRocks, the

EXTRA

column is the definitive source of truth for active indexes. For a generated column, you will see

GENERATED COLUMN

. If a Bloom filter were active, it would show

BLOOM_FILTER

(or both). Since you only see the property in

SHOW CREATE TABLE

but not in

DESC

, the index was never actually built. 3. Table Model Restrictions: If your table is an Aggregate or Unique Key table, Bloom filters are only allowed on Key columns. Since generated columns cannot be part of the Key, they are ineligible for Bloom filters in these models regardless. How to solve this If you need Bloom filter performance on a value shredded from JSON, you have two main options: Option 1: Shred during ingestion (Recommended) Instead of using a generated column, shred the JSON during your

INSERT

or

ROUTINE LOAD

process and store the result in a regular column. * Pros: You can then add a Bloom filter, Bitmap index, or even make it a Sort Key. * Cons: Requires updating your load logic. Option 2: Use an Asynchronous Materialized View Create an Asynchronous Materialized View (MV) that selects the shredded column. StarRocks does support Bloom filters on the columns of an Asynchronous MV.

sql
CREATE MATERIALIZED VIEW my_table_mv
DISTRIBUTED BY HASH(shredded_col)
PROPERTIES ("bloom_filter_columns" = "shredded_col")
AS 
SELECT id, get_json_string(json_col, '$.my_key') as shredded_col
FROM MY_TABLE;

Verification Step To confirm there isn't a stuck background job, you can run:

sql
SHOW ALTER TABLE COLUMN;

If you don't see a

FINISHED

job corresponding to your column, it confirms the index was never initiated because the column type (Generated) was ineligible. References • sql-reference/sql-statements/generated_columns.md • knowledge/usage/best-practice.md • table_design/indexes/indexes.md

@Rocky does StarRocks currently support bitmap indexes on generated columns?

To answer your question directly: Yes, StarRocks supports Bitmap indexes on generated columns. In fact, creating a Bitmap index on a generated column is the recommended best practice for accelerating queries on semi-structured data like JSON or Arrays. Why your Bloom Filter isn't working Even though StarRocks allows you to set the

bloom_filter_columns

property on a table with generated columns, the background process that builds the index often ignores generated columns. This is likely why: 1. Metadata vs. Storage: Bloom filter properties are stored in the table's metadata, but the index itself must be built into the physical data files (segments). For "shredded" or "virtual" generated columns, this build process is currently restricted in many StarRocks versions. 2. Display: If you don't see

BLOOM_FILTER

in the

EXTRA

column of

DESC MY_TABLE

, the index is definitely not active. The Solution: Use a Bitmap Index Unlike Bloom filters, Bitmap indexes are fully supported and highly effective for generated columns. StarRocks documentation specifically suggests this pattern to speed up point queries on JSON values. How to apply it: You can add a Bitmap index to your existing generated column using an

ALTER TABLE

statement:

sql
-- Replace 'my_column' with your generated column name
CREATE INDEX index_my_column ON MY_TABLE (my_column) USING BITMAP;

How to verify it: 1. Check Job Progress: Index creation is asynchronous. Monitor it with:

sql
    SHOW ALTER TABLE COLUMN;

2. Verify via DESC: Once finished, run

DESC MY_TABLE

. You should now see

BITMAP_INDEX

(or similar) in the

EXTRA

column. 3. Check Profile: When you run your query, the profile should now show

BitmapIndexFilterRows

instead of Bloom filter metrics. Key Advantages of Bitmap Index on Generated Columns * Faster for JSON: Since you are shredding JSON keys (often with lower cardinality than the whole JSON blob), Bitmap indexes provide excellent filtering for

=

and

IN

predicates. * ZoneMap Support: Generated columns of "simple" types (like VARCHAR or INT) also benefit from automatic ZoneMap (Min/Max) indexes, which help with range queries. Rocky's Tip: If you absolutely need a Bloom Filter (e.g., for very high cardinality columns where Bitmap performance might dip), the only workaround currently is to shred the JSON at ingestion time into a regular column, rather than using the

AS

expression` syntax. References • sql-reference/sql-statements/generated_columns.md • table_design/indexes/Bitmap_index.md • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md References • sql-reference/sql-statements/generated_columns.md

@Rocky the field has a cardinality of over 100M

With a cardinality of 100M+, you are correct to lean toward a Bloom filter. A Bitmap index on a column with 100M+ unique values would likely become very large and potentially slower than a simple scan due to the overhead of mapping so many bits. However, the reason you don't see it in the

EXTRA

column is a specific architectural limitation in StarRocks: The "Stored" vs. "Virtual" Conflict Even though generated columns in StarRocks are stored on disk (not calculated on the fly during every query), they are handled differently by the background index-building processes. 1. Bloom Filters on Generated Columns: Currently, StarRocks does not support Bloom filter indexes on generated columns. When you add a column to

bloom_filter_columns

, the FE (Frontend) accepts the property, but the BE (Backend) skips building the Bloom filter for any column marked as a "Generated Column." This is why it is missing from the

EXTRA

column in `DESC`—the index literally doesn't exist on the data files. 2. Bitmap Indexes on Generated Columns: These are supported. However, at 100M+ cardinality, a Bitmap index is generally not recommended. It would create a massive mapping table that could bloat your storage and slow down ingestion. The Solution for High-Cardinality JSON Fields Since you need the performance of a Bloom filter for a 100M+ cardinality field shredded from JSON, you should use the "Load-Time Shredding" approach: Instead of using a generated column: 1. Create a standard column (e.g.,

my_shredded_col VARCHAR(256)

). 2. Shred the JSON during ingestion using the

COLUMNS

clause in your load command (Routine Load, Stream Load, or Insert Into). * Example for `INSERT INTO`:

sql
        INSERT INTO MY_TABLE (id, my_shredded_col, raw_json)
        SELECT id, get_json_string(json_field, '$.path.to.key'), json_field
        FROM source_data;

3. Apply the Bloom Filter to this standard column:

sql
    ALTER TABLE MY_TABLE SET ("bloom_filter_columns" = "my_shredded_col");

Why this works: * When the column is a standard column, the BE will successfully build the Bloom filter index during the next compaction or during the initial load. * Once it's a standard column, you will see

BLOOM_FILTER

appear in the

EXTRA

column of

DESC MY_TABLE

. * The

BloomFilterFilterRows

metric will then appear in your query profiles. Summary of Recommendations Feature | Generated Column | Standard Column (Shredded at Load) Bloom Filter | ❌ Not Supported | ✅ Best for 100M+ Cardinality Bitmap Index | ✅ Supported (but slow for 100M+) | ✅ Supported ZoneMap (Min/Max) | ✅ Supported | ✅ Supported Primary Key | ❌ Not Supported | ✅ Supported Rocky's Verdict: For your specific scale (100M+ unique values), stop using the

GENERATED COLUMN

syntax for this specific field. Move the shredding logic into your ETL/Load process to unlock Bloom filter support. References • table_design/indexes/Bloomfilter_index.md • sql-reference/sql-statements/generated_columns.md • knowledge/usage/best-practice.md • table_design/indexes/Bitmap_index.md References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md