starrocks-join-optimization

What problem does it solve?

This Skill helps you diagnose and fix slow or resource-heavy join queries in StarRocks by choosing the most efficient join strategy and verifying it from EXPLAIN output.

Core Features & Use Cases

• Join strategy selection: Covers broadcast, shuffle (hash partition), colocate, bucket shuffle, and nested loop (for non-equi and cross joins), plus the optimizer decision flow and how hints can override it.
• Runtime filter impact: Explains how runtime filters (Bloom/IN/MIN_MAX) reduce probe-side scan work and how to confirm their pushdown using EXPLAIN VERBOSE.
• Skew join handling: Detects join skew symptoms in query profiles and provides a practical salting approach to prevent uneven BE workloads.
• EXPLAIN-based verification: Shows what to look for in plan fragments (e.g., BROADCAST/COLOCATE/PARTITIONED/BUCKET_SHUFFLE/NESTLOOP and Exchange placement) to ensure the intended strategy actually executed.
• Practical guardrails: Includes guardrail guidance for stale statistics (ANALYZE), broadcast memory risk (broadcast_row_limit), and colocate group stability requirements (SHOW PROC '/colocation_group').

Quick Start

Use StarRocks EXPLAIN VERBOSE on your slow join query to identify the chosen join strategy (e.g., BROADCAST/COLOCATE/PARTITIONED) and the presence of pushed-down runtime filters, then apply the corresponding hint or design change.