Diagnosing and Fixing Hibernate N+1 Selects During Pagination

The recognizable condition

A paginated endpoint that lists parent entities — say, 20 orders per page — works fine in development but crawls in production. When you turn on SQL logging, you see one SELECT for the page of orders, followed by 20 nearly identical SELECTs, one per order, each fetching that order's line items. That is the classic N+1 select problem: one query for the parents, then N additional queries for a lazily loaded association touched during serialization or mapping.

The fix is rarely "make everything eager." Eager fetching on the mapping often does not even apply to JPQL/HQL queries, and the naive fix — JOIN FETCH combined with pagination — introduces a worse failure mode: Hibernate silently abandons database-level pagination and pages in memory. This guide walks through confirming the diagnosis, choosing the right fix, and knowing when the problem needs a different tool entirely.

Cause and diagnostic table

Step 1: Confirm the query count

Enable SQL logging so you can observe, not guess. In a Spring Boot application's application.properties:

hibernate.show_sql=true
hibernate.format_sql=true
# or, for cleaner output:
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Run this on the application JVM; no elevated permissions are needed beyond normal config access. Then trigger the paginated endpoint once and count the SELECTs. If a page of 20 parents produces 21 statements, the N+1 diagnosis is confirmed. For a less noisy alternative in production-like environments, Hibernate's statistics (hibernate.generate_statistics=true with a metrics exporter) or the database's own query log give the same count without log volume. Do not rely on response time alone — connection pooling and small test datasets can mask N+1 completely.

Step 2: Identify where the extra queries originate

The extra SELECTs fire when code touches a lazy association after the initial query returns — typically inside a loop mapping entities to DTOs, or during JSON serialization. Find the exact association by matching the logged SQL's table and join columns to your mappings. Also check whether the initial load is a JPQL/HQL query: if it is, FetchType.EAGER on the mapping is frequently ignored and the provider issues secondary selects anyway, which surprises many teams.

Step 3: Apply the fix that matches the finding

Finding: single collection, no pagination on that query

Use JOIN FETCH in the JPQL. This retrieves parents and the collection in one SQL statement:

@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.lineItems WHERE o.customer.id = :customerId")
List findWithLineItems(@Param("customerId") Long customerId);

The DISTINCT matters: the join duplicates parent rows, and without it your result list contains repeated parent references.

Finding: you need pagination over parents plus a collection

Do not combine JOIN FETCH on a collection with setFirstResult/setMaxResults. Hibernate cannot apply LIMIT/OFFSET to a joined collection without corrupting page boundaries, so it fetches all matching rows and paginates in memory — with a large table this is a performance collapse, logged as warning HHH000104. Instead, split the work into two queries:

// Query 1: paginate parent IDs only
List ids = em.createQuery(
    "SELECT o.id FROM Order o WHERE o.customer.id = :cid ORDER BY o.createdDate DESC", Long.class)
    .setParameter("cid", customerId)
    .setFirstResult(page * size)
    .setMaxResults(size)
    .getResultList();

// Query 2: fetch the page's parents with their collection
List orders = em.createQuery(
    "SELECT DISTINCT o FROM Order o JOIN FETCH o.lineItems WHERE o.id IN :ids", Order.class)
    .setParameter("ids", ids)
    .getResultList();

Run these in the same persistence context (one @Transactional method). This keeps pagination in the database, loads each page's collections in one query, and scales with page size rather than table size.

Finding: fetching strategy varies by use case

If some endpoints need the collection and others do not, a named Entity Graph decouples the fetch plan from the mapping:

@NamedEntityGraph(name = "Order.withItems",
    attributeNodes = @NamedAttributeNode("lineItems"))
@Entity
public class Order { ... }

Apply it per query via the hint javax.persistence.fetchgraph (or jakarta.persistence.fetchgraph on Jakarta EE 9+ / Hibernate 6). Compare the query count of a plain findAll against the graph-hinted query to confirm the association loads in one round-trip.

Finding: two collections needed at once

Joining two collections in one query produces a Cartesian product — parents × collection A × collection B rows. Prefer fetching one collection via JOIN FETCH and letting the second load via batch fetching (e.g., @BatchSize or hibernate.default_batch_fetch_size=20), which collapses N lazy loads into ceil(N/batch) IN-clause queries.

Step 4: Verify the fix

Re-run the same endpoint with SQL logging on. Success criteria: the request issues a small constant number of SELECTs (two or three) regardless of page content, page boundaries are correct against a known dataset, and no HHH000104 warning appears. If you changed mappings or queries, this is a code change — rollback is simply reverting the commit; no data migration is involved.

Escalation criteria

Escalate beyond query tuning when: the parent table is large enough that even ID-based pagination scans are slow (look at keyset/seek pagination instead of OFFSET); the read shape diverges sharply from the entity model (consider a DTO projection or a separate read model); or query counts stay high because serialization triggers lazy loads outside your control — in that case, map to DTOs inside the transactional boundary and never expose entities to the web layer. If LazyInitializationException appears during diagnostics, fix the transaction boundary first; it indicates your test path differs from production behavior and any measurements are unreliable.