Merge Join

A Merge Join reads two inputs already sorted on the join key and walks them together. Each side is read once and the output comes out sorted. The plan has either a Sort under each side or an index scan that delivers rows in key order. Joining all of commerce.orders to all of commerce.order_items on order_id is the shape. orders_pkey gives orders in id order and order_items_order_idx gives items in order_id order, so no Sort is needed.

Why it matters

Fine when the order comes free from an index, or when the query needs sorted output anyway. The problem is a Merge Join that needs a Sort of a large input, because that Sort costs more than hashing would. At work_mem = 4MB a sort of 12,000,000 orders goes to disk. Look under the join. Index scans in key order: cheap. A Sort with external merge: the join is paying for its ordering.

What to do

1. If a Sort feeds the join, see whether an index on the join column would hand the rows over in order.
2. If that sort spilled, raise work_mem for the session, or test SET enable_mergejoin = off for one run and compare against the Hash Join you get.
3. Check the estimates on both inputs. Two small sorted inputs that turned out large is an estimate problem, not a join problem. Estimated rows explains the ≈N label.

In the Plan tab

A Merge Join is drawn like any join, with two bands in. A Sort under it that went to disk carries its own spill label and finding. The wheel on an index scan feeding the join matches Index Scan · in order when the heap order agrees with the index, as order_items.order_id does at 0.97.