> [!NOTE] Merge Join > <table> > <tr> > <td width="25%"><img src="assets/ex_mergejoin.png"></td> > <td>A join strategy used when both input sets are already sorted by the join keys. The engine steps through both relations simultaneously, like merging two sorted lists. Efficient for large joins where sorting is already performed or required for the final output.</td> > </tr> > </table> > > ```sql > -- Joining two tables using pre-sorted indexes > SET enable_hashjoin = off; > SET enable_nestloop = off; > > EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) > SELECT * FROM animals a > JOIN species s ON a.species_id = s.id > ORDER BY a.species_id; > ``` > >  > > ```text > Merge Join (cost=1.39..611.66 rows=10000 width=42) (actual time=0.013..1.179 rows=10000 loops=1) > Output: a.id, a.name, a.species_id, a.created_at, s.id, s.name, s.diet_type > Inner Unique: true > Merge Cond: (a.species_id = s.id) > Buffers: shared hit=382 > -> Index Scan using idx_animals_species_id on public.animals a (cost=0.29..485.53 rows=10000 width=27) (actual time=0.005..0.585 rows=10000 loops=1) > Output: a.id, a.name, a.species_id, a.created_at > Buffers: shared hit=381 > -> Sort (cost=1.11..1.12 rows=5 width=15) (actual time=0.007..0.007 rows=5 loops=1) > Output: s.id, s.name, s.diet_type > Sort Key: s.id > Sort Method: quicksort Memory: 25kB > Buffers: shared hit=1 > -> Seq Scan on public.species s (cost=0.00..1.05 rows=5 width=15) (actual time=0.001..0.002 rows=5 loops=1) > Output: s.id, s.name, s.diet_type > Buffers: shared hit=1 > Planning: > Buffers: shared hit=180 > Planning Time: 0.329 ms > Execution Time: 1.386 ms > ``` > >  > > <table> > <tr> > <td rowspan="2" width="25%"><img src="assets/ex_merge.svg"></td> > <td><b>Performance</b></td><td>High performance for pre-sorted inputs or when indexes cover the join keys.</td> > </tr> > <tr><td><b>Cost</b></td><td><code>sort cost + cpu_operator_cost * rows joined</code></td></tr> > </table>