A join strategy connects two input streams; it is not a permanent property of the SQL text. Restricting one side to three customers can make indexed repeated lookups attractive, while joining every purchase may favor scanning once and building a hash table. PostgreSQL chooses among legal alternatives using estimated work, available access paths, ordering, and memory expectations. Table size matters, but the size after filtering matters more than a table’s name or reputation.

Repeated lookups and a reusable lookup table

A nested loop takes an outer row and executes an inner path using that row. The inner path need not scan the entire table: it can be an index or bitmap lookup. A small outer population and selective inner access can make repeated work economical. Read the inner node’s loops alongside its per-loop rows to see the total amount requested.

A hash join builds a temporary hash table from one input and probes it with the other. This execution structure is unrelated to a persistent index created with USING hash; creating such an index is not a prerequisite. The build side is the subtree below the plan’s Hash node. Inspect it instead of assuming that SQL’s left or right spelling, or the smaller base table, always determines the build.

Merge joins provide another alternative when suitable ordered inputs are available or worth sorting. Consequently, comparing nested loops with hashes does not exhaust the planner’s choices. Different predicates, output requirements, and legal join conditions can change which alternatives make sense.

When it breaks

An underestimated outer population can turn an inexpensive inner lookup into excessive repeated work. A hash table can also exceed its memory budget and use multiple batches with temporary I/O; it is not guaranteed to remain entirely in memory. Check batch counts and temporary-buffer activity instead of assuming the word Hash promises linear, constant-cost performance.

Large numbers of duplicate join keys can produce a huge result regardless of the lookup strategy. No algorithm avoids producing the rows that the query actually requests. Fixing an accidental many-to-many join requires correcting the relationship or query semantics, not simply selecting a different node.

Plain PostgreSQL does not interpret optimizer-hint comments such as /*+ HashJoin */. Extensions can add hint behavior, but that is a separate environment contract. Planner switches can support controlled comparisons; keeping a globally disabled join method after one small experiment can damage unrelated workloads.

Check it quickly

Run this transaction in PostgreSQL 18 with psql. It creates five hundred customers and fifty thousand purchases, one hundred purchases per customer, with a B-tree index on the reference column. Both statements compute sums over the same relationship, with different filtering.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE customers (id integer PRIMARY KEY);
CREATE TEMP TABLE purchases (id integer PRIMARY KEY,customer_id integer,amount integer);
INSERT INTO customers SELECT g FROM generate_series(1,500) AS g;
INSERT INTO purchases SELECT g,1+(g-1)%500,10 FROM generate_series(1,50000) AS g;
CREATE INDEX purchases_customer ON purchases(customer_id);
ANALYZE customers;
ANALYZE purchases;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id WHERE c.id<=3;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id;
DO $$ BEGIN
  IF (SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id WHERE c.id<=3)
     IS DISTINCT FROM 3000::bigint THEN RAISE EXCEPTION 'wrong selective join'; END IF;
  IF (SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id)
     IS DISTINCT FROM 500000::bigint THEN RAISE EXCEPTION 'wrong broad join'; END IF;
END $$;
ROLLBACK;

On PostgreSQL 18.6, filtering customers to identifiers at most three produced a nested loop. Its inner bitmap heap scan returned one hundred purchases per loop and ran three times. The join emitted three hundred rows, yielding a sum of 3,000. Seeing a bitmap scan inside a nested loop is expected: the node describes how the inner rows are obtained, while the join describes repeated combination.

Without the customer filter, the plan used a hash join. It built from five hundred customers and scanned fifty thousand purchases, yielding 500,000. The measured hash used one batch; that observation applies to this fixture, not all hash joins. The SQL assertions verify both sums, so a different plan must still return the same answers.

Compare equivalent work before choosing

These two statements request different result populations. Their elapsed times cannot establish that one algorithm is universally faster. To compare strategies for one workload, keep its parameters, output, dataset, and concurrency constant, then examine total loops, rows, buffers, memory, and latency. Refresh stale statistics when the divergence supports that diagnosis, and inspect the useful inner access path when repeated work dominates. A correct response may be a targeted index or a better estimate; it need not be a permanent preference for either join algorithm.

Sources