Right, so you’ve written your beautiful JOIN clause. You feel good. You’re a SQL-wielding hero. But have you ever stopped to wonder what unholy incantations the database mutters under its breath to actually execute that thing? It doesn’t just magically combine data. It has a whole bag of tricks, officially called join strategies, and the planner picks one based on a cold, hard calculation of expected cost. Let’s pull back the curtain.

The planner’s job is to turn your declarative “what” into a procedural “how” with the lowest estimated cost (a unit combining I/O, CPU, and memory). It stares at your tables, glares at your WHERE clause, checks the indexes, and makes some educated guesses. Get this wrong, and a query on a million rows can feel like it’s running on a potato powered by a lemon.

The Join Strategy Arsenal

The planner has three main weapons in its join strategy arsenal, and it will mix and match them for each pair of tables in your query.

Nested Loop Join: The naive one. It’s the database equivalent of a nested for loop. For each row in the outer table (the “driving” table), it scans the entire inner table to find matches. The complexity is O(n*m), which is a fancy way of saying “brutally slow for large tables.” So why does the planner ever choose it? Because it’s brilliant for small tables, or when you’re joining a huge table to a tiny one and there’s an index on the join key of the huge table. In that case, for each of the 10 rows in the tiny table, it does a super-fast index lookup into the billion-row table. Problem solved.

-- The planner will likely pick a Nested Loop here if `small_table` is tiny
-- and `huge_table.id` is indexed.
EXPLAIN ANALYZE
SELECT *
FROM small_table
INNER JOIN huge_table ON small_table.id = huge_table.id;

Hash Join: This is where things get interesting. The planner builds an in-memory hash table from the smaller of the two tables, using the join key as the hash key. Then it scans the larger table, hashing each row’s join key and probing the hash table for a match. The complexity is more like O(n + m), which is vastly superior for large tables. The catch? It needs enough work_mem to fit the hash table. Give it less, and it has to spill to disk, turning your speedy hash join into a sluggish nightmare.

-- A classic case for a Hash Join. Two decently-sized tables, no useful index
-- on the join key for a nested loop, and an equality condition (`=`).
EXPLAIN ANALYZE
SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

Merge Join: The precision instrument. This requires both inputs to be sorted on the join key. It then zips through both sorted inputs simultaneously, like a zipper, finding matches in a single pass. It’s incredibly efficient, O(n + m), and doesn’t require a hash table in memory. The massive caveat? The data must be pre-sorted. This happens naturally if you have an index on the join key for both tables, or if the planner can use an index to sort one side for free. If it has to sort the data itself just to do the join, the cost can be prohibitive.

-- A good candidate for a Merge Join if both `id` columns are indexed (and thus sorted).
EXPLAIN ANALYZE
SELECT *
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.id;

How the Planner Chooses Its Weapon

The planner isn’t guessing. It’s running the numbers. Here’s its thought process:

  1. Cost Estimation: It uses statistics (ANALYZE) to estimate the size (number of rows and pages) of each table and the selectivity of your WHERE clauses. Wrong statistics lead to catastrophically wrong plans. If you think a table has 1000 rows but it actually has 100 million, the planner will choose a Nested Loop plan fit for a kitten and unleash a tiger on your server.

  2. Available Indexes: This is huge. An index on the join key makes a Nested Loop viable for larger outer tables. A sorted index enables a Merge Join. No indexes often force a Hash Join.

  3. Join Type and Condition: An INNER JOIN with an equality condition (=) opens up all three strategies. A LEFT JOIN or a non-equi join (e.g., ON a.date >= b.date) often rules out Merge Join and sometimes Hash Join, pushing the planner toward a less efficient Nested Loop.

  4. Memory (work_mem): This is the lever you can pull. The planner will only choose a Hash Join if it estimates the hash table will fit in work_mem. If you see “Hash” in your EXPLAIN plan but also “Disk: Buffers” or “Loops,” you’ve got a spill. Crank up work_mem for that session (or globally if you’re brave and have RAM to burn).

The LATERAL Wildcard

LATERAL is a fun curveball. It lets the right-hand side of the join “see” columns from the left-hand side. The planner often treats this as a correlated subquery, meaning for each row from the left table, it executes the query on the right. This almost always forces a Nested Loop plan. It can be perfectly efficient if the right-side query is indexed and fast per-row, but it’s a disaster waiting to happen if the right-side query is a heavy table scan.

-- This will likely result in a Nested Loop. For each user, it gets their 3 most recent orders.
-- If `orders.user_id` and `orders.created_at` are indexed, it's fast. If not, pray.
EXPLAIN ANALYZE
SELECT u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
    SELECT *
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) AS recent_orders;

The takeaway? Trust the planner more often than not, but always verify with EXPLAIN ANALYZE. Your job is to give it the right tools: accurate statistics, appropriate indexes, and enough memory to do its job without panicking and writing temp files all over your disk.