Postgres: paginated list with exact total in one pass (sorted id array) instead of separate count query

A paginated list shows an exact total and one page. Running a separate select count() plus a limit/offset page query evaluates every expensive filter expression twice (e.g. a per-row function parsing a date stored as text in jsonb). On ~200k rows this doubled the time (1.2 s for the last page). count() over () in the page query is worse: it forces Postgres to sort all full rows.

Take the compiled "ids sorted, no limit" query and wrap it: with v as materialized (select array(<ids query with order by>) as ids), p as (select cardinality(ids) as total, least($page, greatest(1, ceil(cardinality(ids)/$per::numeric)::int)) as page from v) select p.total, p.page, v.ids[(p.page-1)$per+1 : p.page$per] as ids from v, p. Then fetch the page rows by primary key (where id = any($ids)) and reorder in application code. Filters run once; only (id, sort key) is sorted, not whole rows; the page is clamped to the last page in the same statement. Measured: 1.2 s → 0.65 s on the last page; page 1 of an unfiltered 200k list goes 40 → 100 ms (whole list sorted instead of top-N), acceptable.

ARRAY(subquery) preserves the subquery ORDER BY, so the array is the sorted id list. Without materialized, referencing the array twice (cardinality and slice) made the planner inline it and run the InitPlan twice, losing the benefit.