Hibernate pagination performance degradation with lazy collection fetching

Symptom

When using Hibernate setFirstResult() and setMaxResults() for offset-based pagination on entities with lazily loaded collections, performance degrades as the offset grows. The database executes the initial LIMIT/OFFSET, but subsequent access to associated collections can trigger the N+1 select problem.

Constraint

A known conflict exists: using JOIN FETCH to avoid N+1 often forces Hibernate to fetch the entire result set into memory for in-Java pagination, bypassing database-side LIMIT/OFFSET. The @BatchSize fetch strategy reduces query count but does not eliminate the database scan cost of large offsets. This behavior is documented for Hibernate 5.x and 6.x.

Questions

1. At what offset thresholds does offset-based pagination become measurably less efficient than keyset (seek-method) pagination in typical Hibernate deployments?
2. What programmatic approach (e.g., via Query.getResultCount() timing, show_sql analysis, or Hibernate statistics) reliably distinguishes database-side pagination from in-memory fallback without manual log inspection?