### Nested Loop ![[assets/ex_nestedloop.png|256]] ### The Explain Trace ```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 (...) ``` --- - **Description**: Joins tables using a nested loop algorithm. - **Performance**: High performance when the inner relation has a supporting index and the outer relation is small. - **Factors**: Size of the outer dataset and the efficiency of the inner lookup. - **Cost**: `outer cost + inner cost * rows in outer` ![[assets/ex_nested_loop_semi_join.svg|256]] - **Operates on**: [[Structures/Result Set]] - **Workloads**: - [[Workloads/IO/DataFile/DataFileRead|IO: DataFileRead]] - [[Workloads/LWLock/Buffers/BufferContent|LWLock: BufferContent]] - [[Workloads/LWLock/Buffers/BufferMapping|LWLock: BufferMapping]]