Query Performance and N+1

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Laravel 13.x (13.34.0) on PHP 8.3 to 8.5, checked on 2026-10-05. The measurements use the Bookshelf models on PostgreSQL 17.11, loaded with 200 authors, 1000 books and 50000 reviews, from a script that boots the application; the absolute times are from one laptop-class container and matter only relative to each other. The general method for slow queries (find them, read the plan, fix the access path) is on Diagnosing and Fixing Slow Queries.

The N+1 problem

The page under test lists 50 books with the author’s name and the number of reviews. Written naively, it runs one query for the books, then one per book for the author and one per book for the reviews:

sequenceDiagram participant C as Controller loop participant DB as PostgreSQL Note over C,DB: Lazy loading, 101 queries C->>DB: select * from books limit 50 loop for each of the 50 books C->>DB: select * from authors where id = ? C->>DB: select * from reviews where book_id = ? end Note over C,DB: Eager loading, with(author) and withCount(reviews), 2 queries C->>DB: select books.*, (select count(*) from reviews where ...) as reviews_count from books limit 50 C->>DB: select id, name from authors where id in (1, 2, ..., 10)

<?php
use App\Models\Book;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

$n = 0; $ms = 0.0;
DB::listen(function ($q) use (&$n, &$ms) { $n++; $ms += $q->time; });
$run = function (string $label, Closure $page) use (&$n, &$ms) {
    $n = 0; $ms = 0.0; gc_collect_cycles(); memory_reset_peak_usage(); $mem = memory_get_usage(); $t = microtime(true);
    $page();
    printf("%-34s %4d queries %8.1f ms in SQL %8.1f ms total %6.1f MB peak%s", $label, $n, $ms, (microtime(true) - $t) * 1000, (memory_get_peak_usage() - $mem) / 1048576, PHP_EOL);
};

$run('lazy loading (N+1)', function () {
    foreach (Book::orderBy('id')->limit(50)->get() as $b) { $x = $b->author->name.' '.$b->reviews->count(); }
});
$run('with(author, reviews)', function () {
    foreach (Book::with(['author', 'reviews'])->orderBy('id')->limit(50)->get() as $b) { $x = $b->author->name.' '.$b->reviews->count(); }
});
$run('with(author)->withCount(reviews)', function () {
    foreach (Book::with('author:id,name')->withCount('reviews')->orderBy('id')->limit(50)->get() as $b) { $x = $b->author->name.' '.$b->reviews_count; }
});
Model::automaticallyEagerLoadRelationships();
$run('automatic eager loading', function () {
    foreach (Book::orderBy('id')->limit(50)->get() as $b) { $x = $b->author->name.' '.$b->reviews->count(); }
});
Model::automaticallyEagerLoadRelationships(false);

Source: Laravel — Eager Loading

The first measurement, before the index described below existed:

lazy loading (N+1)                  101 queries    211.2 ms in SQL    272.4 ms total
with(author, reviews)                 3 queries     11.2 ms in SQL     41.2 ms total
with(author)->withCount(reviews)      2 queries    144.8 ms in SQL    147.2 ms total
automatic eager loading               3 queries     10.0 ms in SQL     42.3 ms total

Source: Laravel — Automatic Eager Loading

The N+1 version made 101 round trips; with() made 3. Each lazy query is fast on its own, which is why N+1 hides in development with ten rows and appears in production with ten thousand. Surprisingly, withCount(), which should be the cheapest (no review models at all), was the second slowest. Its plan explains why.

Reading the plan: a missing index

<?php
use App\Models\Book;
use Illuminate\Support\Facades\DB;

$q = Book::withCount('reviews')->where('id', 1);
echo $q->toRawSql(), PHP_EOL;
foreach (DB::select('explain analyze '.$q->toRawSql()) as $row) { echo '  ', $row->{'QUERY PLAN'}, PHP_EOL; }

select "books".*, (select count(*) from "reviews" where "books"."id" = "reviews"."book_id" and "reviews"."deleted_at" is null) as "reviews_count" from "books" where "id" = 1
  Index Scan using books_pkey on books  (cost=0.28..1202.43 rows=1 width=616) (actual time=4.581..4.587 rows=1 loops=1)
    Index Cond: (id = 1)
    SubPlan 1
      ->  Aggregate  (cost=1194.12..1194.13 rows=1 width=8) (actual time=4.538..4.539 rows=1 loops=1)
            ->  Seq Scan on reviews  (cost=0.00..1194.00 rows=50 width=0) (actual time=0.107..4.522 rows=50 loops=1)
                  Filter: ((deleted_at IS NULL) AND (books.id = book_id))
                  Rows Removed by Filter: 49950
  Planning Time: 1.063 ms
  Execution Time: 4.703 ms

Source: PostgreSQL 17 — Using EXPLAIN

The count subquery reads all 50000 reviews (Seq Scan, Rows Removed by Filter: 49950) to find the 50 of one book, and it runs once per book. foreignId('book_id')->constrained() created a foreign key but no index: PostgreSQL, unlike MySQL’s InnoDB, does not index the referencing column of a foreign key. The eager load of reviews hid this, because a single where book_id in (...) scan serves all 50 books. One migration fixes it, with deleted_at included because SoftDeletes adds deleted_at is null to every query:

<?php
// database/migrations/2026_10_05_210612_add_book_id_index_to_reviews_table.php (up() only)
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

Schema::table('reviews', function (Blueprint $table) {
    $table->index(['book_id', 'deleted_at']);             // PostgreSQL does not index foreign keys itself
});

            ->  Index Only Scan using reviews_book_id_deleted_at_index on reviews  (cost=0.29..5.29 rows=50 width=0) (actual time=0.035..0.038 rows=50 loops=1)
                  Index Cond: ((book_id = books.id) AND (deleted_at IS NULL))
                  Heap Fetches: 0
  Execution Time: 0.208 ms

lazy loading (N+1)                  101 queries     96.7 ms in SQL    163.4 ms total    7.1 MB peak
with(author, reviews)                 3 queries      4.9 ms in SQL     33.9 ms total    5.4 MB peak
with(author)->withCount(reviews)      2 queries      1.9 ms in SQL      3.9 ms total    0.1 MB peak
automatic eager loading               3 queries      4.7 ms in SQL     36.1 ms total    5.5 MB peak

Source: Laravel — Creating Indexes

With the index, one book’s count fell from 4.7 ms to 0.2 ms, and the withCount() page from 147 ms to 3.9 ms. It is now ten times faster than eager-loading the reviews, which builds 2500 Review models (5.4 MB) only to count them. The order of fixes that the numbers suggest: first remove N+1 (query count), then make each query use an index (plan), then stop loading data the page does not show (withCount, select).

Eager loading strategies

with(['author', 'reviews']) in the query, or $books->load('author') on a collection already fetched; nested with dots (with('reviews.user')), constrained with a closure, and limited to columns with 'author:id,name'.
withCount(), withSum(), withAvg(), withExists() when the page needs a number, not the rows.
Model::automaticallyEagerLoadRelationships() (Laravel 12) in AppServiceProvider::boot(): the first time a relationship is accessed on a model from a collection, it is loaded for the whole collection. The measurement above shows it matching hand-written with() (3 queries), while still loading full models; $books->withRelationshipAutoloading() enables it for one collection.
$with = ['author'] on the model eager-loads on every query, which is wasteful where the relationship is not used.

Preventing and reporting lazy loading

Model::preventLazyLoading(! app()->isProduction()) throws LazyLoadingViolationException in development when a relationship is lazy-loaded on a model that came with others (single models are exempt, see Eloquent Models). In production it is better to report than to fail:

<?php
use App\Models\Book;
use Illuminate\Database\Eloquent\Model;

Model::preventLazyLoading();
$violations = [];
Model::handleLazyLoadingViolationUsing(function (Model $model, string $relation) use (&$violations) {
    $violations[] = get_class($model).'::'.$relation;
});
foreach (Book::orderBy('id')->limit(3)->get() as $b) { $b->author; }
echo 'violations logged: ', count($violations), ' (', $violations[0], ')', PHP_EOL;
// violations logged: 3 (App\Models\Book::author)

Source: Laravel — Preventing Lazy Loading

In an application, the handler calls Log::warning() or report(), and the loading still happens, so users are not affected while the log collects the places to fix.

Narrowing selects

<?php
use App\Models\Book;

$run('get() all columns, 1000 books', fn () => Book::all());
$run('select(id, title), 1000 books', fn () => Book::select('id', 'title')->get());
$run('toBase()->select, 1000 rows', fn () => Book::toBase()->select('id', 'title')->get());

get() all columns, 1000 books         1 queries      1.7 ms in SQL     11.1 ms total    2.1 MB peak
select(id, title), 1000 books         1 queries      0.9 ms in SQL      9.9 ms total    1.7 MB peak
toBase()->select, 1000 rows           1 queries      0.9 ms in SQL      1.0 ms total    0.5 MB peak

Source: Laravel — Specifying a Select Clause

Selecting two columns halved the SQL time but hardly changed the total: most of the 10 ms is building 1000 Eloquent models (casts, attributes, events). toBase() returns plain stdClass rows from the same query and took 1 ms. For exports, reports and dashboards that only read, the query builder or toBase() is often the biggest win; narrowing with select() matters more when rows carry large text or json columns. With strict mode on, accessing a column that was not selected throws instead of returning null.

Finding slow queries

Tool Use

Tool	Use
`DB::listen()`, `DB::enableQueryLog()`	Count and time queries in a script or test, as above. The log of the last snippet held `select * from "books" where "id" in (?, ?)` and `select * from "authors" where "authors"."id" in (1)`.
`DB::whenQueryingForLongerThan(500, fn (...) => ...)`	Alert when a request’s total query time passes a budget.
Laravel Debugbar (`barryvdh/laravel-debugbar`)	A toolbar in the browser with every query, its bindings, duplicates and time, in development.
Telescope and Pulse	Telescope records queries per request in development; Pulse shows slow queries and endpoints in production (Monitoring and Debugging).
Database slow query logs and `EXPLAIN`	`log_min_duration_statement` in PostgreSQL, the slow query log in MySQL, and `EXPLAIN ANALYZE` on the worst offenders.
Tests	`$this->expectsDatabaseQueryCount(2)` fails a test when a change adds queries.

DB::listen(), DB::enableQueryLog()

Count and time queries in a script or test, as above. The log of the last snippet held select * from "books" where "id" in (?, ?) and select * from "authors" where "authors"."id" in (1).

DB::whenQueryingForLongerThan(500, fn (...) => ...)

Alert when a request’s total query time passes a budget.

Laravel Debugbar (barryvdh/laravel-debugbar)

A toolbar in the browser with every query, its bindings, duplicates and time, in development.

Telescope and Pulse

Telescope records queries per request in development; Pulse shows slow queries and endpoints in production (Monitoring and Debugging).

Database slow query logs and EXPLAIN

log_min_duration_statement in PostgreSQL, the slow query log in MySQL, and EXPLAIN ANALYZE on the worst offenders.

Tests

$this->expectsDatabaseQueryCount(2) fails a test when a change adds queries.

Source: Laravel — Monitoring Cumulative Query Time

Caching query results

A result that is expensive and changes rarely can be cached:

<?php
use App\Models\Book;
use Illuminate\Support\Facades\Cache;

Cache::forget('top-rated');
$heavy = fn () => Book::withAvg('reviews', 'rating')->orderByDesc('reviews_avg_rating')->limit(5)->pluck('title')->all();
foreach (['miss', 'hit'] as $label) {
    $t = microtime(true);
    $top = Cache::remember('top-rated', now()->plus(minutes: 10), $heavy);
    printf("cache %s: %.1f ms -> %s%s", $label, (microtime(true) - $t) * 1000, implode(', ', $top), PHP_EOL);
}

cache miss: 170.1 ms -> Book 20, Book 10, Book 15, Book 5, Book 25
cache hit: 0.4 ms -> Book 20, Book 10, Book 15, Book 5, Book 25

Source: Laravel — Retrieving Items From the Cache

The aggregate over 50000 reviews took 170 ms; the cached copy 0.4 ms. Cache plain arrays (->all(), IDs) rather than models, so cached values do not depend on class changes, and invalidate on writes (an observer or $touches updating a version key) or accept staleness explicitly with the TTL. Cache::flexible() serves a stale value while refreshing it in the background. Caching hides slow queries rather than fixing them, so it comes after the index. Cache stores and locks are on Cache and Rate Limiting.

For batch work over large tables, chunkById() and lazyById() keep memory flat (Database and Query Builder).

Where older books and tutorials are outdated

Assuming foreign keys are indexed holds for MySQL InnoDB, not for PostgreSQL or SQLite, where foreignId()->constrained() creates no index.
Hunting N+1 queries by reading logs by hand is replaced by preventLazyLoading() (Laravel 8) in development and automatic eager loading (Laravel 12) as a safety net.
$book->reviews->count() in views loads every review to count them; withCount() does it in SQL.
Clockwork and Debugbar as the only profilers are joined by Telescope, Pulse and Nightwatch in the Laravel ecosystem.