@Rocky Is there sense in putting bloom filter and bitmap index on high cardinality column? In cases when bitmap index won’t kick in because ratio is not high enough, would bloom still be used and potentially improve performance? If I use multiple columns with bitmap index would

bitmap_max_filter_ratio

be calculated from product of columns cardinality? Does column cardinality come from statistics or it’s part of bitmap index itself?

Hello, I'm Rocky! Here are the answers to your questions based on StarRocks' documentation and best practices: 1. Is there sense in putting Bloom filter and bitmap index on high cardinality columns? Yes, but they serve different optimization purposes: * Bloom Filter Index: Specifically recommended for high cardinality columns (e.g., ID, user_id) where values are rarely repeated. It helps StarRocks skip entire data files/blocks by quickly detecting if a value is definitely not present. * Bitmap Index: Contrary to some older conventions, StarRocks documentation notes that bitmap indexes are suitable for high cardinality columns or combinations of multiple low cardinality columns. However, be aware that for extremely high cardinality, bitmap indexes can increase disk space usage and impact data loading performance. 2. If a bitmap index isn't used due to the ratio, will the Bloom filter still improve performance? Yes. Bitmap and Bloom filter indexes can be used simultaneously. StarRocks has an adaptive selection mechanism for bitmap indexes; if the "number of filter values / cardinality" ratio exceeds the

bitmap_max_filter_ratio

threshold, it skips the bitmap index to avoid the overhead of loading sparse bits. In this scenario, the Bloom filter remains active and can still provide performance gains by skipping data at the block/file level. 3. How is

bitmap_max_filter_ratio

calculated for multiple columns? For queries involving multiple columns with bitmap indexes, the ratio is calculated based on the product of the values and cardinalities. * Formula:

(Values_Col1 _ Values_Col2 _ ...) / (Cardinality_Col1 _ Cardinality_Col2 _ ...)

* Example: If you filter

gender = 'male'

(1 value, cardinality 2) and

city IN ('NY', 'LA')

(2 values, cardinality 10,000), the ratio is

(1 _ 2) / (2 _ 10,000) = 0.0001

. If this result is less than

bitmap_max_filter_ratio / 1000

(default threshold is 0.001), the bitmap index will be used. 4. Does column cardinality come from statistics or the bitmap index itself? The cardinality used for the adaptive selection mechanism is typically derived from the index metadata. Since the bitmap index must store every distinct value in its dictionary-like structure, the index inherently knows the exact cardinality of the column it covers. While the StarRocks optimizer uses general table statistics (collected via

ANALYZE

) for join reordering and plan selection, the specific bitmap filtering decision relies on the metadata associated with the index. Pro-tip: You can check if your indexes are working by looking at the query profile for metrics like

BitmapIndexFilterRows

and

BloomFilterFilterRows

. References • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md • table_design/indexes/Bitmap_index.md

@Rocky does this calculation happen at tablet or segment level?

The adaptive selection and calculation of the bitmap index ratio in StarRocks happen at the segment level. Here is the breakdown of how this works and where the information comes from: 1. Calculation Level: Segment The decision to use a bitmap index is made by the storage engine on the Backend (BE) as it processes individual data files (segments). * Why Segment? In StarRocks, bitmap indexes are "secondary indexes" that are physically stored within each segment file. Each segment has its own independent bitmap index metadata. * Adaptive Selection: For each segment, StarRocks checks if the query conditions satisfy the

bitmap_max_filter_ratio

. If one segment has high cardinality for a column but another segment (perhaps from a different time range or partition) has lower cardinality, the engine can theoretically make different decisions, though in practice, cardinality is often consistent across a column. 2. Source of Cardinality: Index Metadata The column cardinality used for this calculation is part of the bitmap index itself, not the general table statistics (which are used by the FE optimizer for join planning). * Index Metadata: Every bitmap index contains a dictionary (mapping values to bit offsets). The index naturally "knows" exactly how many unique values it contains for that specific segment. * Efficiency: Because this information is in the segment footer/index metadata, StarRocks can check the cardinality and the ratio before actually loading the large bitsets into memory. 3. Summary of the Logic * The Ratio:

(Number of values in filter) / (Cardinality in index metadata)

. * The Threshold:

bitmap_max_filter_ratio / 1000

. * The Execution: If the ratio is below the threshold, the BE loads the bitmap index for that segment. If it's above, it skips the index and falls back to other methods (like Bloom filters or Page-level ZoneMaps) or a full scan of that column's data blocks. As noted previously, this is why Bloom filters and bitmap indexes can work together: even if the bitmap index is skipped at the segment level because the ratio is too high, the Bloom filter (which also operates at the segment/block level) can still be used to skip data. References • table_design/indexes/Bitmap_index.md References • table_design/indexes/Bitmap_index.md

@Rocky what are the consequences of tuning that ratio, increasing it it from default 1/1000 to 1/100 for example

Increasing the

bitmap_max_filter_ratio

(default is

1

, which results in a $1/1000$ threshold) makes the engine more "aggressive" in choosing to use the bitmap index. If you want to increase the effective ratio to $1/100$, you would set the parameter to

10

. Here are the consequences of increasing this ratio: 1. Potential Performance Gain (The "Pro") * More frequent Page Skipping: If your filtered rows are physically clustered together in the data files, a bitmap index can skip entire pages of data. By increasing the ratio, you allow the engine to use this optimization for queries that filter out a smaller percentage of data (up to 1% instead of 0.1%). * Reduced IO for "Medium" selectivity: For queries that are on the edge of the default threshold, enabling the index might significantly reduce the

CompressedBytesRead

if it successfully identifies that large chunks of pages don't need to be loaded. 2. Potential Performance Degradation (The "Cons") Increasing the ratio can lead to "False Positives" where the engine decides to use an index that actually slows the query down: * Loading Overhead: Bitmap indexes must be loaded from disk into memory. If the index itself is large (common on high-cardinality columns), the time spent loading the index (

BlockFetch

in profiles) might exceed the time saved from skipping data. * The "Scattered Data" Problem: This is the most common risk. In StarRocks, the smallest unit of IO is a Page. Even if a bitmap index filters 99% of the rows, if those remaining 1% of rows are randomly scattered across every single page in the segment, the engine still has to read 100% of the pages. * In this scenario, using the index results in:

(Time to read all pages) + (Time to load index) + (Time to process bitmap logic)

. * This is strictly slower than just reading the pages directly. * CPU Overhead: Processing the bitmap (the

BitmapIndexFilter

stage) consumes CPU cycles. On very high-speed NVMe storage, the CPU cost of bitmasking can sometimes be more expensive than just scanning the raw data. 3. Memory Pressure * Bitmap indexes can be memory-intensive. Forcing the engine to use them more often across many concurrent queries can increase the memory footprint of the Backend (BE) process, potentially leading to memory limit issues if many large indexes are being pulled into the cache simultaneously. Recommendation If you are considering increasing this value: 1. Check the Profile first: Look at a query profile for

BitmapIndexFilterRows

vs

RawRowsRead

. If

ReadPagesNum

is nearly the same as the total pages even when the index is used, the index is not helping and the ratio should stay low. 2. Test with `SET`: You can test this at the session level using

SET bitmap_max_filter_ratio = 10;

before applying it globally in

be.conf

. 3. Physical Sorting: If you frequently query a specific column with "medium" selectivity, consider making it a Prefix Key (sorting the data by that column). This clusters the data, making both the bitmap index and ZoneMap indexes significantly more effective. References • table_design/indexes/Bitmap_index.md References • table_design/indexes/Bitmap_index.md