@Rocky For a table with

DISTRIBUTED BY HASH(col_a, col_b)

, does bucket pruning activate if a query has

col_a IN (x, y, z)

but

col_b

only appears as a range predicate (

col_b BETWEEN a AND b

)? And does the answer change if the distribution key is just

HASH(col_a)

instead?

For a table with

DISTRIBUTED BY HASH(col_a, col_b)

, bucket pruning will not activate if one of those columns (in this case,

col_b

) is only present as a range predicate (

BETWEEN

). 1.

DISTRIBUTED BY HASH(col_a, col_b)

In StarRocks, bucket pruning for hash-distributed tables requires an equality (

=

) or

IN

predicate for all columns defined in the distribution key. * Reasoning: The hash function is applied to the combination of

(col_a, col_b)

. To determine which bucket a row belongs to, the system must calculate

hash(val_a, val_b)

. Because

col_b

is a range, there are theoretically infinite (or many) possible values, and because hash functions are non-linear, a range of input values does not map to a predictable range of buckets. * Outcome: The query will scan all buckets in the relevant partitions. 2.

DISTRIBUTED BY HASH(col_a)

In this case, bucket pruning will activate. * Reasoning: Since the distribution depends only on

col_a

, the system can calculate the hash for each value in your

IN (x, y, z)

list. It will then only target the specific buckets where

hash(x)

,

hash(y)

, and

hash(z)

reside. * Note: The range predicate on

col_b

will still be used to filter data within those buckets, potentially utilizing the Prefix Index or Zone Maps if

col_b

is part of the table's sort key (

ORDER BY

). Summary Table Distribution Key | Predicates | Bucket Pruning?

HASH(col_a, col_b)

|

col_a IN (...)

,

col_b BETWEEN ...

| No

HASH(col_a, col_b)

|

col_a IN (...)

,

col_b IN (...)

| Yes

HASH(col_a)

|

col_a IN (...)

,

col_b BETWEEN ...

| Yes Pro-tip: You can verify this by running your query with

EXPLAIN

. Look at the

OlapScanNode

section; it will show the number of tablets being scanned (e.g.,

partitions=1/1, tablets=1/10

). If the tablet count is less than the total, pruning is working. References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • unloading/Spark_connector.md