Fetch 10 paginated ranked posts of each category

Question

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.

Erwin Brandstetter · Accepted Answer · 2019-05-09T02:22:42.113

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:

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

Fetch 10 paginated ranked posts of each category

1 Answers1

Alternative for few groups