Hibernate setMaxResults memory limits with join fetches

Answer the exact question first

Hibernate applies setMaxResults only to the root entity list that the SQL SELECT clause returns. When a JOIN FETCH brings a collection into the same result set, the generated SQL still contains the LIMIT/OFFSET clause, but the JDBC driver materialises the entire Cartesian product before Hibernate can truncate the collection in memory. The framework therefore switches the pagination logic from the database to the JVM once the fetched collection exceeds the configured hibernate.jdbc.batch_size or when the driver’s internal fetch buffer is filled.

How Hibernate decides to use in‑memory pagination

• Hibernate adds LIMIT to the SQL only if the query does not contain a JOIN FETCH on a collection that can produce more rows than the root count.
• If a collection is fetched eagerly (e.g., JOIN FETCH or @Fetch(FetchMode.JOIN)), Hibernate still generates the LIMIT clause, but the JDBC driver receives all rows that match the join condition.
• When the number of rows returned by the join exceeds the configured batch size or the driver’s fetch buffer, Hibernate switches to in‑memory truncation: it loads the full result set into a List and then applies subList(0, maxResults).

Indicators in the generated SQL

• Presence of LIMIT/OFFSET – this shows that the database was asked to constrain the root entities.
• Absence of DISTINCT or a GROUP BY around the root table – the query is a simple Cartesian product, so duplicate roots are possible.
• Large SELECT clause that includes all columns of the joined table – the driver will stream every joined row.

To confirm in‑memory pagination:

1. Enable SQL logging (e.g., hibernate.show_sql=true and format_sql=true).
2. Run the query with setMaxResults(10) and a JOIN FETCH on a collection that has >10 rows.
3. Inspect the log: you should see LIMIT 10 in the SQL, but the JDBC driver’s fetch statistics (e.g., PostgreSQL’s fetchSize or MySQL’s useCursorFetch) will report many more rows processed.
4. Measure heap usage with a profiler (e.g., VisualVM). If the heap grows beyond the size of 10 root entities, in‑memory pagination is active.

Controlling memory consumption

• Use @Fetch(FetchMode.SELECT) or batch-size to load collections in separate, bounded queries.
• Apply distinct or a GROUP BY on the root to avoid duplicate rows if you must keep a join fetch.
• Enable JDBC streaming (e.g., setFetchSize(Integer.MIN_VALUE) for PostgreSQL) to let the driver stream rows instead of buffering.
• Consider projection into DTOs or using CriteriaQuery.multiselect to fetch only the columns you need.

What diagnostic detail is still needed?

To give a precise recommendation, could you tell me the size of the fetched collection (average rows per root) and whether you use @BatchSize or FetchMode.SELECT on that association?