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

Direct answer

Yes: switch the collection to selectinload(). The subquery wrapping you see is expected behavior for joinedload() of a one-to-many collection combined with limit()/offset() — SQLAlchemy wraps the query so the limit applies to distinct parent rows instead of the row-multiplied join result. It is loader-strategy dependent, not universal: joinedload() of a many-to-one does not trigger it, and selectinload() never does.

1. joinedload vs selectinload for this case

With selectinload() the parent query becomes a flat SELECT ... LIMIT ... OFFSET ..., and children load in a second query with WHERE parent_id IN (...). Page sizes become exact and the parent query plans predictably against your unique ORDER BY.

from sqlalchemy import select
from sqlalchemy.orm import selectinload

stmt = (
    select(Parent)
    .options(selectinload(Parent.children))
    .where(Parent.status == "active")
    .order_by(Parent.created_at, Parent.id)
    .limit(page_size)
    .offset((page - 1) * page_size)
)

Trade-offs: two round trips instead of one, and a large IN list if pages are wide — keep page sizes bounded (e.g., ≤ 100). For paginated listings this is almost always the right exchange. An alternative hybrid — fetch parent IDs with limit/offset first, then a joinedload query filtered by those IDs — keeps one join query but adds a round trip and complexity, so it only pays off when the join itself is cheap and pages are large.

2. Efficient total count

Never count from the eager-loaded query: the collection join inflates rows and forces a DISTINCT or subquery. Build a separate minimal statement with no eager loads, ordering, limit, or offset:

from sqlalchemy import func, select

count_stmt = (
    select(func.count())
    .select_from(Parent)
    .where(Parent.status == "active")
)
total = session.scalar(count_stmt)

If the filter joins are many-to-one only, they don't change the parent count, so join just the tables the WHERE clause needs. If a filter touches a one-to-many, prefer WHERE EXISTS over a join plus count(distinct Parent.id) — PostgreSQL can use a semi-join plan and avoid scanning the many side:

from sqlalchemy import exists

count_stmt = select(func.count()).select_from(Parent).where(
    Parent.status == "active",
    exists().where(Child.parent_id == Parent.id, Child.flag.is_(True)),
)

3. Is the wrapping guaranteed?

It is deterministic for the specific combination — joined eager loading of a collection (one-to-many or many-to-many) plus limit/offset — in SQLAlchemy 2.0. It is not a property of eager loading in general, which is why the behavior appears to change when you alter loader options.

Verification

• Enable echoing or a before_cursor_execute listener: with selectinload you should see a flat parent SELECT plus a second IN query, no subquery.
• Inspect the count SQL: no ORDER BY, LIMIT, OFFSET, or join to the collection table unless the filter requires it.
• Run EXPLAIN ANALYZE on both queries against production-like data to confirm the count stops scanning the child table.
• Add a test with a known dataset (e.g., 25 parents with varying children) asserting page 2 of size 10 returns exactly 10 distinct parents and the total equals the true parent count.

Assumes SQLAlchemy 2.0 on PostgreSQL as stated; the loader behavior described is stable across the 2.0 series, but confirm the emitted SQL on your exact version before shipping.