Like Akina suggested, add
row_number() and filter on the next query level:
SELECT ranking, id, full_count
SELECT dense_rank() OVER w AS ranking
, count(*) OVER () AS full_count
, row_number() OVER w AS rn
-- JOIN c ON c.id = p.cid
WINDOW w AS (PARTITION BY p.cid ORDER BY p.score DESC NULLS LAST)
WHERE rn BETWEEN 21 AND 30;
This return row 21 to 30 per window (per
pid) - no rows if there are less than 21.
A subquery is typically faster than a CTE in Postgres (at least up to version 11). Only use CTEs where needed.
Also, if referential integrity is enforced with a foreign key constraint, the join to
c adds nothing but cost. Drop it.
And since we are reusing the same window definition twice, a
WINDOW clause avoids spelling it out repeatedly (no effect on performance, though).
full_count looks odd. If you need the full count per window, make that
count(*) OVER w as well.
Alternative for few groups
For a small number of groups (few distinct
LATERAL join with index support is typically faster:
CROSS JOIN LATERAL (
SELECT dense_rank() OVER (ORDER BY p.score DESC NULLS LAST) AS ranking
, count(*) OVER () AS full_count_per_window
WHERE p.cid = c.id
ORDER BY p.score DESC NULLS LAST
-- WHERE c.* ... -- some filter on c?
This time with a full count per window.
"Index support" would ideally be:
CREATE INDEX ON p (pid, score DESC NULLS LAST);
score DESC NULLS LAST is the perfect inversion of
score ASC, so a simpler index on
(pid, score) is also applicable using index scan backwards. See:
OFFSET is a poor tools for pagination. See: