When does Hibernate’s setFirstResult cause excessive row scanning that degrades pagination performance?

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.

• At what offset size and under which database dialect does Hibernate’s setFirstResult consistently cause the optimizer to abandon index seeks in favor of full or partial table scans?
• How does the presence of a composite primary key or a natural unique identifier affect the feasibility of a cursor‑based migration without application‑level refactoring?
• Does enabling Hibernate Statistics provide sufficient granularity to quantify query‑plan cost differences and justify a pagination‑strategy shift in production environments?