A join created duplicate rows and someone added SELECT DISTINCT — why is that costly?
This report joined orders to a one-to-many order_items table, which duplicated each order row, so someone put DISTINCT on the front to hide the duplicates.
Constraint: the report only needs one row per order — the item rows are not aggregated or selected.
SELECT DISTINCT o.order_id, o.user_id, o.status, o.created_at
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;Explain why DISTINCT is costly here and fix the root cause.
DISTINCT sorts or hashes every column of the exploded result to drop duplicates — costly on wide fan-out rows, and it only masks the bug. The duplicates come from the one-to-many join; fix the cause with WHERE EXISTS (...) (a semi-join) or pre-aggregate to one row per key.
- ✗Thinking DISTINCT is a cheap streaming flag, not a sort or hash
- ✗Assuming a LEFT JOIN returns one row per order despite fan-out
- ✗Patching duplicates with DISTINCT instead of removing the join fan-out
- →How would EXPLAIN show the DISTINCT as a Sort or HashAggregate node?
- →When is EXISTS faster than pre-aggregating the child table?
DISTINCT forces a sort or hash of all four selected columns across the whole exploded result to drop duplicates — on wide rows that is an expensive sort/hash step, and it only hides the real cause. The duplicates come from the one-to-many join: each order_items row produces a copy of the order row.
Since the item table is only there to check that an order has at least one item, replace the join with a semi-join:
SELECT o.order_id, o.user_id, o.status, o.created_at
FROM orders o
WHERE EXISTS (
SELECT 1 FROM order_items i WHERE i.order_id = o.order_id
);EXISTS does not multiply rows, so DISTINCT is unnecessary and the planner stops at the first match. If you later need item aggregates, collapse order_items in a subquery with GROUP BY order_id and join one row per order.
