Paginated ORM query with joinedload returns wrong page sizes and slow total count

Symptom

A server-side paginated listing built on SQLAlchemy ORM (2.0-style select() with Session.execute()) needs to show page numbers plus a total result count. The query applies limit()/offset() and also eager-loads a one-to-many collection with joinedload().

Observed behavior to diagnose

With SQL echoing enabled, the generated statement wraps everything in a subquery before applying LIMIT/OFFSET, which appears to come from the joined eager load of the collection. Separately, the COUNT query used for the page-number UI runs over the same filtered joins and costs roughly as much as the page fetch itself, doubling the load per request.

Constraints

The UI requires jumping to arbitrary page numbers, so a pure keyset/seek approach may not fit. The collection must be eager-loaded to avoid N+1 queries. Assume SQLAlchemy 2.0 on PostgreSQL, with a unique ORDER BY including the primary key already in place.

Questions

1. Should the joinedload() be switched to selectinload() so the limit applies cleanly to parent rows, and what trade-offs does that introduce?

2. What is the recommended pattern for an efficient total count alongside a paginated query when the filter involves joins?

3. Is the subquery wrapping with joinedload plus limit/offset guaranteed behavior in 2.0, or loader-strategy dependent?