PostgreSQL 10 High Performance - Third Edition

Merge Join

A Merge Join requires that both its input sets are sorted. It then scans through the two in that sorted order, generally moving forward one row at a time through both tables as the joined column values change. The inner table can be rescanned more than once if the outer one has duplicate values. That's where the normally-forwards scan on it goes backwards, to consider the additional matching set of rows from the duplication.

You can only see a Merge Join when joining on an equality condition, not an inequality or a range. To see one, let's ask for a popular report: how much net business was done by each customer?

EXPLAIN ANALYZE SELECT C.customerid,sum(netamount) FROM customers C, orders O WHERE C.customerid=O.customerid GROUP ...