Outer Join Reordering Rules #

A search endpoint joins tickets to five tables with LEFT JOIN, then filters on assignees.team = 'billing'. The plan builds the full left-joined result for 8 million tickets before applying the filter, although only 20,000 tickets belong to that team. Rewriting one LEFT JOIN as JOIN — which returns exactly the same rows because of the filter — lets the planner start from the 20,000 and finishes in a fraction of the time. Sometimes the planner does that rewrite itself; sometimes it cannot.

Join order search is described in join order and collapse limits. Inner joins can be reordered freely within that search. Outer joins cannot, and the rules determine how much room the planner has.

The Planner Condition #

Inner joins commute and associate. (A ⋈ B) ⋈ C equals A ⋈ (B ⋈ C) equals (A ⋈ C) ⋈ B whenever the conditions allow it, so the search considers every order.

Outer joins are constrained. A LEFT JOIN preserves every row of its left side, so moving it can change which rows are null-extended. The planner only applies reorderings proven to be equivalent. The main ones:

Outer join reduction. Before any of that, the planner looks for WHERE conditions that reject null-extended rows. A LEFT JOIN B ON … WHERE B.col = 'x' can never return a row where B is null — the comparison with null is not true — so the left join is reduced to an inner join, which is then freely reorderable. The same applies to strict functions of B’s columns and to B.col IS NOT NULL. It does not apply to B.col IS NULL, COALESCE(B.col, …) = …, or conditions inside OR branches that could be satisfied without B.

PostgreSQL 16 reworked how the planner tracks which expressions can be nulled by which outer joins, removing several cases where earlier versions refused valid reorderings; results on older versions can therefore be more rigid for the same SQL.

Does the WHERE clause reduce the outer join? Starting from a LEFT JOIN with a WHERE clause on the nullable table. If the condition is strict, such as an equality or IS NOT NULL on its columns, the join is reduced to an inner join and can be reordered freely. If the condition tolerates nulls, such as IS NULL or COALESCE, the join stays an outer join with limited reordering. If there is no condition on the nullable side, it stays an outer join. WHERE condition on the LEFT JOIN's nullable table? strict: B.col = 'x', IS NOT NULL reduced to inner join reorderable freely null-tolerant: IS NULL, COALESCE stays outer join limited reordering no condition on B stays outer join limited reordering an explicit INNER JOIN says the same thing and avoids relying on reduction

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE)
SELECT t.id, t.subject
FROM tickets t
LEFT JOIN assignees a ON a.ticket_id = t.id
LEFT JOIN teams tm   ON tm.id = a.team_id
WHERE coalesce(tm.name, '') = 'billing';
Hash Left Join  (actual time=9104.2..14210.8 rows=20412 loops=1)
  Hash Cond: (a.team_id = tm.id)
  Filter: (COALESCE(tm.name, ''::text) = 'billing'::text)
  Rows Removed by Filter: 7980220
  ->  Hash Left Join  (actual rows=8000632 loops=1)
        Hash Cond: (t.id = a.ticket_id)
        ->  Seq Scan on tickets t  (actual rows=8000632 loops=1)
        ->  Hash  ->  Seq Scan on assignees a
  ->  Hash  ->  Seq Scan on teams tm
Execution Time: 14302.4 ms
-- COALESCE tolerates nulls, so the left joins were not reduced
-- the filter runs after joining all 8M tickets

With a strict condition, the same result:

... WHERE tm.name = 'billing';
Nested Loop  (actual time=0.9..84.2 rows=20412 loops=1)
  ->  Nested Loop  (actual rows=20412 loops=1)
        ->  Index Scan using teams_name_idx on teams tm  (actual rows=1 loops=1)
              Index Cond: (name = 'billing'::text)
        ->  Index Scan using assignees_team_id_idx on assignees a  (actual rows=20412 loops=1)
              Index Cond: (team_id = tm.id)
  ->  Index Scan using tickets_pkey on tickets t  (actual rows=1 loops=20412)
        Index Cond: (id = a.ticket_id)
Execution Time: 86.1 ms
-- both LEFT JOINs reduced to inner joins; plan now starts from the selective team
Outer join or reduced inner join? Three plan lines are annotated. Hash Left Join nodes with a Filter above them show outer joins that were not reduced and a filter applied late. A Rows Removed by Filter count close to the table size shows the wasted work. Plain Nested Loop join nodes with the selective table first show the joins were reduced and reordered. Hash Left Join … Filter: (COALESCE(tm.name, '') = …) not reduced: filter after the join Rows Removed by Filter: 7980220 whole table joined first Nested Loop → Index Scan on teams (rows=1) reduced and reordered node names ending in Left Join tell you the outer join survived

Step-by-Step Resolution #

  1. Check the join node names. Left Join, Right Join or Full Join means the outer join was kept; plain Hash Join, Merge Join or Nested Loop means it was an inner join or was reduced.

  2. Look for filters on nullable columns applied above outer joins. A large Rows Removed by Filter above a left join is the target.

  3. Make the condition strict when null rows should be excluded anyway. Replace COALESCE(tm.name, '') = 'billing' with tm.name = 'billing'.

  4. Better, say what you mean. If the query requires a match, write JOIN instead of LEFT JOIN. The result is the same and the planner does not need to prove it.

  5. Keep genuinely optional joins last. Joins whose columns are only displayed and never filtered can stay LEFT JOIN; placing them after the selective inner joins in the SQL costs nothing and helps readability, even though the planner may reorder them where allowed.

  6. Test on the server version you run. PostgreSQL 16 accepts reorderings that 15 rejects; verify plans after upgrades as described in comparing plans before and after upgrades.

Three spellings of the same filter With COALESCE on the team name the outer joins are kept and the query takes 14.3 seconds. With a strict equality the joins are reduced and it takes 86 milliseconds. With explicit inner joins it takes 85 milliseconds. LEFT JOIN + COALESCE filter 14,302 ms LEFT JOIN + strict filter 86 ms explicit JOIN 85 ms same rows returned in all three cases

Before and After #

-- BEFORE: LEFT JOINs with a null-tolerant filter
Hash Left Join  Filter: (COALESCE(tm.name, '') = 'billing')  Rows Removed by Filter: 7980220   Execution Time: 14302.4 ms

-- AFTER: strict filter (or explicit JOIN)
Nested Loop → Index Scan on teams (name = 'billing') → assignees → tickets                    Execution Time: 86.1 ms

Why ORMs produce so many unreduced left joins #

ORM query builders generally emit LEFT OUTER JOIN for any relationship traversed through a nullable foreign key, because they cannot know whether you intend to keep rows without a match. Filters built on those relationships are usually strict, so PostgreSQL reduces most of them silently. The trouble starts with generated null-handling: filters wrapped in COALESCE, case-insensitive lookups on nullable columns that compile to expressions tolerating null, or OR conditions combining a relationship filter with a base-table filter. Each of those blocks reduction. When a search endpoint is slow and its SQL is full of LEFT OUTER JOIN, check each filter on a joined table for null tolerance before looking at indexes. The capture methods are in ORM-generated EXPLAIN output.

A related gain comes from the other direction: once joins are inner joins, join removal no longer applies to them, so a LEFT JOIN whose columns are never used should stay a LEFT JOIN to remain removable.

Common Pitfalls #

COALESCE in filters on joined tables. It keeps outer joins unreduced. Diagnostic signal: Left Join nodes with a filter above them. Fix: strict comparisons or explicit inner joins.

OR mixing nullable and base-table columns. WHERE tm.name = 'billing' OR t.priority = 1 can be true without a match, so no reduction. Diagnostic signal: outer joins preserved with an OR filter. Fix: split into UNION branches.

Filters in ON versus WHERE. A condition on the nullable table in ON keeps unmatched rows; in WHERE it removes them. Diagnostic signal: different row counts after moving a condition. Fix: decide which semantics you need before tuning.

Right joins in hand-written SQL. RIGHT JOIN is rewritten to LEFT JOIN internally but confuses readers. Diagnostic signal: hard-to-review join direction. Fix: write left joins consistently.

Frequently Asked Questions #

Can PostgreSQL reorder LEFT JOINs? #

Partly. Inner joins reorder freely, but outer joins only move where the planner can prove the result is unchanged, such as associating left joins with strict conditions. Queries with many outer joins therefore have fewer legal join orders.

When does a LEFT JOIN become an inner join? #

When a WHERE condition on the nullable table’s columns cannot be true for null-extended rows, for example an equality or IS NOT NULL test. The planner then reduces the outer join to an inner join, which it can reorder freely.

Does COALESCE prevent join optimisation? #

A COALESCE over a nullable joined column in a WHERE clause can be true for rows without a match, so the planner cannot reduce the outer join. The filter then runs after the full outer join is built.

Up: Join Order and Collapse Limits