### Merge Join
![[assets/ex_mergejoin.png|256]]
### The Explain Trace
```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.42..1224.87 rows=20000 width=42) (actual time=1.010..3.539 rows=20000 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=763
-> Index Scan using idx_animals_species_id on public.animals a (...)
-> Index Scan using species_pkey on public.species s (...)
```
---
- **Description**: Joins tables using merge join algorithm.
- **Performance**: High performance for pre-sorted inputs or when indexes cover the join keys.
- **Factors**: Size of input datasets, existence of supporting indexes, and CPU costs for sorting.
- **Cost**: `sort cost + cpu_operator_cost * rows joined`
![[assets/ex_merge_semi_join.svg|256]]
- **Operates on**: [[Structures/Result Set]]
- **Workloads**:
- [[Workloads/IO/DataFile/DataFileRead|IO: DataFileRead]]
- [[Workloads/IO/BufFile/BufFileRead|IO: BufFileRead]]
- [[Workloads/IO/BufFile/BufFileWrite|IO: BufFileWrite]]
- [[Workloads/LWLock/Buffers/BufferContent|LWLock: BufferContent]]