The N+1 Query and the Endpoint That Got Slow

The endpoint was fine yesterday. Today it takes eleven seconds. Nothing in the application changed, the database looks idle, and the slow-query log shows nothing above 3ms. This is the shape of an N+1 problem, and it is the most common performance bug in Java backends.

The bug#

@Entity
public class Order {
    @Id private Long id;
 
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private List<OrderLine> lines;
 
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;
}
 
@GetMapping("/orders")
public List<OrderDto> list() {
    return repository.findAll().stream()      // 1 query
            .map(o -> new OrderDto(
                    o.getId(),
                    o.getCustomer().getName(),  // +1 query PER ORDER
                    o.getLines().size()))       // +1 query PER ORDER
            .toList();
}

With 500 orders that is 1,001 queries. Each takes 2ms of database time and 8ms of network round-trip, so the endpoint takes about ten seconds while the database reports every query as fast.

That mismatch — a slow endpoint and a fast database — is the diagnostic signature. The slow-query log is empty because no individual query is slow.

Seeing it#

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE

spring.jpa.properties.hibernate.generate_statistics=true

The statistics log prints a summary per session, and 1001 statements on an endpoint that should issue one is unmissable.

Fix 1: JOIN FETCH#

@Query("""
        SELECT DISTINCT o FROM Order o
        JOIN FETCH o.customer
        JOIN FETCH o.lines
        WHERE o.placedAt >= :since
        """)
List<Order> findRecentWithDetails(@Param("since") Instant since);

DISTINCT here removes duplicate parent references caused by the join producing one row per line; since Hibernate 6 it is applied automatically for entity queries, so it is no longer required, but seeing it in older code is normal.

Fix 2: EntityGraph#

@EntityGraph(attributePaths = {"customer", "lines"})
List<Order> findByStatus(OrderStatus status);

Same effect, declared separately from the query. Preferable when several methods need the same entity with different amounts loaded, because you are not duplicating the JPQL.

Fix 3: batch fetching#

@Entity
public class Order {
    @OneToMany(mappedBy = "order")
    @BatchSize(size = 25)          // Hibernate loads lazy collections 25 at a time
    private List<OrderLine> lines;
}

spring.jpa.properties.hibernate.default_batch_fetch_size=25

Hibernate replaces 500 individual WHERE order_id = ? queries with 20 queries using WHERE order_id IN (?, ?, ... 25 times). This is the single highest-value line of configuration in most JPA applications, because it improves every lazy association at once without touching any query.

Fix 4: do not load entities at all#

public interface OrderSummary {
    Long getId();
    String getCustomerName();
    int getLineCount();
}
 
@Query("""
        SELECT o.id AS id, c.name AS customerName, COUNT(l) AS lineCount
        FROM Order o JOIN o.customer c LEFT JOIN o.lines l
        GROUP BY o.id, c.name
        """)
List<OrderSummary> findSummaries();

One query, no entities in the persistence context, no dirty checking, no lazy proxies. For a read-only list endpoint this is both the fastest option and the one least able to go wrong later, because there is no lazy association for someone to accidentally touch.

The two traps#

WARN o.h.h.i.QueryTranslatorImpl - HHH000104: firstResult/maxResults specified with
collection fetch; applying in memory!

A JOIN FETCH on a collection produces one row per child, so LIMIT 20 would cut off mid-parent. Rather than return corrupt objects, Hibernate fetches every matching row and paginates in memory — so a "page 1 of 20" request can load a million rows into the heap. This is a genuine OutOfMemoryError cause, and the warning is easy to miss in a busy log.

// 1. Page the identifiers only — no join, so LIMIT is correct.
@Query("SELECT o.id FROM Order o WHERE o.status = :status ORDER BY o.placedAt DESC")
Page<Long> findIdPage(@Param("status") OrderStatus status, Pageable pageable);
 
// 2. Fetch the full aggregates for exactly those ids.
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.lines WHERE o.id IN :ids")
List<Order> findAllWithLines(@Param("ids") List<Long> ids);

When it is not N+1#

Sometimes one query really is slow, and the tool is EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'PENDING';
 
-- Seq Scan on orders  (cost=0.00..48291.00 rows=1 width=248)
--   Filter: ((customer_id = 42) AND (status = 'PENDING'))
--   Rows Removed by Filter: 1999999
--   Execution Time: 1847.221 ms

Seq Scan with two million rows removed by a filter is a missing index:

CREATE INDEX CONCURRENTLY idx_orders_customer_status
    ON orders (customer_id, status)
    WHERE status IN ('PENDING', 'PROCESSING');

Equality columns first, then range columns. CONCURRENTLY avoids locking the table during creation. The partial WHERE keeps the index small when most rows are in states you never query.

Four things that silently prevent index use, all worth knowing: a function on the column (WHERE LOWER(email) = ? needs a functional index), an implicit type cast, a leading wildcard (LIKE '%foo'), and OR across columns that have separate indexes.

Preventing it#

@Test
void listOrdersIssuesAtMostFiveQueries() {
    Statistics stats = entityManagerFactory.unwrap(SessionFactory.class).getStatistics();
    stats.clear();
 
    mockMvc.perform(get("/orders")).andExpect(status().isOk());
 
    assertThat(stats.getPrepareStatementCount())
            .as("N+1 regression on GET /orders")
            .isLessThanOrEqualTo(5);
}

This is the guardrail that matters. N+1 is introduced by a one-line change — someone adds o.getCustomer().getName() to a mapper — and no code review catches it reliably. A test that fails the build does.

The incident, end to end#

1. Symptom. GET /orders p99 at 11 seconds. Database CPU at 15%, no slow queries logged.
2. The mismatch is the clue. Slow endpoint, fast database, means round-trips, not query cost.
3. Confirm. Enable generate_statistics on one instance: 1,001 statements per request.
4. Root cause. A DTO mapper added last week reads order.getCustomer().getName(), triggering a lazy load per row.
5. Mitigate. Set default_batch_fetch_size=25 — one property, no code change, 1,001 queries become 41.
6. Fix. Replace the endpoint with a projection query.
7. Guardrail. The query-count assertion above, plus an alert comparing endpoint latency against database time so the next mismatch is caught by monitoring rather than by users.

Step 5 is worth highlighting: a configuration-only mitigation that can ship in minutes, separate from the proper fix. Interviewers listen for that distinction.

What gets asked#

"What is the N+1 problem and how do you fix it?" is extremely common. The complete answer names the cause, gives at least two fixes with their trade-offs, and mentions detection. Adding the pagination trap — that JOIN FETCH with LIMIT silently paginates in memory — is the detail that shows you have hit it in production.