Spring Data JPA Pagination Returns Fewer Rows Than Expected: Diagnosing Missing Count Queries

The symptom: a valid Pageable that under-delivers

You call a repository method like Page<Order> findByStatus(String status, Pageable pageable), the table clearly holds hundreds of matching rows, yet page.getContent() comes back empty or truncated, and getTotalElements() reports zero or a number that makes no sense. The page size, page number, and sort are all valid. In most cases the data is fine and the pagination math is fine — the problem is that Spring Data JPA never generated (or generated a broken version of) the COUNT query that backs the Page object.

The useful takeaway: a Page result in Spring Data JPA is always two queries — the entity SELECT and a separate COUNT SELECT. If you only see one query in your logs, you have found the fault line. Everything below is about confirming that and fixing the specific cause.

Quick cause-and-check table

Ordered diagnostic checks

1. Turn on SQL logging and count the queries

In application.properties of a local or staging run (never leave this on in production at volume):

spring.jpa.show-sql=true
logging.level.org.springframework.data.jpa=DEBUG
logging.level.org.hibernate.SQL=DEBUG

Run the failing request once. You should see two statements: a select ... from orders ... limit ? and a select count(...) from orders .... If you see only the entity SELECT, Spring Data never issued a count — go to cause A below. If both appear but the count value is wrong, go to cause B.

2. Inspect the repository method signature

Look at the exact annotation on the method. A method declared as:

@Query("SELECT o FROM Order o JOIN FETCH o.lines WHERE o.status = :status")
Page<Order> findByStatus(@Param("status") String status, Pageable pageable);

forces Spring Data to derive a count query from your JPQL. With JOIN FETCH or DISTINCT, that derivation is frequently wrong or fails outright. This is the single most common root cause.

3. Reproduce against H2

Write a @DataJpaTest with an H2 in-memory database, seed a known number of rows (say 25), request PageRequest.of(0, 10), and assert:

assertThat(page.getTotalElements()).isEqualTo(25);
assertThat(page.getContent()).hasSize(10);
assertThat(page.getTotalPages()).isEqualTo(3);

If the test passes against H2 but fails against your production datasource, suspect the connection pool, a proxy, or database-specific SQL generation rather than your repository code.

Fixes tied to each finding

Cause A: count query never generated

Supply the count explicitly so Spring Data does not have to derive it:

@Query(value = "SELECT o FROM Order o WHERE o.status = :status",
       countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status")
Page<Order> findByStatus(@Param("status") String status, Pageable pageable);

For native queries, the same applies — nativeQuery = true bypasses automatic count generation, so always pair it with an explicit native countQuery.

Cause B: count query generated but wrong

Remove JOIN FETCH from the paginated query. Fetch joins exist to avoid N+1 selects, but they break count derivation. Instead, paginate on the root entity and load associations via @EntityGraph or a follow-up query:

@EntityGraph(attributePaths = "lines")
@Query(value = "SELECT o FROM Order o WHERE o.status = :status",
       countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status")
Page<Order> findByStatus(@Param("status") String status, Pageable pageable);

Cause C: you do not actually need the total

If the UI only needs "next page" behavior and never displays "page 3 of 47", switch the return type to Slice<Order>. A slice executes only one query (it fetches page size + 1 rows to know whether a next page exists) and eliminates the count problem entirely — and it is noticeably cheaper on large tables.

Escalation criteria

Escalate beyond configuration fixes when any of these hold:

• The count query is correct in logs but totals still mismatch in the deployed environment — suspect a proxy, sharding layer, or read replica lag, and test against the primary datasource directly.
• The query is inherently uncountable by derivation (deeply nested native SQL, vendor-specific functions). Implement a custom repository fragment where you run the entity query and count query manually via EntityManager and assemble new PageImpl<>(content, pageable, total) yourself.
• Count performance itself is the problem on a very large table — move to keyset (cursor) pagination with Slice and a WHERE id < :lastSeenId predicate, which avoids both OFFSET scans and COUNT scans.

Limitations and verification

Automatic count derivation behavior differs across Spring Data JPA versions (the 2021.x–2023.x lines changed how derived counts handle DISTINCT and fetch joins), so confirm behavior against your exact Spring Boot dependency version before assuming a fix ported from another project applies. The diagnostics above assume Spring Boot 2.x/3.x with Hibernate as the JPA provider.

To verify any fix: enable SQL logging, fire the paginated request, and confirm two queries execute and that getTotalElements() equals the count query result while getContent().size() matches the requested page size (or the remainder on the last page). The H2 test in check 3 gives you a repeatable harness for exactly this assertion.