JOIN FETCH can be slower than N+1: a reproducible Doctrine benchmark (+ 1-row-per-entity JSON aggregation)

Source: Dev.to

The real problem: multiple OneToMany JOINs explode rows

JOINing multiple OneToMany relations multiplies the SQL result set (cartesian product).

Example

• 3 images × 5 reviews = 15 rows per product
• for 2000 products → ~30,000 rows transferred from DB and processed

Doctrine’s identity map hides duplicates in PHP, but the DB still returns the multiplied rowset. That can kill performance even when query count is low.

Links (package + reproducible benchmark)

• Benchmark repository: https://github.com/rgalstyan/doctrine-aggregated-queries-benchmark
• Bundle repository: (link omitted in original)

Reproducible benchmark (Symfony + PostgreSQL)

I published a standalone Symfony benchmark project with fixtures and a CLI command so anyone can reproduce the results locally:

https://github.com/rgalstyan/doctrine-aggregated-queries-benchmark

Sample run (limit = 2000)

Note: timings depend on your machine/DB/cache state. The trend is what matters.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
PRODUCTS PERFORMANCE TEST
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Dataset size: 2000 products

TRADITIONAL DOCTRINE (2000 records)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Time:    327.73ms
Memory:  44559.6 KB (43.52 MB)
Queries: 43
Result:  2000 Product entities

DOCTRINE JOIN FETCH (entities) (2000 records)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Time:    816.37ms
Memory:  39795.9 KB (38.86 MB)
Queries: 2
DB rows: ~30000 (Cartesian product in SQL)
Result:  2000 Product entities

SIMPLE JOINS (naive) (2000 records)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Time:    103.14ms
Memory:  11659.4 KB (11.39 MB)
Queries: 1
DB rows: 30000 (Cartesian product!)
Result:  2000 products (after deduplication)

AGGREGATED QUERIES (2000 records)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Time:    66.18ms
Memory:  13989.1 KB (13.66 MB)
Queries: 1
DB rows: 2000
Result:  2000 products (arrays)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
COMPARISON
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Comparison table