Slow query response when paginating deep result sets with Hibernate setFirstResult

Answer

The slowdown occurs because Hibernate’s setFirstResult/setMaxResults translates to SQL OFFSET/LIMIT. The database must read and discard all rows before the offset, so execution time grows linearly with the page number even when an index exists.

Confirmed facts

• OFFSET forces a sequential scan of the index up to the offset value; the cost is proportional to the offset size.
• In Hibernate 5 the query hint org.hibernate.fetchSize only influences JDBC fetch size, not OFFSET processing. Hibernate 6 retains this behavior but improves second‑level cache interaction when the query result is cacheable.
• Mixing native SQL LIMIT/OFFSET with HQL criteria can change the projection or add hidden columns, leading to row‑count mismatches unless the SELECT list is identical.

Recommended steps for this case

1. Enable Hibernate statistics and log the generated SQL to verify that OFFSET appears:

   hibernate.generate_statistics=true
   hibernate.show_sql=true
   

2. Run EXPLAIN ANALYZE (or DB‑specific equivalent) on the logged SQL with increasing offset values to confirm rising rows read.
3. Replace offset‑based pagination with keyset (seek) pagination:
   • Identify a deterministic, unique ordering column (usually the primary key or a combination that never changes).
   • For the next page, add a WHERE clause that excludes the last seen value, e.g. WHERE id > :lastId ORDER BY id LIMIT :pageSize.
   • In Hibernate, use createQuery or createCriteria with the WHERE predicate and setMaxResults; do not use setFirstResult.
   • If you need second‑level caching, make the query cacheable; keyset queries are cacheable when the underlying data is immutable for the page.
4. If you must keep native SQL, ensure the native query’s SELECT list exactly matches the HQL projection; otherwise use Hibernate’s built‑in pagination to avoid count mismatches.

Missing diagnostic detail

Do you have a guaranteed unique, immutable ordering column (e.g., primary key or a stable composite) that can be used for keyset pagination? If not, the keyset approach may need additional columns or may skip/duplicate rows, changing the recommendation.