> **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; > ``` > >  > > <!-- literal-explain-plan > Captured EXPLAIN provenance for the adjacent reader-facing visual plan. > Canonical capture metadata lives in scratch/actual_operation_plans.json. > > 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 > --> > > > <!-- > Raw-capture provenance — separate run. > SQL, setup, dataset, settings, and scope: artifacts/chapter4_capture_matrix.json. > Target: PostgreSQL 18.x companion fixture. Cache state: uncontrolled. > Boundary: pg_wait_tracer backend execution root; client states are included when the chart shows them. > Fidelity: exact pg_wait_tracer export. Not the adjacent EXPLAIN run; compare state shape, not durations. > --> > >  > > <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>