Parameter · Query Tuning / Planner Method Configuration
enable_hashjoin
Enables the planner's use of hash join plans.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
A hash join reads the smaller table into an in-memory hash table, then streams the bigger table past it, looking up each row. It is the usual choice for joining large unsorted sets. Turning it off makes the planner try merge joins or nested loops instead.
For DBAs and operators
The inner (build) side goes into a hash table sized by work_mem × hash_mem_multiplier. If it does not fit, the join splits both inputs into batches written to temp files: EXPLAIN ANALYZE shows Buckets: 262144 Batches: 8 Memory Usage: 6446kB (a real line from a 1-million-row join with default settings). More batches means more temp I/O.
Measured on 18.6, joining 200,000 and 100,000 rows: Hash Join cost 8,814. With hash joins off: Merge Join (Index Scan + Sort) cost 19,326. With merge joins off as well: Nested Loop with an inner index scan, cost 56,587. With all three off the planner still used the Nested Loop and printed Disabled: true: PostgreSQL 18 counts disabled nodes rather than adding a fixed penalty cost.
Hash joins only work for equality conditions. They are bad when the build side is badly under-estimated (many batches) or when the outer side is tiny and an index nested loop would touch only a few rows. Turning the switch off for one session is a classic way to check whether an estimate is the real problem.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
enable_mergejoin enable_nestloop enable_parallel_hash hash_mem_multiplier work_mem