StarRocks Optimization: Null Filtering + Bloom Index Cuts 12s Query to 1s, Fixes CN Restarts

Business Scenario and Original Query Problem

1. Business Background

An internet platform's user Q&A analysis scenario required querying valid Q&A records (table: user_qa_record) for specific time ranges to compute core metrics such as Q&A count and popular question types. The table is a StarRocks Duplicate Key table with approximately 200 million rows and 15 columns including question, qa_answer, and create_time. The qa_answer column contains a large number of NULL values, \N (import-time null identifier), and empty strings.

2. Original Query Statement

Core query requirement: retrieve valid answers (non-null, non-\N, non-empty) for specified asset_id, cust_id, and time range (answer_end_date), ordered by answer_end_date descending. Original SQL:

SELECT 
    qa_id,
    qa_end_date,
    cust_id,
    asset_id,
    qa_title,
    qa_answer
FROM user_qa_record
WHERE 
    asset_id = %s
    AND cust_id = %s
    AND answer_end_date BETWEEN %s AND %s
    AND qa_answer IS NOT NULL
    AND qa_answer != '\N'
    AND qa_answer != ''
ORDER BY answer_end_date DESC;

3. Original Query Problems

Poor performance: single query latency stable at ~12s, far from business "second-level response" requirement.

Stability risk: repeated executions caused CN (Coordinator Node) restarts; StarRocks logs showed "Out of Memory (OOM)" errors, indicating excessive resource consumption.

Deep Root Cause Analysis

Used StarRocks EXPLAIN command to view execution plan, combined with FE (Frontend) and CN node monitoring metrics (CPU, memory, IO).

1. Execution Plan Analysis

Running EXPLAIN original_query revealed three core issues:

No index hit, full table scan overhead: OlapScanNode showed partitions=6/6 (scanning all 6 partitions) and tabletRatio=216/216 (scanning all 216 tablets). Equality conditions on asset_id and cust_id hit no indexes, requiring full table traversal with high IO cost.

Late null filtering, dependent on post-decode judgment: Null filters on qa_answer (IS NOT NULL, != '\N', != '') executed only after the 3:Decode node decoded all string columns (dictionary-encoded), dramatically increasing CPU load.

No partition pruning: Despite answer_end_date range condition, partition pruning did not trigger (still scanning all 6 partitions), further expanding invalid data scan scope.

2. Resource Usage Analysis

CN node monitoring during query execution showed:

CPU usage spiked to 95%+, sustained over 10s.

Memory usage surged from normal 45GB to 65GB (exceeding 80% of 75GB node threshold).

Disk IO throughput peaked at 100MB/s, mainly from full table scan reads.

3. Root Cause Summary

Full table scan + no partition pruning → IO explosion: Scanning all 6 partitions, 216 tablets, 200M rows; 30% invalid null data; IO throughput peaked at 100MB/s, extreme disk pressure.

Full table decode overhead → CPU spike: qa_answer uses dictionary encoding; Decode node must decode entire table before null judgment, consuming massive CPU, keeping usage at 95%+.

Memory exhaustion: Full scan data loaded into memory, compounded by decode and sort memory usage, driving CN memory to 8GB over threshold, triggering OOM restart.

CN node OOM restart mechanism triggered by memory and CPU exhaustion.

Optimization Solution Design

Designed a three-dimensional approach: "early null filtering + Bloom index + trigger partition pruning". Core idea: "reduce scanned data volume (index + partition pruning) + lower compute overhead (avoid full table decode)".

1. Early Null Filtering to Reduce Scan Volume

Null data (NULL, \N, empty string) accounts for 30%. Early filtering directly cuts 30% scan volume. Two approaches:

ETL preprocessing: Filter invalid nulls in qa_answer during import via Spark/Flink, reducing invalid data at source.

Query statement optimization: Reorder conditions to place null filters early, leveraging StarRocks predicate pushdown so null filtering happens at scan phase, not after full scan.

2. Create Composite Bloom Index for Efficient Filtering and Partition Pruning

Based on query condition characteristics, create Bloom indexes to address equality queries, range queries, null filtering, and partition pruning:

Bloom index: On asset_id, cust_id columns. Bloom index can pre-determine if field is null/specific value without decoding, completely avoiding full table decode overhead.

Optimization Implementation Steps

1. Step 1: ETL Preprocessing + Query Optimization

(1) ETL preprocessing: add null filtering logic at import, example (Spark SQL):

INSERT INTO user_qa_record
SELECT * FROM source_qa_data
WHERE question_answer IS NOT NULL
  AND question_answer != '\N'
  AND question_answer != '';

(2) Query optimization: reorder conditions, place null filters and equality checks early to ensure predicate pushdown and partition pruning trigger:

SELECT 
    qa_id,
    qa_end_date,
    cust_id,
    asset_id,
    qa_answer
FROM user_qa_record 
WHERE asset_id = %s  -- equality first
  AND cust_id = %s
  AND answer_end_date BETWEEN %s AND %s  -- range query
ORDER BY answer_end_date DESC;

2. Step 2: Create Targeted Indexes

(1) Create Bloom index on qa_answer column to support null filtering:

ALTER TABLE user_qa_record 
SET ("bloom_filter_columns" = "asset_id,cust_id");  
-- Bloom index default parameters sufficient for non-null filtering

3. Step 3: Verify Index Effectiveness and Execution Plan Optimization

Run EXPLAIN optimized_query. Key changes in optimized plan:

3:Decode node completely eliminated: No full table dictionary decode needed, CPU overhead drastically reduced.

Index hit effective: Composite Bloom index supports asset_id, cust_id equality and answer_end_date range queries; no full traversal, only scans matching rows.

Scanned data volume significantly reduced: Index filtering + null filtering cut scanned rows from 200M to 50M (75% reduction). If answer_end_date is partition key, partition pruning triggers (partitions scanned from 6/6 down to matching time range partitions).

Predicate pushdown fully effective: All filters (null, equality, range) complete at OlapScanNode scan phase, no downstream compute node processing needed.

Optimization Effect Verification

1. Performance Metrics Comparison

Charts show query latency dropped from ~12s to 0.7-0.9s range.

2. Stability Verification

Ran optimized query 10 times consecutively, observed CN node status:

No node restarts: CN CPU and memory usage stable, no OOM errors.

Stable response latency: 10 queries all between 0.7s-0.9s, minimal variance, meeting second-level response requirement.

Optimization Experience Summary and Extensions

1. Core Optimization Points

Null handling first: prioritize ETL preprocessing to filter invalid nulls.

Precise index selection: use Bloom index for non-null/equality filtering to avoid resource waste.

Trigger partition pruning: time-range queries must ensure condition field is partition key, or link partition key via composite index to reduce scanned partitions and IO.

2. General StarRocks Query Optimization Approach

Check execution plan first: use EXPLAIN to identify full table scans and index hits.

Reduce data scan: early filter invalid data (nulls, out-of-range), leverage partition pruning (e.g., partition by create_time) to further shrink scan range.

Optimize compute overhead: avoid inefficient functions, complex aggregations; use indexes properly to lower CPU load.

Monitor resource bottlenecks: watch CN/BE node CPU, memory, IO metrics to pinpoint root cause of exhaustion.

3. Considerations

More indexes not always better: excessive indexes increase import overhead (write-time index maintenance); create only necessary indexes for core query scenarios.

Partition table optimization: for billion-row tables, recommend partitioning by time field (e.g., create_time); use partition pruning at query time to reduce scan volume.

Parameter tuning assist: if resource pressure persists, adjust CN memory params (e.g., exec_mem_limit) and parallelism params (e.g., parallel_fragment_exec_instance_num).

Conclusion

This optimization via " early null filtering + Bloom index " combo not only cut query latency from 12s to under 1s but completely resolved CN node restart stability issues. Core insight: StarRocks query optimization centers on " reduce invalid data scan " and " lower compute overhead ". Must fully utilize Explain plans to locate bottlenecks (full scans, Decode nodes), then apply index optimization (Bloom index), predicate pushdown, partition pruning for targeted fixes. Cultivating the habit of " check execution plan first, locate bottleneck second, optimize precisely last " efficiently resolves slow queries and node stability problems in production.