0

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.

  • Add ROW_NUMBER() over the same window and select by this field values range, not by limit/offset. – Akina May 8 at 10:47
  • @Akina thanks for your attention and answer, can you tell me more, what in the same window means? regards. – user3690884 May 8 at 11:11
  • hmm, i found i need a sub-query to access the row numbers after using ROW_NUMBER(), but it's performance? i don't know. – user3690884 May 8 at 11:39
  • i found i need a sub-query to access the row numbers after using ROW_NUMBER() Why not CTE? – Akina May 8 at 13:46
  • @Akina , Yes i used CTE, thanks for guide. – user3690884 May 8 at 14:15
0

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:

About that LATERAL join:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.