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 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 BY C.customerid;
QUERY PLAN 
----------
GroupAggregate  (cost=0.05..2069.49 rows=12000 width=12) (actual time=0.099..193.668 rows=8996 loops=1)
   ->  Merge Join  (cost=0.05..1859.49 rows=12000 width=12) (actual time=0.071..146.272 rows=12000...