starrocks-aggregation-optimizer

What problem does it solve?

This Skill helps you identify why StarRocks GROUP BY and aggregation queries are slow and how the engine chooses one-phase vs two-phase aggregation, including how to control pre-aggregation, distinct counting, and spill behavior.

Core Features & Use Cases

• Plan-driven diagnosis with EXPLAIN: Interpret aggregation-related nodes (AggregationNode/AggregateNode), detect whether the plan uses local aggregation, shuffle exchanges, and whether storage-level pre-aggregation is enabled.
• Optimization levers for aggregation strategy: Understand and influence one-phase vs two-phase aggregation using distribution-key alignment and session planner settings (new_planner_agg_stage).
• Distinct and sketch aggregation tuning: Optimize COUNT DISTINCT via multi-phase strategies and bucketing, and use HLL/BITMAP sketches for approximate/exact unique counting at scale.
• Pre-aggregation and Aggregate Key design guidance: Diagnose when PREAGGREGATION: ON/OFF occurs for Aggregate Key tables and adjust table design (e.g., store SUM components for AVG).
• Spill prevention and monitoring: Configure spill mode and thresholds and interpret spill metrics from query profiles to prevent OOM on large groupings.

Quick Start

Ask the agent to explain the EXPLAIN plan for a StarRocks query with slow GROUP BY and recommend whether to use one-phase aggregation, distinct optimizations (multi-phase COUNT DISTINCT / HLL / BITMAP), and whether pre-aggregation or spill tuning is likely the root cause.