Cartesian Products and Missing Join Clauses #

A reporting query over invoices, invoice_lines, products and warehouses returns the right totals — eventually — but a Nested Loop near the bottom shows rows=4000000000. Somebody joined warehouses with a condition on products.warehouse_id, but products was aliased twice and the condition referenced the wrong alias. Every warehouse was paired with every line, and a later DISTINCT hid the duplication.

The planner searches join orders over the join graph, as described in join order and collapse limits. When that graph has a gap — two relations with no condition connecting them, directly or indirectly — the only way to combine them is a Cartesian product.

The Planner Condition #

The planner builds a join graph: relations are nodes, join conditions are edges. During the search it prefers to join relations connected by an edge, and only considers clauseless joins — pairs with no connecting condition — when no connected option remains for a subset. A clauseless join has no hash, merge or index condition, so it can only be executed as a nested loop producing rows(A) × rows(B), usually with a Materialize over the inner side.

Two mechanisms add edges you did not write:

Equivalence classes only form from equality with mergeable operators. Conditions such as a.x < b.x, a.id = b.id + 1 or lower(a.code) = b.code connect relations but do not create transitive edges, and some cannot be used as join conditions for hash or merge joins at all.

Accidental Cartesian products usually come from: a missing ON clause for one table in a comma-separated FROM; a condition referencing the wrong alias; a join through a table that was later removed from the query; or ORMs generating CROSS JOIN for relationships whose conditions end up in WHERE clauses on other branches.

A gap in the join graph Four relations in a chain. invoices connects to invoice_lines by invoice_id, and invoice_lines connects to products by product_id. warehouses has no condition connecting it to any of them because its condition references the wrong alias. Joining warehouses therefore requires pairing it with every row of the rest. invoices invoice_id invoice_lines product_id products warehouse_id unused warehouses no edge: Cartesian every relation needs a path of join conditions to every other

Annotated EXPLAIN Evidence #

A comma-joined FROM list where one relation lost its condition during an edit:

EXPLAIN (ANALYZE)
SELECT i.id, w.region
FROM invoices i, invoice_lines l, warehouses w
WHERE l.invoice_id = i.id
  AND i.issued_on >= '2026-09-01';            -- no condition for w at all
Nested Loop  (actual time=0.9..184102.4 rows=402108000 loops=1)
  ->  Hash Join  (actual rows=2010540 loops=1)
        Hash Cond: (l.invoice_id = i.id)
        ->  Seq Scan on invoice_lines l
        ->  Hash  ->  Index Scan using invoices_issued_on_idx on invoices i
  ->  Materialize  (actual time=0.00..0.02 rows=200 loops=2010540)
        ->  Seq Scan on warehouses w  (actual rows=200 loops=1)
Execution Time: 191204.8 ms
-- Nested Loop with no Join Filter and no inner Index Cond: a clauseless join
-- rows = 2,010,540 × 200 = 402,108,000
Recognising a Cartesian product Three plan lines are annotated. A Nested Loop with no Join Filter, Hash Cond or inner Index Cond. A Materialize node over the inner scan with loops equal to the outer rows. An output row count exactly equal to the product of the two inputs. Nested Loop (no Join Filter, no Index Cond) nothing connects the inputs Materialize (rows=200 loops=2010540) inner side replayed per outer row rows=402108000 = 2010540 × 200 output is the full product output rows equal to input rows multiplied is the tell

Step-by-Step Resolution #

  1. Find nested loops without any condition — no Join Filter, no Hash Cond, no Merge Cond, and no inner Index Cond referencing the other side.

  2. Check the arithmetic. Output rows equal to the product of the two inputs’ rows confirms a Cartesian product.

  3. Draw the join graph from the SQL. List every relation and every condition referencing two relations; any relation not reachable from the others is the gap.

  4. Add or correct the missing condition. Convert comma joins to explicit JOIN … ON, so every relation’s condition sits next to it:

    SELECT i.id, w.region
FROM invoices i
JOIN invoice_lines l ON l.invoice_id = i.id
JOIN products p      ON p.id = l.product_id
JOIN warehouses w    ON w.id = p.warehouse_id
WHERE i.issued_on >= '2026-09-01';

  5. Remove DISTINCT that was masking duplication, and compare row counts before and after; a DISTINCT added “to fix duplicates” is frequently covering a missing join condition.

  6. Allow intended cross joins explicitly. When a product is intended — a calendar table crossed with a small list of categories — write CROSS JOIN so reviewers can see it was deliberate.

What the missing condition cost Without the warehouse condition the join produces 402 million rows before DISTINCT and takes 191 seconds. With the condition added it produces 2 million rows and takes 1.9 seconds. missing condition — rows 402M rows, 191 s condition added — rows 2.0M rows, 1.9 s a DISTINCT above the join had hidden the duplicated rows

Before and After #

-- BEFORE: warehouses in FROM with no join condition
Nested Loop (rows=402108000) → Hash Join + Materialize (warehouses)     Execution Time: 191204.8 ms

-- AFTER: JOIN warehouses w ON w.id = p.warehouse_id
Hash Join  Hash Cond: (p.warehouse_id = w.id) (rows=2010540)              Execution Time: 1904.6 ms

Deliberate small cross joins #

Not every clauseless join is a mistake. Crossing a date series with a short list of categories to produce a report skeleton, or joining a one-row settings table to every row, are legitimate products and cost little when one side is tiny. The planner handles them well: it postpones clauseless joins until connected joins are done, so the product usually happens late and over the smallest possible inputs. What deserves attention is a clauseless join between two large inputs, or one that appears in the middle of a join chain and multiplies rows that later joins must process. EXPLAIN makes the size visible immediately; the habit worth building is multiplying the two input row counts of any unconditioned nested loop before accepting it.

Equivalence classes can also remove products you might expect. In FROM a, b, c WHERE a.x = b.x AND b.x = c.x, there is no written condition between a and c, yet the planner may join them first using the derived a.x = c.x — a plan that looks like a Cartesian product to a reader but is not one. Check for a Hash Cond or Index Cond before concluding a join is clauseless.

Common Pitfalls #

Comma joins with a forgotten condition. Easy to miss in long WHERE clauses. Diagnostic signal: an unconditioned nested loop with a materialized inner side. Fix: explicit JOIN … ON syntax.

DISTINCT covering duplication. Results look correct but work is multiplied. Diagnostic signal: Unique or HashAggregate removing most rows above a join. Fix: find the missing condition.

Non-equality conditions only. a.ts BETWEEN b.start AND b.end connects relations but cannot hash or merge. Diagnostic signal: Join Filter with huge Rows Removed. Fix: see join filter in nested loops without an index.

Wrong alias after copy-paste. A condition referencing an alias of the same table joins the wrong pair. Diagnostic signal: a self-join where none was intended. Fix: review aliases for every ON clause.

Frequently Asked Questions #

How do I recognise a Cartesian product in EXPLAIN? #

Look for a Nested Loop with no Join Filter, no hash or merge condition, and no inner Index Cond referencing the other input, typically with a Materialize node on the inner side. Its output row count equals the product of its inputs’ row counts.

What are equivalence classes in the PostgreSQL planner? #

Sets of expressions known to be equal because of equality conditions in the query. From a.x = b.x and b.x = c.x the planner derives a.x = c.x, which lets it join a and c directly and push constants into every member’s scan.

Does PostgreSQL avoid Cartesian products automatically? #

It avoids clauseless joins whenever a connected join is possible and postpones them to the end of the search. If the query genuinely has no condition connecting two relations, a product is the only correct plan.

Up: Join Order and Collapse Limits