PostgreSQL pagination along with row number indexing

Question

Okay, so I am working on query porting of an oracle DB to postgres. My query needs to give me numbered records, along with pagination.

Consider the following oracle code:

select * from (
    select RS.*, ROWNUM as RN from ( 
        select * from STUDENTS order by GRADES
    ) RS where ROWNUM <= (#{startIndex} + #{pageSize}) 
) where RN > #{startIndex}

Notice that there are 2 uses of ROWNUM here:

1. To provide a row number to each row in query result.
2. For pagination.

I need to port such a query to postgres.

I know how to paginate using LIMIT and OFFSET commands for pagination, but I am not able to provide a global row number (each row in query result gets a unique row number).

On the other hand, I was able to find ROW_NUMBER() command which can provide me with the global row numbers, but it is not reccommended for pagination purposes, since the number of tuples in my DB are very large.

How do I write a similar code in postgres?

Answer 1

The solution looks much simpler in PostgreSQL:

SELECT *,
       row_number() OVER (ORDER BY grades, id) AS rn
FROM students
ORDER BY grades, id
OFFSET $1 LIMIT $2;

Here, id stands for the primary key and is used to disambiguate between equal grades.

That query is efficient if there is an index on grades and the offset is not too high:

EXPLAIN (ANALYZE)
SELECT *,
       row_number() OVER (ORDER BY grades, id) AS rn
FROM students
ORDER BY grades, id
OFFSET 10 LIMIT 20;

                             QUERY PLAN                                                                   
-------------------------------------------------------------------
 Limit  (cost=1.01..2.49 rows=20 width=20)
        (actual time=0.204..0.365 rows=20 loops=1)
   ->  WindowAgg  (cost=0.28..74.25 rows=1000 width=20)
                  (actual time=0.109..0.334 rows=30 loops=1)
         ->  Index Scan using students_grades_idx on students
                        (cost=0.28..59.25 rows=1000 width=12)
                        (actual time=0.085..0.204 rows=30 loops=1)
 Planning time: 0.515 ms
 Execution time: 0.627 ms
(5 rows)

Observe the actual values in the plan.

Pagination with OFFSET is always inefficient with large offsets; consider keyset pagination.