### Hash ![[assets/ex_hash.png|256]] ### The Explain Trace ```sql -- Building a hash table for a Join EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) SELECT * FROM animals a JOIN species s ON a.species_id = s.id; ``` ```text -> Hash (cost=2.05..2.05 rows=5 width=15) (actual time=1.341..1.341 rows=5 loops=1) Output: s.id, s.name, s.diet_type Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared read=1 -> Seq Scan on public.species s (...) ``` --- - **Description**: Builds an in-memory hash table from the inner relation of a join. - **Performance**: High performance for large equality joins; memory-intensive as it stores the entire inner relation in `work_mem`. - **Factors**: Size of the inner relation, available `work_mem`, and hash collision rates. - **Cost**: `cpu_operator_cost * number of tuples` ![[assets/ex_hash.svg|256]] - **Operates on**: [[Structures/Result Set]] - **Workloads**: - [[Workloads/IO/BufFile/BufFileRead|IO: BufFileRead]] - [[Workloads/IO/BufFile/BufFileWrite|IO: BufFileWrite]] - [[Workloads/LWLock/Parallel/ParallelHashJoin|LWLock: ParallelHashJoin]] - [[Workloads/IPC/Hash/HashBuildAllocate|IPC: HashBuildAllocate]] - [[Workloads/IPC/Hash/HashBuildElect|IPC: HashBuildElect]] - [[Workloads/IPC/Hash/HashGrowBatchesElect|IPC: HashGrowBatchesElect]] - [[Workloads/IPC/Hash/HashGrowBucketsElect|IPC: HashGrowBucketsElect]]