starrocks-join-optimization

To fix slow StarRocks join queries, run EXPLAIN VERBOSE to identify the executed join strategy (BROADCAST/COLOCATE/PARTITIONED/BUCKET_SHUFFLE/NESTLOOP) and Exchange placement. Then apply optimizer hints or design changes to select the most efficient join strategy and validate that runtime filters are pushed down to reduce probe-side scans.

To prevent data skew in StarRocks, detect skew symptoms in query profiles and apply a practical salting approach to distribute workloads evenly across Backends (BEs). Selecting appropriate partitioned or bucket-shuffle join strategies mitigates uneven processing during execution.

Runtime filters (Bloom/IN/MIN_MAX) improve StarRocks join performance by reducing probe-side scan work. Verify their pushdown effectiveness by checking EXPLAIN VERBOSE output to ensure filters are actively applied before the join operation executes.

Use colocate join in StarRocks when joining tables with matching bucketing distributions to eliminate network shuffle. Ensure colocate group stability by verifying SHOW PROC '/colocation_group' and maintaining consistent bucket definitions across participating tables.

StarRocks broadcast joins cause memory failures when the right-side table exceeds safe broadcast_row_limit thresholds. Run ANALYZE to update stale statistics so the optimizer accurately evaluates table sizes, or switch to a shuffle partition strategy to distribute data across nodes.

StarRocks supports non-equi and cross joins using the nested loop strategy. Verify NESTLOOP execution via EXPLAIN output to ensure no unintended regressions occur, and apply appropriate session variables or hints to optimize execution for these specific join types.