The N+1 problem: spot it, understand it and fix it

Your application works fine in development. You put it in production, the data comes in and a page that answered in fifty milliseconds now takes four thousand. Nothing changed in the code and the culprit is almost always the same. It is the N+1 problem.

It is the most common performance flaw as soon as you use an ORM. It is also one of the easiest to fix. You still need to know how to spot it.

What the N+1 problem is#

The name comes from the number of queries sent to the database. One query fetches a list of N items, then one more query per item loads related data. N + 1 in total.

An example in Laravel.

$articles = Article::all();

foreach ($articles as $article) {
    echo $article->author->name;
}

Three lines that look harmless. Here is what actually goes to the database.

SELECT * FROM articles;

SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- and so on

Ten articles, eleven queries. A thousand articles, a thousand and one.

That is the trap! In development, your database holds ten test rows and the page answers instantly. The flaw stays invisible and only shows up when the volume grows. So in production.

Why ORMs produce this flaw#

It is not a bug. It is the direct consequence of lazy loading, a deliberate mechanism.

An ORM cannot guess what you will need. If it loaded every relation every time, the smallest query would bring back half the database. So it loads the main object and waits until you ask for a relation before fetching it.

$article = Article::find(1);
// One query on articles

$article->author;
// Second query, triggered by this access

On a single object, this behaviour is fine. In a loop, it becomes a problem. Each iteration triggers its own round trip to the database.

The real cost is not even the SQL execution time. Each query costs a network round trip, parsing by the engine and a pass through the connection layer. On a remote database, one millisecond per query becomes a full second after a thousand iterations.

Detect it before production#

In Laravel#

The most direct way is to throw an error as soon as a relation is loaded on the fly.

// In AppServiceProvider::boot()
Model::preventLazyLoading(!app()->isProduction());

Outside production, any relation that was not eager loaded throws a LazyLoadingViolationException. It is brutal but effective. You can no longer let the flaw slip through.

If you prefer a version that does not break the page, log instead of throwing an exception.

Model::preventLazyLoading(!app()->isProduction());

Model::handleLazyLoadingViolationUsing(function (Model $model, string $relation) {
    Log::warning('Lazy loaded relation: ' . $model::class . '::' . $relation);
});

Then open storage/logs/laravel.log and you see exactly where your N+1 are.

To watch the real SQL traffic:

DB::listen(function ($query) {
    Log::debug($query->sql, ['time' => $query->time]);
});

Laravel Debugbar and Telescope also show the number of queries per page, with duplicates flagged.

In Spring Boot and Hibernate#

Turn on Hibernate statistics.

spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=DEBUG

For each transaction, Hibernate shows the number of prepared statements and the time spent. A gap between the number of entities and the number of queries stands out immediately.

To read the SQL:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Never leave these options on in production. The volume of logs would be unmanageable.

The datasource-proxy library goes further. It lets a test fail above a given number of queries. It is the best way to prevent a regression.

Fixing it in Laravel#

Eager loading#

The basic solution is with().

$articles = Article::with('author')->get();

foreach ($articles as $article) {
    echo $article->author->name;
}

Two queries, whatever the number of articles.

SELECT * FROM articles;
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);

Eloquent collects the ids, runs a single query with in, then attaches the results in memory.

Nested relations#

// Article, its author, its comments and the author of each comment
$articles = Article::with('author', 'comments.user')->get();

Limiting the columns#

Loading the whole row to display a name is a waste.

$articles = Article::with('author:id,name,avatar')->get();

There are two traps here. First, no space after the commas. Eloquent splits the list without trimming the spaces and the query fails on an unknown column " name".

Second, the column that links the two tables must always be in the list. For a belongsTo like author, it is the primary key id of users: Eloquent compares it with the author_id of the article. Without it, the author is attached to no article and the relation comes back empty without any error. For a hasMany, it is the other way round: keep the foreign key of the child table, for example article_id.

Counting without loading#

To display the number of comments, do not load the comments.

// Bad: loads every comment just to count them
$articles = Article::with('comments')->get();

foreach ($articles as $article) {
    echo $article->comments->count();
}

// Good: the database does the counting
$articles = Article::withCount('comments')->get();

foreach ($articles as $article) {
    echo $article->comments_count;
}

withCount adds a subquery to the main query. No useless object is built on the PHP side.

The same variants exist for aggregates.

$articles = Article::withSum('comments', 'score')
    ->withAvg('ratings', 'value')
    ->withExists('comments')
    ->get();

Conditional eager loading#

$articles = Article::with(['comments' => function ($query) {
    $query->where('is_approved', true)
        ->latest()
        ->limit(5);
}])->get();

Since Laravel 11, limit(5) applies to each article. Before that, it applied to the whole query: five comments in total, spread at random across the articles.

Loading afterwards#

When the collection already exists.

$articles = Article::all();

// Depending on the context
if ($needsAuthors) {
    $articles->load('author');
}

loadMissing() does the same thing and skips the relations already loaded.

Default eager loading#

When a relation is needed in almost every case.

class Comment extends Model
{
    protected $with = ['user'];
}

Handle with care. A relation loaded every time makes every query heavier, including those that do not need it. Keep it for the truly essential relations.

Fixing it in JPA and Hibernate#

Same principle, different tools.

The default values trap#

A key point that is often ignored. JPA does not have the same default behaviour for every type of relation.

The last two rows are the problem. A @ManyToOne in EAGER means that loading an entity also loads its parent every time, even when you do not need it. On an entity with several @ManyToOne relations, a single read can trigger a cascade of joins.

Everyone recommends the same thing: make everything LAZY, then load what you need explicitly.

@Entity
public class Article {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private User author;

    @OneToMany(mappedBy = "article", fetch = FetchType.LAZY)
    private List<Comment> comments;
}

JOIN FETCH#

The direct equivalent of Laravel's with().

@Query("""
    SELECT a FROM Article a
    JOIN FETCH a.author
    WHERE a.publishedAt IS NOT NULL
    """)
List<Article> findPublishedWithAuthor();

Mind the difference between join and join fetch. A plain join is used to filter and does not load the relation. Only join fetch brings it into memory.

Entity graphs#

More flexible, they separate the query from the loading strategy.

@Entity
@NamedEntityGraph(
    name = "Article.withAuthorAndComments",
    attributeNodes = {
        @NamedAttributeNode("author"),
        @NamedAttributeNode("comments")
    }
)
public class Article { }

public interface ArticleRepository extends JpaRepository<Article, Long> {

    @EntityGraph(value = "Article.withAuthorAndComments")
    List<Article> findByPublishedAtIsNotNull();
}

The benefit is clear. The loading strategy is separate from the query. The same graph serves several methods, and a derived query like findByPublishedAtIsNotNull does not need to be rewritten in JPQL to load its relations.

Batch fetching#

A middle-ground solution, very effective and often forgotten.

spring.jpa.properties.hibernate.default_batch_fetch_size=25

Hibernate then groups lazy loads in batches. Instead of a hundred queries, it sends four with an in of twenty-five ids. You do not go from N+1 to 1, you go from N+1 to N/25 + 1. That is more than enough in most cases.

The @BatchSize annotation gives the same setting relation by relation.

DTO projections#

The fastest solution when you only need to read.

public record ArticleSummary(Long id, String title, String authorName) { }

@Query("""
    SELECT new com.blog.dto.ArticleSummary(a.id, a.title, a.author.name)
    FROM Article a
    """)
List<ArticleSummary> findAllSummaries();

A single query and only the useful columns. No entity managed by the persistence context. For a read-only list page, it is often the best choice.

Hibernate-specific traps#

Three classic mistakes that catch everyone once.

MultipleBagFetchException#

// Throws an exception at startup
@Query("""
    SELECT a FROM Article a
    JOIN FETCH a.comments
    JOIN FETCH a.tags
    """)

Hibernate refuses to load two collections of type List in the same query. The message is clear, the cause less so. A List without an index keeps duplicates. Two joins would give an ambiguous result.

Three solutions: replace List with Set, run two separate queries (the persistence context merges the results on its own) or put @BatchSize on the second collection.

The Cartesian product#

Even with Set, two join fetch on collections produce a Cartesian product in the database. An article with twenty comments and five tags returns a hundred SQL rows. Hibernate deduplicates in memory, but the hundred rows did travel over the network.

In practice, one collection per query. The others go through @BatchSize or a separate query.

Pagination and JOIN FETCH#

This warning is the most dangerous of all because the code still works. It is called HHH000104 in Hibernate 5 and HHH90003004 in Hibernate 6, the one used by Spring Boot 3.

@Query("SELECT a FROM Article a JOIN FETCH a.comments")
Page<Article> findAll(Pageable pageable);

Hibernate cannot apply limit in SQL. The join multiplies the rows and a limit would cut collections in half. So it loads the whole table and paginates in Java memory.

With a thousand articles, your page of twenty items loads a thousand. No error, just an application that collapses as soon as the volume grows.

To stop letting it slip through, turn the warning into an error.

spring.jpa.properties.hibernate.query.fail_on_pagination_over_collection_fetch=true

The solution is to split the work in two steps.

// 1. Fetch the ids of the page without a join
@Query("SELECT a.id FROM Article a ORDER BY a.publishedAt DESC")
Page<Long> findPageOfIds(Pageable pageable);

// 2. Load these entities with their collections, in the same order
@Query("SELECT a FROM Article a JOIN FETCH a.comments WHERE a.id IN :ids ORDER BY a.publishedAt DESC")
List<Article> findAllWithCommentsByIds(@Param("ids") List<Long> ids);

The ORDER BY of the second query is not decoration. An IN clause guarantees no order: without it, the page comes back shuffled.

The opposite excess#

Fixing an N+1 does not mean eager loading everything.

// Expensive and probably useless
$articles = Article::with([
    'author.profile.settings',
    'comments.user.roles',
    'tags',
    'category.parent',
])->paginate(15);

If your view only shows the title and the author's name, everything else is transfer and memory thrown away. One big query can be slower than a few small ones.

The rule fits in one sentence: eager load exactly what the view uses, not what it might need one day. Eager loading answers an observed need. It is not a precaution.

A working method#

Four steps, in this order.

Measure first: Count the queries of your page. In Laravel, Debugbar shows it. In Spring, Hibernate statistics give it. Without measuring, you optimise blindly.

Make the flaw visible: preventLazyLoading in Laravel, a query threshold in the tests in Spring. A flaw that the tool reports cannot sneak back in.

Fix it in the right place: Eager loading is declared in the controller or the repository, never in the view. A view should never trigger a query.

Check afterwards: Count again. Going from a hundred and one queries to two is something you observe, not something you assume.

Further reading#

• The Eloquent documentation on eager loading
• Laravel Debugbar
• Vlad Mihalcea's blog, the reference on Hibernate performance
• Use The Index, Luke, to understand what the database does with your queries