Join Removal for Unused LEFT JOINs #

A reporting view joins orders to eight lookup tables with LEFT JOIN so every consumer can pick the columns it needs. A dashboard selects only three columns from orders through the view. One copy of the database plans it as a single index scan; another, identical except for a missing unique constraint on one lookup table, joins all eight tables for every row. The difference is join removal.

The planner can only reorder what remains in the query, as described in join order and collapse limits. Join removal happens earlier: it deletes joins that cannot affect the result.

The Planner Condition #

A LEFT JOIN to relation B can be removed when both of these hold:

  1. No column of B is used anywhere above the join — not in the SELECT list, WHERE, GROUP BY, ORDER BY, or other join conditions. The join condition itself does not count.
  2. The join cannot duplicate rows of the left side. The planner must prove each left row matches at most one row of B. It does this with a unique index or constraint on B’s join columns (primary key, UNIQUE, or a unique index whose columns are all equated by the join condition), or with B being a subquery that is unique by DISTINCT or GROUP BY on those columns.

Under those conditions the left join returns exactly one row per left row, with B’s columns unused, so dropping it cannot change the result.

What is not removed:

Removed joins leave no trace in EXPLAIN: the relation simply does not appear.

Can this join be removed? For a LEFT JOIN to table B, the planner checks two conditions. If any column of B is referenced above the join, the join stays. If no column is referenced but the join key is not provably unique, the join stays because it could duplicate rows. If no column is referenced and a unique index covers the join key, the join is removed and B disappears from the plan. LEFT JOIN b — is it removable? b's columns referenced join kept used by the query unused, key not unique join kept could duplicate rows unused, unique index on key join removed b absent from plan inner joins are never removed this way, even with foreign keys

Annotated EXPLAIN Evidence #

CREATE VIEW order_report AS
SELECT o.*, c.name AS customer_name, s.label AS status_label, cur.symbol AS currency_symbol
FROM orders o
LEFT JOIN customers  c   ON c.id   = o.customer_id
LEFT JOIN statuses   s   ON s.code = o.status_code
LEFT JOIN currencies cur ON cur.iso = o.currency_iso;

EXPLAIN (ANALYZE)
SELECT id, total, placed_at FROM order_report WHERE placed_at >= now() - interval '1 day';

When statuses.code has no unique constraint:

Hash Left Join  (actual time=0.9..612.4 rows=184002 loops=1)
  Hash Cond: (o.status_code = s.code)
  ->  Index Scan using orders_placed_at_idx on orders o  (actual rows=184002 loops=1)
  ->  Hash  (actual rows=14 loops=1)
        ->  Seq Scan on statuses s  (actual rows=14 loops=1)
Execution Time: 640.2 ms
-- customers and currencies were removed (unique keys, unused columns)
-- statuses stays: code is not provably unique, so the join might duplicate orders

After adding the constraint:

ALTER TABLE statuses ADD CONSTRAINT statuses_code_key UNIQUE (code);
Index Scan using orders_placed_at_idx on orders o  (actual time=0.02..402.1 rows=184002 loops=1)
  Index Cond: (placed_at >= (now() - '1 day'::interval))
Execution Time: 418.6 ms
-- all three lookup joins removed: no join nodes at all
Reading what was removed Three items are annotated. The absence of customers and currencies from the plan shows those joins were removed. The Hash Left Join on statuses shows a join kept because its key was not unique. After adding a unique constraint the plan is a single index scan with no join nodes. (customers, currencies absent) removed: unique key, no columns used Hash Left Join … Hash Cond: (o.status_code = s.code) kept: code not provably unique Index Scan on orders only all lookups removed after UNIQUE a removed join leaves nothing in EXPLAIN — compare with the view definition

Step-by-Step Resolution #

  1. Compare the relations in the plan with those in the query or view. Relations missing from the plan were removed; those present with unused columns were not.

  2. For each retained LEFT JOIN with unused columns, check uniqueness of the join key:

    SELECT i.indexrelid::regclass, i.indisunique, i.indkey
FROM pg_index i WHERE i.indrelid = 'statuses'::regclass;

  3. Add the missing unique constraint where the data really is unique. Build the index concurrently first on large tables, then attach it:

    CREATE UNIQUE INDEX CONCURRENTLY statuses_code_uidx ON statuses (code);
ALTER TABLE statuses ADD CONSTRAINT statuses_code_key UNIQUE USING INDEX statuses_code_uidx;

  4. Stop referencing columns you do not need. SELECT * through a view references every column and prevents all removal; select explicit columns.

  5. Keep optional lookups as LEFT JOIN in views. Converting them to inner joins makes them non-removable and also filters rows without matches.

  6. Verify with EXPLAIN for the narrow query shapes consumers actually run.

Joins actually executed through the view Selecting star from the view executes all three lookup joins. Selecting three order columns before the unique constraint executes one join, on statuses. After adding the constraint, the same narrow query executes no joins. SELECT * FROM order_report 3 joins 3 cols, code not unique 1 join 3 cols, UNIQUE (code) 0 joins removal needs both unused columns and a provably unique join key

Before and After #

-- BEFORE: statuses.code without a unique constraint
Hash Left Join (statuses) → Index Scan on orders                 Execution Time: 640.2 ms

-- AFTER: UNIQUE (code) on statuses
Index Scan using orders_placed_at_idx on orders                  Execution Time: 418.6 ms

Designing views that stay cheap #

Join removal is what makes wide “everything” views practical. A view can offer dozens of optional lookup columns while each consumer pays only for the joins it uses — provided every optional relation is left-joined on a unique key. That design rule is worth enforcing in schema reviews: every lookup table referenced by a reporting view should have a primary key or unique constraint on the join column, and the view should use LEFT JOIN for anything a consumer might not need.

Two limits apply. First, removal is decided per query, so an ORM that always selects every column of a model mapped onto such a view defeats it; map narrower models or use explicit column lists for hot paths. Second, removal only considers the join’s uniqueness, not whether the joined table is empty or small, so a cheap-looking join to a tiny table is still worth removing in a hot query that runs thousands of times per second. Joins retained because a filter references the lookup table are candidates for outer join reduction instead.

Common Pitfalls #

Assuming foreign keys enable removal. PostgreSQL does not remove inner joins using foreign keys. Diagnostic signal: an inner join with unused columns still in the plan. Fix: left join on a unique key if the lookup is optional, or drop the join from the query.

SELECT * through views. Every column is referenced. Diagnostic signal: all joins retained for a narrow consumer. Fix: explicit column lists.

Uniqueness enforced only by the application. The planner cannot trust it. Diagnostic signal: retained joins on natural keys. Fix: add a unique constraint.

Partial unique indexes. A unique index with a WHERE clause does not prove uniqueness for all rows. Diagnostic signal: join retained despite a unique partial index. Fix: a full unique constraint where valid.

Frequently Asked Questions #

When does PostgreSQL remove a LEFT JOIN? #

When no column of the joined table is used by the query and the join key is provably unique on that table, usually through a primary key or unique constraint. The join then cannot change the result and is dropped from the plan.

Does PostgreSQL remove inner joins on foreign keys? #

No. Inner joins also filter out rows without a match, and PostgreSQL does not use foreign key constraints to prove every row has one, so inner joins stay in the plan even when their columns are unused.

How can I tell whether a join was removed? #

Removed relations simply do not appear in EXPLAIN output. Compare the relations listed in the plan with those in the query or view definition.

Up: Join Order and Collapse Limits