ES010: Cartesian product

• Signal: a Nested Loop with no join filter that pairs each outer row with all inner rows. Its output must be at least 90% of outer rows × inner rows, and at least 1,000 rows.
• Evidence: outer rows, inner rows per loop, rows produced.
• Action: check the query for a missing join condition between the relations on either side.
• Stays silent when: either side returns a single row (as in a deliberate cross join with a one-row subquery), the inner side refers to the outer side (a parameterized scan), or the join is a semi or anti join.

Example

The nested_loop_cartesian scenario of the test corpus, captured on PostgreSQL 16: missing join condition: every selected customer is paired with every book.

SELECT c.id, p.id FROM customers c, products p WHERE c.id <= 100 AND p.category = 'books';

Nested Loop  (cost=0.29..1355.79 rows=100000 width=8) (actual time=0.023..12.047 rows=100000 loops=1)
  Output: c.id, p.id
  Buffers: shared hit=40
  ->  Seq Scan on public.products p  (cost=0.00..99.50 rows=1000 width=4) (actual time=0.006..0.511 rows=1000 loops=1)
        Output: p.id, p.name, p.category, p.price
        Filter: (p.category = 'books'::text)
        Rows Removed by Filter: 4000
        Buffers: shared hit=37
  ->  Materialize  (cost=0.29..6.54 rows=100 width=4) (actual time=0.000..0.004 rows=100 loops=1000)
        Output: c.id
        Buffers: shared hit=3
        ->  Index Only Scan using customers_pkey on public.customers c  (cost=0.29..6.04 rows=100 width=4) (actual time=0.012..0.019 rows=100 loops=1)
              Output: c.id
              Index Cond: (c.id <= 100)
              Heap Fetches: 0
              Buffers: shared hit=3
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=126
Planning Time: 0.426 ms
Execution Time: 15.101 ms

explainsql reports:

HIGH  ES010 Cartesian product
Nested Loop pairs each of 1,000 outer rows with all 100 inner rows, with no condition between them.
Outer rows: 1,000
Inner rows per loop: 100
Rows produced: 100,000
→ Check the query for a missing join condition between products and customers.

All rules