Joins

How PostgreSQL executes joins

When you write a query such as:

you tell PostgreSQL which rows should be joined.

You don't tell PostgreSQL how to find those matching rows.

PostgreSQL's query planner decides how to execute the join.

To inspect that decision, run:

Somewhere in the execution plan, you should see the join strategy PostgreSQL selected.

PostgreSQL primarily uses three algorithms to execute joins:

Nested Loop
Hash Join
Merge Join

The result of the SQL query is the same regardless of which algorithm PostgreSQL chooses. What changes is how PostgreSQL finds the matching rows.

Nested Loop

A nested loop takes a row from one input and looks for matching rows in the other input.

A simplified view looks like this:

For example, consider:

PostgreSQL can retrieve the small number of matching orders and, for each one, find the corresponding customer.

If the execution plan uses this strategy, you will see:

Nested loops can be efficient when PostgreSQL only needs to process a small number of rows, especially when it has an efficient way to find matching rows in the other table.

Hash Join

A hash join works differently.

PostgreSQL reads one of the inputs and builds an in-memory hash table using the join key.

For our join:

PostgreSQL could build a hash table using customer IDs:

It can then read rows from orders and use each customer_id to look for a matching entry in the hash table.

A simplified view looks like this:

If PostgreSQL selects this strategy, the execution plan will contain:

and typically a Hash node showing where the hash table was built.

Hash joins are useful when PostgreSQL expects hashing one input and probing it with the other to be cheaper than repeatedly searching for individual matching rows.

Merge Join

A merge join works with both inputs ordered by the join key.

Imagine the two inputs are ordered like this:

Because both sides are ordered, PostgreSQL can move through them together and match equal values as it goes.

A simplified view looks like this:

PostgreSQL advances through both ordered inputs rather than repeatedly searching one of them from the beginning.

If this strategy is selected, the execution plan contains:

The inputs must be available in the required order. PostgreSQL might get that ordering from an index, or it might need to sort the rows first.

PostgreSQL chooses the strategy

You normally don't choose between nested loops, hash joins, and merge joins yourself.

You write the relationship:

and PostgreSQL's planner considers different ways to execute it.

Its decision depends on factors such as:

how many rows PostgreSQL expects to process
which indexes are available
how selective the query conditions are
whether the rows are already available in a useful order
the estimated cost of the available plans

This is why the same type of SQL join doesn't always produce the same join algorithm.

For example:

describes the logical result you want.

But:

describe different execution strategies PostgreSQL can use to produce that result.

Join type and join algorithm are different things

This distinction is important.

These are join types:

They determine which rows should appear in the result.

These are join algorithms:

They describe how PostgreSQL executes the join.

So an INNER JOIN does not mean PostgreSQL will use one particular algorithm.

PostgreSQL may execute an inner join using a nested loop, hash join, or merge join depending on the plan it considers cheapest.

The same principle applies to other join types, although not every algorithm can be used for every possible join condition.

The important point is:

You describe the rows you want. PostgreSQL decides how to find them efficiently.

Nested Loop

Hash Join

Merge Join

PostgreSQL chooses the strategy

Join type and join algorithm are different things

Output