The short answer
In a distributed relational database, the slowest physical operation is sending data over the network between cluster nodes, known as network . A colocated join is an optimized join where matching rows from both tables already reside on the exact same compute slice before the query begins.
How a colocated join works
Suppose you have an orders and an order_items fact table. Both tables have hundreds of millions of rows.
If both tables are created with DISTSTYLE KEY and both use the join column (order_id) as their DISTKEY:
CREATE TABLE orders (order_id BIGINT, customer_id BIGINT)
DISTSTYLE KEY DISTKEY(order_id);
CREATE TABLE order_items (item_id BIGINT, order_id BIGINT, price NUMERIC)
DISTSTYLE KEY DISTKEY(order_id);Because Redshift uses the exact same hash function across the cluster, any given order_id (such as 104857) will always hash to the same slice (for example, Slice 3) for both tables.
When you execute:
SELECT o.order_id, sum(i.price)
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
GROUP BY o.order_id;Slice 3 joins its local orders rows with its local order_items rows directly in local memory. Zero bytes travel across the network.
Contrast with redistribution joins
If the tables are distributed on different keys, Redshift must execute one of two costly data movement operations:
- DS_DIST_BOTH: Redshift re-hashes and sends rows from both tables across the network to new slices.
- DS_BCAST_INNER: Redshift broadcasts an entire copy of one table to every single compute node in the cluster.
In an , finding DS_DIST_NONE indicates a colocated join, representing the fastest possible join execution.