@Rocky We have a large table distributed by

organization_uuid

. We run queries that join two subsets of this table — one filtered to a single org (

a

), another filtered to a set of orgs (

b

) — on a shared domain column. What we tried: To keep memory within a resource group limit, we introduced two approaches: 1. Subset filtering on `b`— passing a smaller list of`organization_uuid`values per query. Since the table is distributed by`organization_uuid`, StarRocks prunes tablets and genuinely reduces the scan. 2. Hash bucketing on `b`— within each subset, applying`crc32(some_id) % n = k`to further limit the join result size per query. The problem: Hash bucketing does not reduce the scan — StarRocks must read all rows in the subset first, evaluate the expression on every row, then discard

(n-1)/n

of them. The scan cost is fixed regardless of

n

. Is this suspicion correct? Even after reducing

b

significantly, queries are still OOMing. Our suspicion is that the

a

side (filtered to a single

organization_uuid

) is now the bottleneck — it is always fully scanned in every query with no way to reduce it further. Questions: 1. Is our understanding correct that`crc32(col) % n = k`cannot be used for tablet or partition pruning, and a full scan is unavoidable? 2. Is there any way to further limit the scan on the`a`side beyond what`organization_uuid`tablet pruning already gives us, without schema changes? 3. Would adding a pre-computed stored column (e.g.

crc32(id) % 16

) and making it a sort key allow StarRocks to prune on it effectively?

Your understanding is correct, and your suspicions regarding the OOM cause are likely accurate. Here is the breakdown: 1. Expression Pruning Behavior Your suspicion is correct. StarRocks cannot use

crc32(col) % n = k

for tablet or partition pruning. * Tablet/Partition Pruning: This happens at the Planner level. The optimizer only understands how to map raw column values (e.g.,

organization_uuid = 'abc'

) to physical storage locations. * Expression Evaluation: Expressions like

crc32

or

%

are evaluated at the Executor level (during the scan). StarRocks must read all data from the disk for the relevant tablets, calculate the hash and modulo for every row, and only then discard the rows that don't match. * Cost: The I/O cost remains identical whether you filter for 1/10th or 1/100th of the data using this method. 2. Scanning Side

a

(Single Organization) If

organization_uuid

is your distribution key, StarRocks already uses it to prune the scan to only the tablets containing that specific

organization_uuid

. However, if a single organization has a massive amount of data, that scan is still heavy. Without schema changes, your options to reduce this scan are limited: * Existing Sort Keys: Check if the table has a sort key (the first few columns in the

DUPLICATE/UNIQUE KEY

or

ORDER BY

clause). If there is a filterable column in the sort key (like a timestamp), adding that to your query will trigger Prefix Index and Zone Map pruning. * Indexes: If you have Bloom Filter or Bitmap Indexes on the domain column you are joining on, StarRocks can use them to skip data blocks, but this is less effective than sort-key pruning for reducing raw scan volume. * Runtime Filters: StarRocks often generates Bloom filters from the smaller side of a join to prune the larger side. However, if the "a" side is the one being used to build the Hash Table in memory (the "Build" side), and it is too large, you will OOM before the probe starts. 3. Pre-computed Column + Sort Key (Recommended) Yes, this is the most effective solution. Adding a pre-computed column and making it part of the sort key will allow StarRocks to prune data within the tablet. Starting with StarRocks v3.1, you can use Generated Columns to handle this automatically without changing your application's

INSERT

logic: 1. Add a Generated Column:

sql
    ALTER TABLE your_table ADD COLUMN join_bucket INT AS (abs(crc32(domain_col)) % 16);

2. Update Sort Key: Since v3.3, you can modify sort keys. You want the sort key to start with the columns you filter on most.

sql
    ALTER TABLE your_table ORDER BY (organization_uuid, join_bucket);

3. How it helps: When you query

WHERE organization_uuid = 'a' AND join_bucket = 0

, StarRocks will: * Use the distribution key to find the correct Tablets. * Use the Prefix Index and Zone Maps (min/max) to skip nearly all data pages within those tablets that don't belong to

join_bucket = 0

. This physically reduces the number of bytes read from disk and processed in memory. Additional Stability Tip: Spill to Disk If you still encounter OOMs during the join itself (even after reducing the scan), you can enable Spill to Disk. This allows the join to complete by using disk space when memory limits are reached, at the cost of performance:

sql
SET enable_spill = true;
-- Optional: trigger spill earlier to stay within resource group limits
SET spill_mem_limit_threshold = 0.8;

Summary Recommendation: Use a Generated Column + Sort Key to turn your "Hash Bucketing" into physical data pruning. This is the only way to genuinely reduce the scan and memory footprint of the

a

side. References • administration/management/resource_management/spill_to_disk.md

So @Rocky StarRocks cannot skip rows before evaluating a computed expression like

crc32(master_uuid) % n = k

. It has to read every single row first, compute the CRC32 on each one, and only then discard the ones that don't match. So if an org has say 10M of their own accounts, every query still scans all 10M rows to evaluate the filter — even if only 1M survive into the join. Is this correct?

Yes, that is exactly correct. In your current setup, StarRocks must perform a "full scan" of all rows belonging to that organization to evaluate the filter. Here is why: 1. Pruning vs. Filtering * Pruning (Storage Level): This happens before any data is read. The storage engine looks at the Zone Maps (Min/Max values) of each 64KB-1MB data page. If your filter is

WHERE col = 5

, and a page's Min/Max is

10

to

20

, StarRocks skips that page entirely without touching the disk. * Filtering (Execution Level): When you use

crc32(col) % n = k

, StarRocks has no way of knowing the "Min/Max" of a CRC32 result for a range of raw UUIDs. It is forced to: 1. Read the

master_uuid

column from disk for every row in the organization's tablets. 2. Load those values into memory (CPU cache). 3. Compute the CRC32 and the Modulo for every row. 4. Discard the rows that don't match. 2. The "Late Materialization" Nuance StarRocks does have an optimization called Late Materialization. If your query is

SELECT col_A, col_B FROM table WHERE crc32(master_uuid) % 10 = 1

, StarRocks will: 1. Read only the

master_uuid

column first. 2. Filter the rows. 3. Only then read

col_A

and

col_B

from disk for the 1M surviving rows. While this saves I/O on

col_A

and

col_B

, you are still performing a heavy CPU and I/O scan on the

master_uuid

column itself. If

master_uuid

is large or the number of rows is in the tens/hundreds of millions, this scan alone can cause the memory pressure or latency issues you are seeing. 3. Why the Generated Column is the solution When you move to a Generated Column (e.g.,

join_bucket

) and add it to the Sort Key: * The result of

crc32(...) % n

is calculated once at write time and stored on disk. * Because it is part of the Sort Key, all rows with

join_bucket = 1

are physically stored together. * The Zone Maps for each data page will now reflect the

join_bucket

values. * When you query

WHERE join_bucket = 1

, the storage engine sees a page with Min/Max

[2, 2]

and completely skips it. Summary: Your current approach forces a "Read-then-Calculate" workflow. The Generated Column approach enables a "Skip-without-Reading" workflow, which is the only way to scale the

a

side of your join effectively. References • using_starrocks/accelerate-cardinality-preserving-joins.md