@Rocky what do I need to look at in explain analyze to see if partition pruning is working with a query

To verify if partition pruning is working in StarRocks using

EXPLAIN ANALYZE

, you should focus on the Scan operator (often labeled as

OlapScanNode

or

SCAN

) in the execution plan. Here are the specific indicators to look for: 1. The

partitions

or

partitionRatio

Field The most direct way to check partition pruning is to look at the Scan operator's summary in the plan output. * What to look for:

partitions=X/Y

or

partitionRatio: X/Y

. * Interpretation: * X is the number of partitions that the optimizer has selected to scan. * Y is the total number of partitions in the table. * Pruning is working if X is significantly smaller than Y. If you see

1/100

, it means 99 partitions were pruned. 2. The

tabletRatio

Field (Bucket Pruning) While partitions are the top-level distribution, StarRocks also prunes "buckets" (tablets) within those partitions. * What to look for:

tabletRatio=A/B

. * Interpretation: If the number of tablets scanned (A) is less than the total tablets in the selected partitions (B), then bucket pruning (based on your distribution key) is also working. 3. Runtime Metrics in the Profile Since

EXPLAIN ANALYZE

provides the actual runtime profile, you can verify if the execution matched the plan: * TabletCount: Found under the

OLAP_SCAN

metrics. This shows the actual number of tablets processed. * PushdownPredicates: Look for your partition column (e.g.,

event_date

) under the

Predicates

or

PushdownPredicates

section of the Scan operator. This confirms the engine is using your filter to limit the data scan. Example Plan Output

text
0:OlapScanNode
   TABLE: sales_data
   PREAGGREGATION: ON
   PREDICATES: 3: event_date >= '2023-10-01', 3: event_date <= '2023-10-02'
   partitions=2/365     <-- Pruning is working (2 partitions out of 365)
   rollup: sales_data
   tabletRatio=20/20    <-- All tablets in those 2 partitions are scanned

Pro-Tips * FE Level: Partition pruning happens at the Frontend (FE) during query planning. If the

partitions

ratio in a standard

EXPLAIN

already shows pruned values, it means the optimizer successfully recognized your filter. * Common Pitfall: If you don't see pruning, ensure your filter is on the partition column and that you aren't performing a function on the column in the

WHERE

clause (e.g.,

WHERE YEAR(date_col) = 2023

) that prevents the optimizer from identifying the range, unless you are using Expression Partitioning. References • best_practices/query_tuning/query_profile_operator_metrics.md • unloading/Spark_connector.md