Fetch 10 paginated ranked posts of each category Posts table as p:

| id | cid | score | ...
--------------------
|  1 |  1  |   3   | ...


Categories Table as c:

| id |...
--------------------
|  1 |...


What i need? Reterive paginated ranked posts in each category.

What i did do:

SELECT
    DENSE_RANK() OVER (
    PARTITION BY c.id
    ORDER BY p."score" DESC NULLS LAST) AS ranking,
    p.id,
    count(*) OVER() AS full_count
FROM p                              
INNER JOIN c ON c.id=p.cid
offset 20 limit 10


This query will return whole records and affect offset/limit on it. But i need to affect offset/limit on each category, separated.
Like Akina suggested, add row_number() and filter on the next query level:

SELECT ranking, id, full_count
FROM  (
   SELECT dense_rank() OVER w  AS ranking
        , p.id
        , count(*)     OVER () AS full_count
        , row_number() OVER w  AS rn
   FROM   p                              
   -- JOIN   c ON c.id = p.cid
   WINDOW w AS (PARTITION BY p.cid ORDER BY p.score DESC NULLS LAST)
   ) sub
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).

That 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 pid), a LATERAL join with index support is typically faster:

SELECT p.*
FROM   c
CROSS  JOIN LATERAL (
   SELECT dense_rank() OVER (ORDER BY p.score DESC NULLS LAST) AS ranking
        , p.id
        , count(*) OVER () AS full_count_per_window
   FROM   p                              
   WHERE  p.cid = c.id
   ORDER  BY p.score DESC NULLS LAST
   OFFSET 20
   LIMIT  10
   ) p
-- 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:


PostgreSQL sort by datetime asc, null first?


About that LATERAL join:


Optimize GROUP BY query to retrieve latest record per user
What is the difference between LATERAL and a subquery in PostgreSQL?


Generally, OFFSET is a poor tools for pagination. See:


What is the recommended way to join junction tables for efficient ordering/pagination?
Efficient pagination for big tables