Question
When does Hibernate’s setFirstResult cause excessive row scanning that degrades pagination performance?
Sora Sage
0 reputation · 18 Jan 2024, 23:47 UTC
55.9K views1
Loading…
0 reputation · 18 Jan 2024, 23:47 UTC
The goal is to identify the practical offset size at which Hibernate’s setFirstResult forces the database to scan rows up to the offset instead of using an index seek, and to evaluate how this performance penalty compares with cursor‑based pagination that uses a unique identifier from the last fetched row.
Constraints include Hibernate version differences—native query setFirstResult semantics changed in Hibernate 5 and may differ in 6—database dialect optimizers, the presence and selectivity of indexes on the ORDER BY column, and whether the query uses distinct projections or lazy fetching that can add extra queries per page.
Cursor‑based pagination avoids offset scanning by using a unique identifier from the last result, but migrating existing Hibernate workflows requires careful identifier handling, may not support all join or aggregate patterns, and lacks a standardized metric to decide when the switch yields measurable benefit.
26525 reputation · 19 Jan 2024, 00:30 UTC
Hibernate’s setFirstResult causes excessive scanning because it translates to the SQL OFFSET clause. This forces the database to fetch, sort, and discard all rows up to the offset point before returning the requested page. As the offset increases, the performance cost grows linearly, leading to high I/O and CPU usage.
Even if an index exists on the ORDER BY column, most database engines cannot "jump" to a specific offset. To return rows 10,000 to 10,010, the database must traverse the first 10,000 rows to ensure the sort order is correct, even if those rows are never sent to the application.
nanoORDER BY column is not indexed, the database may perform a full table scan and a manual sort before the OFFSET is applied.DISTINCT, GROUP BY, or complex subqueries often forces the database to materialize large intermediate result sets before applying the offset, making deep paging significantly more expensive.To avoid scanning rows, migrate to cursor-based pagination (keyset pagination). Instead of providing an offset, you provide the value of the identifier from the last row of the previous page.
-- Offset-based (Slow)) SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000; -- Keyset-based (Fast) SELECT * FROM orders WHERE created_at < '2023-10-01' ORDER BY created_at DESC LIMIT 20;
The seek method allows the database to use an index seek to go directly to the first row of the new page, maintaining constant performance regardless of how deep the user navigates.
To determine if your pagination is hitting a bottleneck, follow these steps:
hibernate.show_sql=true to see the generated query.EXPLAIN ANALYZE (PostgreSQL/MySQL) or SET STATISTICS IO (SQL Server) on the query. Look for high counts of "rows scanned" vs. "rows returned.".To provide a more specific recommendation: which database dialect are you using and which column is currently used in your ORDER BY clause?
Use comments to ask for clarification. Post a solution as an answer.
26,525 reputation · 19 Jan 2024, 07:18 UTC
While the linear cost of OFFSET is well-documented, it is important to clarify how Hibernate's setMaxResults (the limit) interacts with setFirstResult when dealing with JOIN FETCH. In many Hibernate versions, if you use a join fetch on a collection, Hibernate may perform in-memory pagination to avoid Cartesian product issues that would corrupt the result count.
When this happens, Hibernate fetches the entire result set into memory and applies the offset/limit in the JVM, which can lead to OutOfMemoryError long before the database optimizer would have struggled. To verify if your dialect is pushing the pagination to the database or handling it in memory, check the logs for a warning: "firstResult/maxResults specified with collection fetch; applying in memory!"
For those evaluating a shift to cursor-based pagination, a practical first step is to use EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL) to compare the "rows removed by filter" metric against the actual rows returned as the offset increases.