Offset-based pagination

The examples below are for Postgres, but you can adjust them for your RDBMS.

First, let’s dive into theory and consider LIMIT and OFFSET separately.

LIMIT

Restricts the number of rows returned by a query. For instance, this one returns the first 10 rows:

select * from users limit 10;

And these effectively disable LIMIT:

select * from users limit all;
select * from users limit null;

OFFSET

Skips a specified number of rows before returning results. For instance, this one skips the first 20 rows:

select * from users offset 20;

Offset equals zero effectively disabling it:

select * from users offset 0;

Offset-Based Pagination

OFFSET and LIMIT together are commonly used together for pagination:

select ... from ...
order by ...
offset (page_number - 1) * page_size
limit page_size;

Consider this example. Fetching the second page of 10 users:

select * from products
order by id
offset 10 limit 10;

Key points and considerations:

• The rows skipped by an OFFSET clause still have to be computed inside the server; therefore, a large OFFSET might be inefficient.
• LIMIT without OFFSET returns the first rows.
• OFFSET without LIMIT returns all rows after skipping.
• Order of execution: FROM, WHERE, GROUP BY, HAVING, SELECT, OFFSET, LIMIT.
• Use ORDER BY with OFFSET for predictable results, as row order isn’t guaranteed without it.

Have a look at the justification that DB has to process all rows even with offset specified. Execute this query:

explain analyse select *
from users
offset 90000 limit 10;

The output will look like this:

Limit  (cost=1561.90..1562.08 rows=10 width=27) (actual time=10.275..10.277 rows=10 loops=1)
->  Seq Scan on users  (cost=0.00..1909.01 rows=110001 width=27) (actual time=0.425..6.666 rows=90010 loops=1)
Planning Time: 1.070 ms
Execution Time: 10.302 ms

Now, you can see it has read 90010 rows for returning only 10 rows! Execution time also takes a significant amount of 10.302ms.

However, offset can still be very useful in large number of cases.