@Rocky All 8 BE nodes hit 100% memory on a query that was pruned to a single bucket — why does an

account_id

filter not localize it? Setup • Shared-nothing cluster, StarRocks

v4.0.10

, 8 BE nodes. • The tables involved are colocated (

colocate_with

= same group),

DISTRIBUTED BY HASH(account_id)

, identical bucket count (24),

replication_num = 3

. What happened A dev accidentally fired a query containing a pathological

LIKE

pattern — a literal prefix followed by thousands of consecutive

%

(e.g.

LIKE '%ashley%si%%%%…(2,000+ %)…%b%'

). The same pattern is also used inside

any_match(array_map(x -> x LIKE '…', emails))

on an

ARRAY<VARCHAR>

column, plus on two scalar columns, combined with

OR

. We ran it a couple of times. Each run effectively timed out, but memory climbed steadily on all 8 BEs up to ~100% — not a clean

Memory limit exceeded

cancel, just RSS rising until the nodes became unresponsive. Why we’re confused The query already has

WHERE account_id = '<uuid>'

, and the tables are distributed by

account_id

. So with bucket pruning we expected only the 3 replicas holding that one bucket to do any work. Instead every BE spiked. The pathological pattern eating memory is understandable; all 8 nodes being hit is what we can’t reconcile with pruning + the account filter. What we’ve found so far The

EXPLAIN

confirms pruning actually worked — but the join shape is the catch:

2:HASH JOIN
|  join op: RIGHT OUTER JOIN (COLOCATE)      <-- our LEFT JOIN got flipped
|  colocate: true
|  other predicates: (any_match(array_map(... LIKE '%...%%%...')) OR lower(full_name) LIKE '%...' OR lower(address) LIKE '%...')
|
|--1:OlapScanNode  TABLE: contact_base       tabletRatio=1/24   <-- pruned to ONE bucket
0:OlapScanNode     TABLE: contact_property   tabletRatio=1/24   <-- pruned to ONE bucket

So the scan was correctly pruned to a single tablet, but our

LEFT JOIN

was rewritten to a

RIGHT OUTER JOIN (COLOCATE)

, and the OR-of-`LIKE`s sits as a post-join

other predicates

conjunct. Tracing the FE scheduler, this seems to be the reason: in

ColocatedBackendSelector

, for a right/full bucket-shuffle (colocate) fragment, the pruned buckets are added back and round-robined across the whole cluster (to keep outer-join semantics correct): java

// the right join or full join will get wrong result ...
// we need to add empty bucket scan range which is pruned by predicate ...
if (isRightOrFullBucketShuffleFragment && colocatedAssignment.isAllScanNodesAssigned()) {
    for (int bucketSeq = 0; bucketSeq < bucketNum; ++bucketSeq) {
        if (!bucketSeqToWorkerId.containsKey(bucketSeq)) {
            long workerId = workerProvider.selectNextWorker(); // round-robin over BEs

With 24 buckets and 8 BEs, that instantiates the join fragment on all 8 nodes — 7 of them holding only empty buckets. Each instance still `open()`s the predicate, and since

convert_like_pattern

expands *every

%

into

.*

with no collapsing* (then compiles via Hyperscan, falling back to RE2), the pathological regex gets compiled on every node — including the ones with zero rows — before any matching happens. That, plus the compile/open path not being under the per-chunk memory-limit enforcement, looks like why it’s a slow climb to OOM rather than a clean cancel. Questions for the group 1. Is the right/full-outer colocate bucket add-back the correct explanation for the cluster-wide fan-out here, or are we missing something? 2. Is there a way to avoid scheduling the predicate-bearing fragment onto empty-bucket nodes for outer colocate joins — or to evaluate the

other_predicates

only where data exists? 3. Since the cost is largely at expr `open()`/compile time, is

query_mem_limit

/ resource-group memory expected to be ineffective here, and are CPU-second + concurrency limits the right guardrail instead? 4. Has

convert_like_pattern

gained consecutive-

%

collapsing in any newer version? (We’re planning to sanitize

%%%…

→

%

at the app layer regardless.)

Based on your technical analysis and the StarRocks architecture (specifically for versions around

v3.x

and the newer

v4.x

branch), here is a breakdown of why you are seeing cluster-wide fan-out and OOM despite successful bucket pruning. 1. Is the "Bucket Add-Back" the correct explanation? Yes. Your analysis of

ColocatedBackendSelector

is correct. In StarRocks, for a Right Outer Join or Full Outer Join that is executed as a Colocated Join (or Bucket Shuffle Join), the scheduler must ensure that all buckets are accounted for to maintain the semantic correctness of the "outer" side of the join. * The Problem: Even if the scan of the "inner" table is pruned to 1 bucket, the "outer" side (which was flipped to the Right side in your case) represents the entire set of keys that must be preserved. * The Fan-out: If the join fragment is responsible for the full range of 24 buckets, and you have 8 BEs, the scheduler "pads" the missing buckets (the 23 pruned ones) with empty scan ranges and distributes them across the cluster. This ensures that if any node was supposed to process a bucket, it still runs the join operator to produce the NULL-extended rows for that bucket. * Result: The fragment is instantiated on all 8 BEs. 2. Can we avoid scheduling predicates on empty-bucket nodes? Currently, this is a limitation of the "bucket-shuffle/colocate" execution model for outer joins. The fragment is treated as a unit. When the fragment is opened on a BE: 1. It prepares the entire plan tree, including Expression Initialization (

open()

). 2. The

other_predicates

(the OR-of-LIKES) are part of that initialization. 3. Because the join is

COLOCATE

, the BE assumes it might have data (even if the specific scan range for that bucket is empty) and prepares the expression state for all rows passing through that join. There isn't currently a way to "lazy-initialize" these predicates only when the first row arrives. The engine compiles the regex/expression during the fragment startup phase. 3. Why did

query_mem_limit

not stop the OOM? This is a classic "Initial Footprint" issue: * Compile-time Memory: The memory spike occurs during the compilation/initialization of the

LIKE

pattern (especially if it falls back to RE2 or a complex Hyperscan graph). * Tracking Gap:

query_mem_limit

and Resource Group limits are primarily enforced at the operator execution layer (e.g., when processing chunks of data). Memory consumed by the expression compiler or the static state within

Expr::open()

is often not yet tracked by the query's "active" memory tracker or happens so fast that the tracker doesn't have a chance to kill the query before the OS/process hits the hard RSS limit. * Fan-out Multiplier: Because of the fan-out mentioned in Point 1, you are paying this "compilation tax" on 8 nodes simultaneously. Guardrails: CPU-second limits and concurrency limits can help prevent the cluster from being overwhelmed, but they won't stop the memory spike if the compilation itself is the cause. 4. Has

convert_like_pattern

gained

%

collapsing? In StarRocks

v4.0.x

, the engine still translates

LIKE

to Regex patterns (e.g.,

%

becomes

.*

). While many SQL engines collapse

%%%

to

%

during the parsing phase, StarRocks' current expression optimizer may not aggressively collapse thousands of consecutive

%

symbols before passing the string to the regex compiler. * Pathological Case:

LIKE '%%%...'

with 2,000

%

signs creates a regex string with 2,000

.*

sequences. Most regex engines (RE2/Hyperscan) will experience exponential memory growth or massive state-table generation when trying to compile such a redundant and non-deterministic pattern. * Recommendation: As you planned, sanitize at the app layer. A simple regex replace of

%+

with

%

on the input string before sending the query to StarRocks will eliminate the root cause of the compilation OOM. Summary / Recommendations

in what cases would compiler convert left join to right outer join?

In StarRocks, the Cost-Based Optimizer (CBO) and the query compiler will convert a

LEFT JOIN

to a

RIGHT OUTER JOIN

primarily to optimize physical execution. Since StarRocks uses a Hash Join where the right side of the join always builds the hash table, the compiler will flip the join if it believes the original left table should be the build side. Here are the specific cases where this conversion occurs: 1. Build-Side Optimization (Cost-Based) The most common reason is Join Commutativity. StarRocks prefers to use the table with the smaller volume of data (in terms of bytes or rows) as the "build" side to minimize memory usage and time spent constructing the hash table. * Case: You write

Table_Large LEFT JOIN Table_Small

. * Action: If the optimizer estimates that

Table_Large

is actually smaller (perhaps due to filters/pruning) or that building on the left side is more efficient, it will transform the logical plan to

Table_Small RIGHT OUTER JOIN Table_Large

. * Result: The "Build" phase happens on the right side, even if that table was originally on the left in your SQL. 2. Colocate Join Distribution Requirements In a Colocate Join, both tables must be distributed identically. However, for an Outer Join to be executed as a Colocate Join, the "preserved" side (the side that keeps all rows) often dictates how the join is scheduled across the cluster. * Case: If the optimizer chooses a Right Outer Join (Colocate), it is often because it has decided that the right-hand table's distribution should be the master for scheduling the fragment. * The User's Scenario: In your case, the flip to

RIGHT OUTER JOIN (COLOCATE)

was likely triggered because the optimizer estimated that the

contact_property

table (the original right side) should be the "probe" side and

contact_base

should be the "build" side, or vice-versa, to fit the memory constraints or join weight. 3. Null-Rejecting Predicate Rewrites (Logical) While not a direct flip to

RIGHT JOIN

, the compiler frequently converts

LEFT/RIGHT JOIN

to

INNER JOIN

before any reordering happens. * Case: If there is a filter in the

WHERE

clause on a column from the "nullable" side of the join (e.g.,

WHERE right_table.col > 10

), any NULL rows produced by the outer join would be filtered out anyway. * Action: The optimizer rewrites this to an

INNER JOIN

, which then gives it total freedom to reorder the tables (

Table A JOIN Table B

vs

Table B JOIN Table A

) based purely on size. 4. Distributed Strategy (Shuffle vs. Broadcast) If the optimizer determines that a Broadcast Join is more efficient, it will always put the broadcasted table on the right side. * If you wrote a

LEFT JOIN

but the left table is tiny and the right table is massive, the optimizer might flip it to a

RIGHT JOIN

so it can broadcast the small left table to all BEs and keep the large table local. Summary of why your query flipped: In your specific "pathological query," the

LEFT JOIN

was flipped to a

RIGHT OUTER JOIN (COLOCATE)

. This happened because: 1. Join Reordering: The CBO calculated that building the hash table from the original left-side input was cheaper. 2. Scheduling Side-Effect: Once it became a

RIGHT OUTER JOIN

, the StarRocks scheduler (as you noted in the

ColocatedBackendSelector

source) was forced to instantiate the fragment on every node that could possibly hold a bucket for the "outer" (Right) side to ensure no rows were lost. This fan-out is what caused your regex to compile on all 8 nodes. How to prevent the flip: If you want to force the engine to keep the

LEFT JOIN

as written (and potentially avoid the fan-out if the left side is pruned to one node), you can use a join hint:

sql
SELECT ... FROM table_a [left_join] JOIN table_b ON ...

@Rocky Would it localize to that particular node if we provided account ID to all the tables as a filter in the query?

Adding the

account_id

filter to all tables in the query is the most effective way to localize the execution, provided it allows the optimizer to simplify the join. Here is how that change would affect the specific behaviors you observed: 1. Conversion to Inner Join (The "Fix") When you add

WHERE table_b.account_id = <uuid>

to a join that was originally a

LEFT JOIN table_a ON ... table_b

, the optimizer performs a Null-Rejecting Predicate optimization. Since

account_id

on the right side (

table_b

) cannot be NULL if it must equal a specific UUID, the join is logically equivalent to an

INNER JOIN

. * Result: Because it is no longer a

RIGHT

or

FULL

join, the

isRightOrFullBucketShuffleFragment

condition in the

ColocatedBackendSelector

will be false. * Localization: The scheduler will only assign the fragment to the specific BE nodes holding that single bucket (3 replicas). It will no longer "add back" the other 23 empty buckets or round-robin them across the cluster. 2. Pruning on Both Sides With the filter on both sides, the

OlapScanNode

for every table will be pruned to

1/24

tablets. * In a Colocate Inner Join, StarRocks only instantiates the join fragment on the nodes where the data for that specific bucket sequence exists. * The "Pathological LIKE" predicate would then only be compiled and executed on the 3 BEs that actually hold the data for that

account_id

, sparing the other 5 nodes in your cluster from the memory spike. 3. What if you must keep the Outer Join? If you add the filter but use a syntax that preserves the outer join (e.g.,

WHERE table_a.account_id = 'uuid' AND (table_b.account_id = 'uuid' OR table_b.account_id IS NULL)

), the following applies: * The optimizer may still keep it as a

RIGHT OUTER JOIN (COLOCATE)

. * As you noted in the source code, the

ColocatedBackendSelector

for

RIGHT/FULL

joins currently pads the assignment with all buckets to ensure that rows from the "preserved" side are not lost. * Crucial Detail: Even if the scan is pruned, the "preserved" side of an outer join conceptually represents the entire keyspace. The scheduler's current implementation ensures that every bucket sequence is processed by some node to handle the "no match found" case. * Therefore: If the query remains a

RIGHT/FULL

join in the final physical plan, the cluster-wide fan-out may still occur even with pruning, because the scheduler wants to ensure the "outer" side is fully accounted for. Summary & Recommendations 1. Add filters to both sides: This is best practice. It usually triggers the conversion to

INNER JOIN

, which is the only way to bypass the "bucket add-back" logic you identified. 2. Verify with `EXPLAIN`: Check if the join type in the plan changes from

RIGHT OUTER JOIN (COLOCATE)

to

INNER JOIN (COLOCATE)

. If it does, localization is guaranteed. 3. Force the Join Side: If the optimizer persists in flipping the join to

RIGHT OUTER

, use the

[left_join]

hint. A

LEFT JOIN (COLOCATE)

where the left side is pruned to one bucket does not trigger the "add-back" fan-out, because the right side (the nullable side) does not need to be preserved for missing buckets. Example of the localized version:

sql
SELECT ...
FROM contact_base cb
LEFT JOIN contact_property cp ON cb.account_id = cp.account_id AND cb.id = cp.contact_id
WHERE cb.account_id = 'uuid' 
  AND cp.account_id = 'uuid' -- This filter localizes the query by turning the join into an INNER join
  AND (cb.full_name LIKE '%%%...')

By ensuring the join is either an

INNER JOIN

or a

LEFT JOIN

(where the pruned side is the Left side), you keep the "pathological" predicate confined to the nodes that actually hold the data. References • using_starrocks/accelerate-cardinality-preserving-joins.md