November 19, 2026
When StarRocks Picked the Wrong Side of a Join
How stale table statistics made a query that returned zero rows run out of memory every hour, and how a one-word hint fixed it.

By Sahil Khaire
6 min read
The short version
We had an hourly job that kept failing with Memory of process exceed limit on StarRocks. The strange part: the hour it was processing had zero new rows. The cause was not our data volume. It was the query planner. Because table statistics were out of date, StarRocks believed a 100-million-row table was small and tried to load all of it into memory to run a join. The fix was to tell StarRocks which side of the join to keep in memory, using a [broadcast] join hint. Memory use went from "more than the node has" to a few MB, and the query now runs in under 3 seconds.
This post walks through the bug, how we found it, why the obvious fix did not work, and what we changed. If you run joins on StarRocks (or any MPP database with a cost-based optimizer), the same thing can happen to you.
The setup
To keep things simple, imagine a product analytics platform. Each customer gets their own workspace (a separate database). Inside every workspace there are:
- a
userstable, one row per end user, with alast_active_atcolumn - three event tables:
page_views,clicksandpurchases. Each row has auser_id, anevent_time(when it happened) and acreated_at(when we stored it)
An hourly job keeps users.last_active_at up to date. For each workspace it runs one query that does two things:
- The aggregate. From the three event tables, find every user_id that got new events in the last window (filtered by created_at), along with its latest event_time.
- The join. Join that result to users, so we skip users that were deleted and never move last_active_at backwards.
The original query looked like this:
SELECT u.id, a.last_event_at
FROM (
SELECT user_id, MAX(event_time) AS last_event_at
FROM (
SELECT user_id, event_time FROM analytics.page_views
WHERE created_at >= ? AND created_at < ?
UNION ALL
SELECT user_id, event_time FROM analytics.clicks
WHERE created_at >= ? AND created_at < ?
UNION ALL
SELECT user_id, event_time FROM analytics.purchases
WHERE created_at >= ? AND created_at < ?
) e
GROUP BY user_id
) a
JOIN analytics.users u ON u.id = a.user_id
WHERE u.is_deleted = 0
AND (u.last_active_at IS NULL OR a.last_event_at > u.last_active_at);SELECT u.id, a.last_event_at
FROM (
SELECT user_id, MAX(event_time) AS last_event_at
FROM (
SELECT user_id, event_time FROM analytics.page_views
WHERE created_at >= ? AND created_at < ?
UNION ALL
SELECT user_id, event_time FROM analytics.clicks
WHERE created_at >= ? AND created_at < ?
UNION ALL
SELECT user_id, event_time FROM analytics.purchases
WHERE created_at >= ? AND created_at < ?
) e
GROUP BY user_id
) a
JOIN analytics.users u ON u.id = a.user_id
WHERE u.is_deleted = 0
AND (u.last_active_at IS NULL OR a.last_event_at > u.last_active_at);The job is built to be safe: it saves a checkpoint (the end of the last window it finished). If a run fails, it logs the error, keeps the old checkpoint, and tries the same window again next hour. Nothing is lost.
The symptom
On our dev cluster, one workspace kept failing. It was our load-test workspace, which we had filled with about 100 million users and 1.4 billion events. Every hour, the same error:
Memory of process exceed limit. ... Used: 6.1GB, Limit: 6GBMemory of process exceed limit. ... Used: 6.1GB, Limit: 6GBA few things made this confusing:
- It failed even when there was nothing new to process. Nobody had written events to that workspace for days.
- The other 11 workspaces were fine.
- The checkpoint logic did its job, so no data was corrupted. But since every retry hit the same error, that workspace would never be updated again. A safe failure that repeats forever is still an outage.
A quick refresher: how a hash join works
To understand the bug, you need one idea about how StarRocks runs a join.
StarRocks (like most analytical databases) uses a hash join. It picks one side of the join, called the build side, and loads it completely into memory as a hash table. Then it reads the other side, the probe side, row by row, and looks up each row in the hash table. The probe side is streamed. It never has to fit in memory.
So the golden rule is simple: the smaller side should be the build side. If the planner picks the wrong side, you are trying to fit the big table into RAM.
How does StarRocks know which side is smaller? It does not run the query first to find out. It guesses, using table statistics: row counts, column cardinality and so on, collected in the background.
The investigation
I split the query into its two parts and ran each one by itself.
The aggregate alone (the three event tables, filtered by created_at) took about 2.9 seconds and returned 0 rows. So scanning 1.4 billion events by created_at was not the problem.
The join was what ran out of memory.
Then I ran EXPLAIN on the full query and looked at what the planner thought the row counts were. This was the answer:
StarRocks believed Reality
Rows in users 595,000 ~100,000,000
Events matching the 1-hour window 359,200,001 0StarRocks believed Reality
Rows in users 595,000 ~100,000,000
Events matching the 1-hour window 359,200,001 0The statistics were taken long before the load test. As far as StarRocks knew, users was a small table with 595K rows, and the aggregate was going to produce hundreds of millions of rows. So it made a completely sensible choice based on wrong numbers: put users on the build side.
In reality that meant loading 100 million users into a hash table. Each compute node has about 6 GB of memory. It never had a chance.
Why swapping the table order did not help
My first idea was to flip the SQL so users came first and the aggregate came second. In many databases, the order you write the tables in has some effect.
It made no difference. StarRocks has a cost-based optimizer (CBO). It is free to reorder the join and choose the build side on its own, and it did, using the same bad statistics. The order in the SQL text is a suggestion at most.
If the planner is making the decision, changing how the query looks will not change the decision. You have to either fix what the planner sees (the statistics) or take the decision away from it (a hint).
The fix: a broadcast join hint
We moved users to the front and added a [broadcast] hint on the aggregate:
SELECT u.id, a.last_event_at
FROM analytics.users u
JOIN [broadcast] (
SELECT user_id, MAX(event_time) AS last_event_at
FROM ( ... same UNION ALL of the three event tables ... ) e
GROUP BY user_id
) a
ON u.id = a.user_id
WHERE u.is_deleted = 0
AND (u.last_active_at IS NULL OR a.last_event_at > u.last_active_at);SELECT u.id, a.last_event_at
FROM analytics.users u
JOIN [broadcast] (
SELECT user_id, MAX(event_time) AS last_event_at
FROM ( ... same UNION ALL of the three event tables ... ) e
GROUP BY user_id
) a
ON u.id = a.user_id
WHERE u.is_deleted = 0
AND (u.last_active_at IS NULL OR a.last_event_at > u.last_active_at);[broadcast] tells StarRocks: take the right-hand side of this join (our aggregate), send a full copy of it to every compute node, and build the hash table from it. The users table stays where it is and is only streamed past that hash table on each node. It is never held in memory.
The key point is that the plan no longer depends on the statistics being right. Even if the stats say users has 5 rows, StarRocks will still do what we told it.
How it behaves now
Memory now depends on how many users were active in the window, not on how big the tables are. For a one-hour window that is anything from zero to a few thousand rows, which is a few MB per node at most.
Speed on the load-test workspace (100M users, 1.4B events):
Window Before After
1 hour Out of memory ~2.8 s
30 days Out of memory ~2.8 s
Full run, all 12 dev workspaces Failed on 1 workspace 0.65 s totalWindow Before After
1 hour Out of memory ~2.8 s
30 days Out of memory ~2.8 s
Full run, all 12 dev workspaces Failed on 1 workspace 0.65 s totalResults are unchanged. The filters are the same (user exists, not deleted, never move backwards) and the values are the same. We updated the unit test to match the new SQL, and the whole test suite passes with Go's race detector on.
When broadcast is the wrong choice
A hint is a promise you make to the database, so it is worth knowing when that promise breaks.
A broadcast join copies the build side to every node. Memory use is roughly size of the aggregate ร number of nodes. That is great when the aggregate is small, and bad when it is not. If someone ever runs this job over a huge backfill window where tens of millions of users were active, the broadcast side gets big and you can run out of memory again, just from the other direction.
For our job this is safe, because the aggregate is bounded by "users active in the window", and the window is normally one hour. Even our 30-day test was fine. But if your build side can grow without limit, consider a guard: cap the window size, or process large backfills in smaller chunks.
What about just fixing the statistics?
You can, and you probably should. Running ANALYZE TABLE analytics.users (and on the event tables) would have given the planner correct numbers, and it would most likely have chosen the right build side on its own.
We still kept the hint, for two reasons:
- Statistics go stale again. Bulk loads, load tests and fast-growing tenants can change a table much faster than background collection runs. We did not want the job's correctness to depend on that timing.
- We know something the planner cannot know. The aggregate is always "recent activity", which is small by design. The hint writes that knowledge into the query.
Think of it as belt and braces: keep statistics healthy for every other query, and pin the plan for the one query where you know the right answer.
Takeaways
- A hash join holds one side fully in memory. Make sure it is the small side.
- The planner picks that side from statistics, not from your data. Stale stats can make it choose a 100M-row table as "small".
- "It failed with zero rows" is a clue. When a query fails with no input, look at the plan, not the data.
- Split the query and test each part. That is how we knew the scan was fine and the join was the problem.
- Rewriting the SQL does not beat a cost-based optimizer. Fix the statistics or use a hint.
- [broadcast] makes memory depend on the small side. Use it when the small side is small by design, and add a guard if it might not be.
- Safe failure is not the same as working. A job that keeps its checkpoint and fails the same way forever still needs an alert.