Mastering PostgreSQL 12 - Third Edition

Merge joins

Finally, there is the merge join. The idea here is to use sorted lists to join the results. If both sides of the join are sorted, the system can just take the rows from the top and see if they match and return them. The main requirement here is that the lists are sorted. Here is a sample plan:

Merge join   Sort table 1      Sequential scan table 1   Sort table 2      Sequential scan table 2 

To join these two tables (table 1 and table 2), data has to be provided in a sorted order. In many cases, PostgreSQL will just sort the data. However, there are other options we can use to provide the join with sorted data. One way is to consult an index, as shown in the following example:

Merge join    Index scan table 1    Index scan table 2

One side of ...