Fan-Out Join Explosion

Many-to-many join cardinality mistakes multiply rows before GROUP BY or SUM, inflating metrics and hiding true totals.

Recipe

Quick-reference recipe card - copy-paste ready.

-- WRONG: tags fan out line items
SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN order_tags ot ON ot.order_id = o.order_id
GROUP BY o.order_id;
 
-- RIGHT: aggregate facts before optional dimensions
SELECT o.order_id, agg.revenue
FROM orders o
JOIN (
  SELECT order_id, SUM(quantity * unit_price) AS revenue
  FROM order_items
  GROUP BY order_id
) agg ON agg.order_id = o.order_id
LEFT JOIN order_tags ot ON ot.order_id = o.order_id;

When to reach for this:

• Dashboard totals disagree with payment provider or invoice table.
• EXPLAIN row counts explode after adding a new join.
• ORM eager-loads multiple has_many associations in one query.

Working Example

BEGIN;
 
CREATE TABLE orders (order_id bigint PRIMARY KEY);
CREATE TABLE order_items (
  order_id    bigint REFERENCES orders (order_id),
  product_id  bigint,
  quantity    integer,
  unit_price  numeric(12,2),
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE order_tags (
  order_id  bigint REFERENCES orders (order_id),
  tag       text,
  PRIMARY KEY (order_id, tag)
);
 
INSERT INTO orders VALUES (1);
INSERT INTO order_items VALUES (1, 10, 2, 50.00), (1, 20, 1, 30.00);
INSERT INTO order_tags VALUES (1, 'gift'), (1, 'rush'), (1, 'vip');
 
-- True revenue: 2*50 + 1*30 = 130
SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = 1;
 
-- Fan-out wrong answer: 130 * 3 tags = 390
SELECT SUM(oi.quantity * oi.unit_price)
FROM order_items oi
JOIN order_tags ot ON ot.order_id = oi.order_id
WHERE oi.order_id = 1;
 
COMMIT;

What this demonstrates:

• Three tags multiply two line items into six joined rows.
• SUM without GROUP BY on inflated join over-counts by tag cardinality.
• Subquery aggregation preserves correct fact grain.

Deep Dive

How It Works

• Join multiplies rows when both sides have multiple matches per join key (M:N or dual 1:N).
• GROUP BY after fan-out can still be wrong if non-aggregated columns come from fan-out side incorrectly.
• COUNT(*) after fan-out counts joined rows, not distinct business entities.
• ORMs generating LEFT JOIN chains are a common source of silent inflation.

Cardinality Diagnostic

SELECT
  (SELECT COUNT(*) FROM order_items WHERE order_id = 1) AS items,
  (SELECT COUNT(*) FROM order_tags WHERE order_id = 1) AS tags,
  (SELECT COUNT(*) FROM order_items oi JOIN order_tags ot ON ot.order_id = oi.order_id WHERE oi.order_id = 1) AS joined_rows;

If joined_rows = items * tags, you have fan-out.

Safe Patterns

Gotchas

• Chained 1:N joins - orders -> items -> shipments multiplies items by shipments per order. Fix: aggregate at lowest fact grain first.
• DISTINCT as band-aid - SELECT DISTINCT hides duplicates but breaks sums. Fix: fix grain, not DISTINCT on wrong query.
• Average on fan-out - AVG(rating) weighted wrong when reviews duplicate per line item. Fix: subquery per entity.
• BI tool auto-joins - semantic layer creates M:N silently. Fix: define fact table grain in semantic model.
• Missing junction table - M:N modeled as duplicate FKs. Fix: normalize schema; see Entity-Relationship Modeling.

Alternatives

FAQs

Related

• Entity-Relationship Modeling - M:N junction tables
• Defect Scenarios Basics - incident patterns
• EXPLAIN Basics - reading row counts
• Normalization Basics - decomposition

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

pbqrthvqrf.vb|ptvb1018|202607