> [!NOTE] Nested Loop > <table> > <tr> > <td width="25%"><img src="assets/ex_nestedloop.png"></td> > <td>The most basic join algorithm. For every row processed in the 'outer' relation, the engine performs a lookup in the 'inner' relation. Extremely efficient when the outer set is small and the inner relation has a supporting index to avoid repeated full scans.</td> > </tr> > </table> > > ```sql > -- Joining with a highly selective filter on one side > EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) > SELECT * FROM animals a > JOIN species s ON a.species_id = s.id > WHERE s.id = 1; > ``` > > ```text > Nested Loop (cost=51.29..290.35 rows=4000 width=42) (actual time=1.091..1.757 rows=4000 loops=1) > Output: a.id, a.name, a.species_id, a.created_at, s.id, s.name, s.diet_type > Buffers: shared hit=154 > -> Seq Scan on public.species s (...) > -> Bitmap Heap Scan on public.animals a (...) > ``` > > <table> > <tr> > <td rowspan="5" width="25%"><img src="assets/ex_nested_loop_semi_join.svg"></td> > <td><b>Performance</b></td><td>High performance when the inner relation has a supporting index and the outer relation is small.</td> > </tr> > <tr><td><b>Factors</b></td><td>Size of the outer dataset and the efficiency of the inner lookup.</td></tr> > <tr><td><b>Cost</b></td><td><code>outer cost + inner cost * rows in outer</code></td></tr> > <tr><td><b>Operates on</b></td><td><a href="Structures/Result Set">Result Set</a></td></tr> > <tr><td><b>Workloads</b></td><td><a href="Workloads/IO/DataFile/DataFileRead">IO: DataFileRead</a>, <a href="Workloads/LWLock/Buffers/BufferContent">LWLock: BufferContent</a>, <a href="Workloads/LWLock/Buffers/BufferMapping">LWLock: BufferMapping</a></td></tr> > <tr><td colspan="3"><b>Description</b>: Joins tables using a nested loop algorithm.</td></tr> > </table>