@Rocky In starrocks shared-nothing we have a periodic task to incrementally build a regular

cool_table

from queries on colocated tables

table_a

and

table_b

.

cool_table

has no colocation set. Would it make incremental build out more efficient if

cool_table

was on the same colo group as

table_a

and

table_b

?

Yes, colocating

cool_table

with

table_a

and

table_b

will make the incremental build-out more efficient by avoiding a final data shuffle during the

INSERT

operation. Why it is more efficient In a StarRocks shared-nothing architecture, the efficiency gain comes from eliminating network overhead at two stages: 1. The Read/Join Stage (Existing): Since

table_a

and

table_b

are already in a colocation group, any join on their distribution keys is a Colocate Join. This means the join is performed locally on each BE node using its local data slices, with zero network transmission between nodes during the join process. 2. The Write/Sink Stage (The Improvement): * Without Colocation: Even if

cool_table

has the same bucket count and keys, StarRocks does not guarantee that "Bucket 1" of the source tables and "Bucket 1" of

cool_table

reside on the same physical BE node. Consequently, after the join is computed, the BE must shuffle (redistribute) the resulting data over the network to the specific BE nodes where `cool_table`'s tablets are located. * With Colocation: By adding

cool_table

to the same colocation group, you guarantee that its buckets are mapped to the exact same BE nodes as the source tables. If your incremental query joins on the distribution keys and the results are inserted into

cool_table

, the data is already sitting on the correct BE node for the final write. StarRocks can then perform a shuffle-free insert, skipping the expensive network redistribution phase. Requirements for this to work To realize this efficiency, the tables must strictly follow the Colocation Group rules: * Identical Distribution Keys: The

DISTRIBUTED BY HASH(...)

columns must have the same data types and order. * Identical Bucket Count: All tables in the group must have the same number of buckets. * Key Matching: The

INSERT INTO ... SELECT

query must be structured such that the distribution of the result set (determined by the join/group-by keys) matches the distribution key of

cool_table

. How to verify You can verify if the shuffle is being avoided by running an

EXPLAIN

on your build-out query:

sql
EXPLAIN INSERT INTO cool_table SELECT ... FROM table_a JOIN table_b ON ...;

* Efficient: If you see a plan where the

OlapTableSink

(the write operation) follows the join without an intervening

EXCHANGE

(shuffle) node, the colocation is successfully avoiding the network transfer. * Inefficient: If an

EXCHANGE

node exists just before the

OlapTableSink

, the data is being redistributed across the network before being written. References • using_starrocks/Colocate_join.md