In this lesson, you will learn how distributed query optimizers minimize execution costs by strategically choosing between local processing and network data movement.
The Cost of Distance in Distributed Queries
In a single-node database, the optimizer focuses on disk I/O and CPU. In a distributed environment, the primary bottleneck often shifts to the network. When you execute a JOIN across shards or regions, the query optimizer must decide where to compute the result to minimize the total latency incurred by moving data across the wire.
Cost-Based Decision Making
A distributed query optimizer calculates the "cost" of various plans by evaluating three factors:
- Local Processing Cost: The CPU and I/O required to scan indices or filter data on a specific shard.
- Network Transfer Cost: The volume of data moved between shards (often the dominant factor).
- Communication Latency: The time required to synchronize nodes.
Consider a JOIN between a Users table (sharded by user_id) and an Orders table (sharded by order_id). To join these, the system must choose between:
- Broadcasting: Sending a small reference table to all shards.
- Shuffle Join: Re-partitioning both datasets by the join key so matching rows land on the same node.
-- Example: Joining across shards
-- The optimizer must decide whether to pull 'Orders' to 'Users'
-- or push a 'Users' subset to 'Orders' nodes.
SELECT u.name, o.total
FROM Users u
JOIN Orders o ON u.id = o.user_id;
If the Users table is massive and Orders is small, the optimizer will likely choose to broadcast the Orders data to all Users shards to avoid moving the larger table. If both are massive, it will trigger a shuffle join, redistributing both tables across the cluster to align the keys.
The goal is to reduce the "Data Movement Factor." A sophisticated optimizer uses statistics (like histograms of key distributions) to estimate how many rows will remain after filters are applied. If a WHERE clause drastically reduces the row count on one shard, the optimizer will prioritize performing that local filter before any network transit begins.
Key Takeaway: Distributed query optimization is the art of moving the computation to the data, or minimizing the volume of data moved when computation must be centralized.