Hash Right and Full Joins for Outer Joins #

You wrote customers LEFT JOIN orders, and the plan says Hash Right Join. It is not a bug and the results are correct: the planner has flipped the join’s sides so that it can hash the smaller relation. But the flip changes which side sits in memory, which side is scanned in full, and what happens when the hash table spills — so it is worth knowing how to read.

The basic build-and-probe mechanism is covered in hash join mechanics. Outer joins add one requirement: rows from the preserved side must appear even when nothing matches.

The Algorithmic Condition #

In a hash join, the inner input is hashed and the outer input probes it. For an inner join the choice of sides is free. For outer joins it is constrained by which side must be preserved:

So customers LEFT JOIN orders can be executed either as Hash Left Join (probe with customers, hash orders) or as Hash Right Join with the inputs swapped (probe with orders, hash customers). When customers is far smaller than orders, hashing customers is the cheaper option, and the plan shows the “right” variant of the join you wrote as “left”.

Anti-joins follow the same idea: Hash Anti Join hashes the other side and emits unmatched probe rows, while Hash Right Anti Join (PostgreSQL 16+) hashes the preserved side — the pattern in semi-join and anti-join plan shapes.

Full joins have a restriction worth knowing: they need a hashable or mergeable equality condition covering the join. A FULL JOIN whose condition includes non-equality predicates may have no executable plan at all, and the planner reports that the join is only supported with merge-joinable or hash-joinable conditions.

Four hash join shapes for outer joins A table of join node types. Hash Left Join hashes the nullable side and preserves the probe side, emitting unmatched rows during probing. Hash Right Join hashes the preserved side and emits unmatched hash entries at the end. Hash Full Join preserves both. Hash Right Anti Join hashes the preserved side and emits only unmatched entries at the end. hashed side preserved side unmatched rows emitted Hash Left Join nullable probe side while probing Hash Right Join preserved hashed side after probing ends Hash Full Join either both during and after Hash Right Anti Join preserved hashed side after probing ends Right variants let the smaller preserved table be the one in memory

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.region = 'nordics';
Hash Right Join  (actual time=412.8..8840.2 rows=611204 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (actual rows=94022110 loops=1)       -- probe side: all orders
  ->  Hash  (actual time=412.2..412.2 rows=40210 loops=1)        -- hashed: filtered customers
        Buckets: 65536  Batches: 1  Memory Usage: 4212kB
        ->  Seq Scan on customers c  (actual rows=40210 loops=1)
              Filter: (region = 'nordics'::text)
Execution Time: 8902.1 ms
-- the written LEFT JOIN runs as a RIGHT join: customers (preserved) are in the hash
-- unmatched customers are emitted after every order row has probed

Here the choice is correct for memory — 4 MB instead of hashing 94 million orders — but it forces a full scan of orders, because every order must probe to find out which customers matched. When an index on orders.customer_id exists and the customer set is small, a nested loop left join may be far faster:

Nested Loop Left Join  (actual time=0.06..1210.4 rows=611204 loops=1)
  ->  Seq Scan on customers c  (actual rows=40210 loops=1)
        Filter: (region = 'nordics'::text)
  ->  Index Scan using orders_customer_id_idx on orders o  (actual rows=15 loops=40210)
        Index Cond: (customer_id = c.id)
Execution Time: 1302.7 ms
Reading a flipped outer join Three plan lines are annotated. Hash Right Join means the preserved customers are in the hash table. The probe side scans all 94 million orders. The small hash of 40 thousand customers fits in 4 megabytes, which is why the planner flipped the sides. Hash Right Join preserved side is the hashed one Seq Scan on orders o (rows=94022110) every order probes the hash Hash rows=40210 Memory Usage: 4212kB small preserved side in memory correct results either way; the flip trades memory for a full probe scan

Step-by-Step Resolution #

  1. Map the node to the SQL. Identify which relation is preserved in your query and which input of the hash join it is. Right in the node name means the preserved relation is the hashed input.

  2. Check the probe side’s size. A right-variant join always scans its whole probe input. If that input is huge and the preserved side small, an index on the nullable side’s join key may enable a better nested loop.

    CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);
ANALYZE orders;

  3. Verify the estimate on the preserved side. The planner picks nested loop versus hash from the estimated number of preserved rows; if customers after filtering is misestimated, the choice follows the error.

  4. For FULL JOIN errors, rewrite the join condition as a pure equality and move other predicates into WHERE or into subqueries on each side, or express the full join as a UNION ALL of a left join and an anti-join.

  5. Watch memory under skew on the preserved side, since unmatched-entry tracking keeps the whole table in memory until probing completes; batching follows the rules in diagnosing hash join batch spills.

Same result, three shapes The Hash Right Join with a full scan of orders takes 8.9 seconds. A Hash Left Join hashing all orders would need over 3 gigabytes and takes 31 seconds with batching. A Nested Loop Left Join with an index on orders.customer_id takes 1.3 seconds. Hash Left Join 31 s — 3 GB hash, batched Hash Right Join 8.9 s — full probe scan Nested Loop Left Join 1.3 s — 40k index probes the index on the nullable side's key makes the third option possible

Before and After #

-- BEFORE: no index on orders.customer_id
Hash Right Join  → Seq Scan on orders (94M rows)                       Execution Time: 8902.1 ms

-- AFTER: index on orders(customer_id)
Nested Loop Left Join  → Index Scan using orders_customer_id_idx (loops=40210)   Execution Time: 1302.7 ms

Why the planner does not always pick the nested loop #

With 40,000 preserved customers and 15 orders each, the nested loop performs 40,000 index descents and 611,000 heap fetches. If the region filter matched 4 million customers instead, that becomes 4 million descents and 60 million random heap fetches, and the hash right join over a sequential scan is the better plan. The planner’s choice is correct relative to its estimates; the useful question is whether those estimates match the distribution of values you actually query. Test with the largest realistic preserved set, not only the typical one, before forcing a shape with planner settings — the trade-offs of that are in forcing a nested loop for small inner tables.

Common Pitfalls #

Reading Hash Right Join as a wrong result. The planner may swap sides for any outer join. Diagnostic signal: node name contradicts the written join direction. Fix: map the preserved relation to the hashed input; results are unaffected.

Missing the full probe scan. A right-variant join must read all of its probe input. Diagnostic signal: a huge sequential scan under a join returning a small preserved set. Fix: index the nullable side’s join key.

Adding non-equality conditions to FULL JOIN. They can leave no executable join method. Diagnostic signal: the error about merge-joinable or hash-joinable conditions. Fix: keep only equality in the ON clause.

Filtering the nullable side in WHERE. WHERE o.status = 'paid' removes null-extended rows and silently turns the left join into an inner join — which the planner does recognise, changing the plan shape. Diagnostic signal: Hash Join where you expected an outer join. Fix: put the condition in ON if unmatched customers should remain.

Frequently Asked Questions #

Why does my LEFT JOIN show as Hash Right Join? #

The planner swapped the join’s inputs so that the smaller, preserved relation is hashed. A Hash Right Join keeps unmatched hash entries and emits them after probing, which produces exactly the rows your LEFT JOIN specifies.

Can PostgreSQL execute FULL OUTER JOIN as a hash join? #

Yes, as Hash Full Join, when the join condition is a hashable equality. Full joins with non-equality conditions in the ON clause may have no valid join method and raise an error.

Is a Hash Right Join slower than a Hash Left Join? #

Not inherently. It must scan its entire probe input and track matches in the hash table, but it hashes the smaller preserved side. Whether it is faster depends on the relative sizes and on whether an index enables a nested loop instead.

Up: Hash Join Mechanics