Slow query response when paginating deep result sets with Hibernate setFirstResult

Symptom

When paginating large result sets using Hibernate’s setFirstResult and setMaxResults, response time grows noticeably as the offset increases, even though the underlying table is indexed.

Goal and Constraints

The goal is to retrieve pages of data efficiently while guaranteeing deterministic ordering and accurate row counts. Constraints include avoiding full table scans caused by deep offsets, requiring a stable primary key for cursor‑based approaches, and accounting for version‑specific differences between Hibernate 5 and Hibernate 6 regarding fetch size and second‑level cache interaction.

Uncertainty remains about which pagination technique offers the best trade‑off for a given workload and whether mixing native SQL LIMIT/OFFSET with HQL criteria can introduce inconsistencies.

Open Questions

• Which pagination strategy minimizes database load while preserving ordering?
• How do Hibernate 5 versus Hibernate 6 affect fetch size and second‑level cache behavior during offset‑based pagination?
• Is it safe to combine native SQL LIMIT/OFFSET with HQL criteria without risking row‑count mismatches?